T_SQL::SP_WHO2 + DBCC

SQL SERVER 2005 이상

1. --===================================

1. -- SP_WHO2와명령어동시확인하기

1. --===================================

1.  

1. SELECT

1.        ISNULL(D.text, '') AS SQLStatement,

1.        object_name(D.objectid) AS 'sp_ame',

1.        A.Session_ID SPID,

1.        ISNULL(B.status,A.status) AS [Status],

1.        A.login_name AS [Login],

1.        ISNULL(A.host_name, '  .') AS HostName,

1.        ISNULL(CAST(C.BlkBy AS varchar(10)), '  .') AS BlkBy,

1.        DB_NAME(B.Database_ID) AS DBName,

1.        B.command,

1.        ISNULL(B.cpu_time, A.cpu_time) AS CPUTime,

1.        ISNULL((B.reads + B.writes),(A.reads + A.writes)) AS DiskIO,

1.        A.last_request_start_time AS LastBatch,

1.        ISNULL(A.program_name, '') AS ProgramName,

1.        ISNULL(A.client_interface_name, '') AS ClientInterfaceName

1. FROM sys.dm_exec_sessions A

1.        LEFT OUTER JOIN sys.dm_exec_requests B ON A.session_id = B.session_id

1.        LEFT OUTER JOIN (

1.              SELECT A.request_session_id SPID, B.blocking_session_id BlkBy

1.              FROM sys.dm_tran_locks A

1.                     INNER JOIN sys.dm_os_waiting_tasks B ON A.lock_owner_address = B.resource_address

1.        ) C ON A.Session_ID = C.SPID

1.        OUTER APPLY sys.dm_exec_sql_text(B.sql_handle) D

1. go

 

이 글은 스프링노트에서 작성되었습니다.