TABLE and INDEX fragmentation
SQL Server 2005
SELECT dbschemas.[name] as ‘Schema’, dbtables.[name] as ‘Table’, dbindexes.[name] as ‘Index’, indexstats.avg_fragmentation_in_percent, indexstats.page_count FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS indexstats
INNER JOIN sys.tables dbtables ON dbtables.[object_id] = indexstats.[object_id]
INNER JOIN sys.schemas dbschemas ON dbtables.[schema_id] = dbschemas.[schema_id]
INNER JOIN sys.indexes AS dbindexes ON dbindexes.[object_id] = indexstats.[object_id]
AND indexstats.index_id = dbindexes.index_id
WHERE indexstats.database_id = DB_ID()
ORDER BY indexstats.avg_fragmentation_in_percent desc;
Â
ALTER INDEX ON REBUILD WITH (MAXDOP = 1);
dbcc show_statistics(CommandLog,PK_CommandLog);
select * from sys.dm_db_index_physical_stats(dbid, objid, indexid, partition_no, mode);
use nulls to get values for all databases etc
Â
Discussion ¬