#title Statment 타입별 실행 빈도 및 리소스 사용량 확인
[[TableOfContents]]

==== 참고 ====
 * ExecutionCount: 분당 실행 횟수
 * http://www.sqlleader.com/mboard.asp?exec=view&strBoardID=SS2005Admin&intPage=1&intCategory=0&strSearchCategory=|s_name|s_subject|&strSearchWord=&intSeq=3300&strScYear=2009&strScMonth=6&strScDay=26

==== 소스 ====
{{{
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
}}}