[스크립트] ActiveSession 수집 결과 리포팅 형태로 가공

Heavy Query에 대한 실시간/통계 리포팅 서비스 구현 요건으로 작성했던 쿼리입니다.
아래의 사유로 해당 쿼리를 작성하였습니다
  - sys.dm_exec_query_stats은 쿼리 통계정보를 제공하지만 트랜잭션별 AP/서비스계정 정보등이 없음
  - sys.dm_exec_requests는 실시간 쿼리 정보를 제공, sql_handle 값을 통해 쿼리 분류 가능

수행결과예시

추가 설명

• sql_handle / statement_start_offset / statement_end_offset

  • sql_handle로 배치단위 (일괄 처리 단위)를 구분 가능
  • offset은 배치내 해당 개별 트랜잭션 단위를 구분 가능
  • 배치의 쿼리내용 (Adhoc 파라미터 변경등) 변경시 sql_handle/offset도 변경됨
    • 프로시저 변경배포시

      sys.dm_exec_query_stats에 대상 정보가 재생성되는 이유 (실행계획만 변경시에는 재생성 X)

 

 

 

• 첨부된 쿼리는 당시 요건에 따라 배치단위의 프로시저 구분이 목적으로 sql_handle로 분류

• 실제 ActiveSession 모니터링을 구현할 경우, 모니터링 DB가 비대해져 조회 속도 저하가 발생하지 않는 방향 고려 필요
  • 데일리 DB 및 OBJ 생성 및 초기화 자동화 등..

 

 쿼리문

실시간 수집 쿼리 (x초단위)

--by jyoh
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED  
					 
SELECT @@servername as [SQLServer명],  
	   es.session_id as [세션번호],
	   es.last_request_end_time as [세션 요청 시간],
	   er.sql_handle as [쿼리 HASH 값],
	   er.session_id AS [세션ID], 
	   DB_NAME(er.database_id) AS [데이터베이스],
	   es.host_name as [요청서버],
	   es.login_name as [로그인명],
	   CONVERT(float, er.total_elapsed_time / 1000.0) AS [경과시간(s)], 
	   CONVERT(float, er.wait_time / 1000) AS [대기시간(s)],  
	   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 [프로시저이름],
	   st.text 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   
	WHERE 1=1 
		and er.session_id > 50		
		and er.total_elapsed_time >1000
		and st.text not like 'BACKUP%'
		and st.text not like 'UPDATE STATISTICS%'
		and st.text not like '--by jyoh%'

 

가공쿼리(통계용)

--테이블 aggri_test 는  '실시간 수집 쿼리 (x초단위)' 결과 적재 테이블

 -- 대기 시간 미포함
	select  [요청서버],[로그인명],[데이터베이스], max([프로시저이름]) as [프로시저이름], 
			count([세션번호]+[세션 요청 시간]) as [수행횟수] ,
			max([경과시간(s)]) as [최대수행시간(s)], max([쿼리문]) as [쿼리문]
	from aggri_test with (nolock)
	group by [쿼리 HASH 값],[데이터베이스],[요청서버],[로그인명]
	 
  
-- 대기 시간 포함
select A.[데이터베이스],A.[요청서버],A.[로그인명],B.[프로시저이름],A.[수행횟수],A.[경과시간(s)],
	    B.[대기시간(s)], B.[쿼리문]
from
	(
	select [쿼리 HASH 값],[데이터베이스],[요청서버],[로그인명],
	 count([세션번호]+[세션 요청 시간]) as [수행횟수] ,max([경과시간(s)]) as [경과시간(s)]
	from aggri_test with (nolock)
	group by [쿼리 HASH 값],[데이터베이스],[요청서버],[로그인명]
	) A
	inner join aggri_test B
	on A.[쿼리 HASH 값] =B.[쿼리 HASH 값] and A.[데이터베이스] =B.[데이터베이스]
	 and A.[요청서버] =B.[요청서버] and A.[로그인명] =B.[로그인명] and A.[경과시간(s)] =B.[경과시간(s)]