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
얼마전에는 캐시된 SP의 XML플랜을 기반으로 컴파일 정보 확인 하는 것을 소개하였습니다.
이번에도 XML플랜을 이용하여 캐시된 플랜 중 FULL SCAN 하는 SP를 찾는 방법에 대해 소개합니다.
[성능 모니터 – FULL Scans/Sec]
위와 같이 운영하고 있는 서버에서 많은 FULL SCAN 수치가 보인다면,
해당 서버는 성능적으로 문제가 발생할 가능성이 있다고 생각해 볼 수 있습니다.
그렇다면 빠르고 쉽게 FULL SCAN하는 쿼리를 찾아 수정 하여야 할 것 입니다.
하지만, 기본적인 DMV로는 인덱스에 대한 SCAN 수에 대해서는 확인 할 수 있지만, SP에 대해서는 SCAN 여부 조차 확인 할 수 없습니다.
그래서 DMV로 확인 할 수 있는 XML플랜을 XQUERY를 사용하여 찾아보는 쿼리를 만들어 보았습니다.
with XMLNAMESPACES ('http://schemas.microsoft.com/sqlserver/2004/07/showplan' as sql)
SELECT * FROM
(
SELECT
db_name(qt.dbid) AS 'DB Name'
,qt.dbid
,OBJECT_NAME(qp.objectid,qp.dbid) as sp_name
,qt.text AS 'sp_text'
, 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 'Execution Count'
, ISNULL(qs.execution_count/DATEDIFF(Second, qs.creation_time, GetDate()), 0) AS 'Calls/Second'
, DATEDIFF(Minute, qs.creation_time, GetDate()) AS 'Age in Cache'
, ISNULL(qs.total_elapsed_time/qs.execution_count, 0) AS 'AvgElapsedTime(㎲)'
, qs.total_elapsed_time/1000.0/1000.0 AS 'TotalElapsedTime(sec)'
, max_elapsed_time /1000.0 AS 'maxelapsedTime(ms)'
, qs.total_worker_time/qs.execution_count AS 'AvgWorkerTime(㎲)'
, qs.total_worker_time AS 'TotalWorkerTime(㎲)'
, max_worker_time as 'max_worker_time(㎲)'
, ISNULL(qs.total_logical_reads/qs.execution_count, 0) AS 'AvgLogicalreads'
, total_logical_reads
, qs.max_logical_reads
, ISNULL(qs.total_physical_reads/qs.execution_count, 0) AS 'AvgphysicalReads'
, total_physical_reads
, qs.max_physical_reads
, ISNULL(qs.total_logical_writes/qs.execution_count, 0) AS 'AvglogicalWrites'
, qs.total_logical_writes
, qs.max_logical_writes
,text
,cast(query_plan as xml) as query_plan
FROM sys.dm_exec_query_stats as qs
CROSS APPLY sys.dm_exec_sql_text(plan_handle) as qt
CROSS APPLY sys.dm_exec_text_query_plan(qs.plan_handle,qs.statement_start_offset,qs.statement_end_offset) qp
)Y
CROSS APPLY
(
SELECT
c.value('(./@PhysicalOp)[1]','varchar(100)') as PhysicalOp
FROM Y.query_plan.nodes('//sql:RelOp')B(C)
)X
where PhysicalOp IN ('Table Scan','Index Scan')and
Y.dbid not in (1,2,3,4,32767)
[결과]
sqler.pe.kr // sqlleader.com
hyoksong.tistory.com
