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.

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

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 Windows NT Performance Monitor.
  2. 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 Monitoring with SQL Server 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.

    In SQL Server 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 being 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 where possible. For more information, see Analyzing a Query.

  3. Was the query optimized with useful statistics?

    Statistics regarding the distribution of values in a column are created on indexed columns automatically by SQL Server. They can also be created on nonindexed columns either manually, using SQL Server 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 Server Profiler or the graphical execution plan in SQL Server 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 Server 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  

  


(c) 1988-98 Microsoft Corporation. All Rights Reserved.