Bookmark and Share
find fragments in indexes and optimize the indexes
(Publish Date: 2014-6-16 2:45pm, Total Visits: 461, Today: 1, This Week: 3, This Month: 10)

-- Be careful of executing it against large production system without detailed filtering

USE wwdev03;
GO

SELECT OBJECT_NAME(ips.object_id) AS Objectname,
    i.name AS IndexName,
    i.type_desc AS IndexType,
    ips.avg_fragmentation_in_percent AS FragmentationPercent, *
FROM sys.dm_db_index_physical_stats (DB_ID('wwdev03'), NULL, NULL, NULL, 'DETAILED') AS ips
INNER JOIN sys.indexes AS i
ON ips.object_id = i.object_id
AND ips.index_id = i.index_id
WHERE avg_fragmentation_in_percent > 30
AND ips.index_level = 0;
GO

-- Rebuild and reorganize indexes


ALTER INDEX id_ix on dbo.users REBUILD;
GO

ALTER INDEX id_ix on dbo.users REORGANIZE;
GO