SQL Server SET OPTION 쿼리 성능 이슈

   

"어플리케이션에서는 쿼리가 느린데 SSMS에서 실행하면 빨라요"이런 상황이 종종 발생할 수 있다.

이의 원인중의 하나가 실행계획이 달라서 발생할 수 있다.

   

[데모]

-- 세션 55

use tempdb

go

 

create proc procname

as

    select * from sys.sysprocesses

 

 

-- 세션 55 쿼리 수행

exec procname

GO

select @@options

   

   

-- 세션 54 옵션 변경 후

SET ARITHABORT OFF

 

-- 세션 54 쿼리 수행

exec procname

GO

select @@options

   

   

위 처럼 세션의 옵션에 따라 @@OPTION 값이 달라진다.

이런 경우 실행계획은 어떻게 생길까?

아래와 같이 같은 프로시저지만 2개의 실행계획이 만들어진다.

select o.object_id, s.plan_handle, h.query_plan

from sys.objects o

inner join sys.dm_exec_procedure_stats s on o.object_id = s.object_id

cross apply sys.dm_exec_query_plan(s.plan_handle) h

where o.object_id = object_id('procname')

   

   

   

이것은 쉽게 세션창에서 확인이 가능하다.

   

그러나 어플리케이션에서 들어오는 쿼리에 대해서는 어떻게 확인할 수 있을까?

위에서 query_plan 필드에 보면 실행계획을 볼 수 있다.

실행계획의 속성을 보면 SET OPTION 을 확인 할 수 있다.

Arithabort 옵션을 확인하면 된다.

   

   

[참고문서]

sys.dm_exec_plan_attributes (Transact-SQL)

https://msdn.microsoft.com/en-us/library/ms189472.aspx

SELECT plan_handle, pvt.set_options, pvt.sql_handle  

FROM (  

    SELECT plan_handle, epa.attribute, epa.value   

    FROM sys.dm_exec_cached_plans   

        OUTER APPLY sys.dm_exec_plan_attributes(plan_handle) AS epa  

    WHERE cacheobjtype = 'Compiled Plan') AS ecpa   

PIVOT (MAX(ecpa.value) FOR ecpa.attribute IN ("set_options", "sql_handle")) AS pvt;  

GO