I found this gem while looking at a database that needed all indexes rebuilt.
I actually had to create the programmatic way of doing rebuilding all indexes in a database based on the information in these posts:
See the fragmentation:
USE __REPLACE_WITH_DATABASE_NAME__
GO
Print 'Selecting Index Fragmentation in the database.'
SELECT
DB_NAME(DPS.DATABASE_ID) AS [DatabaseName]
,OBJECT_NAME(DPS.OBJECT_ID) AS TableName
,SI.NAME AS IndexName
,DPS.INDEX_TYPE_DESC AS IndexType
,DPS.AVG_FRAGMENTATION_IN_PERCENT AS AvgPageFragmentation
,DPS.PAGE_COUNT AS PageCounts
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, NULL) DPS --N'LIMITED') DPS
INNER JOIN sysindexes SI
ON DPS.OBJECT_ID = SI.ID
AND DPS.INDEX_ID = SI.INDID
ORDER BY DPS.avg_fragmentation_in_percent DESC
GO
Fix the fragmentation with a rebuild:
Print 'Rebuilding indexes on every table in the database.'
EXEC sp_MSforeachtable @command1="print 'Rebuilding indexes for ?' ALTER INDEX ALL ON ? REBUILD WITH (FILLFACTOR = 90)"
GO
Fix the fragmentation with reorganize:
Print 'Reorganizing indexes on every table in the database.'
EXEC sp_MSforeachtable @command1="print 'Reorganizing indexes for ?' ALTER INDEX ALL ON ? REORGANIZE"
GO
Notice how we have items that are at 99% fragmentation and how they are gone once we run this rebuild.
Awesome?
If you are fuddy-duddy DBA, you would say only to rebuild indexes with greater than 30% fragmentation and reorganize the rest. That's fine, show me some code on how to programmatically do this! :D
Or put the Ayende way, send me a patch!
