移行後のクエリのチューニング
あなたは法律事務所のデータベース管理者であるとします。 週末に、顧客関係管理 (CRM) データベースを含むいくつかのデータベースを SQL Server 2019 に移行しました。 あなたは月曜日の朝早くに出勤しました。営業チームが数時間ほどで出勤し、顧客との電話のやり取りを始めると予想したからです。 営業チームは CRM データベースを利用しており、顧客との関係を効率的に築くには、アプリケーションがすぐに応答する必要があります。
データベースを移行した後、あなたは互換レベルを以前の設定のままにしました。 CRM アプリケーションは、新しいサーバーに接続されています。 あなたは、互換性レベルを変更する前に、標準的な月曜日のワークロードに対して新しいシステムでのクエリのパフォーマンスを測定したいと考えています。 データベースのアップグレード時には、クエリのパフォーマンスを維持することが重要です。
営業チームが CRM アプリケーションが低速であることに気付くまで待つのではなく、ワークロードとパフォーマンスを自動的に測定するプロセスが必要です。 このようなプロセスがあれば、互換性レベルを変更した後に低速なクエリを確実に修正できます。
クエリ ストア
SQL Server 2016 で導入されたクエリ ストア機能では、クエリに関する情報が継続的に収集されています。 クエリ ストアとオンプレミスとクラウドのインストールを連携させて、クエリを実行する際のトラブルシューティングに役立つ情報を収集できます。 キャッシュされたクエリ プランはプロシージャ キャッシュに格納されますが、SQL Server で格納されるのは最新の実行プランのみです。
スキーマの変更またはインデックスの追加や削除が原因で、クエリの実行プランが時間と共に変化する可能性があります。 また、メモリが不足すると、プラン キャッシュからプランが削除される可能性があります。
既定では、データベースを作成するとき、または SQL Server 2017 や SQL Server 2019 に移行するときは、クエリ ストアは有効になりません。 ただし、移行後のデータベースでデータベース互換レベルを更新する前に、クエリ ストア機能を有効にすることができます。 クエリ ストアを有効にするには、オブジェクト エクスプローラーでデータベースを右クリックして [プロパティ] を選んだ後、[クエリ ストア] ページを選びます。 [操作モード (要求)] 選択リストで、[オフ]、[読み取り専用]、または [読み取り/書き込み] を選択します。 Transact-SQL を使ってクエリ ストアを有効にすることもできます。 CustomerServices という名前のデータベースのクエリ ストアを有効にするには、次のコマンドを実行します。
ALTER DATABASE CustomerServices SET QUERY_STORE = ON
クエリが初めてコンパイルされるとき、クエリ テキストと最初のプランがクエリ ストアに渡されます。 クエリが再コンパイルされると、クエリ ストア内のプランが更新されます。 新しいプランが作成されると、クエリ ストアに追加され、前のプランと実行の統計情報と共に保存されます。
クエリが実行されると、クエリ ストアによって実行時の統計情報が保存されます。 コンパイルまたは再コンパイルの間に、SQL Server によって、クエリに適用する必要があるプランがクエリ ストアに存在するかどうかが確認されます。 強制されたプランがプロシージャ キャッシュ内のものと異なる場合は、クエリが再コンパイルされます。
互換性レベルを変更するまで、データベースはクエリ オプティマイザーの最新の変更に反映されません。 既存の互換性レベルで、標準的なワークロードを収集し、測定するためのベースラインを作成できます。 パフォーマンス ベースライン用に十分なクエリ情報を収集した後、データベースの互換レベルをサーバーのバージョンに変更します。
適切なベースラインを作成するには、典型的なビジネス アクティビティの時間帯から十分なデータを収集する必要があります。 現在のサーバー互換レベルに移行すると、ワークロードは最新のクエリ オプティマイザーの対象になります。 新しいクエリ オプティマイザーではより優れたプランが生成されるため、クエリのパフォーマンスはおそらく向上します。
SQL Server では最適ではないクエリ プランが使用されることがあるため、明らかな理由なしに突然実行が遅くなります。 この問題は、クエリ ストアで以前のプランを適用することによって解決できます。 クエリ ストアの使用は、USE PLAN クエリ ヒントを使用するのと似ていますが、ユーザー アプリケーションを変更する必要はありません。
互換レベルを移行した後、クエリ ストアでは引き続き、最適なパフォーマンスを維持するためのクエリのサポートが提供されます。 低下したクエリ、リソース全体の消費量、リソースを消費するクエリの上位、強制されたプランを持つクエリ、高バリエーションのクエリ、追跡したクエリが測定および監査されます。
クエリの自動調整
SQL Server のカーディナリティ推定 (CE) の役割は、クエリで返される行の数を推定することです。 クエリ オプティマイザーでは、CE の結果を使って実行プランが生成されます。 SQL Server 2014 までは、カーディナリティ推定はほとんど変更されていません。 SQL Server 2014 のリリースでは、CE のアルゴリズムが見直されて、予測が向上し、最も速いクエリ結果が提供されるようになりました。 ただし、データベースの互換レベルによって、使用される CE が決まります。
SQL Server 2014 以降ではクエリのパフォーマンスが大幅に向上しますが、互換レベルが更新されて移行されたデータベースでは、パフォーマンスが低下する可能性があります。 自動クエリ調整は SQL Server 2017 で導入されました。 クエリ ストアと共に使用して、低下したクエリを発見し、自動的に修正することができます。 データベースを移行し、以前の互換レベルでデータベースのワークロードをキャプチャした後、互換レベルをアップグレードして、クエリ ストアの実行を続けます。
自動調整では、クエリ ストアのデータを使用して、互換レベルを移動した後でカーディナリティ推定における変更のために低下し始めたクエリが検索されます。 自動調整では低下したクエリでパフォーマンス向上のための実験が行われるので、改善されたプラン ガイドを作成できます。
SQL Server 2019 でクエリのパフォーマンスを調整する方法
法律事務所の例では、データベース サーバーを SQL Server 2019 にアップグレードしたため、クエリ ストアと自動調整の両方を使用して、パフォーマンスを最適化し、互換レベルの変更によって低下が発生しないようにすることができます。 次のプロセスをお勧めします。
- データベース サーバーを SQL Server 2019 にアップグレードしますが、互換レベルは変更しません。
- クエリ ストアを有効にします。
- クエリ ストアで通常の業務時間全体のパフォーマンス データを収集できるようにします。
- データベース互換レベルをアップグレードします。
- クエリ ストアで低下が検出されたかどうかを確認します。 その場合は、最後に確認された良好なクエリ プランを強制して、より高いパフォーマンス レベルに戻すことができます。
クエリ ストアでは、クエリのパフォーマンスを監視するプロセスが自動化されます。 これは、バックグラウンドで実行して、データベースが可能な限り高速に結果を継続的に提供できるようにするための、便利なツールです。 クエリ ストアと組み合わされたクエリ調整アシスタント (QTA) では、クエリ ストアのデータを利用して、アップグレードされたデータベース内で低下したクエリが検索されます。
データベースを移行し、互換レベルを SQL Server 2014 以降にアップグレードした後は、新しいカーディナリティ推定を使って、クエリによって返される行数が推測されます。 QTA を使用して、新しい CE への切り替えによって低下したクエリを探して修正します。