SQL Serverクエリストアとは？設定方法から実行プランの強制まで徹底解説

Microsoft® SQL Server® のクエリストア（Query Store）は、その名のとおり、データベース上で実行されたクエリの履歴、ランタイム実行統計、実行プランを「保管庫」のように記録してくれる機能です。収集されたデータはディスク上に保存されるため、トラブルシューティングが必要になったときにいつでも参照でき、SQL Server を再起動してもデータは失われません。SQL Server 2016 で導入され、それ以降のすべてのエディションで利用可能なクエリストアを活用すれば、クエリプランの変更に起因するパフォーマンス問題の切り分けが格段に容易になります。

はじめに

パフォーマンスのトラブルシューティングではベースラインデータの分析が役立ちますが、クエリストアが登場する以前は、そのような情報を SQL Server がネイティブに提供していませんでした。データベースに対してクエリストアを有効化すると、実行されたクエリに関する情報が、実行プランやランタイム統計とともに保持されます。クエリストアはデータベース単位で有効化する仕組みで、すべてのユーザーデータベースおよび MSDB システムデータベースに対して有効にできます。

クエリストア関連の情報やメタデータは、対象データベース内部の内部テーブルに保存されるため、別途バックアップを管理する必要はありません。通常のデータベースバックアップに必要な情報はすべて含まれています。クエリストアのデータを閲覧するには View Database State 権限が必要で、プランの強制・解除を行うには DB_Owner 権限が必要です。データは SQL Server Management Studio および T-SQL のいずれからでも確認できます。

クエリストアの有効化手順

データベースに対してクエリストアを有効化するには、以下の手順を実行します。

1. データベースを右クリック →「プロパティ」を選択します。
2. 「ページの選択」で「クエリストア」を選びます。
3. 「全般」セクションで、「操作モード（要求）」を Off から ReadWrite に変更します。
4. その他のフィールドは、あらかじめ入力されている既定値のままにしておきます。
5. データベースプロパティ画面で 「OK」 をクリックすると、選択したデータベースでクエリストアが有効になります。

T-SQL を使ってクエリストアを有効化することもできます。以下のコードを使用してください。

ALTER DATABASE [DB_Name] SET QUERY_STORE = ON;

クエリストアの主な構成オプション

クエリストアには、以下のような構成オプションがあります。

• 操作モード（要求）：Off、ReadOnly、ReadWrite の3つの値があります。Read モードでは、新しい実行プランやクエリのランタイム統計は収集されません。このモードは、クエリストアに関連する読み取り専用操作のためのものです。ReadWrite に変更すると、選択したデータベースで実行されたクエリ、それらのクエリに使用された実行プラン、およびランタイム統計の収集が有効になります。

• データフラッシュ間隔（分）：収集した実行プランとクエリのランタイム統計を、メモリからディスクへ書き出す頻度を設定します。既定値は15分です。

• 統計収集間隔：クエリストア内で使用されるクエリのランタイム統計の集計間隔を定義します。既定値は60分です。

• 最大サイズ（MB）：クエリストアの最大サイズを構成します。既定値は100 MBです。クエリストアのデータは、クエリストアが有効化されているデータベース自体に保存されます。ここで設定したサイズに達すると、操作モードは自動的に ReadOnly に切り替わります。

• キャプチャモード：どの種類のクエリをクエリストアにキャプチャするかを選択できます。既定のオプションは All で、実行されたすべてのクエリを保存します。Auto に設定すると、優先度に応じてキャプチャ対象を振り分け、実行頻度の低いクエリやアドホッククエリなどを無視しようとします。

• 古いクエリのしきい値（日）：データがクエリストアに保持される期間を定義します。既定値は30日です。

クエリストアのレポート

クエリストアには、次のレポートが用意されています。

• 回帰したクエリ（Regressed Queries）：実行メトリクスが最近悪化した、または変化したクエリを特定します。
• 全体のリソース消費（Overall Resource Consumptions）：任意の実行メトリクスについて、データベース全体のリソース消費量を分析します。
• 上位リソース消費クエリ（Top Resource Consuming Queries）：データベースのリソース消費に最も大きな影響を与えているクエリを表示します。
• プランが強制されたクエリ（Queries with Forced Plans）：強制された実行プランを持つすべてのクエリを表示する組み込みレポートです。
• 変動の大きいクエリ（Queries with High Variation）：パラメーター化の問題が最も頻繁に発生しているクエリを表示します。
• 追跡対象クエリ（Tracked Queries）：最重要クエリの実行状況をリアルタイムで追跡します。

