SQLSERVER 索引 重建/重组
SELECT 'ALTER INDEX [' + ix.name + '] ON [' + s.name + '].[' + t.name + '] ' + CASE WHEN ps.avg_fragmentation_in_percent>15 THEN 'REBUILD' ELSE 'REORGANIZE' END + CASE WHEN pc.partition_count>1 THEN ' PARTITION = ' + CAST(ps.partition_number AS NVARCHAR(MAX)) ELSE '' END, avg_fragmentation_in_percent FROM sys.indexes AS ix INNER JOIN sys.tables t ON t.object_id=ix.object_id INNER JOIN sys.schemas s ON t.schema_id=s.schema_id INNER JOIN (SELECT object_id, index_id, avg_fragmentation_in_percent, partition_number FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL)) ps ON t.object_id=ps.object_id AND ix.index_id=ps.index_id INNER JOIN (SELECT object_id, index_id, COUNT(DISTINCT partition_number) AS partition_count FROM sys.partitions GROUP BY object_id, index_id) pc ON t.object_id=pc.object_id AND ix.index_id=pc.index_id WHERE ps.avg_fragmentation_in_percent>10 AND ix.name IS NOT NULL;
查看
SELECT OBJECT_NAME(dt.object_id) Tablename, si.name IndexName, dt.avg_fragmentation_in_percent AS ExternalFragmentation, dt.avg_page_space_used_in_percent AS InternalFragmentation FROM (SELECT object_id, index_id, avg_fragmentation_in_percent, avg_page_space_used_in_percent FROM sys.dm_db_index_physical_stats(DB_ID('DB_DJSMS'), NULL, NULL, NULL, 'DETAILED') WHERE index_id<>0) AS dt INNER JOIN sys.indexes si ON si.object_id=dt.object_id AND si.index_id=dt.index_id AND dt.avg_fragmentation_in_percent>10 AND dt.avg_page_space_used_in_percent<75 ORDER BY avg_fragmentation_in_percent DESC;