Over the years (and various versions of SQL Server), I have always had a need for different bits of information from the system tables to view activity on a SQL instance. The Microsoft sp_who, sp_who2 procedures have always provided good information, but never really everything that I needed (and, more importantly, how I needed it... hence the reason for building my own version).
-- usp_who5
--
-- By Sean Smith, 2014/11/03
-- http://www.sqlservercentral.com/scripts/sp_who/68607/
--
-- Over the years (and various versions of SQL Server), I have always had a need
-- for different bits of information from the system tables to view activity on
-- a SQL instance. The Microsoft "sp_who / sp_who2" procedures have always
-- provided good information, but never really everything that I needed (and,
-- more importantly, how I needed it... hence the reason for building my own
-- version).
--
-- It can be run as is (example: EXECUTE dbo.usp_who5) or with optional input filter parameters:
--
-- @Filter: Limit the result set by passing one or more values listed below (can be combined in any order)
--
-- A - Active sessions only
-- B - Blocked sessions only
-- C - Exclude "SQL_Statement_Batch", "SQL_Statement_Current", "Batch_Pct", and "Query_Plan_XML" columns from the output (less resource-intensive)
-- X - Exclude system reserved SPIDs (1-50)
--
-- @SPID : Limit the result set to a specific session
-- @Login : Limit the result set to a specific Windows user name (if populated, otherwise by SQL Server login name)
-- @Database : Limit the result set to a specific database
-- @SQL_Text : Limit the result set to SQL statements containing specific text (ignored when "@Filter" parameter contains "C")
--
-- When using the procedure you will notice that the query output contains a lotof handy information:
--
-- SPECID : System Process ID with Execution Context ID
-- Blocked : Blocking indicator (includes type of block and blocking SPID)
-- Running : Indicates if the session is executing (X), waiting (*), inactive (blank), has open transactions (•), or is a background task (--)
-- Login_ID : Windows user name (or login name if user name is unavailable)
-- Login_Name : Full name of the user associated to the "Login_ID" (if available)
-- Elapsed_Time : Elapsed time since the request began (DAYS HH : MM : SS)
-- CPU_Total : CPU time used since login (DAYS HH : MM : SS)
-- CPU_Current : CPU time used for the current process (DAYS HH : MM : SS)
-- Logical_Reads : Logical reads performed by the current process
-- Physical_Reads : Physical reads performed by the current process
-- Writes : Writes performed by the current process
-- Pages_Used : Pages in the procedure cache allocated to the process
-- Nesting_Level : Nesting level of the statement currently executing
-- Open_Trans : Open transactions for the process
-- Last_Row_Count : Row count produced by the last statement executed in the batch
-- Wait_Time : Current wait time (DAYS HH : MM : SS)
-- Wait_Type : Current wait type
-- Last_Wait_Type : Previous wait type
-- Status : Status of the current process
-- Command : Command currently being executed
-- Isolation_Level : Isolation level of the session
-- SQL_Statement_Batch : Batch statement of the session
-- SQL_Statement_Current : Current statement of the session
-- Batch_Pct : Percentage of the batch which has been processed
-- End_Of_Batch : Indicates if the current statement is the last of the entire batch
-- Command_Pct : Percentage of work completed (applicable to a limited set of commands)
-- Command_Time_Left : Time left before the command completes (DAYS HH : MM : SS)
-- Command_Completion : Estimated completion time for the command
-- Previous_Error : Previous error returned by the session
-- Lock_Details : Lock details of the session (in XML format)
-- Lock_Timeout_Seconds : Lock timeout of the session
-- Deadlock_Priority : Deadlock priority of the session
-- Plan_Handle : Identifier for the in-memory plan
-- Query_Plan_XML : Execution plan of the session (in XML format)
-- Plan_Cache_Object_Type : Displays which mechanism is being used for the cached plan (used in conjunction with "Plan_Object_Type")
-- Plan_Object_Type : Displays which mechanism is being used for the cached plan (used in conjunction with "Plan_Cache_Object_Type")
-- Plan_Times_Used : Number of times the plan has been utilized since its creation
-- Plan_Size_MB : Size consumed by the plan in megabytes
-- Since_SPID_Login : Elapsed time since the client logged in (DAYS HH : MM : SS)
-- Since_Last_Batch : Elapsed time since the client last completed a batch request (DAYS HH : MM : SS)
-- Workstation_Name : Workstation name
-- Database_Name : Database context of the session
-- Application_Description : Application accessing SQL Server
-- SPECID : System Process ID with Execution Context ID
--
-- If you ever need to remember what the input parameters / output columns are
-- and what they mean, you can simply execute the following:
--
-- EXECUTE dbo.usp_who5 '?'
--
-- Best of all, if there is any blocking occurring on the server it will come
-- right to the top of the result set and show you the details immediately
-- (which SPID is blocked and by who, which SPIDs are blocking other processes,
-- which are running in parallelism).
--
-- I typically map the procedure to keyboard combinations in SQL Server in order to
-- run it on the fly with various input parameter combinations.
--
-- Any friendly feedback is always welcome. Enjoy!
--
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SET NOCOUNT ON
-----------------------------------------------------------------------------------------------------------------------------
-- Error Trapping: Check If Procedure Already Exists And Create Shell If Applicable
-----------------------------------------------------------------------------------------------------------------------------
IF OBJECT_ID (N'dbo.usp_who5', N'P') IS NULL
BEGIN
EXECUTE ('CREATE PROCEDURE dbo.usp_who5 AS SELECT 1 AS shell')
END
GO
-----------------------------------------------------------------------------------------------------------------------------
-- Stored Procedure Details: Listing Of Standard Details Related To The Stored Procedure
-----------------------------------------------------------------------------------------------------------------------------
-- Purpose: Return Information Regarding Current Users / Sessions / Processes On A SQL Server Instance
-- Create Date (MM/DD/YYYY): 10/27/2009
-- Developer: Sean Smith (s.smith.sql AT gmail DOT com)
-- Additional Notes: N/A
-----------------------------------------------------------------------------------------------------------------------------
-- Modification History: Listing Of All Modifications Since Original Implementation
-----------------------------------------------------------------------------------------------------------------------------
-- Description: Converted Script To Dynamic-SQL
-- : Minor Changes To Code Style
-- : Added "@Database" Filter Variable
-- : Added "Last_Wait_Type", "Query_Plan_XML", And "Wait_Type" Fields To Output
-- Date (MM/DD/YYYY): 08/08/2011
-- Developer: Sean Smith (s.smith.sql AT gmail DOT com)
-- Additional Notes: N/A
-- Description: Renamed Input Variables
-- : Added "Plan_Cache_Object_Type", "Plan_Object_Type", "Plan_Times_Used", And "Plan_Size_MB" Fields To Output
-- : Changed Help Output From RAISERROR To PRINT
-- : Merged "I?" And "O?" Help Parameters Into "?"
-- : Added "C" Type "@Filter" Option
-- : Rewrote Time Calculation Logic
-- Date (MM/DD/YYYY): 11/09/2011
-- Developer: Sean Smith (s.smith.sql AT gmail DOT com)
-- Additional Notes: N/A
-- Description: Expanded "Running" Type Indicators
-- : Added System Reserved SPID Indicator To "SPECID"
-- : Added "SQL_Statement_Current" And "End_Of_Batch" Fields To Output
-- Date (MM/DD/YYYY): 02/01/2012
-- Developer: Sean Smith (s.smith.sql AT gmail DOT com)
-- Additional Notes: N/A
-- Description: Minor Code Formatting Changes
-- : Bug Fixes
-- : Changes To Date Calculation Method
-- Date (MM/DD/YYYY): 08/19/2013
-- Developer: Sean Smith (s.smith.sql AT gmail DOT com)
-- Additional Notes: N/A
-- Description: Added "Batch_Pct", "Command_Completion", "Command_Pct", "Command_Time_Left", "Deadlock_Priority", "Isolation_Level", "Last_Row_Count", "Lock_Details", "Lock_Timeout_Seconds", And "Previous_Error" Fields To Output
-- Date (MM/DD/YYYY): 11/24/2013
-- Developer: Sean Smith (s.smith.sql AT gmail DOT com)
-- Additional Notes: N/A
-----------------------------------------------------------------------------------------------------------------------------
-- Main Query: Create Procedure
-----------------------------------------------------------------------------------------------------------------------------
ALTER PROCEDURE dbo.usp_who5
@Filter AS VARCHAR (5) = NULL
,@SPID AS SMALLINT = NULL
,@Login AS NVARCHAR (128) = NULL
,@Database AS NVARCHAR (512) = NULL
,@SQL_Text AS NVARCHAR (MAX) = NULL
AS
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SET NOCOUNT ON
SET ANSI_WARNINGS OFF
SET ARITHABORT OFF
SET ARITHIGNORE ON
SET TEXTSIZE 2147483647
-----------------------------------------------------------------------------------------------------------------------------
-- Error Trapping: Check If "@Filter" Parameter Is An Input / Output Help Request
-----------------------------------------------------------------------------------------------------------------------------
IF @Filter = '?'
BEGIN
PRINT
N'
Optional Input Parameters:
@Filter : Limit the result set by passing one or more values listed below (can be combined in any order)
A - Active sessions only
B - Blocked sessions only
C - Exclude "SQL_Statement_Batch", "SQL_Statement_Current", "Batch_Pct", and "Query_Plan_XML" columns from the output (less resource-intensive)
X - Exclude system reserved SPIDs (1-50)
@SPID : Limit the result set to a specific session
@Login : Limit the result set to a specific Windows user name (if populated, otherwise by SQL Server login name)
@Database : Limit the result set to a specific database
@SQL_Text : Limit the result set to SQL statements containing specific text (ignored when "@Filter" parameter contains "C")
Notes:
Blocked sessions will be displayed first in the result set (when applicable)
'
PRINT
N'
Output:
SPECID : System Process ID with Execution Context ID
Blocked : Blocking indicator (includes type of block and blocking SPID)
Running : Indicates if the session is executing (X), waiting (*), inactive (blank), has open transactions (•), or is a background task (--)
Login_ID : Windows user name (or login name if user name is unavailable)
Login_Name : Full name of the user associated to the "Login_ID" (if available)
Elapsed_Time : Elapsed time since the request began (DAYS HH:MM:SS)
CPU_Total : CPU time used since login (DAYS HH:MM:SS)
CPU_Current : CPU time used for the current process (DAYS HH:MM:SS)
Logical_Reads : Logical reads performed by the current process
Physical_Reads : Physical reads performed by the current process
Writes : Writes performed by the current process
Pages_Used : Pages in the procedure cache allocated to the process
Nesting_Level : Nesting level of the statement currently executing
Open_Trans : Open transactions for the process
Last_Row_Count : Row count produced by the last statement executed in the batch
Wait_Time : Current wait time (DAYS HH:MM:SS)
Wait_Type : Current wait type
Last_Wait_Type : Previous wait type
Status : Status of the current process
Command : Command currently being executed
Isolation_Level : Isolation level of the session
SQL_Statement_Batch : Batch statement of the session
SQL_Statement_Current : Current statement of the session
Batch_Pct : Percentage of the batch which has been processed
End_Of_Batch : Indicates if the current statement is the last of the entire batch
Command_Pct : Percentage of work completed (applicable to a limited set of commands)
Command_Time_Left : Time left before the command completes (DAYS HH:MM:SS)
Command_Completion : Estimated completion time for the command
Previous_Error : Previous error returned by the session
Lock_Details : Lock details of the session (in XML format)
Lock_Timeout_Seconds : Lock timeout of the session
Deadlock_Priority : Deadlock priority of the session
Plan_Handle : Identifier for the in-memory plan
Query_Plan_XML : Execution plan of the session (in XML format)
Plan_Cache_Object_Type : Displays which mechanism is being used for the cached plan (used in conjunction with "Plan_Object_Type")
Plan_Object_Type : Displays which mechanism is being used for the cached plan (used in conjunction with "Plan_Cache_Object_Type")
Plan_Times_Used : Number of times the plan has been utilized since its creation
Plan_Size_MB : Size consumed by the plan in megabytes
Since_SPID_Login : Elapsed time since the client logged in (DAYS HH:MM:SS)
Since_Last_Batch : Elapsed time since the client last completed a batch request (DAYS HH:MM:SS)
Workstation_Name : Workstation name
Database_Name : Database context of the session
Application_Description : Application accessing SQL Server
SPECID : System Process ID with Execution Context ID
'
RETURN
END
-----------------------------------------------------------------------------------------------------------------------------
-- Declarations / Sets: Declare And Set Variables
-----------------------------------------------------------------------------------------------------------------------------
DECLARE
@Filter_Active AS BIT
,@Filter_Blocked AS BIT
,@Filter_Column AS BIT
,@Filter_System AS BIT
,@SQL_String AS VARCHAR (MAX)
SET @Login = NULLIF (@Login, N'')
SET @Database = NULLIF (@Database, N'')
SET @SQL_Text = NULLIF (REPLACE (@SQL_Text, N'''', N''''''), N'')
SET @Filter_Active = (CASE
WHEN @Filter LIKE '%A%' THEN 1
ELSE 0
END)
SET @Filter_Blocked = (CASE
WHEN @Filter LIKE '%B%' THEN 1
ELSE 0
END)
SET @Filter_Column = (CASE
WHEN @Filter LIKE '%C%' THEN 1
ELSE 0
END)
SET @Filter_System = (CASE
WHEN @Filter LIKE '%X%' THEN 1
ELSE 0
END)
-----------------------------------------------------------------------------------------------------------------------------
-- Main Query: Final Display / Output
-----------------------------------------------------------------------------------------------------------------------------
SET @SQL_String =
'
SELECT
CONVERT (VARCHAR (6), SP.spid) + ''.'' + CONVERT (VARCHAR (6), SP.ecid) + (CASE
WHEN SP.spid = @@SPID THEN '' ••''
WHEN SP.spid <= 50 THEN '' •''
ELSE ''''
END) AS SPECID
,(CASE
WHEN SP.blocked = 0 AND sqBLKD.blocked IS NULL THEN ''·············''
WHEN SP.blocked = SP.spid THEN ''> Parallelism <''
WHEN SP.blocked = 0 AND sqBLKD.blocked IS NOT NULL THEN ''>> BLOCKING <<''
ELSE ''SPID: '' + CONVERT (VARCHAR (6), sqBLKR.spid) + '' • '' + (CASE
WHEN sqBLKR.Login_ID_Blocking = ''sa'' THEN ''<< System Administrator >>''
ELSE ISNULL (sqBLKR.Login_ID_Blocking, ''N/A'')
END)
END) AS Blocked
,(CASE
WHEN SP.[status] = ''background'' THEN '' --''
WHEN SP.[status] IN (''dormant'', ''sleeping'') AND SP.open_tran = 0 THEN ''''
WHEN SP.[status] IN (''dormant'', ''sleeping'') THEN '' •''
WHEN SP.[status] IN (''defwakeup'', ''pending'', ''spinloop'', ''suspended'') THEN '' *''
ELSE '' X''
END) AS Running
,ISNULL (NULLIF (SP.nt_username, ''''), SP.loginame) AS Login_ID
,ISNULL ((CASE
WHEN SP.loginame = ''sa'' THEN ''<< System Administrator >>''
ELSE SP.loginame
END), '''') AS Login_Name
,ISNULL ((CASE
WHEN oaDHMS.total_elapsed_time_days <= 0 THEN REPLICATE (''_'', cjMAX.total_elapsed_time_days_length_max)
ELSE REPLICATE (''0'', cjMAX.total_elapsed_time_days_length_max - LEN (oaDHMS.total_elapsed_time_days)) + oaDHMS.total_elapsed_time_days
END) + '' Day(s) '' + (CASE
WHEN oaDHMS.total_elapsed_time_hhmmss = ''00:00:00'' THEN ''__:__:__''
WHEN LEFT (oaDHMS.total_elapsed_time_hhmmss, 5) = ''00:00'' THEN ''__:__:'' + RIGHT (oaDHMS.total_elapsed_time_hhmmss, 2)
WHEN LEFT (oaDHMS.total_elapsed_time_hhmmss, 2) = ''00'' THEN ''__:'' + RIGHT (oaDHMS.total_elapsed_time_hhmmss, 5)
ELSE oaDHMS.total_elapsed_time_hhmmss
END), '''') AS Elapsed_Time
,ISNULL ((CASE
WHEN oaDHMS.cpu_days <= 0 THEN REPLICATE (''_'', cjMAX.cpu_days_length_max)
ELSE REPLICATE (''0'', cjMAX.cpu_days_length_max - LEN (oaDHMS.cpu_days)) + oaDHMS.cpu_days
END) + '' Day(s) '' + (CASE
WHEN oaDHMS.cpu_hhmmss = ''00:00:00'' THEN ''__:__:__''
WHEN LEFT (oaDHMS.cpu_hhmmss, 5) = ''00:00'' THEN ''__:__:'' + RIGHT (oaDHMS.cpu_hhmmss, 2)
WHEN LEFT (oaDHMS.cpu_hhmmss, 2) = ''00'' THEN ''__:'' + RIGHT (oaDHMS.cpu_hhmmss, 5)
ELSE oaDHMS.cpu_hhmmss
END), '''') AS CPU_Total
,ISNULL ((CASE
WHEN oaDHMS.cpu_time_days <= 0 THEN REPLICATE (''_'', cjMAX.cpu_time_days_length_max)
ELSE REPLICATE (''0'', cjMAX.cpu_time_days_length_max - LEN (oaDHMS.cpu_time_days)) + oaDHMS.cpu_time_days
END) + '' Day(s) '' + (CASE
WHEN oaDHMS.cpu_time_hhmmss = ''00:00:00'' THEN ''__:__:__''
WHEN LEFT (oaDHMS.cpu_time_hhmmss, 5) = ''00:00'' THEN ''__:__:'' + RIGHT (oaDHMS.cpu_time_hhmmss, 2)
WHEN LEFT (oaDHMS.cpu_time_hhmmss, 2) = ''00'' THEN ''__:'' + RIGHT (oaDHMS.cpu_time_hhmmss, 5)
ELSE oaDHMS.cpu_time_hhmmss
END), '''') AS CPU_Current
,ISNULL (CONVERT (VARCHAR (20), DER.logical_reads), '''') AS Logical_Reads
,ISNULL (CONVERT (VARCHAR (20), DER.reads), '''') AS Physical_Reads
,ISNULL (CONVERT (VARCHAR (20), DER.writes), '''') AS Writes
,(CASE
WHEN SP.memusage = 0 THEN ''''
ELSE CONVERT (VARCHAR (10), SP.memusage)
END) AS Pages_Used
,ISNULL (CONVERT (VARCHAR (15), DER.nest_level), '''') AS Nesting_Level
,(CASE
WHEN SP.open_tran = 0 THEN ''''
ELSE CONVERT (VARCHAR (10), SP.open_tran)
END) AS Open_Trans
,ISNULL (CONVERT (VARCHAR (20), NULLIF (DXS.row_count, 0)), '''') AS Last_Row_Count
,ISNULL ((CASE
WHEN oaDHMS.waittime_days <= 0 THEN REPLICATE (''_'', cjMAX.waittime_days_length_max)
ELSE REPLICATE (''0'', cjMAX.waittime_days_length_max - LEN (oaDHMS.waittime_days)) + oaDHMS.waittime_days
END) + '' Day(s) '' + (CASE
WHEN oaDHMS.waittime_hhmmss = ''00:00:00'' THEN ''__:__:__''
WHEN LEFT (oaDHMS.waittime_hhmmss, 5) = ''00:00'' THEN ''__:__:'' + RIGHT (oaDHMS.waittime_hhmmss, 2)
WHEN LEFT (oaDHMS.waittime_hhmmss, 2) = ''00'' THEN ''__:'' + RIGHT (oaDHMS.waittime_hhmmss, 5)
ELSE oaDHMS.waittime_hhmmss
END), '''') AS Wait_Time
,ISNULL (DER.wait_type, '''') AS Wait_Type
,ISNULL (DER.last_wait_type, '''') AS Last_Wait_Type
,RTRIM ((CASE
WHEN SP.[status] NOT IN (''dormant'', ''sleeping'') THEN UPPER (SP.[status])
ELSE LOWER (SP.[status])
END)) AS [Status]
,RTRIM ((CASE
WHEN SP.cmd = ''awaiting command'' THEN LOWER (SP.cmd)
ELSE UPPER (SP.cmd)
END)) AS Command
,(CASE DXS.transaction_isolation_level
WHEN 0 THEN ''UNSPECIFIED''
WHEN 1 THEN ''READ UNCOMMITTED''
WHEN 2 THEN ''READ COMMITTED''
WHEN 3 THEN ''REPEATABLE READ''
WHEN 4 THEN ''SERIALIZABLE''
WHEN 5 THEN ''SNAPSHOT''
ELSE ''ERROR''
END) AS Isolation_Level
'
IF @Filter_Column = 0
BEGIN
SET @SQL_String = @SQL_String +
'
,ISNULL (DEST.[text], '''') AS SQL_Statement_Batch
,ISNULL ((CASE
WHEN SP.stmt_start = 0 AND SP.stmt_end = -1 THEN N''<< Single Statement >>''
WHEN SP.stmt_end = -1 AND (DATALENGTH (DEST.[text]) - SP.stmt_start) / 2 < 0 THEN N''<< Derived Statement >>''
WHEN SP.stmt_end = -1 THEN SUBSTRING (DEST.[text], SP.stmt_start / 2, (DATALENGTH (DEST.[text]) - SP.stmt_start) / 2)
ELSE SUBSTRING (DEST.[text], SP.stmt_start / 2, (SP.stmt_end - SP.stmt_start) / 2)
END), '''') AS SQL_Statement_Current
,ISNULL ((CASE
WHEN SP.[status] IN (''dormant'', ''sleeping'') AND SP.open_tran <> 0 THEN ''100.00''
WHEN SP.stmt_start = 0 AND SP.stmt_end = 0 THEN ''''
WHEN SP.stmt_start = 0 THEN ''0.00''
WHEN SP.stmt_end = -1 AND (DATALENGTH (DEST.[text]) - SP.stmt_start) / 2 < 0 THEN ''N/A''
ELSE CONVERT (VARCHAR (20), CONVERT (DECIMAL (20, 2), ROUND (((SP.stmt_start - 2.0) / DATALENGTH (DEST.[text])) * 100, 2)))
END), '''') AS Batch_Pct
'
END
SET @SQL_String = @SQL_String +
'
,(CASE
WHEN SP.stmt_end = -1 THEN ''Yes''
WHEN SP.stmt_end > 0 THEN ''No''
ELSE ''''
END) AS End_Of_Batch
,ISNULL ((CASE DER.percent_complete
WHEN 0 THEN ''''
ELSE CONVERT (VARCHAR (6), CONVERT (DECIMAL (5,2), DER.percent_complete))
END), '''') AS Command_Pct
,ISNULL ((CASE
WHEN oaDHMS.estimated_completion_time_days <= 0 THEN REPLICATE (''_'', cjMAX.estimated_completion_time_days_length_max)
ELSE REPLICATE (''0'', cjMAX.estimated_completion_time_days_length_max - LEN (oaDHMS.estimated_completion_time_days)) + oaDHMS.estimated_completion_time_days
END) + '' Day(s) '' + (CASE
WHEN oaDHMS.estimated_completion_time_hhmmss = ''00:00:00'' THEN ''__:__:__''
WHEN LEFT (oaDHMS.estimated_completion_time_hhmmss, 5) = ''00:00'' THEN ''__:__:'' + RIGHT (oaDHMS.estimated_completion_time_hhmmss, 2)
WHEN LEFT (oaDHMS.estimated_completion_time_hhmmss, 2) = ''00'' THEN ''__:'' + RIGHT (oaDHMS.estimated_completion_time_hhmmss, 5)
ELSE oaDHMS.estimated_completion_time_hhmmss
END), '''') AS Command_Time_Left
,ISNULL ((CASE DER.percent_complete
WHEN 0 THEN ''''
ELSE CONVERT (VARCHAR (19), DATEADD (MILLISECOND, DER.estimated_completion_time, GETDATE()), 120)
END), '''') AS Command_Completion
,ISNULL (CONVERT (NVARCHAR (11), NULLIF (DXS.prev_error, 0))
+ N'': ''
+ M.[text]
+ N'' (Severity ''
+ CONVERT (NVARCHAR (3), M.severity)
+ N'', ''
+ (CASE
WHEN M.is_event_logged = 0 THEN N''Not Logged''
WHEN M.is_event_logged = 1 THEN N''Logged''
END)
+ N'')'', N'''') AS Previous_Error
,ISNULL ((
SELECT DISTINCT
(CASE
WHEN SLI.rsc_type = 2 THEN DB_NAME (SLI.rsc_dbid)
WHEN SLI.rsc_type = 5 AND SLI.req_mode IN (1, 2, 5) THEN N''LOCKED''
ELSE ISNULL (OBJECT_NAME (SLI.rsc_objid, SLI.rsc_dbid), N''N/A'')
END)
+ N'': ''
+ (CASE SLI.rsc_type
WHEN 1 THEN N''NULL Resource''
WHEN 2 THEN N''Database''
WHEN 3 THEN N''File''
WHEN 4 THEN N''Index''
WHEN 5 THEN N''Object''
WHEN 6 THEN N''Page''
WHEN 7 THEN N''Key''
WHEN 8 THEN N''Extent''
WHEN 9 THEN N''Row ID (RID)''
WHEN 10 THEN N''Application''
WHEN 11 THEN N''Metadata''
WHEN 12 THEN N''HoBt''
WHEN 13 THEN N''Allocation Unit''
ELSE N''N/A''
END)
+ ISNULL (N'' ['' + CONVERT (NVARCHAR (11), NULLIF (SLI.rsc_indid, 0)) + N'']'', N'''')
+ N'' - ''
+ (CASE SLI.req_mode
WHEN 0 THEN N''NULL Resource''
WHEN 1 THEN N''Sch-S: Schema Stability''
WHEN 2 THEN N''Sch-M: Schema Modification''
WHEN 3 THEN N''S: Shared''
WHEN 4 THEN N''U: Update''
WHEN 5 THEN N''X: Exclusive''
WHEN 6 THEN N''IS: Intent Shared''
WHEN 7 THEN N''IU: Intent Update''
WHEN 8 THEN N''IX: Intent Exclusive''
WHEN 9 THEN N''SIU: Shared Intent Update''
WHEN 10 THEN N''SIX: Shared Intent Exclusive''
WHEN 11 THEN N''UIX: Update Intent Exclusive''
WHEN 12 THEN N''BU: Bulk Update''
WHEN 13 THEN N''RangeS-S: Serializable Range Scan''
WHEN 14 THEN N''RangeS-U: Serializable Update Scan''
WHEN 15 THEN N''RangeI-N: Insert Key-Range / Null Resource Lock''
WHEN 16 THEN N''RangeI-S: Overlap Of RangeI-N / S Locks''
WHEN 17 THEN N''RangeI-U: Overlap Of RangeI-N / U Locks''
WHEN 18 THEN N''RangeI-X: Overlap Of RangeI-N / X Locks''
WHEN 19 THEN N''RangeX-S: Overlap Of RangeI-N / RangeS-S Locks''
WHEN 20 THEN N''RangeX-U: Overlap Of RangeI-N / RangeS-U Locks''
WHEN 21 THEN N''RangeX-X: Exclusive Key-Range / Exclusive Resource Lock''
ELSE N''N/A''
END)
+ N'' (''
+ (CASE SLI.req_status
WHEN 1 THEN N''Granted''
WHEN 2 THEN N''Convert''
WHEN 3 THEN N''Wait''
WHEN 4 THEN N''RELN''
WHEN 5 THEN N''BLCKN''
ELSE N''N/A''
END)
+ N'' / ''
+ (CASE SLI.req_ownertype
WHEN 1 THEN N''Transaction''
WHEN 2 THEN N''Cursor''
WHEN 3 THEN N''User Session''
WHEN 4 THEN N''Shared Transaction Workspace''
WHEN 5 THEN N''Exclusive Transaction Workspace''
WHEN 6 THEN N''WFR''
ELSE N''N/A''
END)
+ N'')''
+ NCHAR (13)
+ NCHAR (10) AS [text()]
FROM
master.dbo.syslockinfo SLI
WHERE
SLI.req_mode <= 6
AND SLI.req_spid = SP.spid
AND SLI.req_ecid = SP.ecid
FOR
XML PATH ('''')
,TYPE
), N'''') AS Lock_Details
,(CASE DXS.[lock_timeout]
WHEN -1 THEN ''Wait Forever''
WHEN 0 THEN ''Immediately''
ELSE CONVERT (VARCHAR (11), CONVERT (DECIMAL (18,2), ROUND (DXS.[lock_timeout] / 1000.0, 2)))
END) AS Lock_Timeout_Seconds
,(CASE
WHEN DXS.[deadlock_priority] <= -5 THEN ''Low''
WHEN DXS.[deadlock_priority] >= 5 THEN ''High''
ELSE ''Normal''
END) + '': '' + CONVERT (VARCHAR (3), DXS.[deadlock_priority]) AS [Deadlock_Priority]
,ISNULL (CONVERT (VARCHAR (130), DECP.plan_handle, 1), '''') AS Plan_Handle
'
IF @Filter_Column = 0
BEGIN
SET @SQL_String = @SQL_String +
'
,ISNULL (DEQP.query_plan, '''') AS Query_Plan_XML
'
END
SET @SQL_String = @SQL_String +
'
,ISNULL (DECP.cacheobjtype, '''') AS Plan_Cache_Object_Type
,ISNULL ((CASE
WHEN DECP.objtype = ''Adhoc'' THEN ''Ad Hoc Query''
WHEN DECP.objtype = ''Check'' THEN ''CHECK Constraint''
WHEN DECP.objtype = ''Prepared'' THEN ''Prepared Statement''
WHEN DECP.objtype = ''Proc'' THEN ''Stored Procedure''
WHEN DECP.objtype = ''ReplProc'' THEN ''Replication Filter Procedure''
WHEN DECP.objtype = ''SysTab'' THEN ''System Table''
WHEN DECP.objtype = ''UsrTab'' THEN ''User Table''
ELSE DECP.objtype
END), '''') AS Plan_Object_Type
,ISNULL (CONVERT (VARCHAR (10), DECP.usecounts), '''') AS Plan_Times_Used
,ISNULL (CONVERT (VARCHAR (10), CONVERT (DECIMAL (18,2), ROUND (DECP.size_in_bytes / 1048576.0, 2))), '''') AS Plan_Size_MB
,ISNULL ((CASE
WHEN oaDHMS.login_time_days <= 0 THEN REPLICATE (''_'', cjMAX.login_time_days_length_max)
ELSE REPLICATE (''0'', cjMAX.login_time_days_length_max - LEN (oaDHMS.login_time_days)) + oaDHMS.login_time_days
END) + '' Day(s) '' + (CASE
WHEN oaDHMS.login_time_hhmmss = ''00:00:00'' THEN ''__:__:__''
WHEN LEFT (oaDHMS.login_time_hhmmss, 5) = ''00:00'' THEN ''__:__:'' + RIGHT (oaDHMS.login_time_hhmmss, 2)
WHEN LEFT (oaDHMS.login_time_hhmmss, 2) = ''00'' THEN ''__:'' + RIGHT (oaDHMS.login_time_hhmmss, 5)
ELSE oaDHMS.login_time_hhmmss
END), '''') AS Since_SPID_Login
,ISNULL ((CASE
WHEN oaDHMS.last_batch_days <= 0 THEN REPLICATE (''_'', cjMAX.last_batch_days_length_max)
ELSE REPLICATE (''0'', cjMAX.last_batch_days_length_max - LEN (oaDHMS.last_batch_days)) + oaDHMS.last_batch_days
END) + '' Day(s) '' + (CASE
WHEN oaDHMS.last_batch_hhmmss = ''00:00:00'' THEN ''__:__:__''
WHEN LEFT (oaDHMS.last_batch_hhmmss, 5) = ''00:00'' THEN ''__:__:'' + RIGHT (oaDHMS.last_batch_hhmmss, 2)
WHEN LEFT (oaDHMS.last_batch_hhmmss, 2) = ''00'' THEN ''__:'' + RIGHT (oaDHMS.last_batch_hhmmss, 5)
ELSE oaDHMS.last_batch_hhmmss
END), '''') AS Since_Last_Batch
,RTRIM (SP.hostname) AS Workstation_Name
,DB_NAME (SP.[dbid]) AS Database_Name
,CONVERT (NVARCHAR (128), RTRIM (REPLACE (REPLACE (SP.[program_name], ''Microsoft® Windows® Operating System'', ''Windows OS''), ''Microsoft'', ''MS''))) AS Application_Description
,CONVERT (VARCHAR (6), SP.spid) + ''.'' + CONVERT (VARCHAR (6), SP.ecid) + (CASE
WHEN SP.spid = @@SPID THEN '' ••''
WHEN SP.spid <= 50 THEN '' •''
ELSE ''''
END) AS SPECID
FROM
master.sys.sysprocesses SP
INNER JOIN master.sys.dm_exec_sessions DXS ON DXS.session_id = SP.spid
CROSS JOIN
(
SELECT
MAX (LEN (CONVERT (VARCHAR (5), XDER.total_elapsed_time / 1000 / 86400))) AS total_elapsed_time_days_length_max
,MAX (LEN (CONVERT (VARCHAR (5), XSP.cpu / 1000 / 86400))) AS cpu_days_length_max
,MAX (LEN (CONVERT (VARCHAR (5), XDER.cpu_time / 1000 / 86400))) AS cpu_time_days_length_max
,MAX (LEN (CONVERT (VARCHAR (5), XDER.estimated_completion_time / 1000 / 86400))) AS estimated_completion_time_days_length_max
,MAX (LEN (CONVERT (VARCHAR (5), XSP.waittime / 1000 / 86400))) AS waittime_days_length_max
,MAX (LEN (FLOOR ((FLOOR ((((CONVERT (DECIMAL (38, 20), GETDATE ()) - CONVERT (DECIMAL (38, 20), XSP.login_time)) * 24) * 60) * 60)) / 86400))) AS login_time_days_length_max
,MAX (LEN (FLOOR ((FLOOR ((((CONVERT (DECIMAL (38, 20), GETDATE ()) - CONVERT (DECIMAL (38, 20), XSP.last_batch)) * 24) * 60) * 60)) / 86400))) AS last_batch_days_length_max
FROM
master.sys.sysprocesses XSP
LEFT JOIN master.sys.dm_exec_requests XDER ON XDER.session_id = XSP.spid
) cjMAX
LEFT JOIN
(
SELECT
YSP.spid
,ISNULL (NULLIF (YSP.nt_username, ''''), YSP.loginame) AS Login_ID_Blocking
,ROW_NUMBER () OVER
(
PARTITION BY
YSP.spid
ORDER BY
(CASE
WHEN ISNULL (NULLIF (YSP.nt_username, ''''), YSP.loginame) = '''' THEN 2
ELSE 1
END)
,YSP.ecid
) AS sort_id
FROM
master.sys.sysprocesses YSP
) sqBLKR ON sqBLKR.spid = SP.blocked AND sqBLKR.sort_id = 1
LEFT JOIN
(
SELECT DISTINCT
ZSP.blocked
FROM
master.sys.sysprocesses ZSP
) sqBLKD ON sqBLKD.blocked = SP.spid
LEFT JOIN master.sys.dm_exec_requests DER ON DER.session_id = SP.spid
LEFT JOIN master.sys.dm_exec_cached_plans DECP ON DECP.plan_handle = DER.plan_handle
LEFT JOIN master.sys.messages M ON M.message_id = DXS.prev_error
AND M.language_id = SERVERPROPERTY (N''LCID'')
'
IF @Filter_Column = 0
BEGIN
SET @SQL_String = @SQL_String +
'
OUTER APPLY master.sys.dm_exec_sql_text (SP.[sql_handle]) DEST
OUTER APPLY master.sys.dm_exec_query_plan (DER.plan_handle) DEQP
'
END
SET @SQL_String = @SQL_String +
'
OUTER APPLY
(
SELECT
NULLIF (DER.total_elapsed_time, 0) / 1000 AS total_elapsed_time_seconds
,NULLIF (SP.cpu, 0) / 1000 AS cpu_seconds
,NULLIF (DER.cpu_time, 0) / 1000 AS cpu_time_seconds
,NULLIF (DER.estimated_completion_time, 0) / 1000 AS estimated_completion_time_seconds
,NULLIF (SP.waittime, 0) / 1000 AS waittime_seconds
,FLOOR ((((CONVERT (DECIMAL (38, 20), GETDATE ()) - CONVERT (DECIMAL (38, 20), SP.login_time)) * 24) * 60) * 60) AS login_time_seconds
,FLOOR ((((CONVERT (DECIMAL (38, 20), GETDATE ()) - CONVERT (DECIMAL (38, 20), SP.last_batch)) * 24) * 60) * 60) AS last_batch_seconds
) oaSEC
OUTER APPLY
(
SELECT
CONVERT (VARCHAR (5), oaSEC.total_elapsed_time_seconds / 86400) AS total_elapsed_time_days
,CONVERT (CHAR (8), DATEADD (SECOND, oaSEC.total_elapsed_time_seconds % 86400, 0), 108) AS total_elapsed_time_hhmmss
,CONVERT (VARCHAR (5), oaSEC.cpu_seconds / 86400) AS cpu_days
,CONVERT (CHAR (8), DATEADD (SECOND, oaSEC.cpu_seconds % 86400, 0), 108) AS cpu_hhmmss
,CONVERT (VARCHAR (5), oaSEC.cpu_time_seconds / 86400) AS cpu_time_days
,CONVERT (CHAR (8), DATEADD (SECOND, oaSEC.cpu_time_seconds % 86400, 0), 108) AS cpu_time_hhmmss
,CONVERT (VARCHAR (5), oaSEC.estimated_completion_time_seconds / 86400) AS estimated_completion_time_days
,CONVERT (CHAR (8), DATEADD (SECOND, oaSEC.estimated_completion_time_seconds % 86400, 0), 108) AS estimated_completion_time_hhmmss
,CONVERT (VARCHAR (5), oaSEC.waittime_seconds / 86400) AS waittime_days
,CONVERT (CHAR (8), DATEADD (SECOND, oaSEC.waittime_seconds % 86400, 0), 108) AS waittime_hhmmss
,CONVERT (VARCHAR (7), FLOOR (oaSEC.login_time_seconds / 86400)) AS login_time_days
,CONVERT (CHAR (8), DATEADD (SECOND, oaSEC.login_time_seconds % 86400, 0), 108) AS login_time_hhmmss
,CONVERT (VARCHAR (7), FLOOR (oaSEC.last_batch_seconds / 86400)) AS last_batch_days
,CONVERT (CHAR (8), DATEADD (SECOND, oaSEC.last_batch_seconds % 86400, 0), 108) AS last_batch_hhmmss
) oaDHMS
WHERE
1 = 1
'
IF @Filter_Active = 1
BEGIN
SET @SQL_String = @SQL_String +
'
AND (CASE
WHEN SP.open_tran <> 0 THEN ''''
ELSE SP.[status]
END) NOT IN (''dormant'', ''sleeping'')
'
END
IF @Filter_Blocked = 1
BEGIN
SET @SQL_String = @SQL_String +
'
AND SP.blocked <> 0
'
END
IF @Filter_System = 1
BEGIN
SET @SQL_String = @SQL_String +
'
AND SP.spid >= 51
'
END
IF @SPID IS NOT NULL
BEGIN
SET @SQL_String = @SQL_String +
'
AND SP.spid = ' + CONVERT (VARCHAR (10), @SPID) + '
'
END
IF @Login IS NOT NULL
BEGIN
SET @SQL_String = @SQL_String +
'
AND CONVERT (NVARCHAR (128), ISNULL (NULLIF (SP.nt_username, ''''), SP.loginame)) = N''' + @Login + '''
'
END
IF @Database IS NOT NULL
BEGIN
SET @SQL_String = @SQL_String +
'
AND DB_NAME (SP.[dbid]) = N''' + @Database + '''
'
END
IF @SQL_Text IS NOT NULL AND @Filter_Column = 0
BEGIN
SET @SQL_String = @SQL_String +
'
AND DEST.[text] LIKE N''%'' + REPLACE (REPLACE (REPLACE (''' + @SQL_Text + ''', N''['', N''[[]''), N''%'', N''[%]''), N''_'', N''[_]'') + ''%''
'
END
SET @SQL_String = @SQL_String +
'
OR
(
SP.blocked <> 0
OR sqBLKD.blocked IS NOT NULL
)
ORDER BY
(CASE
WHEN SP.blocked = 0 AND sqBLKD.blocked IS NULL THEN 999
WHEN SP.blocked = SP.spid THEN 30
WHEN SP.blocked = 0 AND sqBLKD.blocked IS NOT NULL THEN 20
ELSE 10
END)
,SP.spid
,SP.ecid
'
EXECUTE (@SQL_String)
GO