[스크립트] Adhoc/Proc 실시간or 통계 조회

Proc 통계 조회

SELECT  
	  cached_time,last_execution_time, CAST ( qs.execution_count/(case when datediff(day,cached_time,getdate()) = 0 then 1 else datediff(day,cached_time,getdate()) *1.0 end)  AS decimal(10,2)) as exec_day, 
	  QUOTENAME(DB_NAME(st.dbid))  as [DBName],
		@@servername as servername,qs.execution_count AS [RunCnt], 
	   CASE WHEN st.objectid IS NULL THEN 'Ad-hoc'
	        ELSE QUOTENAME(DB_NAME(st.dbid)) 
	  	 + '.' + QUOTENAME(OBJECT_SCHEMA_NAME(st.objectid, st.dbid))
		 + '.' + QUOTENAME(OBJECT_NAME(st.objectid, st.dbid)) END AS [ProcName], 
	  
	   CONVERT(DECIMAL(10, 2), (qs.total_elapsed_time / qs.execution_count) / 1000.) AS [AVG_Elapsed(ms)],
	    CONVERT(DECIMAL(10, 2), (qs.total_worker_time / qs.execution_count) / 1000.) AS [AVG_CPU(ms)], 
	   CONVERT(DECIMAL(10, 0), ((qs.total_logical_reads +qs.total_logical_writes)/ qs.execution_count)) AS [AVG_logical]  ,
	   CONVERT(DECIMAL(10, 2), qs.max_elapsed_time/1000.) as [max_Elapsed(ms)],
	   CONVERT(DECIMAL(10, 2), (qs.max_worker_time  ) / 1000.) AS [max_CPU(ms)], 
	   CONVERT(DECIMAL(10, 0), ((qs.max_logical_reads +qs.max_logical_writes) )) AS [max_logical]   ,
	   CONVERT(DECIMAL(10, 2), qs.min_elapsed_time/1000.) as [min_Elapsed(ms)],
	   CONVERT(DECIMAL(10, 2), (qs.min_worker_time  ) / 1000.) AS [min_CPU(ms)], 
	   CONVERT(DECIMAL(10, 0), ((qs.min_logical_reads +qs.min_logical_writes) )) AS [min_logical] 
    FROM sys.dm_exec_procedure_stats qs
    CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st  
	WHERE 
		 st.encrypted = 0   /*
		AND    DB_NAME(st.dbid) = ' '
		 
		and OBJECT_NAME(st.objectid, st.dbid) in
			(
			 

			)
		 */
   ORDER BY [ProcName] desc

 

Adhoc/Proc 통계 정보 조회

  - offset단위로 분리 (batch 내 개별 transaction단위)

  - 관련 내용은 링크 내 '추가설명' 참고

       : [스크립트] ActiveSession 수집 결과 리포팅 형태로 가공 :: 일상및기술기록 (tistory.com)

 

 
 
 --*jyoh
DECLARE @From DATETIME
DECLARE @To DATETIME
DECLARE @Dur INT 
 
SET @Dur = 1 -- 기간 
SET @From = DATEADD(day, @Dur * -1, GETDATE()) -- 부터
SET @To = GETDATE() -- 현재 까지
 

 
SELECT @@servername as servername, QUOTENAME(DB_NAME(st.dbid))  as [DBName],
		qs.execution_count AS [RunCnt], 
	   CASE WHEN st.objectid IS NULL THEN 'Ad-hoc'
	        ELSE QUOTENAME(DB_NAME(st.dbid)) 
	  	 + '.' + QUOTENAME(OBJECT_SCHEMA_NAME(st.objectid, st.dbid))
		 + '.' + QUOTENAME(OBJECT_NAME(st.objectid, st.dbid)) END AS [ProcName],
	   SUBSTRING(st.text, (qs.statement_start_offset / 2) + 1, 
	   ((CASE statement_end_offset
			WHEN -1 THEN DATALENGTH(st.text)
			ELSE qs.statement_end_offset
	   END - qs.statement_start_offset) / 2) + 1) AS [SpecificSQLText], 
	   CONVERT(DECIMAL(10, 2), (qs.total_elapsed_time / qs.execution_count) / 1000000.) AS [AVG_Elapsed(s)],
	    CONVERT(DECIMAL(10, 2), (qs.total_worker_time / qs.execution_count) / 1000000.) AS [AVG_CPU(s)], 
	   CONVERT(DECIMAL(10, 0), (qs.total_logical_reads / qs.execution_count)) AS [AVG_Page(MB)],  
	   last_execution_time,
	   st.text AS [FullSQLText] 
    FROM sys.dm_exec_query_stats qs
    CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st  
	WHERE 
		qs.last_execution_time BETWEEN @From AND @To
		AND st.encrypted = 0   
		AND st.text not LIKE '--*jyoh%'
		and st.text not LIKE 'BACKUP%'
		and st.text not LIKE 'UPDATE STATISTICS%'  
 		--and qs.execution_count >= 5 -- 5번 이상 실행건 추출시
		-- and st.dbid >4 -- SystemDB, AdHoc (NULL <0) 제외시
	and (qs.total_elapsed_time / qs.execution_count) > 1000000
   ORDER BY [AVG_Elapsed(s)] desc

