Troubleshooting slow SQL queries to improve performance

Slow SQL queries can significantly impact the performance of a database and the applications that rely on it. Inefficient queries can cause the database to become overloaded, leading to decreased performance and increased response times for users.

This can result in a poor user experience, as users may have to wait a long time for their requests to be fulfilled. Slow queries also consume significant CPU and memory resources, which can impact the performance of other processes running on the same database server.

For these reasons, it’s essential to know how to write efficient SQL queries for your database to perform optimally. This article explores how to identify slow queries in SQL Server, determine their cause, and efficiently resolve them.

Why your SQL queries are slow

When trying to fix poor database performance, the first step is to find which queries take a long time to run. To track down slow-running queries in SQL Server, you can use the sys.dm_exec_query_stats view.

Two important columns returned by this view are last_elapsed_time and last_worker_time. The last_elapsed_time column shows how long it took to execute the most recent request in microseconds. The last_worker_time column indicates how long the task has spent doing work, excluding any time it was blocked. If the last_worker_time column is significantly lower than the last_elapsed_time column, this could indicate that another process is causing the query to wait before being able to run. This is known as a waiting query.

The query is inefficient if both the elapsed time and the worker time are high. Note that both waiting and running queries can contribute to database slowdowns. Identifying and addressing both types of queries is essential to optimize database performance.

Waiting queries

A waiting query can’t proceed until a resource becomes available. Resource waits describe queries that must wait for a lock to be released before they can execute. Queue waits occur when a query is waiting for sufficient resources to execute the query. Finally, external waits, as the name implies, define queries waiting for an external entity, such as a response from a linked server. If many queries are waiting for resources, this can lead to a backlog of requests, resulting in a slowdown.

The first step to address wait-based bottlenecks is to determine the cause of the query to wait. You can use the sys.dm_os_wait_stats view to identify the most common waits on the system. This view also stores data for historical queries, which can be reset by a database admin or on a database restart. Depending on your use case, you can copy the data in this view to track performance issues over time.

Increasing the amount of hardware available can sometimes resolve resource or queue waits. In other cases, you can use query hints such as MAXDOP to control the degree of parallelism for a query and reduce the number of waits. External waits are slightly more complex to resolve as the external task may be inefficient and need diagnosing.

Running queries

A running query is one in which the total runtime is high. A running query can be slow to return results to users. If it’s using too many resources, it can cause other processes to slow down.

You can use the query plan to understand what is causing running bottlenecks. To view the query plan in SQL Server, run the SET STATISTICS PROFILE ON statement before running the query. This provides the plan directly after the results of the query. Another option is to use the sys.dm_exec_query_plan view, which returns the plan for a cached query.

As you examine the query plan, look for operators that are more expensive than others, such as the type of joins, lack of index usage, and caching. You can also look for operators with multiple rows or high data volume passing through them, which may contribute to bottlenecks.

Resolving slow-running queries may involve modifying query logic, adding indexes to tables, or refreshing the table statistics.

Stored procedures

Troubleshooting stored procedures that are slow-running can be particularly difficult for several reasons. When a stored procedure is executed for the first time, the query optimizer creates an execution plan and stores it in the procedure cache. This cached plan will be used when the stored procedure executes in the future. To resolve this, you can run the EXEC sp_recompile '<PROCEDURE NAME>' command to refresh the query plan.

Furthermore, stored procedures can contain multiple queries and may also use variables, loops, and other programming constructs, making it more difficult to understand how they’re executed.

Consider the following example of a slow-running stored procedure that calculates some stats about orders for a given country code:

CREATE PROCEDURE GetOrdersByCountry @Country VARCHAR(50) 
AS 
BEGIN 
    SELECT COUNT(*) as orders, SUM(TotalAmount) as total_amount 
    FROM Orders  
    WHERE Country = @Country; 
END;

Execute this procedure by running the execute command and passing it a country code:

EXECUTE GetOrdersByCountry 'UK';

