十月 2018
« 八月    
1234567
891011121314
15161718192021
22232425262728
293031  

彙整

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 […]