Statment 타입별 실행 빈도 및 리소스 사용량 확인

Statement.sql

SQL Server 2005부터 추가된 DMV를 통해 여러 가지 유용한 스크립트를 만들 수 있습니다.

이번에는 특정 DB 또는 모든 DB에 대해 SELECT, DELETE, INSERT 또는 UPDATE Statmemt가
어떠한 비율로 발생하는지 그리고 리소스별 어떠한 비율로 사용하는지 쉽게 확인 할 수 있는 쿼리 입니다.

지금까지 막연하게 "이 DB는 대부분이 SELECT가 발생하는 것이야."
이러한 생각을 구체적이고 명확하게 보여줄 수 있습니다.

처음 접하는 시스템을 분석시 위와 같은 자료가 있으면 보다 쉽게 파악 할 수 있을 것입니다.
제 생각에는 ~ 꽤 DB관련 업무에 유용하게 사용될 수 있을 것 같습니다.ㅎㅎ

아래는 수행한 결과 입니다.
ExecutionCount는 분당 실행한 횟수 입니다.

[로그성 DB시스템]  

[일반 OLTP DB시스템]

with XMLNAMESPACES ('http://schemas.microsoft.com/sqlserver/2004/07/showplan' as sql)

SELECT

             StatementType

             ,ExecutionCount

             ,cast((ExecutionCount*1.0/total_ExecutionCount)*100 as Decimal(18,2)) as [% ExecutionCount]

             ,cast((Elapsed_Time/total_Elapsed_Time)*100 as Decimal(18,2)) as [% Elapsed_Time]

             ,cast((Worker_Time/total_Worker_Time)*100 as Decimal(18,2)) as [% Worker_Time]

             ,cast((logical_reads/total_logical_reads)*100 as Decimal(18,2)) as [% logical_reads]

             ,cast((logical_writes/total_logical_writes)*100 as Decimal(18,2)) as [% logical_writes]

             ,cast((physical_reads/total_physical_reads)*100 as Decimal(18,2)) as [% physical_reads]

FROM

(

             SELECT

                           StatementType

                           ,sum(avg_ExecutionCount) over(partition by StatementType) as ExecutionCount

                           ,sum(avg_ExecutionCount) over(partition by 1) as total_ExecutionCount

                           ,sum(avg_total_Elapsed_Time) over(partition by StatementType) as Elapsed_Time

                           ,sum(avg_total_Elapsed_Time) over(partition by 1) as total_Elapsed_Time

                           ,sum(avg_total_Worker_Time) over(partition by StatementType) as Worker_Time

                           ,sum(avg_total_Worker_Time) over(partition by 1) as total_Worker_Time

                           ,sum(avg_total_logical_reads) over(partition by StatementType) as logical_reads

                           ,sum(avg_total_logical_reads) over(partition by 1) as total_logical_reads

                           ,sum(avg_total_logical_writes) over(partition by StatementType) as logical_writes

                           ,sum(avg_total_logical_writes) over(partition by 1) as total_logical_writes

                           ,sum(avg_total_physical_reads) over(partition by StatementType) as physical_reads

                           ,case when

                                        sum(avg_total_physical_reads) over(partition by 1) = 0

                                                     THEN NULL ELSE sum(avg_total_physical_reads) over(partition by 1)

                           END        as total_physical_reads

             FROM(

                           SELECT StatementType

                                        ,count(*) as cnt

                                        ,sum(ISNULL(ExecutionCount/DATEDIFF(MINUTE , creation_time,GetDate()),0)) as avg_ExecutionCount

                                        ,cast(sum(ISNULL([total_Elapsed_Time(sec)]/DATEDIFF(MINUTE , creation_time, GetDate()), 0)) as Decimal(18,2)) as avg_total_Elapsed_Time

                                        ,cast(sum(ISNULL([total_Worker_Time(ms)]/DATEDIFF(MINUTE , creation_time, GetDate()), 0)) as Decimal(18,2)) as avg_total_Worker_Time

                                        ,cast(sum(ISNULL([total_logical_reads]/DATEDIFF(MINUTE , creation_time, GetDate()), 0)) as Decimal(18,2)) as avg_total_logical_reads

                                        ,cast(sum(ISNULL([total_logical_writes]/DATEDIFF(MINUTE , creation_time, GetDate()), 0)) as Decimal(18,2)) as avg_total_logical_writes

                                        ,cast(sum(ISNULL(total_physical_reads/DATEDIFF(MINUTE , creation_time, GetDate()), 0)) as Decimal(18,2)) as avg_total_physical_reads

                           FROM (

                                                     SELECT

                                                                  db_name

                                                                  ,ISNULL([StatementType],StatementType2) as StatementType

                                                                  --,[statement_text]

                                                                  ,[creation_time]

                                                                  ,[ExecutionCount]

                                                                  ,[total_Elapsed_Time(sec)]

                                                                  ,[total_Worker_Time(ms)]

                                                                  ,[total_logical_reads]

                                                                  ,[total_logical_writes]

                                                                  ,[total_physical_reads]

                                                     FROM      (

                                                                                             SELECT

                                                                                                          db_name(qp.dbid) AS 'db_name'

                                                                                                          , qs.creation_time

                                                                                                          , qs.execution_count AS 'ExecutionCount'

                                                                                                          , qs.total_elapsed_time/1000.0/1000.0 AS 'total_Elapsed_Time(sec)'

                                                                                                          , qs.total_worker_time/1000.0 AS 'total_Worker_Time(ms)'

                                                                                                          , total_logical_reads

                                                                                                          , qs.total_logical_writes

                                                                                                          , total_physical_reads

                                                                                                          , cast(qp.query_plan as xml) as query_plan

                                                                                             FROM sys.dm_exec_query_stats AS qs

                                                                                                          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('(//sql:StmtSimple/@StatementType)[1]','VARCHAR(100)') AS "StatementType"

                                                                                ,c.value('(//sql:StmtCond/@StatementType)[1]','VARCHAR(100)') AS "StatementType2"

                                                                  FROM query_plan.nodes('//sql:QueryPlan')B(C)

                                                                  )xp

                                        WHERE db_id(db_name) > 4

                                        AND DATEDIFF(MINUTE,creation_time, GetDate()) > 0

                                        AND StatementType is not null

                           )X

                           group by StatementType

             )Y

)Z

 

 

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