[SQL Server] FULL SCAN 하는 SP 찾기

 

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)

 

 

출처 : http://hyoksong.tistory.com/25