Microsoft SQL Server 2000 Index Defragmentation Best Practices

Microsoft SQL Server 2000 Index Defragmentation Best Practices Author: Mike Ruthruff Microsoft Corporation February 2003 Summary: As Microsoft® SQL Server™ 2000 maintains indexes to … … reflect updates to their underlying tables, these indexes can become fragmented. Depending on workload characteristics, this fragmentation can adversely affect workload performance. This white paper provides information to help you determine whether you should defragment table indexes to benefit workload performance. To defragment indexes, …

Poorly Performing Queries When investigating any performance related problems, you must identify which queries in the workload are performing poorly. Some of the information discussed here is also used later in the paper to determine which indexes to focus on for defragmentation. You can use SQL Profiler to identify poorly performing queries. (For more information, see the topic “SQL Profiler” in SQL Server Books Online.) Running SQL Profiler introduces some overhead; however, monitoring only the following events should allow you to collect needed information with minimal impact on performance (this varies, but generally less than 10 percent impact to CPU utilization). SQL Profiler provides a trace template named SQLProfilerTSQL_Duration , which captures relevant events. You can use this to identify poorly performing queries quickly. You can also manually create a SQL Profiler trace that captures these events: • TSQL: SQLBatchCompleted • Stored Procedures: RPC:Completed The length of time you need to run SQL Profiler depends on the server workload. For the trace to be most useful, it should represent a typical workload or, at least, the part of the workload that exhibits poor performance. After the trace has been captured, examine the trace log focusing on the duration column. This is a measure in milliseconds of the duration of each batch or query. Identifying the Poorest Performing Queries Here are some suggestions to best determine the poorest performing queries in the trace: • Group the trace by query duration. Focus first on the “Top 10? worst performing queries. • If your application uses stored procedures extensively, consider using the SQLProfilerSP_Counts trace template to identify the most commonly called stored procedures. Focus on the worst performing stored procedures that are used most frequently. • Consider saving the collected data into SQL Server tables. This allows you to query the tables and perform more detailed analysis (for example, average duration, max duration, and so on) of the workload performance. Underlying Schema After you have identified the longest running/worst performing queries, you must ensure that the underlying schema is optimal for these. Ensure that appropriate indexes exist and are being used appropriately by the queries. Using SQL Query Analyzer to display and review query plans allows you to see what indexes are being used by queries in the workload. When the execution plan of a query is graphically …. Monitoring Fragmentation Levels Regularly monitoring fragmentation levels on indexes is good practice. For a sample script that demonstrates how to automate the capture and rebuild of heavily fragmented indexes, see the “DBCC SHOWCONTIG” topic in SQL Server Books Online. Consider using the DBCC SHOWCONTIG TABLERESULTS option and importing this information into tables at regular intervals. Doing this allows you to monitor the fragmentation levels over time. Also consider using the WITH FAST option when running DBCC SHOWCONTIG on a busy server. Using the WITH FAST option allows DBCC SHOWCONTIG to avoid scanning the leaf pages of an index. This can result in faster performance of DBCC SHOWCONTIG; however, because it does not scan the leaf pages of the index, it cannot report page density numbers. Table 1 shows the amount of time it took to run DBCC SHOWCONTIG against all the indexes in the small-scale and large-scale DSS environments. The ALL_INDEXES and TABLERESULTS options were used for each test. Table 1 DBCC SHOWCONTIG performance DBCC SHOWCONTIG options Total number of index pages (all indexes) Run time (minutes) Small-Scale Environment Not using the WITH FAST option 1,702,889 5.02 Using the WITH FAST option 1,702,889 0.90 Large-Scale Environment