ここから本文です

Azure SQL DatabaseのインデックスやキャッシュをT-SQLで制御する

6/27(火) 8:25配信

@IT

※対象サービス/ソフトウェア:Microsoft Azure、SQL Database、SQL Server Management Studio(SSMS)

 Azureポータルでは、Azure SQL Databaseの作成や監視など、さまざまな作業ができる。しかし、インデックスや実行プランのキャッシュなどを細かく制御するような機能は備わっていない。

 そういった作業は、Azure SQL DatabaseにSQL Server Management Studio(SSMS)で接続して、SSMSで制御用のT-SQLを直接実行することで実現できる。

 本TIPSでは、Azure SQL Databaseのインデックスやキャッシュを制御する方法を紹介する。トラブルシューティングやパフォーマンスチューニングなどに役立てていただければ幸いだ。

●プロシージャキャッシュを消去する

 プロシージャキャッシュとは、SQLの実行プランが格納されるメモリ上の領域のことだ(詳細はDatabase Expertの「『実行プラン』を理解すると、パフォーマンス問題の解決能力が一気に向上する」参照)。実行プランには、過去に実行された際にコンパイルされたSQLステートメントが含まれる。そのためプロシージャキャッシュを消去(クリア)すると、SQLステートメントの再コンパイルが行われる。

 Azure SQL Databaseでプロシージャキャッシュを消去するには、次のT-SQLを実行する。

――――――
alter database scoped configuration clear procedure_cache

-- 実行後に表示されるメッセージ:
コマンドは正常に完了しました。
――――――

 実行すると、選択中(SSMSで接続中)のデータベースの実行プランがプロシージャキャッシュから消去される。

 (ソフトウェアとしての)SQL Serverで実行プラン(正確にはクエリプラン)を消去するT-SQLとしては、「DBCC DROPCLEANBUFFERS」がある。だがこれはAzure SQL Databaseでは利用できない(エラーが生じる)ので注意が必要だ。


●SQLステートメントの再コンパイルを強制する

 プロシージャキャッシュを消去することなく、SQLステートメントをコンパイルし直したい場合は、「sp_recompile」というストアードプロシージャが利用できる。

 例えば、あるテーブルを参照する全クエリを再コンパイルするには、次のようなT-SQLを実行する。
――――――
exec sp_recompile N'<テーブル名>'

-- 実行後に表示されるメッセージ:
オブジェクト '<テーブル名>' には再コンパイルが正しく設定されました。
――――――

 sp_recompileのオプションはnvarchar型なので、テーブル名を指定する際、忘れずに「N'~'」と記述すること。

●インデックスの統計情報を更新する

 インデックスの統計情報とは、「テーブルやインデックス内で、どんな値が、どんな頻度で出現するのかをまとめた情報」である。これは、効率よくSQLステートメントを実行するために利用される。そのため、その内容を更新せず古いままにしておくとSQL Databaseのパフォーマンスが下がる可能性がある。

 統計情報を更新するには、次のT-SQLを実行する。

――――――
update statistics <テーブル名> (<インデックス名>)
-- 実行後に表示されるメッセージ:
コマンドは正常に完了しました。
――――――

●インデックスを再構成または再構築する

 テーブルへのデータ挿入や更新が続くと、それにひも付いているインデックスで断片化が生じ、結果としてパフォーマンスが下がる可能性がある。断片化を解消するには、インデックスを「再構成」または「再構築」する。

 再構成するためのT-SQLは次の通りだ。
――――――
alter index <インデックス名> on <テーブル名> reorganize

-- 実行後に表示されるメッセージ:
コマンドは正常に完了しました。
――――――

 また再構築のT-SQLは次の通りだ。
――――――
alter index <インデックス名> on <テーブル名> rebuild

-- 実行後に表示されるメッセージ:
コマンドは正常に完了しました。
――――――

 インデックスの再構成/再構築は、完了までに長い時間がかかることがよくある。その長さはテーブルのサイズやインデックスの構成などによって大きく異なる。また、その最中のCPU使用率やデータ入出力が大幅に高まることもある。本来のデータベース処理が滞らないよう、実行する時間帯などに注意すべきだ。

最終更新:6/27(火) 8:25
@IT