실시간 트랜잭션 조회

  - 상단 'Adhoc/Proc 통계 정보 조회' 와 동일

SELECT 'RealTime_Active ' as [What],er.session_id AS [세션ID], er.status AS [상태],
	   er.wait_type AS [대기유형],blocking_session_id,wait_resource , CONVERT(float, er.total_elapsed_time / 1000.0) AS [경과시간(s)], 
	   CONVERT(float, er.wait_time / 1000) AS [대기시간(s)],   DB_NAME(er.database_id) AS [데이터베이스],es.host_name as [요청서버],login_name, 
	   CONVERT(float, er.logical_reads * 8 / 1024.0) AS [읽기(MB)],   CONVERT(float, er.granted_query_memory * 8 / 1024.0) AS [쿼리메모리(MB)],  
	   CONVERT(float, er.cpu_time / 1000.0) AS [CPU시간(s)],   er.command AS [명령],
	   CASE WHEN st.objectid IS NULL THEN 'Ad-hoc'  ELSE QUOTENAME(DB_NAME(st.dbid)) 
	  		 + '.' + QUOTENAME(OBJECT_SCHEMA_NAME(st.objectid, st.dbid))  + '.' + QUOTENAME(OBJECT_NAME(st.objectid, st.dbid)) END AS [프로시저이름],
	   left(st.text,3000) AS [쿼리문]-- ,     qp.query_plan AS [실행계획]  transaction_isolation_level as [격리수준],
	FROM sys.dm_exec_requests AS er  
	JOIN sys.dm_exec_sessions AS es    ON er.session_id = es.session_id
	INNER JOIN sys.sysprocesses AS sp    ON er.session_id = sp.spid
	CROSS APPLY sys.dm_exec_sql_text(er.sql_handle) AS st  
	CROSS APPLY sys.dm_exec_query_plan(er.plan_handle) AS qp  
	WHERE 1=1
		--and er.session_id > 50
		--AND ( er.total_elapsed_time > @total_elapsed_time OR er.logical_reads > @logical_reads ) 
	order by er.total_elapsed_time desc

Batch Resp Statistics: Ealpsed Time 체크

   - performance counter 지표로, instance 기동 후 지속 누적형태

     운영중이 아닌 성능부하테스트시 활용

 
select convert(varchar(16),getdate(),120) AS T,'Batch Resp Statistics: Ealpsed Time:Reqeusts' as CounterName,
		sum([under_50ms]) as [under_50ms], sum([under_100ms]) as [under_100ms],sum([under_200ms]) as [under_200ms], 
		sum([under_500ms]) as [under_500ms], sum([under_1000ms]) as [under_1000ms],sum([over_1000ms]) as [over_5000ms]
from
	(
	select (case when counter_name in ('Batches >=000000ms & <000001ms','Batches >=000001ms & <000002ms','Batches >=000002ms & <000005ms',
										'Batches >=000005ms & <000010ms','Batches >=000010ms & <000020ms','Batches >=000020ms & <000050ms') then cntr_value
			else 0 end) as [under_50ms],
			 (case when counter_name in ('Batches >=000050ms & <000100ms') then cntr_value
			else 0 end) as [under_100ms],  
			 (case when counter_name in ('Batches >=000100ms & <000200ms') then cntr_value
			else 0 end) as [under_200ms], 
			(case when counter_name in ('Batches >=000200ms & <000500ms') then cntr_value
			else 0 end) as [under_500ms], 							
			(case when counter_name in ('Batches >=000500ms & <001000ms') then cntr_value
			else 0 end) as [under_1000ms], 
			(case when counter_name in ('Batches >=001000ms & <002000ms','Batches >=002000ms & <005000ms','Batches >=005000ms & <010000ms',
										'Batches >=010000ms & <020000ms','Batches >=020000ms & <050000ms'
										,'Batches >=050000ms & <100000ms','Batches >=100000ms') then cntr_value
			else 0 end) as [over_1000ms]  	
	from  sys.dm_os_performance_counters
	where instance_name = 'Elapsed Time:Requests'   
	) A