SE の雑記

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

パフォーマンスモニターで見る SQL Server のプランキャッシュ

leave a comment

SQL Server のパフォーマンスモニターには [Plan Cache] という SQL Server のプランキャッシュに関するカウンターが用意されています。

このカウンターを使用して、SQL Server の SQL の実行プランをキャッシュしているプランキャッシュの状態を確認してみたいと思います。

■パフォーマンスモニターで見る SQL Server のプランキャッシュ


SQL Server のメモリの使用状況を調べるとき、[Memory Manager] というカウンターを確認することがあります。
このカウンターの中には、以下のカウンターがあります。

SQL Cache Memory (KB) サーバーが動的 SQL キャッシュのために使用している動的メモリの合計サイズを指定します。
Stolen Server Memory (KB) サーバーがデータベース ページ以外に使用しているメモリの量を指定します。

プランキャッシュのサイズを調べる際には Stolen Server memory を使用することがあるかと思います。

Stolen Server Memory はデータベース ページ (正確にはバッファキャッシュになると思いますが) から Stole (盗まれた) されたメモリを表しています。この盗む相手ですがプランキャッシュが該当します。
このため、Sotlen Server Memory を見ることでプランキャッシュで使用しているおおよそのメモリを確認することができます。

Memory Manager にはクエリに関するカウンターとして SQL Cache Memory があります。
ドキュメントを探してみたのですが、このカウンターの実体が見つからなかったので検証のレベルになるのですが、SQL を実行する際にメモリに格納される対象として [SQL 文] と [実行プラン] の 2 種類があります。

SQL を実行する際には実行ン時に生成される実行プランだけではなく、そのプランが生成されるために使用された SQL 文が必要となります。この時の SQL 文のキャッシュが SQL Cache Memory となるようです。

メモリが 32 GB の環境で可能な限り SQL のプランをキャッシュした状態がこちらになります。
image

キャッシュで使用されているメモリは [Cache Pages] から算出することができます。
SQL Server のページは 8KB が基本となりますので、Cache Pages の値に 8KB をかけたものがプランキャッシュで使用しているメモリとなります。
今回は 428,887 × 8 = 3,431,096 KB  = 3.4 GB となります。
# もう少しキャッシュできても良いはずなのですが、これが上限になってしまうのですよね。計算値上は 6 GB 程度キャッシュできるはずなのですが。

この時の Stolen Server memory を確認してみます。
Stolen Server memory の方が高い値を示していますが近い値となっています。
image

アドホッククエリをスタブプランでキャッシュさせるように設定を変更するとこのようになります。
image
image
Stolen Server Memory は 1.7 GB から上昇しなくなりました。
アドホッククエリをスタブプランとしてキャッシュさせた場合、初回の実行時には最小限のスタブプランがキャッシュされるため一度しか実行されないアドホッククエリが大量に実行されている場合のプランキャッシュを抑制することができるようになります。
# 2 回目のクエリ実行時に実行プランがキャッシュされるのでコンパイルの回数は多くなるはずですが。

このデータを取得した状況では 180 万程度のアドホッククエリを実行するようにしていましたが、16 万以上はキャッシュすることができませんでした。私が使用している環境ではおそらくここが上限値になっているのだと思います。
# SQL Cache Memory + Stolen Server Memory がプランキャッシュで使用可能なメモリとなっているのではと考えています。

スタブプランをキャッシュさせるようにした場合、キャッシュできるクエリの数が多くなり、SQL Cache Memory がスタブプランを使用しない場合と比較して上昇しているのでこの部分が SQL 文をキャッシュしている箇所なのではと思っています。
# 1 クエリで 8KB 程度使用するようです。

以下のようなクエリを実行してプランキャッシュを増やしてみます。

USE test
SET NOCOUNT ON
DBCC FREEPROCCACHE
GO
DECLARE @sql nvarchar(max)
DECLARE @i int = 3000
DECLARE @cd int
WHILE (@i <= 3900)
BEGIN
    SET @cd = 65
    WHILE (@cd <= 2000)
    BEGIN
        SET @sql = N’DECLARE @tmp nvarchar(max);’
        SET @sql += N’SELECT @tmp = N”’ + REPLICATE(NCHAR(@cd), @i)
        SET @sql += N””

        SET @sql += N’ FROM sys.objects’
        EXEC (@sql)

        SET @sql += ‘ WHERE name=@param’
        EXEC sp_executesql @sql, N’@param sysname’, N’@param=”A”’

        SET @cd += 1
    END
    SET @i += 1
END

このクエリは大量のアドホッククエリとパラメーター化クエリを実行しています。
このクエリを実行した場合の Plan Cache の状況がこちらです。
image

アドホッククエリとパラメーター化クエリのキャッシュは [SQL Plans] に含まれているのですが、パフォーマンスモニターからでは、どちらのクエリがキャッシュされているかは判断することができません。

SQL Plans が多い場合、アドホッククエリとパラメーター化クエリのどちらが多いかを判断するのが重要となってきますが、これはパフォーマンスモニターからは判断することができません。

これを判断するためには DMV から情報を取得する必要があります。
どの DMV を使用すればよいかという方法について、Community Open Day 2012 東京会場 の私のセッションでお話させていただく予定です。興味のある方がいらっしゃいましたらご参加いただけると幸いです。

Written by masayuki.ozawa

5月 22nd, 2012 at 11:43 pm

Posted in SQL Server

Tagged with

Leave a Reply

*