General availability: Azure SQL Database Query Store でアナウンスされていますが、SQL Database でクエリ ストアが GA したので、仕様も一通り固まったのかなと思い、触ってみました。
Monitoring Performance By Using the Query Store のドキュメントが動作としては一番詳しい感じでしょうか。
Azure SQL Database Query Performance Insight もクエリ ストアの情報がベースとなっています。
Query Performance Insight は、
の DTU 使用状況と、クエリ ストアで取得されているクエリ情報の上位 3～ 10 件を表示している形になるかと。
SQL Server 2016 の SSMS を使用しなくても、ポータルから見れるのが利点にありそうですね。
SQL Server 2016 の SSMS を使用している場合は、クエリ ストアの操作は SSMS からできますので、機能を使用する場合は 2016 の SSMS を使用するのがよいかと思います。
それ以前のバージョンの SSMS で使用する場合には、SQL での管理となります。
今回は SSMS での操作をベースに。
■クエリ ストアって？？
クエリ ストアは、その名の通り、クエリの情報をストアするための機能となります。
情報の取得方法については、SQL Server 2016 CTP Technical Deep Dive のスライドがわかりやすいかと思います。
SQL が実行された際に、
- コンパイル情報をプランストアに格納
- 実行情報情報を実行統計に格納
- 非同期でクエリ ストアのスキーマにフラッシュ
することで、クエリ情報を保存するものになります。
今までの SQL Server / SQL Database ですと、
- キャッシュの情報からクエリ情報を確認
- SQL Server であれば、データコレクション から特定の時間帯のクエリ情報を確認
というような確認をすることができました。
これまでの情報確認の問題点としては、
- キャッシュ情報からの確認は SQL Server が再起動するとクリアされてしまう
- データコレクションはクエリの実行情報を永続化することができるが、クエリの実行プランがどのように変化していったかという観点で追うことができない
- データコレクションのデータベースのメモリ消費が、ユーザーデータベースのメモリ使用量に影響を与える可能性がある
というようなことがありました。
キャッシュの情報については、メモリ内ですのでサービスを再起動するとクリアされるのは通常の動作ですね。
データコレクションについては特定の時間帯に実行されていた負荷の高いクエリという観点で情報を見ることはできるのですが、「このクエリの実行プランの推移を見たい」という観点は不得手でした。
また、データコレクションを長時間実行していると、データコレクションのデータベースで使用されているメモリ消費も無視できなくなってくるため、他のインスタンスから情報を取得することを検討する必要があるというようなこともありました。
クエリ ストアでは、
- 特定のクエリのリソース消費 / 実行プランの推移を確認することができる
- クエリ ストアのサイズを指定することで、情報取得によるメモリ圧迫を抑えることができる
- 実行情報のリソース使用状況の統計は特定の間隔で集計されるため、時系列でリソース使用状況を確認できる
というような特徴があるかと。
クエリ ストアの特徴として、あるクエリで実行プランが変化したときに、変化前 / 変化後の情報を比較できるというものがあります。
これによりクエリのコンパイルにより実行プランが変化し、処理性能に影響を与えたのかを確認することができます。
また、クエリ ストアはストアのサイズを設定する必要があります。
このサイズの設定により、クエリ ストアで使用されるデータサイズ / メモリサイズを制御することが可能です。
クエリ ストアで実施できないこととしては、
- クエリの情報に特化しているため、データコレクションのようなパフォーマンスモニターやディスク使用量というような情報を取得することができない
- データベース単位に有効にし、有効にしたデータベース内に情報が格納されるため情報収集用のインスタンスでデータを一元管理することはできない
- クエリ ストアの情報のみをバックアップして、他の環境で解析をするには、クエリ ストアのスキーマの情報をエクスポートする必要がある
というようなことが考えられますので、この辺は意識しておいた方がよいかと思います。
■クエリ ストアを使用する際に気を付けておきたいこと
クエリ ストアを触っていていくつか気づいた点を。
まず、クエリ ストアのサイズですが、デフォルトでは 100MB になっているかと思います。
クリーンアップの期間内で、このサイズに収まっていれば問題はないのですが、最大サイズに達した場合、操作モードが「読み取り専用」となり、新しい情報が追加されなくなります。
これについては、 sys.database_query_store_options にも記載されており、最大サイズに達すると、読み取り専用モードになります。
そのため、「古いクエリのしきい値」や「サイズ ベース クリーンアップ モード」「最大サイズ」を調整し、適切な期間のデータがクエリ ストアに随時書き込みが行われるような設定にする必要が出てきます。
読み取り専用となった場合は、データの削除や最大サイズを変更した後に、以下のようなクエリで操作モードを再度変更すれば読み取り専用は解除されます。
# 試した感じでは自動で解除はされないようでした。
ALTER DATABASE <DB 名> SET QUERY_STORE (OPERATION_MODE = READ_WRITE)
最大サイズについては、ディスク/メモリ消費量に直結しますので、このサイズをあまり増やしたくないというようなケースもあるかと思います。
そのような場合は「クエリ ストア取り込みモード」を「すべて」ではなく、「自動」にすることで、実行回数とリソース消費量に基づく取り込みとすることができますので、実行回数/リソース消費量が低いクエリの取り込みを抑えることで、クエリ ストア内に格納されるデータ量を調整することができるようになります。
この設定を使うかどうかを検討するのは、「アドホック クエリ」が多いようなパターンでしょうか。
デフォルトの設定では「すべて」となっていますので、クエリ ストアには実行されたすべてのクエリが格納されます。
アドホック クエリについては類似クエリがすべて異なるクエリとして格納されますので、頻繁にアドホック クエリが実行されている環境などでは、アドホック クエリの情報だけでクエリ ストアの最大サイズに達してしまう可能性があります。
# 単純なクエリで試したところ、100MB の最大サイズで、「37,000 個」程度のクエリは格納することはできていました。
このような場合はすべてのクエリを格納するのではなく「自動」にして、特筆すべきクエリのみにするというのも手かと思います。
SQL Server 2008 以降では「optimize for ad hoc workloads」というオプションがあり、アドホック クエリで使用されるキャッシュを抑える設定があります。
この設定とクエリ ストアについて、Best Practice with the Query Store で触れられていますが、上記の設定をしていてもクエリ ストアは、
- コンパイル時
- 実行時
の 2 タイミングで情報の取得を行いますので、一度しか実行されないアドホッククエリについても情報が格納されていました。
また、RECOMPILE オプションを指定したクエリについても情報が格納されているようでしたので、キャッシュされなくても実行されたクエリはすべて取得されると考えていた方がよさそうですね。
アドホック クエリが頻繁に実行される環境下では、一日データを取得してみて、どのようなデータが蓄積されているかを確認するのがよさそうですね。
クエリ ストアのデータについては、システムビュー経由でダイレクトにアクセスすることが可能です。
Query Store Catalog Views (Transact-SQL)
データを持ち帰って解析したい場合などは、BCP でこれらのビューのデータをエクスポートする感じでしょうかね。
クエリ ストアは、クエリの実行状況を確認するための強力な機能となります。
無負荷というわけにはいきませんが、大きな負荷はかからずに取得できる仕組みとなっていますので、SQL Database / SQL Server 2016 を使う際には、有効に使っていきたいですね。