ストアドプロシージャをSSMSで実行すると速いのに、プログラムから呼び出して実行すると何故か遅い、、、、という現象がおきました。
原因は異なるクエリプランが作成、使用されたことでしたが、なんで同一プロシージャに対して二つのプランが作られるのか、、、なかなかググっても出てきません。
DMVを使って、特定のクエリの実行プランを取得するSQL
select top 100 * from sys.dm_exec_cached_plans
cross apply sys.dm_exec_sql_text(plan_handle) ss
--cross apply sys.dm_exec_query_plan(plan_handle) -- プランもみたいときはコメント外す
cross apply sys.dm_exec_plan_attributes(plan_handle) sb -- 細かいメタデータ見なくていいときはコメントアウト
where text like '%ストアド名とか%' -- ここで絞込み
and ss.dbid = DB_ID('DB名') -- DBID指定
同じSQLなのに異なる実行プランが生成されるということは、実行時になんらかの差異が生じているのだろうと思い、いろいろ調べたところ、原因はSETオプションの違いでした。
SETオプションの違い(この記事にたどり着くためのグーグル検索がなかなか大変でした）
http://gurizou94.blog.fc2.com/blog-entry-187.html
SSMSだとSet ARITH ABORT ON がデフォルトで、プログラムからの呼び出しはOFFがデフォルトのためでした。
「マネジメントスタジオで実行して速いのにASPから実行すると遅い」といった現象でお困りの方は試す価値があると思います。
ストアドプロシージャでなく、adhocクエリでも同様の現象を確認できたので、
速度を要求されるクエリの検証をSSMSで行う際は、
Set ARITHABORT OFF
--ここにクエリとかストアドのEXEC文。アプリケーションから呼ばれたときと同じプランが採用される
Set ARITHABORT ON
とすると、実際にWEBアプリから呼び出されるときと同じプラン、速度での確認ができるようです。
※正確には、ARITHABORTじゃなくてもANSI_NULLS ONとかQUAOTED_IDENTIFIERの値が違ってもNGですが、SSMSとプログラム側とのデフォルト値が異なるのがARITHABORT だけなので、基本的にARITHABORTだけ気をつければ大丈夫そうです。