Checklist for Analyzing Slow-Running Queries
Queries or updates that take longer than expected 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.
There are a number of common reasons for slow-running queries and updates:
Slow network communication.
Inadequate memory in the server computer, or not enough memory available for SQL Server.
Lack of useful statistics
Lack of useful indexes.
Lack of useful indexed views.
Lack of useful data striping.
Lack of useful partitioning.
When a query or update takes longer than expected, ask yourself the following questions, which address the reasons for slow-running queries that are listed in the previous section:
Tip
To save time, consult this checklist before you contact your technical support provider.
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. For more information, see Monitoring Resource Usage (System Monitor).
If the performance issue is related to queries, which query or set of queries is involved?
Use SQL Server Profiler to help identify the slow query or queries. For more information, see Using SQL Server Profiler. Use the sys.dm_exec_query_stats and sys.dm_exec_requests dynamic management views to find similar queries that collectively consume a large number of resources. For more information, see Finding and Tuning Similar Queries by Using Query and Query Plan Hashes.
How do I analyze the performance of a slow-running query?
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.
For information about using Transact-SQL SET options to display text and XML execution plans, see Displaying Execution Plans by Using the Showplan SET Options (Transact-SQL).
For information about using SQL Server Management Studio to display graphical execution plans, see Displaying Graphical Execution Plans (SQL Server Management Studio).
For information about using SQL Server Profiler to display text and XML execution plans, see Displaying Execution Plans by Using SQL Server Profiler Event Classes.
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. For more information, see Analyzing a Query.
Was the query optimized with useful statistics?
The query optimizer uses statistics to create query plans that improve query performance. For most queries, the query optimizer already generates the necessary statistics for a high-quality query plan; in a few cases, you need to create additional statistics or modify the query design for best results.
For more information, see Using Statistics to Improve Query Performance. This topic gives guidelines to improve the effectiveness of statistics for query performance. The guidelines include the following:
Using the Database-Wide Statistics Options. For example, you should verify that the automatic create statistics, AUTO_CREATE_STATISTICS, and automatic update statistics, AUTO_UPDATE_STATISTICS, database-wide options are on. If they are off, query plans can be suboptimal and query performance can degrade.
Determining When to Create Statistics. In a few cases, you can improve query plans by creating additional statistics with the CREATE STATISTICS (Transact-SQL) statement. These additional statistics can capture statistical correlations that the query optimizer does not account for when it creates statistics for indexes or single columns.
Determining When to Update Statistics. In some cases you can improve the query plan and therefore improve query performance by updating statistics more frequently than when AUTO_UPDATE_STATISTICS is on. You can update statistics with the UPDATE STATISTICS statement or the stored procedure sp_updatestats.
Designing Queries That Use Statistics Effectively. Certain query implementations, such as local variables and complex expressions in the query predicate, can lead to suboptimal query plans. Following query design guidelines for using statistics effectively can help to avoid this.
Are suitable indexes available? Would adding one or more indexes improve query performance? For more information, see General Index Design Guidelines, Finding Missing Indexes, and Database Engine Tuning Advisor Overview. Database Engine Tuning Advisor can also recommend the creation of necessary statistics.
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. For more information, see Using Files and Filegroups and RAID.
Is the query optimizer provided with the best opportunity to optimize a complex query? For more information, see Query Tuning Recommendations.
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. For more information, see Understanding Partitioning and Tuning the Physical Database Design.