SQL Server 自動判別索引是否需要重建T-SQL與重建索引方式

參考網址：http://blog.miniasp.com/post/2009/01/18/Let-SQL-Server-Tell-You-Which-Indexes-to-Rebuild-or-Reorganize.aspx

自動查詢與建議索引重建之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

查詢方式：

執行 SQL Server Management Studio Express 選文字方式呈現查詢

查詢後建議 索引重建的列表與碎片狀況(此為沒有文字模式呈現查詢結果 不好通通複製)

文字模式查詢出來後用 alt + 滑鼠 可做區塊選取模式

把剪下的指令另開查詢就可以開始重建了。