SQL Server는 호출되는 SP를 최초 매개변수를 기반으로 컴파일 하여, 플랜 캐시에 저장합니다.
플랜 캐시에 저장된 내용을 바탕으로 이후 SP가 호출되면 다시 컴파일 하지 않고 재사용하게 되어
전체적인 성능 향상에 도움을 주고 있습니다.
하지만 파라미터 스니핑으로 인해 전체적인 성능에 나쁜 영향을 줄 수 있습니다.
갑자기 멀쩡한 서버가 각각의 성능 수치가 급격히 증가되었다면,
파라미터 스니핑으로 잘못된 실행계획으로 처리되는 것이 원인일 수 있습니다.
이때 문제가 되는 SP는 프로파일러 또는 DMV를 통해 쉽게 확인할 수 있으나,
과연 어떠한 파라미터로 인해 잘못된 실행계획으로 풀려 문제가 되는지는 확인하기가 쉽지 않습니다.
XML 플랜을 사용하면 이러한 질문에 대한 대답을 얻을 수 있습니다.
XML 플랜에 아래와 같은 데이터가 있으며, 이 값을 XQUERY로 읽어오면 ~ 보다 쉽게
컴파일 때 사용한 매개변수 값을 확인 할 수 있습니다.
|
<ParameterList> |
SELECT
db_name,sp_name,[statement_text]
,[Column]
,ParameterCompiledValue
,[creation_time]
,[ExecutionCount]
,[query_plan]
FROM (
SELECT
db_name(qt.dbid) AS 'db_name'
, substring(qt.text, (qs.statement_start_offset/2)+1
, ((case qs.statement_end_offset
when -1 then datalength(qt.text)
else qs.statement_end_offset
end - qs.statement_start_offset)/2) + 1) as statement_text
, qs.creation_time
, qs.execution_count AS 'ExecutionCount'
, ISNULL(qs.execution_count/DATEDIFF(Second, qs.creation_time, GetDate()), 0) AS 'Calls/Second'
, DATEDIFF(Minute, qs.creation_time, GetDate()) AS 'Age in Cache(min)'
, cast(qp.query_plan as xml) as query_plan
,OBJECT_NAME(qp.objectid,qp.dbid) as sp_name
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt
CROSS APPLY sys.dm_exec_text_query_plan(qs.plan_handle,qs.statement_start_offset,qs.statement_end_offset) qp
)X OUTER APPLY
(
SELECT
c.value('(@Column)[1]','varchar(100)') AS [Column]
,c.value('(@ParameterCompiledValue)[1]','varchar(1000)') AS ParameterCompiledValue
FROM query_plan.nodes('
declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/showplan";//ParameterList/ColumnReference')B(C)
)xp
송 혁, SQL Server MVP
sqler.pe.kr // sqlleader.com
hyoksong.tistory.com