SQL Server 2000 Performance Guidelines - Monitoring

From Guidance Share

Jump to: navigation, search

- J.D. Meier, Srinath Vasireddy, Ashish Babbar, Sharon Bjeletich and Alex Mackman


Keep Statistics Up to Date

SQL Server uses a cost-based optimizer that is sensitive to statistical information provided on tables and indexes, such as the number of rows in a table and the average key length. Without correct and up-to-date statistical information, SQL Server may end up with a less optimal execution plan for a particular query.

Statistics that are maintained on each table in SQL Server to aid the optimizer in cost-based decision making include the number of rows, the number of pages used by the table, and the number of modifications made to the keys of the table since the last statistics update. In addition to maintaining statistics on indexed columns, it is possible to maintain statistics on columns that are not indexed.

Out-of-date or missing statistics are indicated by warnings when the execution plan of a query is graphically displayed in SQL Query Analyzer. The table name is displayed in red text. Monitor the Missing Column Statistics event class by using SQL Profiler so that you know when statistics are missing. To turn on the Update statistics option for a database, right-click the database in SQL Server Enterprise Manager, and then click Properties. Click the Option tab, and then select the Auto update statistics check box. In addition, you can run the sp_updatestats system stored procedure from SQL Query Analyzer in the database to update the statistics for that database.

Use the UPDATE STATISTICS command or the sp_updatestats system stored procedure to manually update statistics after large changes in data, or on a daily basis if there is a daily window available.

More Information

Use SQL Profiler to Tune Long-Running Queries

Periodically use the SQL Profiler as described earlier to continuously tune long-running queries. As statistics and usage change, the queries that appear as the longest queries will change.

Use SQL Profiler to Monitor Table and Index Scans

Periodically use the SQL Profiler to continuously search for table and index scans. As statistics and usage change, the table and index scans that appear will change.

Use Performance Monitor to Monitor High Resource Usage

Periodically use the Performance Monitor to identify areas of high resource usage, and then investigate.

Set Up an Operations and Development Feedback Loop

Implement regular communications between production and operations personnel and the development group. Ensure all parties are exchanging information related to performance and scalability or development changes that might affect performance and scalability.

More Information

Personal tools