Friday, February 22, 2013

Page count and fragmentation

I was working on writing a maintenance script for SQL Server that would perform a rebuild on indexes with fragmentation of 30% or above and re-organize indexes with fragmentation of below 30% and found that page count should also be taken into an account. if a page count is less than 1000 then rebuilding an index will not only have no effect of fragmentation but also rebuild procedure will be more costly than fragmentation of an index with 1000 pages or less.
As a result I changed my query slightly:
SELECT OBJECT_NAME(ps.object_id) As TableName, i.name As IndexName,ps.index_type_desc As IndexType, ps.index_depth, ps.index_level, ps.avg_fragmentation_in_percent, ps.fragment_count, ps.page_count 
FROM sys.dm_db_index_physical_stats(DB_ID('DBName'), null, null, null, 'LIMITED') as ps
INNER JOIN sys.indexes i ON ps.object_id = i.object_id AND ps.index_id = i.index_id 
WHERE NOT i.name IS NULL and ps.avg_fragmentation_in_percent >= 30 AND ps.page_count > 1000

No comments:

Post a Comment