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.
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.
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.
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.
Advanced Query Concepts | Query Processor Architecture |
Monitoring with SQL Server Enterprise Manager | SET |
Parallel Query Processing |