SE の雑記

SSMS からクエリを実行した場合と、アプリケーションからクエリを実行した場合の違いとして、Qiita で書いた アプリから動かすと遅いが SSMS から動かすと速いのは ARITHABORT の違いという情報の動作を理解する というような動作があります。

SET オプションが異なっていると、異なる実行プランとしてキャッシュが行われるため、SSMS とアプリケーションで同一のオプションを使用するというのが記載した内容となります。

この投稿では、「SET ARITHABORT OFF」を SSMS から実行し、アプリケーションと同一の SET オプションの状態とすることで SSMS とアプリケーションで同一のプランキャッシュを使用していました。

クエリの多重実行と SSMS から実行したクエリで同一のプランキャッシュを使用したかったので「SQLQueryStress」と「SET ARITHABORT OFF に設定した SSMS」でクエリを実行したところ、同一のプランキャッシュが使用されないという事象が発生しました。

そこで、同一の SET オプション以外にどのような要因が同一のプランキャッシュが使用されないことにつながっているのかを確認してみました。

SET オプション以外で別クエリとしてキャッシュされる要因

冒頭に記載した通り、SSMS の既定の設定とアプリケーションから実行した場合の既定の設定では SET オプションが異なっています。

select 
    session_id, client_interface_name, quoted_identifier, arithabort, ansi_null_dflt_on, ansi_defaults, ansi_warnings, ansi_padding, ansi_nulls 
from 
    sys.dm_exec_sessions 
where
	session_id = @@spid or program_name = 'SQLQueryStress'

SSMS では既定では ARITABORT が有効となっています。

「SET ARITHABORT OFF」を実行することで、SQLQueryStress と同様の設定となります。

同一の SET オプションとなった状態で、SSMS / SQLQueryStress から同一のクエリを実行しても異なる実行プランとしてキャッシュがされてしまっていました。(1 回の実行が SSMS / 複数回の実行が SQLQueryStress となります)

select creation_time, last_execution_time,plan_generation_num, execution_count,statement_context_id, s.language_id,l.alias
from sys.dm_exec_query_stats	
outer apply sys.dm_exec_query_plan(plan_handle)
left join sys.query_context_settings as s on s.context_settings_id = statement_context_id
left join  sys.syslanguages as l on l.langid = language_id
where sql_handle in(
	0x03000D0078677409BBEA1F015CB1000001000000000000000000000000000000000000000000000000000000
)
order by last_execution_time desc
GO

実際に取得した情報が以下となりますが、同一の SQL ハンドルのクエリですが、異なる実行プランとしてキャッシュされていることが確認できます。

この違いですが、SSMS と SQLQueryStress の言語設定の違いにより発生していました。

プランキャッシュですが、キャッシュ単位で statement_context_id というコンテキスト情報を持っています。どのような設定になっているかは sys.query_context_settings で確認ができますが、今回のケースであれば言語設定の違いによって異なるコンテキストが設定されており、別のプランキャッシュとなっていました。

• SSMS: Japanese
• SQLQueryStress: English

この差により、異なる実行プランとしてキャッシュが行われていました。

今回のケースであれば、対象となるクエリを実行する際に SSMS で次のように実行することで同一の実行プランが利用される状態にすることができます。

SET LANGUAGE 'us_english'
GO
[usp_compiletest]

SSMS とアプリケーションで同一の実行プランを使用する場合には、SET オプションを同一にするというのは、有名な内容となると思いますが、それ以外の設定により異なるプランがキャッシュされるケースがあるというのも覚えておくとよいのではないでしょうか。