I got several times the same questions around index and fragmentation on SQL2008. So I decided to add one more item on Internet to discuss about the reconstruction and reorganization of the index and how detect index fragmentation.
As fragmentation can have a negative impact on the efficiency of data access; one of the main tasks of a DBA is to maintain database indexes.
In the life cycle of a database, fragmentation is expected behavior and natural. If the database is frequently updated via INSERT, UPDATE, or DELETE statements we can expect it to become fragmented over the time.
Since SQL Server 2005 the sys.dm_db_index_physical_stats Dynamic Management Function returns the size and fragmentation information for the data and indexes of the specified table or view.
|NB: Although SQL Server 2008 still supports the SQL Server 2000 DBCC SHOWCONTING command, this feature will be removed on a future version of SQL Server. So, I invite you to remove it from your new development.|
1. There are 3 mode in the syntax of sys.dm_db_index_physical_stats:
|LIMITED (default):||This mode is the fastest and scans the smallest number of pages. It scans all pages for a heap, but only scans the parent-level pages, which means, the pages above the leaf-level, for an index.|
|SAMPLED||This mode returns statistics base on a one percent sample of the entire page in the index or heap. If the index or heap has fewer than 10 000 pages, DETAILD mode is used instead of SAMPLED.|
|DETAILED||Detailed mode scans all pages and returns all statistics. Be careful, from LIMITED to SAMPLED to DETAILED, the mode are progressively slower, because more work is performed in each. In my script I am using this one|
2. There are 2 kind of fragmentation on indexes.
|Logical fragmentation(indexes)||is the percentage of an index that consists of out-of-order pages in the leaf pages. An out-of-order page is the one for which the next page indicated in an IAM is different from the page pointed to by the next page pointer in the leaf page.|
|Extent fragmentation (heap)||is the percentage of a heap that consists of out-of-order extents in the leaf pages. An out-of-order extents is the one for which the extent that contains the current page for a heap is not, physically, the next extent after the extent that contains the previous page.|
In order to reduce fragmentation we will have to reorganize or rebuild the indexes. Choosing between reorganizing and rebuilding depends on defragmentation values. The fragmentation level of an index or heap is shown in the avg_fragmentation_in_percent column. The value should be as close to zero as possible. A value between 5-30% indicates moderate fragmentation, while any value over 30% indicates high fragmentation.
The avg_page_space_used_in_percent is another value that it is worth to look closely. This value represents the amount of spaced used in the indexes. A value below 75% is usually associated to internal fragmentation (more blank pages on our book than recommended).
The script will work for both SQL 2005 and higher versions. The aim is to reduce index fragmentation by recreating, reorganizing, or rebuilding the index:
To run the stored procedure without execute the rebuild : EXECUTE handdleFragmentationIndexes @debugMode = 1
To run the stored procedure and defragement indexes : EXECUTE handdleFragmentationIndexes
To run the stored procedure and defragement indexes on a specific database : EXECUTE handdleFragmentationIndexes @databaseName = ‘myDatabaseName’
This script sample is extract in part from the following BOL : sys.dm_db_index_physical_stats (Transact-SQL)
Michel Degremont | Premier Field Engineer – SQL Server Core Engineer |