tailieunhanh - Microsoft SQL Server 2008 R2 Unleashed- P124

Microsoft SQL Server 2008 R2 Unleashed- P124:SQL Server 2005 provided a number of significant new features and enhancements over what was available in SQL Server 2000. This is not too surprising considering there was a five-year gap between these major SQL Server 2008 is not as much of a quantum leap forward from SQL Server 2005 | 1174 CHAPTER 34 Data Structures Indexes and Performance Listing shows examples of running on the sales_big table using both LIMITED and DETAILED scan modes. LISTING db index physical stats Examples use bigpubs20O8 go select str index_id 3 0 as indid left index_type_desc 20 as index_type_desc index_depth as idx_depth index_level as idx_level str avg_fragmentation_in_percent 5 2 as avg_frgmnt_pct str page_count 10 0 as pg_cnt FROM db_id object_id sales_big null 0 LIMITED select str index_id 3 0 as indid left index_type_desc 20 as index_type_desc index_depth as idx_depth index_level as idx_level str avg_fragmentation_in_percent 5 2 as avg_frgmnt_pct str page_count 10 0 as pg_cnt FROM db_id object_id sales_big null 0 DETAILED go indid index_type_desc idx_depth idx_ level avg_frgmnt_pct pg_cnt 1 CLUSTERED INDEX 0 14519 2 NONCLUSTERED INDEX 3 0 4571 indid index_type_desc idx_depth idx_ level avg_frgmnt_pct pg_cnt 1 clustered inoex 0 14519 1 CLUSTERED INDEX 3 1 38 1 CLUSTERED INDEX 3 2 1 2 NONCLUSTERED INDEX 3 0 4571 2 NONCLUSTERED INDEX 3 1 8 2 NONCLUSTERED INDEX 3 2 1 Again you can see from the output in Listing that the logical fragmentation avg_frgmnt_pct is for the leaf level of the clustered index idx_level 0 . This indicates that nearly two thirds of the data pages are out of sequence in relation to order Download from SQL Server Index Maintenance 1175 ing of the clustered key values. If you want to improve the performance of table scans or clustered index scans for the sales_big table you need to decide whether to rebuild the index or simply defragment the index. The degree of fragmentation helps you decide which defragmentation method to use. A rough guideline to use to help decide is to examine the avg_fragmentation_in_percent value returned by the .