SELECT r.session_id AS [세션ID] ,
r.[status] AS [상태],
r.wait_type AS [대기상태],
r.scheduler_id AS [SchedulerID],
--CASE WHEN qt.objectid IS NULL THEN 'AD-HOC'
-- ELSE dbo.AFN_THREEPARTNAME(qt.objectid, qt.dbid)
--END AS [SP이름] ,
SUBSTRING(qt.[text], r.statement_start_offset / 2,
( CASE WHEN r.statement_end_offset = -1
THEN LEN(CONVERT (NVARCHAR(MAX), qt.[text])) * 2
ELSE r.statement_end_offset
END - r.statement_start_offset ) / 2) AS [statement_executing] ,
DB_NAME(qt.[dbid])AS [DatabaseName] ,
OBJECT_NAME(qt.objectid) AS [ObjectName] ,
r.cpu_time / 1000. AS [CPU시간(s)],
r.logical_reads / 128. AS [논리적읽기(MB)] ,
r.granted_query_memory / 128. AS [쿼리메모리(MB)],
r.start_time ,
r.total_elapsed_time / 1000. AS [실행되고있는시간(s)] ,
( r.total_elapsed_time / 1000. ) / 60 AS [실행되고있는시간(m)] ,
r.reads ,
r.writes ,
r.plan_handle ,
(SELECT CAST(qp.query_plan AS XML) FROM sys.dm_exec_text_query_plan(r.plan_handle, r.statement_start_offset, r.statement_end_offset) AS qp) AS [실행계획보기],
sp.*
FROM sys.dm_exec_requests AS r
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS qt
INNER JOIN sys.sysprocesses AS sp
ON r.session_id = sp.spid
WHERE r.session_id > 50
--AND r.session_id != @@SPID
ORDER BY r.total_elapsed_time DESC ,
r.scheduler_id ,
r.[status] ,
r.session_id ;
'DataBase > MSSQL' 카테고리의 다른 글
특정단어가 포함되어 있는 트리거 찾기 (0) | 2023.10.05 |
---|---|
프로시저 실행 결과를 txt 파일로 저장하기 (0) | 2023.10.05 |
프로시져수정및생성일자확인 (0) | 2023.10.05 |
프로시져해당문구찾기 (1) | 2023.10.05 |
현재 데이터베이스에 접속중인 세션정보 아이피 확인방법 (0) | 2023.10.05 |