How to identify and troubleshoot slow-running queries in SQL Server (4304031)

Probably the most common issue when maintaining SQL Servers are slow-running queries. It is not unusual that a DBA gets information that the application or user database is slow, or even that users are getting timeout messages when working with SQL Server or SQL Server applications. Generally, when such SQL Server performance related issues are encountered, the first step in troubleshooting such an issue is to quickly identify whether and what slow-running queries in SQL Server are the cause of such a problem. The next step is then to determine why these queries run slow and what is the root cause for such behavior.

There are several native ways of identifying the slow-running queries in SQL Server and for those who want to find more about how to do that, please refer to the Useful resources at the bottom of this article for the links to relevant articles

This article won’t go into detail about query blocking since this is essentially a different issue. It should be pointed out, though, that if it is determined that the query is actually blocked (most commonly using sp_who, sp_who2 or sp-whoisactive stored procedures), then it is the query that caused the block, itself, that would require troubleshooting.

ApexSQL Monitor

ApexSQL Monitor is an agentless SQL Server performance monitoring tool capable of` monitoring an extensive range of Windows OS, SQL Server and SQL Server database metrics for multiple SQL Servers simultaneously. It can monitor SQL Server performance on multiple local and remote Windows OS and SQL Server instances and assist in identifying SQL Server performance issues. It can monitor executed queries and collects enough information to ensure fast and easy detection of the slow-running queries in SQL Server or those who are using the most resources. In addition, its alerting system is capable of notifying the user when specific parameters go beyond the predefined or user defined threshold values, or if go beyond the calculated baseline thresholds

ApexSQL Monitor allows for different ways for identifying which query is running slow and why. In this article the focus will be on using the Query performance feature, designed specifically to display the slowest running queries. To make it easier for the user, performance degradation of problematic queries can be evaluated by ApexSQL Monitor not just by the execution time, needed by a query to accomplish the task, but also by the following categories that should allow user to much more precisely determine the potential cause of the query slowness:

• Slowest queries by average CPU usage. This can indicate to user what queries are utilized the highest amount of the CPU, which could be indication that CPU resources are used irrationally

• Slowest queries by average I/O activity. I/O is the very important parameter that have to be taken in calculation when some slowness in query execution is evident. I/O subsystems, which usually translates to communication with hard drive, are still the slowest part and even with the modern high speed SSD hard drives, this is still one of the most often cause of the performance issues

• Slowest queries by average execution time. This is a direct indicator that shows slow running queries in SQL Server. The average execution time is more reliable parameter than execution time of individual queries as high values are indicator of the pattern of slowness in execution of particular queries, rather than just an anomaly that occurred once or twice

Information and details about slowest running queries, is retrieved directly from the SQL Server dynamic management views (DMV). As the name states, these are dynamic, which mean that information collected by DMVs get flushed with each SQL Server restart. In a normal working environment, where SQL Server is up and running 24/7 this works very well, but for rare cases where the SQL Server reboots frequently the data collected via DMVs might not be formed using representative and reliable samples, so some these have to be taken cautiously for the purpose of troubleshooting

ApexSQL Monitor allows for evaluating queries that are running slow in a single mouse click, but displayed information, although very complete and detailed, is just the first part of the troubleshooting process

To find slow-running queries in SQL Server, the following has to be done:

1. Select the SQL Server instance that should be inspected in the left pane of the ApexSQL Monitor in the Home page, or simply go to Dashboard page

   

2. Select the Query performance link in the main page

Usually, there is no need to display more than the 20 slowest queries, so this was set as the default value. More may lower that to 10 or even 5 queries for the SQL Servers that are not experiencing serious issues, but there is no actual limit imposed on how many slow-running queries can be retrieved and displayed. After the number of queries is changed, the Refresh button must be pressed in order to retrieve new set of slow-running queries from SQL Server according to the defined number of queries

You can also filter queries by affected database simply by selecting desired database from drop-down list.

Another important feature that should be pointed out is that ApexSQL Monitor will not retrieve just the 20 slowest queries (if 20 was set for example) and then sort them according to their values for each chart category. ApexSQL Monitor will actually retrieve the 20 slowest-running queries in SQL Server for each of the three categories separately, which basically means that the number of displayed queries will be 60. The chart itself can display no more than the 10 slowest-running queries, while the rest of the queries will be displayed in the grid below the chart. The grid can be easily sorted by desired category by simply clicking at the adequate column title.

Having a query that has a high execution time, doesn’t mean that this is something bad by itself and doesn’t necessarily mean that this is the issue. It might be that the query is just processing a huge set of data regularly, so it needs time to complete that task. With that said, to improve the potential problem identification of the slow-running queries in SQL Server, ApexSQL Monitor can display the following data about the executed queries that are of primary interest for issue identification and troubleshooting purposes:

• Execution count
• Average execution time
• Average CPU time
• Average I/O per sec
• Average physical reads
• Average logical reads
• Average logical writes

In addition to above listed data, ApexSQL Monitor can display the date and time when the query is executed last time, total execution time per execution as well as the query plan for that query

Having reliable and complete data that ApexSQL monitor provides on the performance of queries is very important, but this is just the first step in identifying and resolving any potential performance issues. What is equally important is the proper interpretation of that data provided by ApexSQL Monitor.

High execution time, single execution

So, let’s take a look at a few typical scenarios where a proper data interpretation is crucial. In the following example there is a specific query has a high average execution time, but when looking closer at the details of that query, it can be seen that its execution count is 1 (it is executed just once).

Having that in mind, it is very unlikely that this particular query will affect SQL Server performance, simply because it is executed just once, and even when the execution time is high, such and ad-hoc query should not affect SQL Server

High average execution time, high average CPU time and average I/O per second

On the other hand, if that particular query, besides having a high average execution time, also has a high value for average CPU usage and/or average I/O per second, it is clear that the query has affected the whole system globally and that, as such, it has potential to seriously affect SQL Server performance if executed more than once or during high peak usage of SQL Server.

Having such high values for multiple parameters, even executed only one time, is a clear indication that the query requires a closer look and potential troubleshooting

High execution time, multiple executions

Another common example is that query that has a high average execution time, but also a high number of executions.

This is a typical scenario that involves a query type where immediate troubleshooting should be performed. Often the first step is coordinating with the application developers to determine if the frequency of execution, if not optimization itself, can be reduced

Check the “Last execution time” to make sure the query is still relevant and not simply a one-off event that is unlikely to occur again

Now, once the query that is the potential cause of the performance issue in SQL Server is identified, the database administrator can continue to troubleshooting. The first and obvious troubleshooting step is checking the T-SQL script of that query. The full T-SQL query script is provided by ApexSQL Monitor, allow for a quick overview for critical and obvious problems

Query execution plan

At the other hand, for more complex queries, ApexSQL Monitor is equipped with the ability to display the actual query execution plan for the query that requires troubleshooting. At the right side in the query performance grid of each query, there is the Query plan link. By clicking on the link ApexSQL Monitor user will be able to see the graphical presentation of the actual execution plan for the selected query

In our example we have run the query against the ApexSQLMonitor database, and it is visible that this particular query has quite a high average execution time. So, by clicking on the Query plan link, the actual execution plan for that query will be displayed

Useful resources: