【SQL】資料庫索引自動重建與重組

by zhi hao

資料庫索引自動重建與重組

直接執行後將產生的語法複製在直接執行,
則自動將資料的索引進行重建與重整

--自動幫你算出哪些索引需要被重建或重組,而且直接幫你把 ALTER INDEX 的 T-SQL 都寫好,程式碼如下: 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

相關文章

發表回應

本網站放置cookies,務求為您提供最佳的網上瀏覽體驗。 接受 閱讀更多