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

AzureポータルではAzure SQL Databaseのインデックスやプロシージャキャッシュの細かい制御ができない。そんな場合はSQL Server Management Studio（SSMS）で制御用T-SQLを直接実行すればよい。知っておくと便利なT-SQLを幾つか紹介する。

[島田広道，デジタルアドバンテージ]

連載目次

対象サービス／ソフトウェア：Microsoft Azure、SQL Database、SQL Server Management Studio（SSMS）

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

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

SQL Server Management Studio（SSMS）でT-SQLを実行する
SSMSから接続すれば、Azure SQL Databaseに対して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では利用できない（エラーが生じる）ので注意が必要だ。

　上記T-SQLのオプションなどの詳細は次のレファレンスを参照していただきたい。

• ALTER DATABASE SCOPED CONFIGURATION (Transact-SQL)［英語］（マイクロソフト）

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

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

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

exec sp_recompile N'＜テーブル名＞'

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

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

　上記T-SQLのオプションなどの詳細は次のレファレンスを参照していただきたい。

• sp_recompile (Transact-SQL)［英語］（マイクロソフト）

　sp_recompileの実際の使用例については、次の記事が参考になる。

• 「パラメータースニッフィング」によって、あるタイミングから処理が遅くなった（パフォーマンストラブル）（＠IT Database Expert）

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

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

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

update statistics ＜テーブル名＞ (＜インデックス名＞)

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

　上記T-SQLのオプションなどの詳細は次のレファレンスを参照していただきたい。

• UPDATE STATISTICS (Transact-SQL)［英語］（マイクロソフト）

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

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

• インデックス再構築と再構成の違い（Microsoft Japan SQL Server Support Teamブログ）

　再構成するためのT-SQLは次の通りだ。

alter index ＜インデックス名＞ on ＜テーブル名＞ reorganize

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

　また再構築のT-SQLは次の通りだ。

alter index ＜インデックス名＞ on ＜テーブル名＞ rebuild

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

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

　上記T-SQLのオプションなどの詳細は次のレファレンスを参照していただきたい。

• ALTER INDEX (Transact-SQL)［英語］（マイクロソフト）

　また実際の使用例については、次の記事が参考になる。

• 業務のデータベース処理が「なぜか」だんだん遅くなっていく（パフォーマンストラブル）（＠IT Database Expert）

■関連リンク

• 連載「SQL Serverトラブルシューティング」（＠IT Database Expert）
• Microsoft Azure SQL Database パフォーマンス チューニング (第１回)（Microsoft Japan SQL Server Support Teamブログ）
• インデックスの再編成と再構築（マイクロソフト）

「Tech TIPS」