Query Tuning

It may be tempting to address a performance problem solely by system-level server performance tuning; for example, memory size, type of file system, number and type of processors, and so forth. Experience has shown that most performance problems cannot be resolved this way. They must be addressed by analyzing the application, queries, and updates that the application is submitting to the database, and how these queries and updates interact with the database schema.

Unexpected long-lasting queries and updates can be caused by:

• Slow network communication.
  
  
• Inadequate memory in the server computer, or not enough memory available for Microsoft® SQL Server™ 2000.
  
  
• Lack of useful statistics.
  
  
• Out-of-date statistics.
  
  
• Lack of useful indexes.
  
  
• Lack of useful data striping.

When a query or update takes longer than expected, use the following checklist to improve performance.

Note  It is recommended that this checklist be consulted prior to contacting your technical support provider.

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? Windows NT Performance Monitor can be used to monitor the performance of SQL Server and non-SQL Server related components. For more information, see Monitoring with System Monitor.
   
   
2. If the performance issue is related to queries, which query or set of queries is involved? Use SQL Profiler to help identify the slow query or queries. For more information, see Monitoring with SQL Profiler.

   The performance of a database query can be determined by using the SET statement to enable the SHOWPLAN, STATISTICS IO, STATISTICS TIME, and STATISTICS PROFILE options.

   • SHOWPLAN describes the method chosen by the SQL Server query optimizer to retrieve data. For more information, see SET SHOWPLAN_ALL.
     
     
   • STATISTICS IO reports information about the number of scans, logical reads (pages accessed in cache), and physical reads (number of times the disk was accessed) for each table referenced in the statement. For more information, see SET STATISTICS IO.
     
     
   • STATISTICS TIME displays the amount of time (in milliseconds) required to parse, compile, and execute a query. For more information, see SET STATISTICS TIME.
     
     
   • STATISTICS PROFILE displays a result set after each executed query representing a profile of the execution of the query. For more information, see SET STATISTICS PROFILE.

   In SQL Query Analyzer, you can also turn on the graphical execution plan option to view a graphical representation of how SQL Server retrieves data.

   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.

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 Query Analyzer 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. For more information, see Statistical Information.

   Monitor the query using SQL Profiler or the graphical execution plan in SQL Query Analyzer to determine if the query has enough statistics. For more information, see Error and Warning Event Category.

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 Query Analyzer 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, then the query statistics are still up-to-date.

   If statistics are not set to update automatically, then set them to do so. For more information, see Statistical Information.

5. Are suitable indexes available? Would adding one or more indexes improve query performance? For more information, see Index Tuning Recommendations.
   
   
6. Are there any data or index hot spots? Consider using disk striping. For more information, see Data Placement Using Filegroups and RAID.
   
   
7. Is the query optimizer provided with the best opportunity to optimize a complex query? For more information, see Query Tuning Recommendations.

See Also

Advanced Query Concepts

Query Processor Architecture

Monitoring with SQL Server Enterprise Manager

SET

Parallel Query Processing