After executing the stored procedure, you can see how it performed using the sys.dm_exec_query_stats view. You can CROSS APPLY the sys.dm_exec_sql_text view as shown below to see the statement that was run and the last_worker_time and last_elapsed_time.

SELECT  
  qs.last_worker_time,  
  qs.last_elapsed_time,  
  SUBSTRING( 
    st.text, 
    (qs.statement_start_offset / 2) + 1,  
    ( 
      ( 
        CASE qs.statement_end_offset WHEN -1 THEN DATALENGTH(st.text) ELSE qs.statement_end_offset END - qs.statement_start_offset 
      ) / 2 
    ) + 1 
  ) AS statement_text 
FROM 
  sys.dm_exec_query_stats AS qs 
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st  
ORDER BY qs.last_execution_time;

This gives the following output for the query:

The last_worker_time and last_elapsed time are fairly similar, so this isn’t an example of a waiting query. Nonetheless, the overall time is high, as the query is inefficient.

To investigate the stored procedure, run SET STATISTICS PROFILE ON and then execute the stored procedure again. After the procedure results, you get a data table showing each section of the query plan and its cost.

Doing this before running your stored procedure outputs a table. The columns of note are LogicalOp, Argument, DefinedValues, and TotalSubtreeCost. The LogicalOp, Argument, and DefinedValues columns tell you what operation is being performed and on which entity. The TotalSubtreeCost tells you how much this operation costs.

For the stored procedure, this output identifies the following operation as being the most expensive:

Clustered Index Scan  OBJECT:([master].[dbo].[Orders].[PK__Orders__C3905BAF72AEC27B]), WHERE:([master].[dbo].[Orders].[Country]=[@Country])

This shows that the query is executing a Clustered Index Scan on the table when performing the WHERE part of the query. A clustered index scan is generally slower than a seek operation, as it retrieves all rows from a table or view by scanning the entire clustered index. On the other hand, a seek operation uses the index to directly locate the rows that match the specified criteria and is, therefore, quicker.

This shows that you need to add an index to the Orders table to speed up the stored procedure. You can add an index on the Country and TotalAmount columns with the following command:

CREATE INDEX IX_Orders_Country_Total ON Orders (Country, TotalAmount);

Now, run the stored procedure again and check the execution time using the sys.dm_exec_query_stats view.

You can see that you’ve almost halved the execution time of the stored procedure by simply adding the index.

If you take a look at the query plan, you should also see that the clustered index scan from before has now changed to an Index Seek as follows:

Index Seek      OBJECT:([master].[dbo].[Orders].[IX_Orders_Country_Total]), 
SEEK:([master].[dbo].[Orders].[Country]=[@Country]) ORDERED FORWARD 
[master].[dbo].[Orders].[TotalAmount]

This shows that the query is performing an index seek on the IX_Orders_Country_Total index you created instead of on the whole table primary key index as before.

Best practices to identify and troubleshoot slow SQL queries

This section highlights some best practices for identifying and troubleshooting slow SQL queries, building on the concepts introduced in this article.

Firstly, regularly monitor the performance of your queries using tools such as the sys.dm_exec_query_stats view to look for long-running queries.

Secondly, when troubleshooting slow queries, use the SET STATISTICS PROFILE ON statement to obtain the execution plan for the query. Examine the execution plan to understand how the query is executed and identify any inefficiencies.

Furthermore, keeping statistics updated ensures that the query optimizer has accurate information on data distribution. Use the right data types to ensure the data is stored in the most space-efficient manner, and use set-based queries over cursors as they’re often more efficient.

Finally, as shown in the stored procedure example above, use the appropriate indexing strategy for your workload to ensure that queries can use indexes effectively and avoid full table scans. When making changes to improve the performance of a query, be sure to test and validate the changes to ensure that they have the desired effect.

Conclusion

Several factors can affect a SQL query’s performance, including the structure, data distribution and table statistics, the indexes available, and the server configuration.

By following best practices and using tools such as query plans, execution statistics, and system views, you can identify and resolve performance bottlenecks in your SQL queries, resulting in faster and more efficient database operations.