RSMUS.comThis is one of those warnings you or your clients may notice in their event viewer from time to time (or all the time). It’s really quite helpful in identifying what queries are taking a little longer than you might want and Microsoft has set a default of 10 seconds to this threshold. You error may read something like this: “Query execution time of 12.6 seconds exceeded the threshold of 10 seconds.” Followed by the query that was executed.
If you take the time to run that query you may find it runs a little faster or a little slower but if you start noticing those queries showing up more frequently it may be worth passing them off to your DBA and having them look at tuning options such as indexes.
If you find you have a ton of queries taking over 10 seconds and want to find the biggest offenders you can always use one of the SQL DMV’s such as Sys.dm_exec_query_stats ; Sys.dm_exec_query_plan and Sys.dm_exec_sql_text . These will provide some additional information as well as give you some insight into some potential issues. Another trick is to change that default within CRM so that you can narrow it down to the worst offenders. To do this you need to update the ColumnName ‘LongQueryThresholdInSeconds’ IntColumn value in the MSCRM_CONFIG ServerSettingsProperties table. Change IntColumn to be a higher value, say 15 or 20 seconds. This will remove a bunch of those “just over 10 second” queries and narrow the event viewer down to the really long queries.
USE MSCRM_CONFIG
UPDATE ServerSettingsProperties SET IntColumn=20 WHERE ColumnName=’LongQueryThresholdInSeconds’
Note though that this does nothing for performance, it only reduces the number of events that will show up based on the threshold you establish and Micrososft is pretty happy with the 10 second threshold so I don’t know that I’d leave it set over the 10 second threshold permanently but it can help you narrow it down if you’ve got some really long running queries you want to isolate and identify.
The RSM team offers full-time customer resouces desk and platform development services. Our professionals are happy to assist with any questions or problems. Please contact us at 855.437.7202 or via email at crm@mcgladrey.com.
By: Josh Thompson – Minnesota Microsoft Dynamics CRM Support
