Free Ebook - Download Free Ebook pdf

Microsoft SQL Server 2000 Index Defragmentation Best Practices.pdf ebook This white paper provides information that you can use to determine whether you should defragment indexes to benefit the workload performance in your production environment. In addition, this paper compares DBCC DBREINDEX and DBCC INDEXDEFRAG, the statements provided by Microsoft® SQL Server™ 2000 to perform index defragmentation. Included in this comparison are the results from testing these statements against different databases and hardware environments. For information about these test environments, see "Small-Scale Environment vs. Large-Scale Environment" and Appendix A later in this paper. This white paper describes important considerations and the general process for determining when you should defragment indexes. The following is a summary of the key points presented in this paper: • Before you defragment indexes, ensure that system resources issues, such as physical disk fragmentation or inappropriate schemas, are not adversely affecting workload performance. • DBCC SHOWCONTIG allows you to determine the amount of index fragmentation. When you run this statement, pay particular attention to the values for Logical Fragmentation and Page Density. • Workload type is an important consideration when determining whether you should defragment indexes. Not all workload types benefit from defragmenting. Read-intensive workload types that do significant disk I/O benefit the most. The test results showed that the Decision Support System (DSS) workload type benefited much more than the OLTP (Online Transaction Processing) workload type. • Fragmentation affects disk performance and the effectiveness of the SQL Server read-ahead manager. There are some key indicators available through the Windows Performance Monitor that show this. • The decision of whether to use DBCC DBREINDEX or DBCC INDEXDEFRAG is based on your availability needs and the hardware environment. • Updating statistics is a side effect of DBCC DBREINDEX, which is not the case with DBCC INDEXDEFRAG. You can increase the effectiveness of DBCC INDEXDEFRAG by running UPDATE STATISTICS afterwards.

Microsoft SQL Server 2000 Index Defragmentation Best Practices