Slow performance in the service monitor can be caused by database tables not being indexed when auditing to logging database.
Error Handling for SQL Service error
Issue | Slow response in Service Monitor when logging to the Database |
Possible Cause | This could be caused by the tables being written to, not having indexes |
Resolution / Workaround | Check if the KeyValuePairs and QuotationEvents tables in the Database that the audit requests are being written to are indexed and set indexes if not. |
NOTE: Run the following script to check if the Database is indexed-
SELECT t.name AS TableName, i.name AS IndexName, i.type_desc AS IndexType
FROM sys.indexes i INNER JOIN sys.tables t ON i.object_id = t.object_id
WHERE t.name = 'TABLE_NAME'
AND i.type > 0
For example –
SELECT
t.name AS TableName,
i.name AS IndexName,
i.type_desc AS IndexType
FROM
sys.indexes i
INNER JOIN sys.tables t ON i.object_id = t.object_id
WHERE
t.name = 'KeyValuePairs'
AND i.type > 0
Example of outputs
How to index the MQS Database to improve performance
Issue | Database might not be properly indexed |
Possible Cause | This could be caused by the tables being written to, not having indexes |
Resolution / Workaround | MQS will automatically create 3 (if using SqlAuditor) tables. __MigrationHistory KeyValuePairs QuotationEvents2
It is important that the columns QuotationEvent_Id and QuotationEvent_Id1 on KeyValuePairs have indices. They should be there by default, but if they have been removed or are missing then the customer should run:
CREATE INDEX IX_QuotationEvent_Id ON KeyValuePairs (QuotationEvent_Id) CREATE INDEX IX_QuotationEvent_Id1 ON KeyValuePairs (QuotationEvent_Id1)
|