SQL Server: Measuring The Index Fragmentation

In my previous post , I have explained about the different types of fragmentation and how it will affect the query performance. In this post we will discuss about measuring the index fragmentation.

Method1:

To check the fragmentation of indexes in the entire database, execute:

Use DatabaseName
DBCC SHOWCONTIG WITH TABLERESULTS, ALL_INDEXES

The result will be presented in a tabular format.

For individual table fragmentation, execute:

DBCC SHOWCONTIG (TableName) with all_indexes

In SQL Server 2005, execute:

USE AdventureWorks
GO
DBCC SHOWCONTIG ('HumanResources.Employee')
GO

The results will be in text format with labels:

DBCC SHOWCONTIG scanning 'TableName' table...
Table: 'TableName' (206334645); index ID: 7, database ID: 14
LEAF level scan performed.
- Pages Scanned................................: 62128
- Extents Scanned..............................: 7808
- Extent Switches..............................: 8098
- Avg. Pages per Extent........................: 8.0
- Scan Density [Best Count:Actual Count].......: 95.89% [7766:8099]
- Logical Scan Fragmentation ..................: 17.39%
- Extent Scan Fragmentation ...................: 7.68%
- Avg. Bytes Free per Page.....................: 2412.0
- Avg. Page Density (full).....................: 70.20%

Index ID 1 refers to the clustered index on the table if any. To find out the index name, you have to look in the sysindexes table.

In SQL Server 2005:

select * from sys.indexes

The results above show moderate fragmentation. The page density is the result of indexing with a fill factor of 70. With time that deteriorates, indicating the need to reindex. If logical fragmentation is above 50%, you have to reindex to regain performance.

Alternative to reindexing is indexdefrag. To defrag a specific index, execute:

DBCC INDEXDEFRAG (DatabaseName, TableName, IndexName)

Defragging is on online operation. It will not lock up the table. On the other hand it will slow down the operation. It is a logged operation. So it competes with regular OLTP transactions for writing resources. Assume it takes 1 hour to defrag an index in the middle of the day. Should you do it? Probably not. If it takes only 5 minutes, you should.

Your only protection against quickly deteriorating indexes is the FILL FACTOR. During the night you should reindex with a fill factor of 70 in general. If performance issue persists, you can gradually go down to 40. Fill factor is limited help if the inserted data does not have good distribution. On the other hand, it works really great if the inserted data has a good distribution.

Method 2:

Measuring Internal Fragmentation 

Internal fragmentation  is all about the fullness of the page and it is measured using the        sys.dm_db_index_physical_stats function with DETAILED mode. The avg_page_space_used_in_percent column in the output gives the internal fragmentation of the index. Below query list all the indexes which have more than 10 pages and page fullness is less than 85 percent.

EXEC sp_configure 'show advanced options',1
GO
RECONFIGURE WITH OVERRIDE
GO
DECLARE @DefaultFillFactor INT
DECLARE @Fillfactor TABLE(Name VARCHAR(100),Minimum INT ,Maximum INT,config_value INT ,run_value INT)
INSERT INTO @Fillfactor EXEC sp_configure 'fill factor (%)'     
SELECT @DefaultFillFactor  = CASE WHEN run_value=0 THEN 100 ELSE  run_value  END  FROM @Fillfactor 

SELECTDB_NAME() AS DBname,QUOTENAME(s.name) AS CchemaName,QUOTENAME(o.name) AS TableName,i.name AS IndexName,stats.Index_type_desc AS IndexType,stats.page_count AS [PageCount],stats.partition_number AS PartitionNumber,CASE WHEN i.fill_factor>0 THEN i.fill_factor ELSE @DefaultFillFactor END  AS [Fill Factor],
  stats.avg_page_space_used_in_percent,
  CASE WHEN stats.index_level =0 THEN 'Leaf Level' ELSE 'Nonleaf Level' END AS IndexLevelFROM
        sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL , NULL, 'DETAILED') AS stats,
        sys.objects AS o,
        sys.schemas AS s,
        sys.indexes AS iWHERE
        o.OBJECT_ID = stats.OBJECT_ID AND s.schema_id = o.schema_id       AND     i.OBJECT_ID = stats.OBJECT_ID ANDi.index_id = stats.index_idAND     stats.avg_page_space_used_in_percent<= 85 AND stats.page_count >= 10 AND        stats.index_id > 0 ORDER BY  stats.avg_page_space_used_in_percent ASC, stats.page_count DESC

I have used the where condition to fetch  only the indexes which  have more than 10 pages  and page fullness is less than 85 percentage. This is  based on   my environment and some best practices documentations.Low values for avg_page_space_used_in_percent and higher  value for PageCount together will affect the performance of the system. The value of avg_page_space_used_in_percent will be  low due to various reasons

