SQL Server 2000 Performance Guidelines - Monitoring
From Guidance Share
- 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.
- For more information, see Knowledge Base article 195565, "INF: How SQL Server 7.0 and SQL Server 2000 Autostats Work," at http://support.microsoft.com/default.aspx?scid=kb;en-us;195565.
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.
- For more information about Performance Monitor or System Monitor and SQL Server, see "Monitoring Server Performance and Activity" at http://msdn.microsoft.com/library/en-us/adminsql/ad_mon_perf_00mr.asp.