生活随笔
收集整理的這篇文章主要介紹了
SQL Server 索引重建或索引重組
小編覺得挺不錯的,現在分享給大家,幫大家做個參考.
查詢索引的碎裂狀態T-SQL語法（適用于SQL Server 2005以上）：
SELECT OBJECT_NAME(dt.object_id) ,si.name ,dt.avg_fragmentation_in_percent,dt.avg_page_space_used_in_percentFROM(SELECT object_id ,index_id ,avg_fragmentation_in_percent,avg_page_space_used_in_percentFROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, 'DETAILED')WHERE index_id <> 0) AS dt --does not return information about heapsINNER JOIN sys.indexes siON si.object_id = dt.object_idAND si.index_id = dt.index_id
?
索引重組的時機
??? *檢查 Externalfragmentation 部分
???????? o 當avg_fragmentation_in_percent 的值介于 10 到 15 之間
??? *檢查 Internalfragmentation 部分
???????? o 當avg_page_space_used_in_percent 的值介于 60 到 75 之間
?
索引重建的時機
??? *檢查 Externalfragmentation 部分
???????? o 當avg_fragmentation_in_percent 的值大于 15
??? *檢查 Internalfragmentation 部分
???????? o 當avg_page_space_used_in_percent 的值小于 60
?
調整過的自動幫你算出哪些索引需要被重建或重組T-SQL 語法：
SELECT 'ALTER INDEX [' + ix.name + '] ON [' + s.name + '].[' + t.name + '] ' +CASEWHEN ps.avg_fragmentation_in_percent > 15THEN 'REBUILD'ELSE 'REORGANIZE'END +CASEWHEN pc.partition_count > 1THEN ' PARTITION = ' + CAST(ps.partition_number AS nvarchar(MAX))ELSE ''END,avg_fragmentation_in_percentFROM sys.indexes AS ixINNER JOIN sys.tables tON t.object_id = ix.object_idINNER JOIN sys.schemas sON t.schema_id = s.schema_idINNER JOIN(SELECT object_id ,index_id ,avg_fragmentation_in_percent,partition_numberFROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL)) psON t.object_id = ps.object_idAND ix.index_id = ps.index_idINNER JOIN(SELECT object_id,index_id ,COUNT(DISTINCT partition_number) AS partition_countFROM sys.partitionsGROUP BY object_id,index_id) pcON t.object_id = pc.object_idAND ix.index_id = pc.index_idWHERE ps.avg_fragmentation_in_percent > 10AND ix.name IS NOT NULL
?
參考: http://blog.miniasp.com/post/2009/01/18/Let-SQL-Server-Tell-You-Which-Indexes-to-Rebuild-or-Reorganize.aspx
轉載于:https://www.cnblogs.com/chinalantian/archive/2011/08/05/2128150.html
總結
以上是生活随笔為你收集整理的SQL Server 索引重建或索引重組的全部內容，希望文章能夠幫你解決所遇到的問題。
如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。
