Đang chuẩn bị nút TẢI XUỐNG, xin hãy chờ
Tải xuống
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 releases.Microsoft 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 34.6 shows examples of running sys.dm_db_index_physical_stats on the sales_big table using both LIMITED and DETAILED scan modes. LISTING 34.6 sys.dm 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 sys.dm_db_index_physical_stats 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 sys.dm_db_index_physical_stats 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 63.42 14519 2 NONCLUSTERED INDEX 3 0 14.90 4571 indid index_type_desc idx_depth idx_ level avg_frgmnt_pct pg_cnt 1 clustered inoex 0 63.42 14519 1 CLUSTERED INDEX 3 1 92.11 38 1 CLUSTERED INDEX 3 2 0.00 1 2 NONCLUSTERED INDEX 3 0 14.90 4571 2 NONCLUSTERED INDEX 3 1 87.50 8 2 NONCLUSTERED INDEX 3 2 0.00 1 Again you can see from the output in Listing 34.6 that the logical fragmentation avg_frgmnt_pct is 63.42 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 www.wowebook.com 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 sys.dm_db_index_physical_stats .