• Due to Page split and deleting records: In this scenario we have to REBUILD or REORGANIZE  the indexes. If the fragmentation is reported in the non leaf level , REBUILD is required to reduce the fragmentation.
• Due to fill factor setting : A wrong setting  of fill factor value of the index might cause the internal fragmentation.If the internal fragmentation is due the fill factor setting, we have to REBUILD the index with new fill factor value.
• Due to record size : Some time size of the record might account for internal fragmentation. For example let us assume that size of one record is 3000 bytes and page can hold only two record. The third record can not be fitted into a page as the remaining free space in the page is less than 3000 bytes. In this scenario each page will have empty space of 2060 bytes. To get rid of the fragmentation due to the size of the record , we might need to redesign the table or has to do a vertical partitioning of the table.

Measuring External Fragmentation 

External fragmentation also measured using the sys.dm_db_index_physical_stats function with LIMITED mode ,but we will be using the avg_fragmentation_in_percent from the result to measure the external fragmentation. With LIMITED mode it will give the fragmentation of the leaf level. To get the fragmentation of non leaf level, it  should be executed  with DETAILED or SAMPLE mode. A fragment is a continuous allocation of pages.For example if an index has 150 pages and pages are allocated from 1 to 50, 55 to 60 ,65 to 120 and 140 to 180.Each of these sequences are called as fragment and we can say that this index has four fragment.


EXEC sp_configure 'show advanced options',1
GO
RECONFIGURE WITH OVERRIDE
G
ODECLARE @DefaultFillFactor INT
DECLARE @Fillfactor TABLE(Name VARCHAR(100),Minimum INT ,Maximum INT,config_value INT ,run_value INT)
INSERT INTO @Fillfactor EXEC sp_configure 'fill factor (%)'     
SELECT @DefaultFillFactor  = CASE WHEN run_value=0 THEN 100 ELSE  run_value END  FROM @Fillfactor 

SELECTDB_NAME() AS DBname,QUOTENAME(s.name) AS CchemaName,QUOTENAME(o.name) AS TableName,i.name AS IndexName,stats.Index_type_desc AS IndexType,stats.page_count AS [PageCount],stats.partition_number AS PartitionNumber,CASE WHEN i.fill_factor>0 THEN i.fill_factor ELSE @DefaultFillFactor END  AS [Fill Factor],
  stats.avg_fragmentation_in_percent,stats.fragment_count,
  CASE WHEN stats.index_level =0 THEN 'Leaf Level' ELSE 'Nonleaf Level' END AS IndexLevelFROM
        sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL , NULL, 'LIMITED') AS stats,
        sys.objects AS o,
        sys.schemas AS s,
        sys.indexes AS iWHERE
        o.OBJECT_ID = stats.OBJECT_ID AND s.schema_id = o.schema_id       AND     i.OBJECT_ID = stats.OBJECT_ID ANDi.index_id = stats.index_idAND     stats.avg_fragmentation_in_percent>= 20 AND stats.page_count >= 1000ORDER BY  stats.avg_fragmentation_in_percent DESC,stats.page_count DESC




In this query ,  I have used a where condition to fetch indexes which have fragmentation  greater than 20 percent and have minimum of 1000 pages.  

avg_fragmentation_in_percent can have higher value due to various reasons :

• SQL server storage engine allocates pages from mixed extent to a table or index till the page count reaches eight.Once the page count reaches to eight SQL server storage engine starts assigning full uniform extents to the index. So there is a possibility of having higher fragmentation for small table and rebuilding indexes might increase the fragmentation.For example, let us assume that an index has 7 pages and these pages are allocated from two mixed extent, while rebuilding the index there is possibility of allocating pages from more than 2 extents and maximum of seven extents which in turn increase the fragmentation. 
• Even the pages are allocated from uniform extent , there is possibility of fragmentation. When the size of index grow , it need more pages in the non leaf level also.If last page allocated to leaf level is 250 and to accommodate more row in the leaf lever index structure might need a page in index level 1, then SQL server storage engine allocate page 251 to the index level 1 which create fragment in the leaf level.
• Other common reason  is the page split  due to the DML operations . This I have explained well in my previous post.Rebuild/Reorganize  index may not be effective to fix fragmentation happened due to the fist two reason, but it can reduce the fragmentation caused by the page split or delete operation. 
• In our environment we follow the index maintenance  as given below:
• 20 to 40 percentage of fragmentation is handled with reorganizing the index.
• All index which has more 40 percentage fragmentation will considered for rebuild
• Index which has less than 1000 pages will be ignored by the index maintenance logic.
• Index which has more than 50K pages and fragmentation between 10 and 20 will also be considered for Reorganize.