Measuring Internal Fragmentation
EXEC sp_configure 'show advanced options',1
GORECONFIGURE WITH OVERRIDE
GODECLARE @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
SELECT
DB_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 IndexLevel
FROM
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 AND i.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
- 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
EXEC sp_configure 'show advanced options',1
GORECONFIGURE WITH OVERRIDE
GODECLARE @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
SELECT
DB_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 IndexLevel
FROM
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 AND i.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.
