SQL Server 2014 CTP2 で追加された内容について、少しずつ見ていきたいと思います。
今回はIn-Memory OLTP で利用できる、ネイティブコンパイルされたストアドプロシージャーの実行統計の収集について。
Enabling Statistics Collection for Natively Compiled Stored Procedures
BOL には、以下のように記述されています。
Collection of execution statistics for natively compiled stored procedures is off by default. This is because statistics collection and aggregation affects performance.
ネイティブコンパイルされたストアドプロシージャの実行統計ですが、パフォーマンスへの影響を考慮し、デフォルトではオフになっているようです。
ここでいう実行統計ですが、以下のようなストアドプロシージャーやクエリの実行状態を取得するための DMV の情報となるようです。
それぞれの DMV での情報収集を有効にするためのストアドプロシージャーが用意されています。
- sys.sp_xtp_control_proc_exec_stats → sys.dm_exec_procedure_stats
- sys.sp_xtp_control_query_exec_stats → sys.dm_exec_query_stats
これらのストアドプロシージャは収集を有効にするだけでなく、設定の状態を見ることもできます。
declare @c bit exec sp_xtp_control_proc_exec_stats @collectionstatus=@c output select @c as 'collection status' exec sp_xtp_control_query_exec_stats @collectionstatus=@c output select @c as 'collection status'
デフォルトの状態ではどちらも 0 (無効) となっています。
この状態でネイティブコンパイルされたストアドプロシージャを実行してみます。
exec usp_MemTable GO 50
DMV から以下のようなクエリで情報を取得してみます。
select execution_count, database_id,object_id , sql_handle, plan_handle from sys.dm_exec_procedure_stats order by execution_count desc select execution_count,text, sql_handle, plan_handle from sys.dm_exec_query_stats cross apply sys.dm_exec_sql_text(sql_handle) order by execution_count desc
今回は 50 回ストアドプロシージャを実行しているのですが、対象のクエリは表示されていないですね。
それでは実行統計の収集を有効にしてみます。
exec sys.sp_xtp_control_proc_exec_stats @collectionvalue=1 exec sys.sp_xtp_control_query_exec_stats @collectionvalue = 1
有効にしたら再度ストアドプロシージャを実行してみます。
有効後に実行した回数の情報が取得できていますね。
ここで面白いなと思ったのが、plan_handle が 0x0 になっているところでしょうか。
この辺はネイティブコンパイルされたストアドプロシージャの特徴になるのでしょうね。
# 通常の SQL と異なり DLL にコンパイルされているので。
実際の実行プランは取得できませんが、推定実行プランや [SET SHOWPLAN_XML ON] を指定してのプラン表示はできますので、これで推定プランをを見る感じでしょうか。
なお、収集の設定はサービスを再起動すると 0 (無効) になるようですので、基本スタンスとしては必要な時だけ明示的に有効にするという感じでしょうか。