アプリケーションから SQL Server/Azure SQL Database に対して実行されるクエリの応答が著しく低下し、コマンドタイムアウトが発生したり、通常よりも CPU 負荷が高くなり、全体的にパフォーマンスが低下するといった状況を経験されたことがある方も多いかと思います。
通常、上記のような現象が発生した場合、どのクエリで時間を要しているか、CPU リソースを消費しているかを、拡張イベント、サーバートレース、動的管理ビューなどで情報を収集し、解析することになりますが、即座に現象を解消するための対処を実施したい場合もあるかと思います。
今回は、クエリ パフォーマンスが著しく低下した場合の一時的な対処方法について自分の整理も兼ねて、まとめてみようと思います。
- <対処方法 1> 統計情報の更新
- <対処方法 2> インデックスの再構築
- <対処方法 3> 最適なクエリの実行プランを強制 (クエリ ストアを使用している場合)
- <対処方法 4> 価格レベル、サービスレベル (仮想コア数) を変更 (Azure SQL Database／Azure SQL Managed Instance のみ)
- まとめ
[対象]
SQL Server すべてのバージョン、エディション
Azure SQL Database
Azure SQL Managed Instance
<対処方法 1> 統計情報の更新
インデックス、列 の統計情報が最新の状態でない場合、オプティマイザで最適なクエリの実行プランを生成することができず、最適な実行プランでクエリが実行されないことに起因して、クエリのパフォーマンスが著しく低下したり、通常時よりも多くのリソース (CPU、メモリ、Disk I/O) を消費するなどの現象が発生する可能性があります。
そのため、クエリ パフォーマンスが著しく低下する現象が発生した場合、まずは インデックス、列 の統計情報を手動で更新し、最適なクエリの実行プランが生成されることで現象が解消するかを確認します。
※ 列の統計情報とは、インデックスが作成されていないテーブル列をクエリの条件句 (WHERE) の条件列として指定した場合、既定で内部的に自動生成される統計情報であり、「_WA_Sys_***********」というようなオブジェクト名になります。
1-1) データベース単位で統計情報を更新
特定のクエリではなく全体的にクエリ パフォーマンスが低下している場合、どのテーブルのインデックス、列 の統計情報を更新すべきか明確に特定できていない場合は、データベースに存在する全てのインデックス、列 の統計情報を手動で更新することを検討します。
|
EXEC sp_updatestats go |
※ ストアドプロシージャ sp_updatestats をクエリを実行しているデータベース上で実行することで、データベースに存在する全てのインデックス、列 の統計情報が更新されます。
[参考URL]
1-2) テーブル単位で統計情報を更新
特定のクエリのみパフォーマンスが低下し、該当のクエリで参照しているテーブルを特定できている場合、該当テーブルに存在する全てのインデックス、列 の統計情報を手動で更新することを検討します。
| UPDATE STATISTICS <テーブル名 もしくは インデックス付きビュー名> go |
[例] テーブル "tab1" 上の全てのインデックス、列 の統計情報を更新する場合
| UPDATE STATISTICS [dbo].[tab1] go |
[参考URL]
1-3) テーブル上のインデックス単位で統計情報を更新
特定のクエリのみパフォーマンスが低下し、該当のクエリで参照しているテーブルおよび参照されているインデックスが特定できている場合、該当テーブルに存在する特定のインデックスのみに対して統計情報を手動で更新することを検討します。
| UPDATE STATISTICS <テーブル名 もしくは インデックス付きビュー名> <インデックス名> go |
[例] テーブル "tab1" 上のインデックス "PK_tab1" の統計情報を更新する場合
| UPDATE STATISTICS [dbo].[tab1] [PK_tab1] go |
ストアドプロシージャ、パラメータ化クエリの場合、統計情報の更新を実行することで、ステートメント レベル (ストアドプロシージャの中で実行されている各クエリ) のリコンパイルが行われ、実行プランの再生成が行われますが、ストアドプロシージャ、パラメータ化クエリ全体のリコンパイルは行われず、実行プラン生成時に指定されたパラメータ値は更新されません。
実行プラン生成時に指定されたパラメータ値が非典型的なパラメータ (通常 指定されるパラメータ値の範囲外の値が指定されたパラメータ) であったことに起因し、パフォーマンスが低下していた場合、統計情報の更新のみでは問題が解決しない可能性があります。
そのため、ストアドプロシージャ、パラメータ化クエリでパフォーマンスの低下が疑われる場合は、統計情報の更新後、メモリ上にキャッシュされたクエリの実行プランの削除 もしくは 強制的にリコンパイルさせることを検討します。
2-1) メモリ上にキャッシュされたクエリの実行プランを削除
DBCC FREEPROCCACHE コマンドを実行することで、メモリ上にキャッシュされた実行プラン情報を削除します。
| DBCC FREEPROCCACHE go |
※ DBCC FREEPROCCACHE コマンドでは、特定の実行プランのみを削除することも可能です。詳細な方法については、以下のURLを参照。
2-2) 次回実行時に強制的にリコンパイル
パフォーマンスが低下しているストアドプロシージャが特定できている場合、ストアドプロシージャやパラメータ化クエリが参照しているテーブルが特定できている場合は、ストアドプロシージャ sp_recompile を使用することで、次回 実行時に強制的にリコンパイルさせることが可能です。
| EXEC sp_recompile N'ストアドプロシージャ名 もしくは テーブル名>' go |
[例] ストアドプロシージャ "sp_TEST" を次回実行時にリコンパイルさせる場合
| EXEC sp_recompile N'[dbo].[sp_TEST]' go |
[例] テーブル "tab1" を参照する全てのクエリ (ストアドプロシージャ、パラメータ化クエリ 含む) を次回実行時にリコンパイルさせる場合
| EXEC sp_recompile N'[dbo].[tab1]' go |
[参考URL]
<対処方法 2> インデックスの再構築
インデックスの再構築を実施しインデックスの断片化を解消することで、 クエリで参照されるデータページ数が減り、ディスク I/O 負荷が軽減される、また、インデックスの統計情報がフルスキャンにより更新されることで、より最適なクエリの実行プランが生成されるなどの要因により、パフォーマンスを向上できることが期待できます。
そのため、 [対処方法 1] を実施したとしても、パフォーマンス問題が解消しなかった場合に実施することを検討します。
| ALTER INDEX <インデックス名> ON <テーブル名> REBUILD go |
[例] テーブル "tab1" 上のインデックス "PK_tab1" の再構築を実施する場合
| ALTER INDEX [PK_tab1] ON [dbo].[tab1] REBUILD go |
[例] テーブル "tab1" 上の全てのインデックスの再構築を実施する場合
| ALTER INDEX ALL ON [dbo].[tab1] REBUILD go |
[参考URL]
<対処方法 3> 最適なクエリの実行プランを強制 (クエリ ストアを使用している場合)
クエリ ストアを有効化 (Azure SQL Database の場合は、既定で有効) している環境で、クエリ ストア -「後退したクエリ」、「リソースを消費するクエリの上位」より、クエリの実行プランが変わったことで、処理時間が大幅に伸びている、リソースの消費量 (CPU 時間など) が増えているクエリが確認できた場合、変更される前のクエリの実行プランで該当クエリの実行を強制することで、パフォーマンスを向上できることが期待できます。
そのため、 [対処方法 1、2] を実施したとしても、パフォーマンス問題が解消しなかった場合に実施することを検討します。
※ データの増減により、強制したクエリの実行プランが最適なクエリプランではなくなる可能性があります。そのため、データの増減が激しいテーブルに対してクエリの実行プランを強制した場合は、定期的にパフォーマンスをチェックします。
1) SQL Server Management Studio (SSMS) / Azure Data Studio を起動し、該当のインスタンスに管理者権限が付与されたログインで接続します。
2) 該当データベース -「クエリ ストア」-「後退したクエリ」を選択します。
3) パフォーマンスが低下しているクエリの変更される前のクエリの実行プランを選択後、「プランの強制」を選択します。
[参考URL]
<対処方法 4> 価格レベル、サービスレベル (仮想コア数) を変更 (Azure SQL Database／Azure SQL Managed Instance のみ)
Azure ポータルなどから Azure SQL Database／Azure SQL Managed Instance の価格レベル、サービスレベル (仮想コア数) を変更することで、使用可能なリソース (CPU、メモリ、Disk I/O) が増えることで、パフォーマンスを向上できることが期待できます。
[対処方法 1、2、3] を実施したとしても、パフォーマンス問題が解消しなかった場合に実施することを検討します。
1) Azure ポータルに接続します。
2) SQL データベース - 該当のデータベースを選択します。
3) 設定 : コンピューティングとストレージ より、価格レベル、サービスレベル (仮想コア数) を変更します。
※ 「DTU ベースの購入モデル」から「仮想コアベースの購入モデル」への変更、「仮想コアベースの購入モデル」から「DTU ベースの購入モデル」への変更も可能。
「DTU ベースの購入モデル」の場合、DTUを増やし、「仮想コアベースの購入モデル」の場合、仮想コア数を増やすことを検討。
「DTU ベースの購入モデル」を使用している状態で CPU 使用率が高い値を推移している場合、Standard サービス レベル S2 までは仮想コアは１つであるため、S3 以上に変更することを検討。
[参考URL]
まとめ
今回は、クエリ パフォーマンスが著しく低下した場合の一時的な対処方法についてまとめてみました。
問題点を特定し、対処策を実施することが最も望ましいと思いますが、即座に現象を解消させたい場合などに、今回の一時的な対処方法を実行することを検討すると良いかと思います。
また、一般的に「クエリ パフォーマンスが著しく低下した原因調査」を実施するためには、現象発生時に情報を採取する必要があるため、原因調査を実施する必要がある場合は、今回の一時的な対処方法を実施する前に調査に必要な情報を採取できるように、事前に準備されておくと良いかと思います。
※ 次回、一時的な対処方法を実施する前に採取したほうが良い情報採取手順についてもまとめてみようと思います。
SQL Server の基本については、以下のブログにまとめています。
SQL Server で一度は経験するトランザクション ログの肥大化現象の原因調査方法および対処方法について、以下のブログにまとめています。
