BLOG main image
분류 전체보기 (45)
SQL Server 이야기 (45)
일상 (0)
Visitors up to today!
Today hit, Yesterday hit
daisy rss
tistory 티스토리 가입하기!
2009. 2. 9. 21:11

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


[결과]

 

송 혁, SQL Server MVP
sqler.pe.kr // sqlleader.com
hyoksong.tistory.com