The SQL Diagnostic Manager Query Monitor is a powerful feature that has the capability of greatly reducing the time that it takes to identify problem queries on a monitored server. However, with that power comes some risk of performance degradation. In this post I’d like to cover how to configure for the best balance of data versus monitoring overhead.
Query Monitor On or Off
The most basic question in tuning is: should I even be running the Query Monitor? The Query Monitor is able to gather tremendous amounts of information about poorly performing queries on a monitored server, but even at its most highly tuned, this is unnecessary overhead if the data is not going to be used. Disable the Query Monitor on monitored servers on which you do not anticipate any query tuning, or use the Alert Actions tab to configure the Query Monitor to be enabled as the result of an alert rather than running it continuously. Even if there is no overhead at all on the monitored server, the collected data uses space in the SQL DM repository.
XEvent vs Trace Data Collection
SQL Diagnostic Manager gives the option of collecting query performance data in two ways for SQL 2008 and above: Extended Events (also called XEvents) and SQL Trace. For most environments the Extended Event monitoring will probably present a lower monitoring footprint, especially if you are making use of filters. However, the implementation of SQL Trace that is used by SQL Diagnostic Manager has been diligently refined over the course of more than 10 years, so for SQL 2000 and SQL 2005 installations you should feel comfortable using SQL Trace, and in some isolated cases where recommended by IDERA Support you may want to use this option for more recent versions of SQL Server as well.
Actual vs Estimated Query Plans
Collection of query plans can greatly assist in troubleshooting problematic queries, but neither the collection nor the storage of the data is inexpensive. If you do not intend to use query plans in troubleshooting, or if you do not know how to use them, you may want to disable query plan collection entirely. For standard query monitoring of a production system where you expect to do some analysis of query plans, you should use the Estimated Query Plan option. The Actual Query Plan option is a very performance intensive setting that should not be used on most production systems, nor on a continuing basis on any system. This option is intended for use during isolated, short term monitoring sessions when the Estimated Query Plan data is not sufficient.
Important note: In version 9.0 of SQL Diagnostic Manager the Actual Query Plan option was enabled by default. This was an error, and in 10.0 and beyond the Estimated Query Plan option is used. If you are now using or have upgraded from 9.0 you should verify whether this setting matches your expectations and adjust it accordingly.
Types of Queries To Capture
SQL Diagnostic Manager gives the option to capture SQL Batches, SQL Statements, and Stored Procedures. For typical monitoring, if you are not troubleshooting a particular problem, I would recommend collecting SQL Batches and possibly Stored Procedures and disabling SQL Statements. This greatly reduces the number of statements captured, which reduces monitoring overhead as well as space used to store the collected data.
Poorly Performing Thresholds and Filters
As a general rule, the more and better filters you apply to the Query Monitor, the less data will be captured, resulting in a lowered monitoring footprint and space utilization in the SQL Diagnostic Manager repository. This must of course be balanced against your needs in terms of identifying problematic queries. I would recommend never running with a duration of 0 for more than a few minutes, and I recommend clicking Advanced… and setting filters to exclude applications, databases, and query text that you know you will not be seeking to tune.
Note that in general, changes made to the Query Monitor settings may take 1 or 2 refresh cycles to take effect. You can accelerate this by right clicking on the monitored server in the server tree and clicking “Refresh Alerts.”
The Query Monitor can be used as either a precision troubleshooting tool or as a very wide ranging net to capture a broad sample of a server’s workload. Both usages are valid, but keep in mind that the more that you collect, the more that the overhead of doing that collection may affect either the performance of your SQL Server or of SQL Diagnostic Manager itself.
Have questions about how to tune the Query Monitor for your specific use case? Ask me about it in the SQL Diagnostic Manager forums.