SE の雑記

SQL Server の情報をメインに Microsoft 製品の勉強内容を日々投稿

SQL Server 2008 以降の sys.dm_exec_cached_plans の拡張について

leave a comment

メモリ上にキャッシュされているクエリのプランを確認するための動的管理ビュー (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],
    [cacheobjtype],
    [objtype],
    COUNT(*) AS [Cache Count]
FROM
    [sys].[dm_exec_cached_plans]
GROUP BY
    [pool_id],
    [cacheobjtype],
    [objtype]

 

pool_id とプロシージャキャッシュのタイプ別にどのくらいの件数がキャッシュされているかを確認するクエリになります。
実行結果がこちらです。
image

pool_id 別の利用状況の概要が取得できました。
この pool_id ですが、リソースガバナーのプール名と対応します。
実際の GUI だとこの部分ですね。
image
SQL で情報を取得したい場合には、[sys.dm_resource_governor_resource_pools] から情報を取得することができます。

先ほどのクエリを以下のように変更することで pool_id を名称に変換することが可能となります。

SELECT
    [name],
    [cacheobjtype],
    [objtype],
    COUNT(*) AS [Cache Count]
FROM
    [sys].[dm_exec_cached_plans]
    LEFT JOIN
        [sys].[dm_resource_governor_resource_pools]
    ON
        [sys].[dm_exec_cached_plans].[pool_id] = [sys].[dm_resource_governor_resource_pools].[pool_id]

GROUP BY
    [name],
    [cacheobjtype],
    [objtype]
ORDER BY
    [name]

image

ResPool1 というリソースプールを追加し、そのリソースプールに割り当てられるようにワークロードグループに追加したログインでアドホッククエリを実行した結果がこちらになります。
image

プロシージャキャッシュがリソースプールを意識して管理されているのが確認できますね。

 

■プールを指定したプロシージャキャッシュのクリア


SQL Server のテストをしたいといったときには、バッファプールやプロシージャキャッシュをクリアするという操作をすることがあるかと思います。

その時には以下の DBCC コマンドを使用してクリアができます。

DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE

SQL Server 2008 以降は FREEPROCCACHE が拡張され、特定のプランやリソースプールを指定してプロシージャキャッシュのクリアが可能となっています。
# SQL Server 2005 まではプロシージャキャッシュ全体のクリアしかできませんでした。
また、DBCC FREESYSTEMCACHE にもリソースプールの指定が追加されています。

以下のような状態になったとします。
image

[ResPool1] が大量のアドホッククエリをキャッシュしていることが確認できます。

SQL Server 2005 まではこのような状態でキャッシュをクリアしたいといった場合にはプロシージャキャッシュ全体をクリアする必要がありましたが、SQL Server 2008 以降は以下のようなクエリで対象のリソースプールを限定(またはプランを限定) してキャッシュをクリアすることが可能となっています。
# 2005 まではリソースガバナーがなかったのでできなかったのですが…。

DBCC FREEPROCCACHE(‘ResPool1’)
DBCC FREESYSTEMCACHE(‘ALL’, ‘ResPool1’)

実行前のキャッシュの状態がこちらです。
image

[default] と [ResPool1] にアドホッククエリがキャッシュされています。
2005 以前の FREEPROCCACHE や FREESYSTEMCACHE はリソースプールを指定できませんので、キャッシュ全体をクリアしてしまいますが、2008 以降はリソースプールを指定できますのでこのようなクリアができるようになります。
image

[default] のキャッシュ状況は変わっていませんが、[ResPool1] のキャッシュのみがクリアされていることが確認できますね。

以前から書こうと思っていたのですが、すっかり忘れていた内容をやっと投稿できました。

Written by masayuki.ozawa

6月 30th, 2011 at 7:21 pm

Posted in SQL Server

Tagged with

Leave a Reply

*