We are seeing some ‘bad’ queries in our database causing high CPU utilization and causing SQL performance issues. We want to add an index on the activitylog table. In analyzing with SQL profiler we found the following query to be a problem
SELECT EntryType,EntryClass,BriefMessage,FullMessage, RelatedDocID,RelatedPartnerID,B2BUser, EntryTimestamp,RelatedInstanceID FROM ActivityLog WHERE RelatedDocID = @P1 AND EntryType = 0
Can we add an index to improve the performance or is there another recommendation?
All the TN tables are created via provided out of box DB scripts and normally we shouldn’t edit it…But yes you can add indexes if it is really degrading the performance taking with the help of your DBA in moot and make sure you are not disturbing any co-related tables etc…with cautiously.
One thing to note is how much TN data you are having in the current tables assuming this is a production TN DB we are talking about.
Do you guys regularly run TN archive/purge data periodically which is a good practice and can improve the TN query performance also?
Also you can contact SAG tech support for further advise, since you are trying to edit the internal tables to support your internal needs and see if they can provide a easy work around or fix (as long as you are on recent wM supported versions)
Adding indexes will definitely improve SQL search speed, depends on the volume of data you indexed. But you may need to consider the disk space consumption it require.
-Archive/purge data can be consider, and regular re-index may require for this option.
-Monitor disk space taken by SQL data file and Log File, regularly. insufficient Disk space will slowdown SQL querying tremendously
-Assuming you are using Microsoft SQL Server, make sure the LOG auto grow setting is set property to suit your need