Troubleshoot Slow Running Queries

Below are some common reasons for slow-running queries and what your approach should be for identifying and fixing them. The best and the quickest way to address any slow running query should be by first preparing a Troubleshooting Checklist or Troubleshooting Guide and then by using it during slow running query situations. This way it can help any developer or DBA to easily and quickly narrow down the issue. (This troubleshooting guide or checklist should be posted on a central location from where all the DBA's / Developers of your team can access and update when required)


Please note: If you experience something new to check which is not listed in the below list than you may update your TSG or TSC appropriately


Queries or updates that take more than the expected time to execute can be caused by a variety of reasons. Slow-running queries can be caused by performance problems related to your network or the computer where SQL Server is running. Slow-running queries can also be caused by problems with your physical database design.


Some of the most common reasons for slow-running queries and updates are:


1) Slow network communication.
2) Inadequate memory in the server computer, or No enough memory available for SQL Server.
3) Lack of useful statistics on indexed columns.
4) Out-of-date statistics on indexed columns.
5) Lack of useful indexes.
6) Lack of useful indexed views.
7) Lack of useful data striping.
8) Lack of useful partitioning.


Troubleshooting Checklist (Trying to cover all the common scenarios)
1) Is the performance problem related to a component other than queries? For example, is the problem slow network performance? Are there any other components that might be causing or contributing to performance degradation?


The Windows System Monitor can be used to monitor the performance of SQL Server and non-SQL Server related components.




2) If the performance issue is related to queries, identify which query or set of queries is involved?


First use SQL Server Profiler to help identify the slow query or queries.


After you have identified the slow-running query or queries, you can further analyze query performance by producing a Showplan, which can be a text, XML, or graphical representation of the query execution plan that the query optimizer generates. You can produce a Showplan using Transact-SQL SET options, SQL Server Management Studio, or SQL Server Profiler.




The information gathered by these tools allows you to determine how a query is executed by the SQL Server query optimizer and which indexes are being used. Using this information, you can determine if performance improvements can be made by rewriting the query, changing the indexes on the tables, or perhaps modifying the database design.


3) Was the query optimized with useful statistics?


Statistics on the distribution of values in a column are automatically created on indexed columns by SQL Server. They can also be created on nonindexed columns either manually, using SQL Server Management Studio or the CREATE STATISTICS statement, or automatically, if the AUTO_CREATE_STATISTICS database option is set to TRUE. These statistics can be used by the query processor to determine the optimal strategy for evaluating a query. Maintaining additional statistics on nonindexed columns involved in join operations can improve query performance.
Monitor the query using SQL Server Profiler or the graphical execution plan in SQL Server Management Studio to determine if the query has enough statistics.


4) Are the query statistics up to date? Are the statistics automatically updated?


SQL Server automatically creates and updates query statistics on indexed columns (as long as automatic query statistic updating is not disabled). Additionally, statistics can be updated on nonindexed columns either manually, using SQL Server Management Studio or the UPDATE STATISTICS statement, or automatically, if the AUTO_UPDATE_STATISTICS database option is set to TRUE. Up-to-date statistics are not dependent upon date or time data. If no UPDATE operations have taken place, the query statistics are still up-to-date.




5) Are suitable indexes available? Would adding one or more indexes improve query performance? For more information, see General Index Design Guidelines in Database Engine Tuning Advisor can also recommend the creation of necessary statistics.


6) Are there any data or index hot spots? Consider using disk striping. Disk striping can be implemented by using RAID (redundant array of independent disks) level 0, where data is distributed across multiple disk drives


7) Is the query optimizer provided with the best opportunity to optimize a complex query?


8) If you have a large volume of data, do you need to partition it? Data manageability is the main benefit of partitioning, but if your tables and indexes on them are partitioned similarly, partitioning can also improve query performance