SQL Azureでのインデックス断片化、誰が知っていた！

私は1年以上このプロジェクトに携わっており、この1年間でアプリケーションとしてもデータとしても大幅に成長しました。新機能の開発が止まることがありませんでした。コードをリファクタリングして戻ることはほとんどありませんでした。先週、データ量の多いページの読み込みが遅いことに気づきました。最悪の場合、1つのビューの読み込みに最大30秒かかることがありました。私の最大読み込み時間の10倍です…

私が世間知らずと言われても仕方ありませんが、SQL Azureでのインデックス断片化を考慮していませんでした。クラウドなのに！オンプレミスの問題から免れているはずなのに…どうやらインデックス断片化もクラウドでの問題のようです。

MSDNブログで、断片化したインデックスを特定し、それらを再構築するクエリをいくつか見つけました。

インデックス断片化を表示する最初のクエリを実行した後、50パーセント以上の断片化を持つインデックスをいくつか発見しました。記事によると、10%を超えるものは対処が必要とのことです。

最初のクエリ：インデックス断片化の表示

--Get the fragmentation percentage

SELECT
 DB_NAME() AS DBName
,OBJECT_NAME(ps.object_id) AS TableName
,i.name AS IndexName
,ips.index_type_desc
,ips.avg_fragmentation_in_percent
FROM sys.dm_db_partition_stats ps
INNER JOIN sys.indexes i
ON ps.object_id = i.object_id
AND ps.index_id = i.index_id
CROSS APPLY sys.dm_db_index_physical_stats(DB_ID(), ps.object_id, ps.index_id, null, 'LIMITED') ips
ORDER BY ps.object_id, ps.index_id

2番目のクエリ：インデックスの再構築

--Rebuild the indexes
DECLARE @TableName varchar(255)

DECLARE TableCursor CURSOR FOR
(
 SELECT '[' + IST.TABLE_SCHEMA + '].[' + IST.TABLE_NAME + ']' AS [TableName]
 FROM INFORMATION_SCHEMA.TABLES IST
 WHERE IST.TABLE_TYPE = 'BASE TABLE'
 )

 OPEN TableCursor
 FETCH NEXT FROM TableCursor INTO @TableName
WHILE @@FETCH_STATUS = 0

 BEGIN
 PRINT('Rebuilding Indexes on ' + @TableName)
Begin Try
 EXEC('ALTER INDEX ALL ON ' + @TableName + ' REBUILD with (ONLINE=ON)')
End Try
Begin Catch
 PRINT('Cannot do rebuild with Online=On option, taking table ' + @TableName+' down for douing rebuild')
 EXEC('ALTER INDEX ALL ON ' + @TableName + ' REBUILD')
 End Catch
FETCH NEXT FROM TableCursor INTO @TableName
END

CLOSE TableCursor
DEALLOCATE TableCursor

Source