지정 SPID에 대한 자세한 정보 보기

지정 SPID 에 대한 Query Text 포함 자세한 정보 보기

DECLARE @wHandle VARBINARY(MAX) DECLARE @wSPID INT SELECT @wSPID = 63 --GET THE HANDLE BY SESSION_ID (SPID) SELECT @wHandle = SQL_HANDLE FROM SYS.DM_EXEC_REQUESTS WHERE SESSION_ID = @wSPID --GET THE QUERY FOR THE HANDLE SELECT S2.DBID, S1.SQL_HANDLE, (SELECT TOP 1 SUBSTRING(S2.TEXT,STATEMENT_START_OFFSET / 2+1 , ( (CASE WHEN STATEMENT_END_OFFSET = -1 THEN (LEN(CONVERT(NVARCHAR(MAX),S2.TEXT)) * 2) ELSE STATEMENT_END_OFFSET END) - STATEMENT_START_OFFSET) / 2+1)) AS SQL_STATEMENT, EXECUTION_COUNT, PLAN_GENERATION_NUM, LAST_EXECUTION_TIME, TOTAL_WORKER_TIME, LAST_WORKER_TIME, MIN_WORKER_TIME, MAX_WORKER_TIME, TOTAL_PHYSICAL_READS, LAST_PHYSICAL_READS, MIN_PHYSICAL_READS, MAX_PHYSICAL_READS, TOTAL_LOGICAL_WRITES, LAST_LOGICAL_WRITES, MIN_LOGICAL_WRITES, MAX_LOGICAL_WRITES FROM SYS.DM_EXEC_QUERY_STATS AS S1 CROSS APPLY SYS.DM_EXEC_SQL_TEXT(@wHandle) AS S2 WHERE S1.SQL_HANDLE = @wHandle ORDER BY S1.SQL_HANDLE, S1.STATEMENT_START_OFFSET, S1.STATEMENT_END_OFFSET