얼마전에는 캐시된 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
