MS SQL Server - index fragmentation
Sometimes it doesn’t make sense to rebuild all of your indexes as part of a maintenance plan. You may have mirroring running with large db tables, and your indexes may be several GB. As part of your maintenance schedule, I recommend manually finding fragmented indexes and rebuilding them yourself (or scripting this)
To do this you, you need to locate the indexes – anything over 50% can be considered a problem.
SELECT ps.database_id, ps.OBJECT_ID, ps.index_id, b.name, ps.avg_fragmentation_in_percent FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS ps INNER JOIN sys.indexes AS b ON ps.OBJECT_ID = b.OBJECT_ID AND ps.index_id = b.index_id WHERE ps.database_id = DB_ID() ORDER BY ps.OBJECT_ID GO
DBCC IndexDefrag ('DBNAME','TABLENAME','INDEXNAME' )
or a full rebuild with
DBCC DBREINDEX ('dbname.ns.tablename','INDEXNAME' )