Edited at

SQLServer: SSMSで実行して速いのにプログラムから実行すると遅いストアド

More than 1 year has passed since last update.

ストアドプロシージャを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だけ気をつければ大丈夫そうです。