SE の雑記

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

SSMS でストアドプロシージャを実行した際の Cache Miss の発生

leave a comment

SSMS でストアドのコンパイル発生の状況を確認していたところ、実行のたびにパフォーマンスモニタ上でコンパイルが発生していました。

初回の実行時にコンパイルイベントが発生しプランが生成され、2 回目以降はコンパイルイベントが発生していないのは確認をしていたのですが、パフォーマンスモニタのコンパイルに関しては実行のたびにカウンタがあがっている状態でした。

この挙動について少しまとめてみたいと思います。

今回の投稿では以下の情報を参考にさせていただいています。
Cache Miss in Profiler… Continued from another thread..
SQL Server の compile ロックが発生したブロックの説明

■SSMS でストアドプロシージャを実行した時の Cache Miss の発生について


一度実行したストアドプロシージャを SSMS で再度実行してみます。
通常の動作を考えてみると、一度実行してプロシージャキャッシュにプランが格納されているため、キャッシュにヒットしてストアドプロシージャが実行されるかと思います。

実際このような動作になっているかをプロファイラを使用して確認してみます。

プロファイラでは以下のイベントを取得しています。
image

Showplan XML For Query Compile クエリがコンパイルされプランが生成されたときに発生
SP:CacheHit 実行したストアドプロシージャがキャッシュに存在する
SP:CacheMiss 実行したストアドプロシージャがキャッシュに存在しない

 

これらのイベントを取得してイベントの発生状況を確認します。
まずは、キャッシュがされていない状況でストアドプロシージャを実行してみます。
image

最初に [DBCC FREEPROCCACHE] を実行してプロシージャキャッシュクリアし、そのあとに [sp_configure] を実行しています。
プロシージャキャッシュをクリアしているので初回の実行時には、[CacheMiss] が発生しています。
プロシージャキャッシュ上にプランがキャッシュされていないので、実行時には、[Query Compile] が発生し、クエリのコンパイルが行われプランが生成されます。

それではプロシージャキャッシュをクリアしないでもう一度同じストアドプロシージャを実行してみます。
image

この場合、[CacheMiss] が発生して、[CacheHit] となりキャッシュにヒットしていますのでクエリのコンパイルイベントは発生していません。

ストアドプロシージャを繰り返し実行するようにして、パフォーマンスモニタでどのような状態となるかを確認してみます。
image

image

バッチリクエスト数に合わせて、コンパイルが発生していることが確認できます。

通常、二回目のストアドプロシージャの実行ではキャッシュにヒットするため、コンパイルは発生しないと思うのですが SSMS で実行した場合はパフォーマンスモニタではストアドプロシージャの実行に合わせてコンパイルが発生していることが確認できます。

プロファイラではコンパイルのイベントは発生していないためプランの生成は行われていないのですが、コンパイルとしてカウントされています。

このような現象が発生する原因なのですが、SSMS でクエリを実行した際に指定されているオブジェクトタイプに起因しています。

SSMS はアドホッククエリ / パラメーター化クエリ / ストアドプロシージャを一つのウィンドウで実行することができます。
このような種類を選ばないで実行できるようにするために、クエリエディタでクエリを実行した場合オブジェクトタイプは [20801 – AQ] として実行されています。
このタイプはアドホッククエリとなります。

このオブジェクトタイプはプログラムからクエリを実行する際に [CommandType = "Text"] を指定した場合と同等のようです。
ストアドプロシージャを実行する場合は [CommandType = "StoredProcedure"] として実行をするのが一般的だと思います。

SSMS から実行したクエリはオブジェクトタイプがアドホッククエリとして実行されるため、ストアドプロシージャをアドホッククエリとして実行されたという認識になり、オブジェクトタイプの不一致により [CacheMiss] が発生し、パフォーマンスモニタではコンパイルとしてカウントがあがってくるようです。

PowerShell で以下のようなコードを実行してみます。

$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = "Server=.SQL2012;Database=TEST2;Integrated Security=True"
$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
# $sqlcmd.CommandType = "Text"
$sqlcmd.CommandType = "StoredProcedure"
$SqlCmd.CommandText = "dbo.sp_stub"
$SqlCmd.Connection = $SqlConnection
for($i=0 ; $i -lt 1; $i++){
$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter.SelectCommand = $SqlCmd
$DataSet = New-Object System.Data.DataSet
$SqlAdapter.Fill($DataSet)
}
$SqlConnection.Close()

このコードでは [CommandType] を [StoredProcedure] として指定してストアドプロシージャを実行しています。
この場合、プロファイラでは以下のような情報が取得できます。
image

[ObjectType] が [8272 – P] のストアドプロシージャとして実行されていることが確認できます。
ストアドプロシージャを [CommandType = "StoredProcedure"] として実行しているのでオブジェクトタイプの不整合は発生せずに [CacheMiss] は発生せずにキャッシュヒットしています。

これをループカウントを増やして実行し、パフォーマンスモニタでデータを取得してみます。
image

この場合、バッチ実行に合わせてコンパイルは発生していないので、想定している動作となっています。

[CommandType = "Text"] にすると SSMS で実行した場合と同じデータの取得になります。
image

バッチリクエストに合わせてコンパイルが頻繁に発生している場合、

  • キャッシュにヒットしないアドホッククエリが実行されている
  • 実行されているクエリとオブジェクトタイプが一致していないためプランの生成が発生していない [CacheMiss] が発生

の可能性が考えられます。

前者に関してはプランキャッシュにおけるアドホッククエリの割合を見ることで判断ができますが、後者の場合はプロファイラで [CacheMiss] [QueryCompile] [CacheHit] の相関を見る必要が出てきます。

バッチリクエストに合わせてコンパイルが発生している場合、アドホッククエリだけでなくオブジェクトタイプの不一致による [CacheMiss] の可能性も検討したほうが良さそうですね。

Written by masayuki.ozawa

4月 10th, 2012 at 8:46 pm

Posted in SQL Server

Tagged with

Leave a Reply

*