クエリストアによる実行プランの強制

昨日までは正常に動作していたクエリが、今日になって実行に時間がかかったり、過剰なリソースを消費したり、タイムアウトしたりすることがあります。これはプランの回帰（plan regression）が原因です。既定では、SQL Server はクエリごとに最新の実行プランのみを保持します。スキーマ、統計情報、インデックスに何らかの変更が加わると、クエリオプティマイザーが使用する実行プランが変わる可能性があります。また、プランキャッシュのメモリ圧迫によってプランが削除されることもあります。

クエリストアは、監視対象の各データベース内に、時間経過に伴って集約されたクエリプランと統計情報を保存します。プランキャッシュとは異なり、クエリストアは1つのクエリに対して複数のプランを保持でき、プランごとの統計情報とともにクエリプランの変更履歴を維持します。複数の実行プランの中から選択し、任意の実行プランを強制することができます。以降のクエリ実行では、クエリオプティマイザーはこの強制された実行プランのみを使用します。

「クエリストア → 上位リソース消費クエリを開く」レポートには、リソースを大量に消費するクエリが一覧表示されます。ここで、調査対象のクエリを選択してみましょう。

「プラン」にマウスカーソルを合わせると、関連する統計情報が表示されます。

続いて、異なるプラン同士を比較してみましょう。

プラン216の詳細：

プラン195の詳細：

プラン216の方が平均実行時間が短いため、今後の実行ではこのプランを強制して使用できます。「プランの強制」をクリックすると、「クエリ42に対してプラン216を強制しますか？」という確認画面が表示されます。

「はい」をクリックします。プランが強制されると、次のスクリーンショットのようにチェックマーク付きで強調表示されます。以後、このプランはクエリオプティマイザーによって実行に使用されます。

クエリストア活用のベストプラクティス

• 最新の機能や拡張を利用できるよう、最新版の SQL Server Management Studio を使用しましょう。

• クエリストアによるデータ収集を検証し、監視しましょう。

• 最適なクエリのキャプチャモードを設定し、必要に応じてクエリストアの構成オプションを見直して調整しましょう。

• 非パラメーター化クエリの使用は避けましょう。

• 強制したプランの状態を定期的に確認しましょう。

まとめ

クエリストアは、SQL Server 2016 で導入された有用な機能です。パフォーマンスチューニングは、すべてのデータベース管理者（DBA）に求められる重要なスキルの一つであり、クエリストアの構成方法と使用方法を習得しておきましょう。クエリストアを使えば、パフォーマンスの変化を追跡し、実行プランを比較することでクエリパフォーマンスの劣化をトラブルシューティングできます。また、任意のクエリに対して実行プランを強制することも可能で、これによりプランキャッシュに保存されているプランを上書きし、パフォーマンス上のメリットを得られます。クエリストアは、後から参照できるようにクエリの実行統計やプランを記録・保存するためのものであり、SQL Server のパフォーマンスへの大きな影響はありません。

コメントやご質問がある場合は、フィードバックタブをご利用ください。

専門家による管理・運用・構成で環境を最適化

Rackspace のアプリケーションサービス（RAS）のエキスパートは、幅広いアプリケーションポートフォリオにわたって、以下のプロフェッショナルサービスおよびマネージドサービスを提供しています。

• eコマースおよびデジタルエクスペリエンスプラットフォーム
• エンタープライズリソースプランニング（ERP）
• ビジネスインテリジェンス
• Salesforce カスタマーリレーションシップマネジメント（CRM）
• データベース
• メールホスティングおよび生産性向上ソリューション

私たちが提供するもの：

• 偏りのない専門知識：即座に価値をもたらす機能に焦点を当て、モダナイゼーションの道のりをシンプルにし、ご案内します。
• Fanatical Experience™：「プロセスファースト、テクノロジーセカンド®」のアプローチと専任のテクニカルサポートを組み合わせ、包括的なソリューションを提供します。
• 比類のないポートフォリオ：豊富なクラウド経験を活かし、適切なクラウド上に適切なテクノロジーを選択・導入できるよう支援します。
• アジャイルなデリバリー：お客様の現在地に合わせて伴走し、私たちの成功をお客様の成功と一致させます。

今すぐチャットして始めましょう。