メモリ上にキャッシュされているクエリのプランを確認するための動的管理ビュー (DMV) として [sys.dm_exec_cached_plans] があります。
SQL Server 2005 から実装されている DMV になるのですが、SQL Server 2008 になって列が拡張されています。
今回は、この拡張された列についてまとめてみたいと思います
■pool_id の追加
SQL Server 2008 になり、この DMV には [pool_id] という列が追加されています。
以下は BOL から pool_id の説明を抜粋したものになります。
列名 | データ型 | 説明 |
pool_id | int | このプランのメモリ使用量の大部分を占めるリソース プールの ID。 |
[リソース プールの ID] と書かれているように、この列は 2008 で追加されたリソースの調整機能である [リソース ガバナー] と関連性があります。
まずは、sys.dm_exec_cached_plans から以下のクエリでデータを取得してみたいと思います。
SELECT |
pool_id とプロシージャキャッシュのタイプ別にどのくらいの件数がキャッシュされているかを確認するクエリになります。
実行結果がこちらです。
pool_id 別の利用状況の概要が取得できました。
この pool_id ですが、リソースガバナーのプール名と対応します。
実際の GUI だとこの部分ですね。
SQL で情報を取得したい場合には、[sys.dm_resource_governor_resource_pools] から情報を取得することができます。
先ほどのクエリを以下のように変更することで pool_id を名称に変換することが可能となります。
SELECT GROUP BY |
ResPool1 というリソースプールを追加し、そのリソースプールに割り当てられるようにワークロードグループに追加したログインでアドホッククエリを実行した結果がこちらになります。
プロシージャキャッシュがリソースプールを意識して管理されているのが確認できますね。
■プールを指定したプロシージャキャッシュのクリア
SQL Server のテストをしたいといったときには、バッファプールやプロシージャキャッシュをクリアするという操作をすることがあるかと思います。
その時には以下の DBCC コマンドを使用してクリアができます。
DBCC DROPCLEANBUFFERS DBCC FREEPROCCACHE |
SQL Server 2008 以降は FREEPROCCACHE が拡張され、特定のプランやリソースプールを指定してプロシージャキャッシュのクリアが可能となっています。
# SQL Server 2005 まではプロシージャキャッシュ全体のクリアしかできませんでした。
また、DBCC FREESYSTEMCACHE にもリソースプールの指定が追加されています。
[ResPool1] が大量のアドホッククエリをキャッシュしていることが確認できます。
SQL Server 2005 まではこのような状態でキャッシュをクリアしたいといった場合にはプロシージャキャッシュ全体をクリアする必要がありましたが、SQL Server 2008 以
降は以下のようなクエリで対象のリソースプールを限定(またはプランを限定) してキャッシュをクリアすることが可能となっています。
# 2005 まではリソースガバナーがなかったのでできなかったのですが…。
DBCC FREEPROCCACHE(‘ResPool1’) |
[default] と [ResPool1] にアドホッククエリがキャッシュされています。
2005 以前の FREEPROCCACHE や FREESYSTEMCACHE はリソースプールを指定できませんので、キャッシュ全体をクリアしてしまいますが、2008 以降はリソースプールを指定できますのでこのようなクリアができるようになります。
[default] のキャッシュ状況は変わっていませんが、[ResPool1] のキャッシュのみがクリアされていることが確認できますね。
以前から書こうと思っていたのですが、すっかり忘れていた内容をやっと投稿できました。