Wednesday, June 1, 2011

Query to return fragmentation information on partitioned indexes

This will very useful, if you have implemented the partitioning functionality .

 

SELECT  OBJECT_NAME(a.object_id) AS object_name
       ,a.index_id
       ,b.name
       ,b.type_desc
       ,a.partition_number
       ,a.avg_fragmentation_in_percent
FROM    sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'LIMITED') a
JOIN    sys.indexes b ON a.object_id = b.object_id AND a.index_id = b.index_id
WHERE   a.object_id = OBJECT_ID('tableName')-- need to change the tablename here
ORDER BY OBJECT_NAME(a.object_id)
       ,a.index_id
       ,b.name
       ,b.type_desc
       ,a.partition_number 


once the fragmentation has been identified use the following script to rebuild the index on a single partition. in this example I am rebuilding the partition 18



ALTER INDEX PK_tableName
ON dbo.tableName
REBUILD PARTITION = 18 ;
GO

0 comments:

Post a Comment