usp_who5
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 (includes sleeping SPIDs with open transactions)
-
B - Blocking / blocked sessions only
-
S - Exclude sleeping SPIDs with open transactions
-
X - Exclude system processes
-
@Database_Name: Limit the result set to a specific database (use ---------- for NULL database names)
-
@Exclude_Lock: Suppress lock details from the output (can increases procedure performance on busy servers; defaulted to 1)
-
@Exclude_Log: Suppress log details from the output (can increases procedure performance on busy servers; defaulted to 1)
-
@Exclude_Plan: Suppress execution plan details from the output (can increases procedure performance on busy servers; defaulted to 1)
-
@Exclude_SQL: Suppress SQL statement details from the output (can increases procedure performance on busy servers; defaulted to 0)
-
@Exclude_SQL_XML: Suppress SQL statement XML details from the output (can increases procedure performance on busy servers; defaulted to 1)
-
@Exclude_TXN: Suppress transaction details from the output (also suppresses log details due to an interdependency; can increases procedure performance on busy servers; defaulted to 1)
-
@Login: Limit the result set to a specific Windows user name (if populated, otherwise by SQL Server login name)
-
@SPID: Limit the result set to a specific session
When using the procedure you will notice that the query output contains a lot of handy information:
-
SPID: System Process ID
-
Database_Name: Database context of the session
-
Running: Indicates if the session is executing (X), waiting ([]), inactive (blank), inactive with open transactions (•), a background task (--), or not defined (N/A)
-
Blocking: Blocking indicator (includes type of block, SPID list, and deadlock detection when applicable)
-
Status: Status of the session -> request
-
Object_Name: Object being referenced (blank for ad hoc and prepared statements)
-
Command: Command executed
-
Threads: Process thread count
-
SQL_Statement_Batch: Batch statement of the session
-
SQL_Statement_Current: Current statement of the session
-
Isolation_Level: Isolation level of the session
-
Wait_Time: Current wait time (DAYS HH:MM:SS)
-
Wait_Type: Current wait type
-
Last_Wait_Type: Previous wait type
-
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_Total: Logical reads performed since login
-
Logical_Reads_Current: Logical reads performed by the current process
-
Physical_Reads_Total: Physical reads performed since login
-
Physical_Reads_Current: Physical reads performed by the current process
-
Writes_Total: Writes performed since login
-
Writes_Current: Writes performed by the current process
-
Last_Row_Count: Row count produced by the last statement executed
-
Allocated_Memory_MB: Memory allocated to the query in megabytes
-
Pages_Used: Pages in the procedure cache allocated to the process
-
Transactions: Open transactions for the process
-
Transaction_ID: Transaction ID
-
Transaction_Time: Elapsed time since the transaction began (DAYS HH:MM:SS)
-
Transaction_Type: Type of transaction
-
Transaction_State: State of the transaction
-
Nesting_Level: Nesting level of the statement executing
-
TempDB_Session_Total_MB: Temp DB space used since login for the session in megabytes
-
TempDB_Session_Current_MB: Temp DB space currently used by the session in megabytes
-
TempDB_Task_Total_MB: Temp DB space used by the entire task in megabytes
-
TempDB_Task_Current_MB: Temp DB space currently used by the task in megabytes
-
Log_Database_Count: Databases involved in the transaction
-
Log_Records_All: Log records generated for the transaction (all databases)
-
Log_Reserved_MB_All: Log space reserved for the transaction in megabytes (all databases)
-
Log_Used_MB_All: Log space used for the transaction in megabytes (all databases)
-
Log_Details: Log usage details for the transaction per database (in XML format)
-
Lock_Timeout_Seconds: Lock timeout of the session
-
Lock_Details: Lock details of the session (in XML format)
-
Deadlock_Priority: Deadlock priority of the session
-
SQL_Statement_Batch_XML: Same as "SQL_Statement_Batch" but in XML format
-
SQL_Statement_Current_XML: Same as "SQL_Statement_Current" but in XML format
-
SQL_Handle: Identifier for the executing batch or object
-
Query_Plan: Execution plan of the session (in XML format)
-
Plan_Handle: Identifier for the in-memory plan
-
Since_SPID_Login: Elapsed time since the client logged in (DAYS HH:MM:SS)
-
Since_Last_Batch_Start: Elapsed time since the last request began (DAYS HH:MM:SS)
-
Since_Last_Batch_End: Elapsed time since the last completion of a request (DAYS HH:MM:SS)
-
Command_Pct: Percentage of work completed (applies to a limited set of commands)
-
Command_Completion: Estimated completion time for the command
-
Command_Time_Left: Time left before the command completes (DAYS HH:MM:SS)
-
Host_Name: Name of the client workstation specific to a session
-
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"
-
Application_Description: Application accessing SQL Server
-
System_Process: Indicates if the session is a system process
-
SPID: System Process 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 N'?'
Best of all, if there is any blocking and / or deadlocking occurring on the server it will come right to the top of the result set and show you the details immediately.
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 (N'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)
-- Latest Release: http://qa.sqlservercentral.com/scripts/sp_who/68607/
-- Script Library: http://qa.sqlservercentral.com/Authors/Scripts/Sean_Smith/776614/
-- LinkedIn Profile: https://www.linkedin.com/in/seanmsmith/
-----------------------------------------------------------------------------------------------------------------------------
-- Modification History: Listing Of All Modifications Since Original Implementation
-----------------------------------------------------------------------------------------------------------------------------
-- Description: Added "@Database_Name" Filter Variable
-- : Added "Last_Wait_Type", "Query_Plan", And "Wait_Type" Fields To Output
-- : Changed Code Formatting
-- : Converted Script To Dynamic-SQL
-- Date (MM/DD/YYYY): 08/08/2011
-- Description: Added "C" Type "@Filter" Option
-- : 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 "?"
-- : Renamed Input Parameters
-- : Rewrote Time Calculation Logic
-- Date (MM/DD/YYYY): 11/09/2011
-- Description: Added "SQL_Statement_Current" And "End_Of_Batch" Fields To Output
-- : Added System Process Indicator To "SPID"
-- : Expanded "Running" Type Indicators
-- Date (MM/DD/YYYY): 02/01/2012
-- Description: Bug Fixes
-- : Changed Code Formatting
-- : Changed Date Calculation Method
-- Date (MM/DD/YYYY): 08/19/2013
-- 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
-- Description: Massive Rewrite Of Entire Stored Procedure
-- Date (MM/DD/YYYY): 11/28/2015
-- Description: Added "Log_Database_Count", "Log_Details", "Log_Records_All", "Log_Reserved_MB_All", "Log_Used_MB_All", "TempDB_Session_Current_MB", "TempDB_Session_Total_MB", "TempDB_Task_Current_MB", "TempDB_Task_Total_MB", "Threads", "Transaction_ID", "Transaction_State", "Transaction_Time", "Transaction_Type" Fields To Output
-- : Added Deadlock Detection To "Blocking" Output Field
-- : No Longer Displays Results At The Execution Context ID (ECID) Level
-- : Removed "@SQL_Text" Filter Variable
-- : Removed "Batch_Pct", "End_Of_Batch", "Plan_Cache_Object_Type", "Plan_Object_Type", "Plan_Size_MB", "Plan_Times_Used", "Previous_Error" Fields From Output
-- : Renamed "SPECID" To "SPID", "Open_Trans" To "Transactions" Output Fields
-- Date (MM/DD/YYYY): 05/07/2016
-- Description: Another Massive Rewrite Of Entire Stored Procedure To Improve Performance
-- Date (MM/DD/YYYY): 01/20/2018
-----------------------------------------------------------------------------------------------------------------------------
-- Main Query: Create Procedure
-----------------------------------------------------------------------------------------------------------------------------
ALTER PROCEDURE dbo.usp_who5
@Filter AS NVARCHAR (5) = NULL
,@Database_Name AS NVARCHAR (512) = NULL
,@Exclude_Lock AS BIT = 1
,@Exclude_Log AS BIT = 1
,@Exclude_Plan AS BIT = 1
,@Exclude_SQL AS BIT = 0
,@Exclude_SQL_XML AS BIT = 1
,@Exclude_TXN AS BIT = 1
,@Login AS NVARCHAR (128) = NULL
,@SPID AS SMALLINT = NULL
AS
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SET NOCOUNT ON
SET ANSI_WARNINGS OFF
SET ARITHABORT OFF
SET ARITHIGNORE ON
SET TEXTSIZE 2147483647
-----------------------------------------------------------------------------------------------------------------------------
-- Declarations / Sets: Declare And Set Variables
-----------------------------------------------------------------------------------------------------------------------------
DECLARE
@Ampersand AS NVARCHAR (1)
,@CR_LF AS NCHAR (2)
,@CR_LF_Tab AS NCHAR (3)
,@Database_ID AS INT
,@Date_Now AS DATETIME
,@Filter_Active AS BIT
,@Filter_Blocked AS BIT
,@Filter_Sleeping AS BIT
,@Filter_System AS BIT
,@Plan_Handle AS VARBINARY (64)
,@Print AS NVARCHAR (MAX)
,@SQL_Handle AS VARBINARY (64)
,@SQL_String AS NVARCHAR (MAX)
SET @Ampersand = N'&'
SET @CR_LF = NCHAR (13) + NCHAR (10)
SET @CR_LF_Tab = @CR_LF + NCHAR (9)
SET @Database_Name = NULLIF (@Database_Name, N'')
SET @Date_Now = GETDATE ()
SET @Filter_Active = (CASE
WHEN @Filter LIKE N'%A%' THEN 1
ELSE 0
END)
SET @Filter_Blocked = (CASE
WHEN @Filter LIKE N'%B%' THEN 1
ELSE 0
END)
SET @Filter_Sleeping = (CASE
WHEN @Filter LIKE N'%S%' THEN 1
ELSE 0
END)
SET @Filter_System = (CASE
WHEN @Filter LIKE N'%X%' THEN 1
ELSE 0
END)
SET @Login = NULLIF (@Login, N'')
-----------------------------------------------------------------------------------------------------------------------------
-- Error Trapping: Check If "@Filter" Parameter Is An Input / Output Help Request
-----------------------------------------------------------------------------------------------------------------------------
IF @Filter = N'?'
BEGIN
SET @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 (includes sleeping SPIDs with open transactions)
B - Blocking / blocked sessions only
S - Exclude sleeping SPIDs with open transactions
X - Exclude system processes
@Database_Name : Limit the result set to a specific database (use ---------- for NULL database names)
@Exclude_Lock : Suppress lock details from the output (can increases procedure performance on busy servers; defaulted to 1)
@Exclude_Log : Suppress log details from the output (can increases procedure performance on busy servers; defaulted to 1)
@Exclude_Plan : Suppress execution plan details from the output (can increases procedure performance on busy servers; defaulted to 1)
@Exclude_SQL : Suppress SQL statement details from the output (can increases procedure performance on busy servers; defaulted to 0)
@Exclude_SQL_XML : Suppress SQL statement XML details from the output (can increases procedure performance on busy servers; defaulted to 1)
@Exclude_TXN : Suppress transaction details from the output (also suppresses log details due to an interdependency; can increases procedure performance on busy servers; defaulted to 1)
@Login : Limit the result set to a specific Windows user name (if populated, otherwise by SQL Server login name)
@SPID : Limit the result set to a specific session
Notes:
Blocking / blocked sessions will always be displayed first in the result set (when applicable)
Output:
SPID : System Process ID
Database_Name : Database context of the session
Running : Indicates if the session is executing (X), waiting ([]), inactive (blank), inactive with open transactions (•), a background task (--), or not defined (N/A)
Blocking : Blocking indicator (includes type of block, SPID list, and deadlock detection when applicable)
Status : Status of the session -> request
Object_Name : Object being referenced (blank for ad hoc and prepared statements)
Command : Command executed
Threads : Process thread count
SQL_Statement_Batch : Batch statement of the session
SQL_Statement_Current : Current statement of the session
Isolation_Level : Isolation level of the session
Wait_Time : Current wait time (DAYS HH:MM:SS)
Wait_Type : Current wait type
Last_Wait_Type : Previous wait type
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_Total : Logical reads performed since login
Logical_Reads_Current : Logical reads performed by the current process
Physical_Reads_Total : Physical reads performed since login
Physical_Reads_Current : Physical reads performed by the current process
Writes_Total : Writes performed since login
Writes_Current : Writes performed by the current process
Last_Row_Count : Row count produced by the last statement executed
Allocated_Memory_MB : Memory allocated to the query in megabytes
Pages_Used : Pages in the procedure cache allocated to the process
Transactions : Open transactions for the process
Transaction_ID : Transaction ID
Transaction_Time : Elapsed time since the transaction began (DAYS HH:MM:SS)
Transaction_Type : Type of transaction
Transaction_State : State of the transaction
Nesting_Level : Nesting level of the statement executing
TempDB_Session_Total_MB : Temp DB space used since login for the session in megabytes
TempDB_Session_Current_MB : Temp DB space currently used by the session in megabytes
TempDB_Task_Total_MB : Temp DB space used by the entire task in megabytes
TempDB_Task_Current_MB : Temp DB space currently used by the task in megabytes
Log_Database_Count : Databases involved in the transaction
Log_Records_All : Log records generated for the transaction (all databases)
Log_Reserved_MB_All : Log space reserved for the transaction in megabytes (all databases)
Log_Used_MB_All : Log space used for the transaction in megabytes (all databases)
Log_Details : Log usage details for the transaction per database (in XML format)
Lock_Timeout_Seconds : Lock timeout of the session
Lock_Details : Lock details of the session (in XML format)
Deadlock_Priority : Deadlock priority of the session
SQL_Statement_Batch_XML : Same as "SQL_Statement_Batch" but in XML format
SQL_Statement_Current_XML : Same as "SQL_Statement_Current" but in XML format
SQL_Handle : Identifier for the executing batch or object
Query_Plan : Execution plan of the session (in XML format)
Plan_Handle : Identifier for the in-memory plan
Since_SPID_Login : Elapsed time since the client logged in (DAYS HH:MM:SS)
Since_Last_Batch_Start : Elapsed time since the last request began (DAYS HH:MM:SS)
Since_Last_Batch_End : Elapsed time since the last completion of a request (DAYS HH:MM:SS)
Command_Pct : Percentage of work completed (applies to a limited set of commands)
Command_Completion : Estimated completion time for the command
Command_Time_Left : Time left before the command completes (DAYS HH:MM:SS)
Host_Name : Name of the client workstation specific to a session
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"
Application_Description : Application accessing SQL Server
System_Process : Indicates if the session is a system process
SPID : System Process ID
'
PRINT SUBSTRING (@Print, 1, 3931)
PRINT SUBSTRING (@Print, 3934, 4000)
RETURN
END
-----------------------------------------------------------------------------------------------------------------------------
-- Error Trapping: Check If Temp Table(s) Already Exist(s) And Drop If Applicable
-----------------------------------------------------------------------------------------------------------------------------
IF OBJECT_ID (N'tempdb.dbo.#temp_core_data', N'U') IS NOT NULL
BEGIN
DROP TABLE dbo.#temp_core_data
END
IF OBJECT_ID (N'tempdb.dbo.#temp_databases', N'U') IS NOT NULL
BEGIN
DROP TABLE dbo.#temp_databases
END
IF OBJECT_ID (N'tempdb.dbo.#temp_deadlocking', N'U') IS NOT NULL
BEGIN
DROP TABLE dbo.#temp_deadlocking
END
IF OBJECT_ID (N'tempdb.dbo.#temp_false_positive_blocking', N'U') IS NOT NULL
BEGIN
DROP TABLE dbo.#temp_false_positive_blocking
END
IF OBJECT_ID (N'tempdb.dbo.#temp_lock_details', N'U') IS NOT NULL
BEGIN
DROP TABLE dbo.#temp_lock_details
END
IF OBJECT_ID (N'tempdb.dbo.#temp_log_details', N'U') IS NOT NULL
BEGIN
DROP TABLE dbo.#temp_log_details
END
IF OBJECT_ID (N'tempdb.dbo.#temp_parallelism', N'U') IS NOT NULL
BEGIN
DROP TABLE dbo.#temp_parallelism
END
-----------------------------------------------------------------------------------------------------------------------------
-- Table Creation: Create Various Temp Tables
-----------------------------------------------------------------------------------------------------------------------------
CREATE TABLE dbo.#temp_databases
(
database_id INT NULL
,database_name NVARCHAR (128) NULL
)
CREATE TABLE dbo.#temp_lock_details
(
req_spid INT NULL
,rsc_dbid SMALLINT NULL
,rsc_objid INT NULL
,rsc_indid SMALLINT NULL
,[object_name] NVARCHAR (275) NULL
,index_name NVARCHAR (128) NULL
,rsc_type TINYINT NULL
,req_mode TINYINT NULL
,req_status TINYINT NULL
,req_ownertype SMALLINT NULL
,req_ecid INT NULL
,req_refcnt INT NULL
)
CREATE TABLE dbo.#temp_log_details
(
transaction_id BIGINT NULL
,database_id INT NULL
,database_transaction_begin_time DATETIME NULL
,database_transaction_type INT NULL
,database_transaction_state INT NULL
,database_transaction_log_record_count BIGINT NULL
,database_transaction_log_bytes_reserved BIGINT NULL
,database_transaction_log_bytes_used BIGINT NULL
,database_transaction_log_bytes_reserved_system INT NULL
,database_transaction_log_bytes_used_system INT NULL
)
-----------------------------------------------------------------------------------------------------------------------------
-- Table Insert: Databases
-----------------------------------------------------------------------------------------------------------------------------
SET @Database_ID = -2147483648
SET LOCK_TIMEOUT 5
WHILE @Database_ID IS NOT NULL
BEGIN
BEGIN TRY
INSERT INTO dbo.#temp_databases
(
database_id
,database_name
)
SELECT TOP (1)
DB.database_id
,DB.name AS database_name
FROM
master.sys.databases DB
WHERE
DB.database_id = @Database_ID
END TRY
BEGIN CATCH
END CATCH
BEGIN TRY
SET @Database_ID = (SELECT TOP (1) DB.database_id FROM master.sys.databases DB WHERE DB.database_id > @Database_ID ORDER BY DB.database_id)
END TRY
BEGIN CATCH
SET @Database_ID = @Database_ID + 1
END CATCH
END
SET LOCK_TIMEOUT -1
-----------------------------------------------------------------------------------------------------------------------------
-- Table Insert: Core Data
-----------------------------------------------------------------------------------------------------------------------------
SELECT
DXS.session_id
,ttDB.database_name
,DER.blocking_session_id
,DXS.[status] AS status_session
,DER.[status] AS status_request
,CONVERT (INT, NULL) AS [dbid]
,CONVERT (INT, NULL) AS objectid
,CONVERT (NVARCHAR (275), NULL) AS [object_name]
,DER.command
,sqSP.threads
,CONVERT (NVARCHAR (MAX), NULL) AS [text]
,sqSP.stmt_start
,sqSP.stmt_end
,DXS.transaction_isolation_level
,DER.wait_time
,DER.wait_type
,DER.last_wait_type
,DER.total_elapsed_time
,DXS.cpu_time AS cpu_time_total
,DER.cpu_time AS cpu_time_current
,DXS.logical_reads AS logical_reads_total
,DER.logical_reads AS logical_reads_current
,DXS.reads AS reads_total
,DER.reads AS reads_current
,DXS.writes AS writes_total
,DER.writes AS writes_current
,DXS.row_count
,DER.granted_query_memory
,DXS.memory_usage
,sqSP.open_tran
,CONVERT (BIGINT, NULL) AS transaction_id
,CONVERT (DATETIME, NULL) AS transaction_begin_time
,CONVERT (INT, NULL) AS transaction_type
,CONVERT (INT, NULL) AS transaction_state
,DER.nest_level
,sqTS.tempdb_page_allocation_session
,sqTS.tempdb_page_deallocation_session
,sqTT.tempdb_page_allocation_task
,sqTT.tempdb_page_deallocation_task
,DXS.[lock_timeout]
,DXS.[deadlock_priority]
,sqSP.[sql_handle]
,CONVERT (XML, NULL) AS query_plan
,DER.plan_handle
,DXS.login_time AS login_time_sessions
,sqSP.login_time AS login_time_processes
,DXS.last_request_start_time
,DXS.last_request_end_time
,sqSP.last_batch
,DER.percent_complete
,DER.estimated_completion_time
,DXS.[host_name]
,DXS.nt_user_name
,DXS.login_name
,DXS.[program_name]
,DXS.is_user_process
INTO
dbo.#temp_core_data
FROM
master.sys.dm_exec_sessions DXS
INNER JOIN
(
SELECT
SP.spid
,SUM (CASE
WHEN SP.kpid = 0 THEN 0
ELSE 1
END) AS threads
,MAX (SP.stmt_start) AS stmt_start
,MAX (SP.stmt_end) AS stmt_end
,MAX (SP.open_tran) AS open_tran
,MAX (NULLIF (SP.[sql_handle], 0x0000000000000000000000000000000000000000)) AS [sql_handle]
,MAX (SP.login_time) AS login_time
,MAX (SP.last_batch) AS last_batch
,MAX (SP.[dbid]) AS [dbid]
FROM
master.sys.sysprocesses SP
GROUP BY
SP.spid
) sqSP ON sqSP.spid = DXS.session_id
LEFT JOIN master.sys.dm_exec_requests DER ON DER.session_id = DXS.session_id
LEFT JOIN dbo.#temp_databases ttDB ON ttDB.database_id = sqSP.[dbid]
LEFT JOIN
(
SELECT
DDSSU.session_id
,SUM (DDSSU.user_objects_alloc_page_count + DDSSU.internal_objects_alloc_page_count) AS tempdb_page_allocation_session
,SUM (DDSSU.user_objects_dealloc_page_count + DDSSU.internal_objects_dealloc_page_count) AS tempdb_page_deallocation_session
FROM
master.sys.dm_db_session_space_usage DDSSU
GROUP BY
DDSSU.session_id
) sqTS ON sqTS.session_id = DXS.session_id
LEFT JOIN
(
SELECT
DDTSU.session_id
,SUM (DDTSU.user_objects_alloc_page_count + DDTSU.internal_objects_alloc_page_count) AS tempdb_page_allocation_task
,SUM (DDTSU.user_objects_dealloc_page_count + DDTSU.internal_objects_dealloc_page_count) AS tempdb_page_deallocation_task
FROM
master.sys.dm_db_task_space_usage DDTSU
GROUP BY
DDTSU.session_id
) sqTT ON sqTT.session_id = DXS.session_id
WHERE
(
(
(
@Database_Name IS NULL
OR ISNULL (ttDB.database_name, N'----------') = @Database_Name
)
AND
(
@Filter_Active = 0
OR (CASE
WHEN @Filter_Sleeping = 0 AND sqSP.open_tran > 0 THEN N''
ELSE DXS.[status]
END) NOT IN (N'dormant', N'sleeping')
)
AND
(
@Filter_Blocked = 0
OR DER.blocking_session_id <> 0
)
AND
(
@Filter_System = 0
OR DXS.is_user_process = 1
)
AND
(
@Login IS NULL
OR DXS.nt_user_name = @Login
OR DXS.login_name = @Login
)
AND
(
@SPID IS NULL
OR DXS.session_id = @SPID
)
)
OR
(
NULLIF (DER.blocking_session_id, 0) IS NOT NULL
OR EXISTS
(
SELECT
*
FROM
master.sys.dm_exec_requests XDER
WHERE
XDER.blocking_session_id <> 0
AND XDER.blocking_session_id = DXS.session_id
)
)
)
-----------------------------------------------------------------------------------------------------------------------------
-- Table Update: Populate Transaction Details
-----------------------------------------------------------------------------------------------------------------------------
IF @Exclude_TXN = 0
BEGIN
UPDATE
ttCD
SET
ttCD.transaction_id = DTST.transaction_id
,ttCD.transaction_begin_time = DTAT.transaction_begin_time
,ttCD.transaction_type = DTAT.transaction_type
,ttCD.transaction_state = DTAT.transaction_state
FROM
dbo.#temp_core_data ttCD
INNER JOIN master.sys.dm_tran_session_transactions DTST ON DTST.session_id = ttCD.session_id
LEFT JOIN master.sys.dm_tran_active_transactions DTAT ON DTAT.transaction_id = DTST.transaction_id
END
-----------------------------------------------------------------------------------------------------------------------------
-- Table Insert: Transaction Log Details
-----------------------------------------------------------------------------------------------------------------------------
IF @Exclude_Log = 0 AND @Exclude_TXN = 0
BEGIN
INSERT INTO dbo.#temp_log_details
(
transaction_id
,database_id
,database_transaction_begin_time
,database_transaction_type
,database_transaction_state
,database_transaction_log_record_count
,database_transaction_log_bytes_reserved
,database_transaction_log_bytes_used
,database_transaction_log_bytes_reserved_system
,database_transaction_log_bytes_used_system
)
SELECT
DTDT.transaction_id
,DTDT.database_id
,DTDT.database_transaction_begin_time
,DTDT.database_transaction_type
,DTDT.database_transaction_state
,DTDT.database_transaction_log_record_count
,DTDT.database_transaction_log_bytes_reserved
,DTDT.database_transaction_log_bytes_used
,DTDT.database_transaction_log_bytes_reserved_system
,DTDT.database_transaction_log_bytes_used_system
FROM
master.sys.dm_tran_database_transactions DTDT
WHERE
EXISTS
(
SELECT
*
FROM
dbo.#temp_core_data ttCD
WHERE
ttCD.transaction_id = DTDT.transaction_id
)
END
-----------------------------------------------------------------------------------------------------------------------------
-- Table Insert: Lock Request Details
-----------------------------------------------------------------------------------------------------------------------------
IF @Exclude_Lock = 0
BEGIN
INSERT INTO dbo.#temp_lock_details
(
req_spid
,rsc_dbid
,rsc_objid
,rsc_indid
,[object_name]
,index_name
,rsc_type
,req_mode
,req_status
,req_ownertype
,req_ecid
,req_refcnt
)
SELECT
SLI.req_spid
,SLI.rsc_dbid
,SLI.rsc_objid
,SLI.rsc_indid
,CONVERT (NVARCHAR (275), NULL) AS [object_name]
,CONVERT (NVARCHAR (128), NULL) AS index_name
,SLI.rsc_type
,SLI.req_mode
,SLI.req_status
,SLI.req_ownertype
,SLI.req_ecid
,SUM (SLI.req_refcnt) AS req_refcnt
FROM
master.sys.syslockinfo SLI
WHERE
EXISTS
(
SELECT
*
FROM
dbo.#temp_core_data ttCD
WHERE
ttCD.session_id = SLI.req_spid
)
GROUP BY
SLI.req_spid
,SLI.rsc_dbid
,SLI.rsc_objid
,SLI.rsc_indid
,SLI.rsc_type
,SLI.req_mode
,SLI.req_status
,SLI.req_ownertype
,SLI.req_ecid
END
-----------------------------------------------------------------------------------------------------------------------------
-- Table Update: Attempt To Populate SQL Text Data And Object Details
-----------------------------------------------------------------------------------------------------------------------------
IF @Exclude_SQL = 0
BEGIN
SET @SQL_Handle = (SELECT TOP (1) ttCD.[sql_handle] FROM dbo.#temp_core_data ttCD WHERE ttCD.[sql_handle] IS NOT NULL ORDER BY ttCD.[sql_handle])
WHILE @SQL_Handle IS NOT NULL
BEGIN
SET LOCK_TIMEOUT 5
BEGIN TRY
UPDATE
ttCD
SET
ttCD.[dbid] = (CASE
WHEN ttCD.[dbid] IS NULL AND ttCD.objectid IS NULL THEN DEST.[dbid]
ELSE ttCD.[dbid]
END)
,ttCD.objectid = (CASE
WHEN ttCD.[dbid] IS NULL AND ttCD.objectid IS NULL THEN DEST.objectid
ELSE ttCD.objectid
END)
,ttCD.[text] = DEST.[text]
FROM
dbo.#temp_core_data ttCD
CROSS APPLY master.sys.dm_exec_sql_text (ttCD.[sql_handle]) DEST
WHERE
ttCD.[sql_handle] = @SQL_Handle
END TRY
BEGIN CATCH
END CATCH
SET LOCK_TIMEOUT -1
SET @SQL_Handle = (SELECT TOP (1) ttCD.[sql_handle] FROM dbo.#temp_core_data ttCD WHERE ttCD.[sql_handle] IS NOT NULL AND ttCD.[sql_handle] > @SQL_Handle ORDER BY ttCD.[sql_handle])
END
END
-----------------------------------------------------------------------------------------------------------------------------
-- Table Update: Attempt To Populate Query Plan Data And Object Details
-----------------------------------------------------------------------------------------------------------------------------
IF @Exclude_Plan = 0
BEGIN
SET @Plan_Handle = (SELECT TOP (1) ttCD.plan_handle FROM dbo.#temp_core_data ttCD WHERE ttCD.plan_handle IS NOT NULL ORDER BY ttCD.plan_handle)
WHILE @Plan_Handle IS NOT NULL
BEGIN
SET LOCK_TIMEOUT 5
BEGIN TRY
UPDATE
ttCD
SET
ttCD.[dbid] = (CASE
WHEN ttCD.[dbid] IS NULL AND ttCD.objectid IS NULL THEN DEQP.[dbid]
ELSE ttCD.[dbid]
END)
,ttCD.objectid = (CASE
WHEN ttCD.[dbid] IS NULL AND ttCD.objectid IS NULL THEN DEQP.objectid
ELSE ttCD.objectid
END)
,ttCD.query_plan = DEQP.query_plan
FROM
dbo.#temp_core_data ttCD
CROSS APPLY master.sys.dm_exec_query_plan (ttCD.plan_handle) DEQP
WHERE
ttCD.plan_handle = @Plan_Handle
END TRY
BEGIN CATCH
END CATCH
SET LOCK_TIMEOUT -1
SET @Plan_Handle = (SELECT TOP (1) ttCD.plan_handle FROM dbo.#temp_core_data ttCD WHERE ttCD.plan_handle IS NOT NULL AND ttCD.plan_handle > @Plan_Handle ORDER BY ttCD.plan_handle)
END
END
-----------------------------------------------------------------------------------------------------------------------------
-- Table Update: Attempt To Populate Object Name
-----------------------------------------------------------------------------------------------------------------------------
IF (@Exclude_Plan = 0 OR @Exclude_SQL = 0)
BEGIN
SET @Database_ID = (SELECT TOP (1) ttCD.[dbid] FROM dbo.#temp_core_data ttCD WHERE ttCD.[dbid] IS NOT NULL AND ttCD.objectid IS NOT NULL ORDER BY ttCD.[dbid])
WHILE @Database_ID IS NOT NULL
BEGIN
SET @SQL_String =
N'
USE [' + (SELECT ttDB.database_name FROM dbo.#temp_databases ttDB WHERE ttDB.database_id = @Database_ID) + N']
UPDATE
ttCD
SET
ttCD.[object_name] = N''['' + S.name + N''].['' + AO.name + N'']''
FROM
dbo.#temp_core_data ttCD
INNER JOIN sys.all_objects AO ON AO.[object_id] = ttCD.objectid
INNER JOIN sys.schemas S ON S.[schema_id] = AO.[schema_id]
WHERE
ttCD.[dbid] = ' + CONVERT (NVARCHAR (11), @Database_ID) + N'
'
IF @SQL_String IS NOT NULL
BEGIN
SET LOCK_TIMEOUT 5
BEGIN TRY
EXECUTE (@SQL_String)
END TRY
BEGIN CATCH
END CATCH
SET LOCK_TIMEOUT -1
END
SET @Database_ID = (SELECT TOP (1) ttCD.[dbid] FROM dbo.#temp_core_data ttCD WHERE ttCD.[dbid] IS NOT NULL AND ttCD.objectid IS NOT NULL AND ttCD.[dbid] > @Database_ID ORDER BY ttCD.[dbid])
END
END
-----------------------------------------------------------------------------------------------------------------------------
-- Table Update: Attempt To Populate Lock Details' Database Name / Object Name / Index Name
-----------------------------------------------------------------------------------------------------------------------------
IF @Exclude_Lock = 0
BEGIN
SET @Database_ID = (SELECT TOP (1) ttLKD.rsc_dbid FROM dbo.#temp_lock_details ttLKD ORDER BY ttLKD.rsc_dbid)
WHILE @Database_ID IS NOT NULL
BEGIN
SET @SQL_String =
N'
USE [' + (SELECT ttDB.database_name FROM dbo.#temp_databases ttDB WHERE ttDB.database_id = @Database_ID) + N']
UPDATE
ttLKD
SET
ttLKD.[object_name] = N''['' + S.name + N''].['' + AO.name + N'']''
,ttLKD.index_name = I.name
FROM
dbo.#temp_lock_details ttLKD
LEFT JOIN sys.all_objects AO ON AO.[object_id] = ttLKD.rsc_objid
LEFT JOIN sys.schemas S ON S.[schema_id] = AO.[schema_id]
LEFT JOIN sys.indexes I ON I.[object_id] = ttLKD.rsc_objid
AND I.index_id = ttLKD.rsc_indid
WHERE
ttLKD.rsc_dbid = ' + CONVERT (NVARCHAR (11), @Database_ID) + N'
'
IF @SQL_String IS NOT NULL
BEGIN
SET LOCK_TIMEOUT 5
BEGIN TRY
EXECUTE (@SQL_String)
END TRY
BEGIN CATCH
END CATCH
SET LOCK_TIMEOUT -1
END
SET @Database_ID = (SELECT TOP (1) ttLKD.rsc_dbid FROM dbo.#temp_lock_details ttLKD WHERE ttLKD.rsc_dbid > @Database_ID ORDER BY ttLKD.rsc_dbid)
END
END
-----------------------------------------------------------------------------------------------------------------------------
-- Table Insert: Identify False Positive Blocking Cause By Timing Issues
-----------------------------------------------------------------------------------------------------------------------------
SELECT
ttCD.session_id
,(CASE
WHEN caCB.matches_filter_criteria = 0 AND caCB.is_truly_blocked = 0 AND caCB.is_truly_blocking = 0 THEN N'D'
ELSE N'U'
END) AS modification_type
INTO
dbo.#temp_false_positive_blocking
FROM
dbo.#temp_core_data ttCD
LEFT JOIN dbo.#temp_core_data ttXCD ON ttXCD.session_id = ttCD.blocking_session_id
LEFT JOIN
(
SELECT DISTINCT
ttCD.blocking_session_id
FROM
dbo.#temp_core_data ttCD
) sqBS ON sqBS.blocking_session_id = ttCD.session_id
CROSS APPLY
(
SELECT
(CASE
WHEN @Filter_Blocked = 1 THEN 0
WHEN ISNULL (ttCD.database_name, N'----------') <> @Database_Name THEN 0
WHEN @Filter_Active = 1 AND (CASE
WHEN @Filter_Sleeping = 0 AND ttCD.open_tran > 0 THEN N''
ELSE ttCD.status_session
END) IN (N'dormant', N'sleeping') THEN 0
WHEN @Filter_System = 1 AND ttCD.is_user_process <> 1 THEN 0
WHEN ISNULL (ttCD.nt_user_name, N'') <> @Login AND ttCD.login_name <> @Login THEN 0
WHEN ttCD.session_id <> @SPID THEN 0
ELSE 1
END) AS matches_filter_criteria
,(CASE
WHEN ttXCD.session_id IS NOT NULL THEN 1
ELSE 0
END) AS is_truly_blocked
,(CASE
WHEN sqBS.blocking_session_id IS NOT NULL THEN 1
ELSE 0
END) AS is_truly_blocking
) caCB
WHERE
(
(
caCB.matches_filter_criteria = 0
AND caCB.is_truly_blocked = 0
AND caCB.is_truly_blocking = 0
)
OR
(
(
caCB.matches_filter_criteria = 1
OR caCB.is_truly_blocking = 1
)
AND caCB.is_truly_blocked = 0
AND NULLIF (ttCD.blocking_session_id, 0) IS NOT NULL
)
)
-----------------------------------------------------------------------------------------------------------------------------
-- Table Delete: Remove False Positives Which Meet Neither Filter Criteria Nor Block Chain Dependencies
-----------------------------------------------------------------------------------------------------------------------------
DELETE
ttCD
FROM
dbo.#temp_core_data ttCD
WHERE
EXISTS
(
SELECT
*
FROM
dbo.#temp_false_positive_blocking ttFPB
WHERE
ttFPB.modification_type = N'D'
AND ttFPB.session_id = ttCD.session_id
)
-----------------------------------------------------------------------------------------------------------------------------
-- Table Update: Update False Positives Which Meet Either Filter Criteria And / Or Block Chain Dependencies
-----------------------------------------------------------------------------------------------------------------------------
UPDATE
ttCD
SET
ttCD.blocking_session_id = NULL
FROM
dbo.#temp_core_data ttCD
WHERE
EXISTS
(
SELECT
*
FROM
dbo.#temp_false_positive_blocking ttFPB
WHERE
ttFPB.modification_type = N'U'
AND ttFPB.session_id = ttCD.session_id
)
IF OBJECT_ID (N'tempdb.dbo.#temp_false_positive_blocking', N'U') IS NOT NULL
BEGIN
DROP TABLE dbo.#temp_false_positive_blocking
END
-----------------------------------------------------------------------------------------------------------------------------
-- Table Insert: Identify Blocking SPIDs Which Are Deadlocking
-----------------------------------------------------------------------------------------------------------------------------
SELECT
sqBL.session_id
,sqBL.blocking_session_id
INTO
dbo.#temp_deadlocking
FROM
(
SELECT
ttCD.session_id
,ttCD.blocking_session_id
FROM
dbo.#temp_core_data ttCD
WHERE
ttCD.blocking_session_id <> 0
AND ttCD.session_id <> ttCD.blocking_session_id
UNION ALL
SELECT
ttCD.blocking_session_id AS session_id
,ttCD.session_id AS blocking_session_id
FROM
dbo.#temp_core_data ttCD
WHERE
ttCD.blocking_session_id <> 0
AND ttCD.session_id <> ttCD.blocking_session_id
) sqBL
GROUP BY
sqBL.session_id
,sqBL.blocking_session_id
HAVING
COUNT (*) > 1
-----------------------------------------------------------------------------------------------------------------------------
-- Table Insert: Identify Queries Running In Parallel With Threads Waiting For Others To Complete
-----------------------------------------------------------------------------------------------------------------------------
SELECT
ttCD.session_id
INTO
dbo.#temp_parallelism
FROM
dbo.#temp_core_data ttCD
WHERE
ttCD.blocking_session_id <> 0
AND ttCD.session_id = ttCD.blocking_session_id
-----------------------------------------------------------------------------------------------------------------------------
-- Table Update: Remove Specific Blocking Session IDs From The Main Blocking Group
-----------------------------------------------------------------------------------------------------------------------------
UPDATE
ttCD
SET
ttCD.blocking_session_id = NULL
FROM
dbo.#temp_core_data ttCD
WHERE
(
ttCD.blocking_session_id = 0
OR EXISTS
(
SELECT
*
FROM
dbo.#temp_deadlocking ttD
WHERE
ttD.session_id = ttCD.session_id
)
OR EXISTS
(
SELECT
*
FROM
dbo.#temp_parallelism ttP
WHERE
ttP.session_id = ttCD.session_id
)
)
-----------------------------------------------------------------------------------------------------------------------------
-- Main Query: Final Display / Output
-----------------------------------------------------------------------------------------------------------------------------
SELECT
caFL.spid AS SPID
,ISNULL (ttCD.database_name, N'----------') AS Database_Name
,REPLICATE (N' ', 5) + (CASE
WHEN caFL.status_session = N'BACKGROUND' THEN N'---'
WHEN caFL.status_session IN (N'dormant', N'sleeping') AND ttCD.open_tran = 0 THEN N''
WHEN caFL.status_session IN (N'dormant', N'sleeping') AND ttCD.open_tran > 0 THEN N'•'
WHEN caFL.status_session IN (N'PENDING', N'PRECONNECT', N'RUNNABLE', N'SPINLOOP', N'SUSPENDED') THEN N'[]'
WHEN caFL.status_session IN (N'ROLLBACK', N'RUNNING') THEN N'X'
ELSE N'N/A'
END) AS Running
,(CASE
WHEN ttD.session_id IS NULL AND caFL.blocking IS NULL AND ttCD.blocking_session_id IS NULL AND ttP.session_id IS NULL THEN N''
ELSE ISNULL (caCP.deadlocking_spid + caCP.separator_01, N'') + ISNULL (caCP.blocking_spids + caCP.separator_02, N'') + ISNULL (caCP.blocked_by_spid + caCP.separator_03, N'') + ISNULL (caCP.parallelism_spid, N'')
END) AS Blocking
,caFL.status_session + N' -> ' + ISNULL (caFL.status_request, N'N/A') AS [Status]
,ISNULL (ISNULL (ttCD.[object_name], N'Database ID: ' + CONVERT (NVARCHAR (11), ttCD.[dbid]) + N', Object ID: ' + CONVERT (NVARCHAR (11), ttCD.objectid)), N'') AS [Object_Name]
,ISNULL (ttCD.command, N'awaiting command') AS Command
,ISNULL (CONVERT (NVARCHAR (11), ttCD.threads), N'') AS Threads
,ISNULL (ttCD.[text], N'') AS SQL_Statement_Batch
,caCP.sql_statement_current AS SQL_Statement_Current
,(CASE ttCD.transaction_isolation_level
WHEN 0 THEN N'UNSPECIFIED'
WHEN 1 THEN N'READ UNCOMMITTED'
WHEN 2 THEN N'READ COMMITTED'
WHEN 3 THEN N'REPEATABLE READ'
WHEN 4 THEN N'SERIALIZABLE'
WHEN 5 THEN N'SNAPSHOT'
ELSE N'N/A'
END) AS Isolation_Level
,ISNULL (CONVERT (NVARCHAR (15), (NULLIF (caFL.wait_time, 0) / 1000) / 86400) + N' Day(s) ' + CONVERT (NVARCHAR (8), DATEADD (SECOND, NULLIF (caFL.wait_time, 0) / 1000, 0), 108), N'') AS Wait_Time
,ISNULL (ttCD.wait_type, N'') AS Wait_Type
,ISNULL (ttCD.last_wait_type, N'') AS Last_Wait_Type
,ISNULL (CONVERT (NVARCHAR (15), (NULLIF (caFL.total_elapsed_time, 0) / 1000) / 86400) + N' Day(s) ' + CONVERT (NVARCHAR (8), DATEADD (SECOND, NULLIF (caFL.total_elapsed_time, 0) / 1000, 0), 108), N'') AS Elapsed_Time
,ISNULL (CONVERT (NVARCHAR (15), (NULLIF (ttCD.cpu_time_total, 0) / 1000) / 86400) + N' Day(s) ' + CONVERT (NVARCHAR (8), DATEADD (SECOND, NULLIF (ttCD.cpu_time_total, 0) / 1000, 0), 108), N'') AS CPU_Total
,ISNULL (CONVERT (NVARCHAR (15), (NULLIF (ttCD.cpu_time_current, 0) / 1000) / 86400) + N' Day(s) ' + CONVERT (NVARCHAR (8), DATEADD (SECOND, NULLIF (ttCD.cpu_time_current, 0) / 1000, 0), 108), N'') AS CPU_Current
,ISNULL (REVERSE (SUBSTRING (REVERSE (CONVERT (NVARCHAR (23), CONVERT (MONEY, NULLIF (ttCD.logical_reads_total, 0)), 1)), 4, 23)), N'') AS Logical_Reads_Total
,ISNULL (REVERSE (SUBSTRING (REVERSE (CONVERT (NVARCHAR (23), CONVERT (MONEY, NULLIF (ttCD.logical_reads_current, 0)), 1)), 4, 23)), N'') AS Logical_Reads_Current
,ISNULL (REVERSE (SUBSTRING (REVERSE (CONVERT (NVARCHAR (23), CONVERT (MONEY, NULLIF (ttCD.reads_total, 0)), 1)), 4, 23)), N'') AS Physical_Reads_Total
,ISNULL (REVERSE (SUBSTRING (REVERSE (CONVERT (NVARCHAR (23), CONVERT (MONEY, NULLIF (ttCD.reads_current, 0)), 1)), 4, 23)), N'') AS Physical_Reads_Current
,ISNULL (REVERSE (SUBSTRING (REVERSE (CONVERT (NVARCHAR (23), CONVERT (MONEY, NULLIF (ttCD.writes_total, 0)), 1)), 4, 23)), N'') AS Writes_Total
,ISNULL (REVERSE (SUBSTRING (REVERSE (CONVERT (NVARCHAR (23), CONVERT (MONEY, NULLIF (ttCD.writes_current, 0)), 1)), 4, 23)), N'') AS Writes_Current
,ISNULL (REVERSE (SUBSTRING (REVERSE (CONVERT (NVARCHAR (23), CONVERT (MONEY, NULLIF (ttCD.row_count, 0)), 1)), 4, 23)), N'') AS Last_Row_Count
,ISNULL (CONVERT (NVARCHAR (23), CONVERT (MONEY, (NULLIF (ttCD.granted_query_memory, 0) * 8) / 1024.0), 1), N'') AS Allocated_Memory_MB
,ISNULL (CONVERT (NVARCHAR (11), NULLIF (ttCD.memory_usage, 0)), N'') AS Pages_Used
,ISNULL (CONVERT (NVARCHAR (6), NULLIF (ttCD.open_tran, 0)), N'') AS Transactions
,ISNULL (CONVERT (NVARCHAR (20), NULLIF (ttCD.transaction_id, 0)), N'') AS Transaction_ID
,ISNULL ((CASE
WHEN ttCD.transaction_begin_time > @Date_Now THEN N'0 Day(s) 00:00:00'
ELSE CONVERT (NVARCHAR (15), DATEDIFF (SECOND, ttCD.transaction_begin_time, @Date_Now) / 86400) + N' Day(s) ' + CONVERT (NVARCHAR (8), DATEADD (SECOND, DATEDIFF (SECOND, ttCD.transaction_begin_time, @Date_Now), 0), 108)
END), N'') AS Transaction_Time
,(CASE
WHEN ttCD.transaction_type IS NULL THEN N''
WHEN ttCD.transaction_type = 1 THEN N'Read / Write'
WHEN ttCD.transaction_type = 2 THEN N'Read-Only'
WHEN ttCD.transaction_type = 3 THEN N'System'
WHEN ttCD.transaction_type = 4 THEN N'Distributed'
ELSE N'N/A'
END) AS Transaction_Type
,(CASE
WHEN ttCD.transaction_state IS NULL THEN N''
WHEN ttCD.transaction_state = 0 THEN N'Initializing'
WHEN ttCD.transaction_state = 1 THEN N'Initialized / Not Started'
WHEN ttCD.transaction_state = 2 THEN N'Active'
WHEN ttCD.transaction_state = 3 THEN N'Ended (Read-Only Transaction)'
WHEN ttCD.transaction_state = 4 THEN N'Commit Initiated (Distributed Transaction)'
WHEN ttCD.transaction_state = 5 THEN N'Prepared / Waiting Resolution'
WHEN ttCD.transaction_state = 6 THEN N'Committed'
WHEN ttCD.transaction_state = 7 THEN N'Rolling Back'
WHEN ttCD.transaction_state = 8 THEN N'Rolled Back'
ELSE N'N/A'
END) AS Transaction_State
,ISNULL (CONVERT (NVARCHAR (11), NULLIF (ttCD.nest_level, 0)), N'') AS Nesting_Level
,ISNULL (CONVERT (NVARCHAR (23), CONVERT (MONEY, (NULLIF (ttCD.tempdb_page_allocation_session, 0) * 8) / 1024.0), 1), N'') AS TempDB_Session_Total_MB
,ISNULL (CONVERT (NVARCHAR (23), CONVERT (MONEY, ((NULLIF (ttCD.tempdb_page_allocation_session, 0) - ttCD.tempdb_page_deallocation_session) * 8) / 1024.0), 1), N'') AS TempDB_Session_Current_MB
,ISNULL (CONVERT (NVARCHAR (23), CONVERT (MONEY, (NULLIF (ttCD.tempdb_page_allocation_task, 0) * 8) / 1024.0), 1), N'') AS TempDB_Task_Total_MB
,ISNULL (CONVERT (NVARCHAR (23), CONVERT (MONEY, ((NULLIF (ttCD.tempdb_page_allocation_task, 0) - ttCD.tempdb_page_deallocation_task) * 8) / 1024.0), 1), N'') AS TempDB_Task_Current_MB
,ISNULL (REVERSE (SUBSTRING (REVERSE (CONVERT (NVARCHAR (23), CONVERT (MONEY, NULLIF (sqLS.log_database_count, 0)), 1)), 4, 23)), N'') AS Log_Database_Count
,ISNULL (REVERSE (SUBSTRING (REVERSE (CONVERT (NVARCHAR (23), CONVERT (MONEY, NULLIF (sqLS.log_record_count_all, 0)), 1)), 4, 23)), N'') AS Log_Records_All
,ISNULL (CONVERT (NVARCHAR (23), CONVERT (MONEY, NULLIF (sqLS.log_bytes_reserved_all, 0) / 1048576.0), 1), N'') AS Log_Reserved_MB_All
,ISNULL (CONVERT (NVARCHAR (23), CONVERT (MONEY, NULLIF (sqLS.log_bytes_used_all, 0) / 1048576.0), 1), N'') AS Log_Used_MB_All
,ISNULL ((
SELECT
ISNULL ((CASE
WHEN ttLGD.database_id = 32767 THEN N'mssqlsystemresource (Hidden Resource Database)'
ELSE ttDB.database_name
END), N'N/A')
+ ISNULL (@CR_LF_Tab
+ LEFT (N'Transaction Time' + REPLICATE (N' ', 25), 25) + N': '
+ (CASE
WHEN ttLGD.database_transaction_begin_time > @Date_Now THEN N'0 Day(s) 00:00:00'
ELSE CONVERT (NVARCHAR (15), DATEDIFF (SECOND, ttLGD.database_transaction_begin_time, @Date_Now) / 86400) + N' Day(s) ' + CONVERT (NVARCHAR (8), DATEADD (SECOND, DATEDIFF (SECOND, ttLGD.database_transaction_begin_time, @Date_Now), 0), 108)
END), N'')
+ @CR_LF_Tab
+ LEFT (N'Transaction Type' + REPLICATE (N' ', 25), 25) + N': '
+ (CASE ttLGD.database_transaction_type
WHEN 1 THEN N'Read / Write'
WHEN 2 THEN N'Read-Only'
WHEN 3 THEN N'System'
ELSE N'N/A'
END)
+ @CR_LF_Tab
+ LEFT (N'Transaction State' + REPLICATE (N' ', 25), 25) + N': '
+ (CASE ttLGD.database_transaction_state
WHEN 1 THEN N'Not Initialized'
WHEN 3 THEN N'No Log Records Generated'
WHEN 4 THEN N'Log Records Generated'
WHEN 5 THEN N'Prepared'
WHEN 10 THEN N'Committed'
WHEN 11 THEN N'Rolled Back'
WHEN 12 THEN N'Committing / Log Records Generating'
ELSE N'N/A'
END)
+ ISNULL (@CR_LF_Tab
+ LEFT (N'Log Records Generated' + REPLICATE (N' ', 25), 25) + N': '
+ REVERSE (SUBSTRING (REVERSE (CONVERT (NVARCHAR (23), CONVERT (MONEY, NULLIF (ttLGD.database_transaction_log_record_count, 0)), 1)), 4, 23)), N'')
+ ISNULL (@CR_LF_Tab
+ LEFT (N'Log MB Reserved' + REPLICATE (N' ', 25), 25) + N': '
+ CONVERT (NVARCHAR (23), CONVERT (MONEY, NULLIF (ttLGD.database_transaction_log_bytes_reserved, 0) / 1048576.0), 1), N'')
+ ISNULL (@CR_LF_Tab
+ LEFT (N'Log MB Used' + REPLICATE (N' ', 25), 25) + N': '
+ CONVERT (NVARCHAR (23), CONVERT (MONEY, NULLIF (ttLGD.database_transaction_log_bytes_used, 0) / 1048576.0), 1), N'')
+ ISNULL (@CR_LF_Tab
+ LEFT (N'Log MB Reserved (System)' + REPLICATE (N' ', 25), 25) + N': '
+ CONVERT (NVARCHAR (23), CONVERT (MONEY, NULLIF (ttLGD.database_transaction_log_bytes_reserved_system, 0) / 1048576.0), 1), N'')
+ ISNULL (@CR_LF_Tab
+ LEFT (N'Log MB Used (System)' + REPLICATE (N' ', 25), 25) + N': '
+ CONVERT (NVARCHAR (23), CONVERT (MONEY, NULLIF (ttLGD.database_transaction_log_bytes_used_system, 0) / 1048576.0), 1), N'')
+ @CR_LF
+ @CR_LF AS [text()]
FROM
dbo.#temp_log_details ttLGD
LEFT JOIN dbo.#temp_databases ttDB ON ttDB.database_id = ttLGD.database_id
WHERE
ttLGD.transaction_id = ttCD.transaction_id
ORDER BY
(CASE
WHEN ttLGD.database_id = 32767 THEN N'mssqlsystemresource (Hidden Resource Database)'
ELSE ttDB.database_name
END)
FOR
XML PATH (N'')
,TYPE
), N'') AS Log_Details
,(CASE
WHEN ttCD.[lock_timeout] = -1 THEN N'Wait Forever'
WHEN ttCD.[lock_timeout] = 0 THEN N'Immediately'
ELSE CONVERT (NVARCHAR (11), CONVERT (DECIMAL (18, 2), ROUND (ttCD.[lock_timeout] / 1000.0, 2)))
END) AS Lock_Timeout_Seconds
,ISNULL ((
SELECT
ISNULL (N'Database ' + ISNULL (N'Name: ' + ttDB.database_name, N'ID: ' + CONVERT (NVARCHAR (11), ttLKD.rsc_dbid)), N'')
+ ISNULL (N' ••• Object ' + ISNULL (N'Name: ' + ttLKD.[object_name], N'ID: ' + CONVERT (NVARCHAR (11), NULLIF (ttLKD.rsc_objid, 0))), N'')
+ ISNULL (N' ••• Index ' + ISNULL (N'Name: ' + ttLKD.index_name, N'ID: ' + CONVERT (NVARCHAR (11), NULLIF (ttLKD.rsc_indid, 0))), N'')
+ @CR_LF_Tab
+ N'Resource Type: '
+ (CASE ttLKD.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'Heap Or B-Tree (HoBt)'
WHEN 13 THEN N'Allocation Unit'
ELSE N'N/A'
END)
+ @CR_LF_Tab
+ N'Request Mode: '
+ (CASE ttLKD.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)
+ @CR_LF_Tab
+ N'Request Status: '
+ (CASE ttLKD.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)
+ @CR_LF_Tab
+ N'Request Owner Type: '
+ (CASE ttLKD.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)
+ @CR_LF_Tab
+ N'ECID: ' + CONVERT (NVARCHAR (11), ttLKD.req_ecid)
+ @CR_LF_Tab
+ N'Request Reference Count: ' + CONVERT (NVARCHAR (6), ttLKD.req_refcnt)
+ @CR_LF
+ @CR_LF AS [text()]
FROM
dbo.#temp_lock_details ttLKD
LEFT JOIN dbo.#temp_databases ttDB ON ttDB.database_id = ttLKD.rsc_dbid
WHERE
ttLKD.req_spid = ttCD.session_id
ORDER BY
ttDB.database_name
,ttLKD.[object_name]
,ttLKD.index_name
,ttLKD.rsc_type
,ttLKD.req_mode
,ttLKD.req_status
,ttLKD.req_ownertype
,ttLKD.req_ecid
,ttLKD.req_refcnt
FOR
XML PATH (N'')
,TYPE
), N'') AS Lock_Details
,(CASE
WHEN ttCD.[deadlock_priority] <= -5 THEN N'Low'
WHEN ttCD.[deadlock_priority] >= 5 THEN N'High'
ELSE N'Normal'
END) + N': ' + CONVERT (NVARCHAR (3), ttCD.[deadlock_priority]) AS [Deadlock_Priority]
,CONVERT (XML, ISNULL ((CASE
WHEN (@Exclude_SQL = 1 OR @Exclude_SQL_XML = 1) THEN N''
ELSE REPLACE (REPLACE (REPLACE (REPLACE (REPLACE (REPLACE (ttCD.[text] COLLATE Latin1_General_BIN, NCHAR (0) COLLATE Latin1_General_BIN, N''), N'&', @Ampersand + N'amp;'), N'<', @Ampersand + N'lt;'), N'>', @Ampersand + N'gt;'), N'"', @Ampersand + N'quot;'), N'''', @Ampersand + N'#39;')
END), N'')) AS SQL_Statement_Batch_XML
,CONVERT (XML, (CASE
WHEN (@Exclude_SQL = 1 OR @Exclude_SQL_XML = 1) THEN N''
ELSE REPLACE (REPLACE (REPLACE (REPLACE (REPLACE (REPLACE (ISNULL (NULLIF (NULLIF (caCP.sql_statement_current, N'<< Derived Statement >>'), N'<< Single Statement >>'), ttCD.[text]) COLLATE Latin1_General_BIN, NCHAR (0) COLLATE Latin1_General_BIN, N''), N'&', @Ampersand + N'amp;'), N'<', @Ampersand + N'lt;'), N'>', @Ampersand + N'gt;'), N'"', @Ampersand + N'quot;'), N'''', @Ampersand + N'#39;')
END)) AS SQL_Statement_Current_XML
,ISNULL (CONVERT (NVARCHAR (130), ttCD.[sql_handle], 1), N'') AS [SQL_Handle]
,ISNULL (ttCD.query_plan, N'') AS Query_Plan
,ISNULL (CONVERT (NVARCHAR (130), ttCD.plan_handle, 1), N'') AS Plan_Handle
,ISNULL ((CASE
WHEN caFL.login_time > @Date_Now THEN N'0 Day(s) 00:00:00'
ELSE CONVERT (NVARCHAR (15), DATEDIFF (SECOND, caFL.login_time, @Date_Now) / 86400) + N' Day(s) ' + CONVERT (NVARCHAR (8), DATEADD (SECOND, DATEDIFF (SECOND, caFL.login_time, @Date_Now), 0), 108)
END), N'') AS Since_SPID_Login
,ISNULL ((CASE
WHEN caFL.last_request_start_time > @Date_Now THEN N'0 Day(s) 00:00:00'
ELSE CONVERT (NVARCHAR (15), DATEDIFF (SECOND, caFL.last_request_start_time, @Date_Now) / 86400) + N' Day(s) ' + CONVERT (NVARCHAR (8), DATEADD (SECOND, DATEDIFF (SECOND, caFL.last_request_start_time, @Date_Now), 0), 108)
END), N'') AS Since_Last_Batch_Start
,ISNULL ((CASE
WHEN caFL.last_request_end_time > @Date_Now THEN N'0 Day(s) 00:00:00'
ELSE CONVERT (NVARCHAR (15), DATEDIFF (SECOND, caFL.last_request_end_time, @Date_Now) / 86400) + N' Day(s) ' + CONVERT (NVARCHAR (8), DATEADD (SECOND, DATEDIFF (SECOND, caFL.last_request_end_time, @Date_Now), 0), 108)
END), N'') AS Since_Last_Batch_End
,ISNULL (CONVERT (NVARCHAR (7), CONVERT (DECIMAL (5, 2), NULLIF (ttCD.percent_complete, 0))), N'') AS Command_Pct
,ISNULL ((CASE
WHEN ttCD.percent_complete = 0 THEN N''
ELSE CONVERT (NVARCHAR (19), DATEADD (MILLISECOND, ttCD.estimated_completion_time, @Date_Now), 120)
END), N'') AS Command_Completion
,ISNULL (CONVERT (NVARCHAR (15), (NULLIF (ttCD.estimated_completion_time, 0) / 1000) / 86400) + N' Day(s) ' + CONVERT (NVARCHAR (8), DATEADD (SECOND, NULLIF (ttCD.estimated_completion_time, 0) / 1000, 0), 108), N'') AS Command_Time_Left
,ISNULL (ttCD.[host_name], N'') AS [Host_Name]
,caFL.login_id AS Login_ID
,(CASE
WHEN ttCD.login_name = N'sa' THEN N'<< System Administrator >>'
ELSE ttCD.login_name
END) AS Login_Name
,ISNULL (REPLACE (REPLACE (ttCD.[program_name], N'Microsoft® Windows® Operating System', N'Windows OS'), N'Microsoft', N'MS'), N'') AS Application_Description
,(CASE ttCD.is_user_process
WHEN 1 THEN N''
ELSE N'Yes'
END) AS System_Process
,caFL.spid AS SPID
FROM
dbo.#temp_core_data ttCD
LEFT JOIN dbo.#temp_deadlocking ttD ON ttD.session_id = ttCD.session_id
LEFT JOIN dbo.#temp_parallelism ttP ON ttP.session_id = ttCD.session_id
LEFT JOIN
(
SELECT
ttLGD.transaction_id
,COUNT (*) AS log_database_count
,SUM (ttLGD.database_transaction_log_record_count) AS log_record_count_all
,SUM (ttLGD.database_transaction_log_bytes_reserved + ttLGD.database_transaction_log_bytes_reserved_system) AS log_bytes_reserved_all
,SUM (ttLGD.database_transaction_log_bytes_used + ttLGD.database_transaction_log_bytes_used_system) AS log_bytes_used_all
FROM
dbo.#temp_log_details ttLGD
GROUP BY
ttLGD.transaction_id
) sqLS ON sqLS.transaction_id = ttCD.transaction_id
CROSS APPLY
(
SELECT
CONVERT (NVARCHAR (6), ttCD.session_id) + ISNULL (N' ' + (CASE
WHEN ttCD.session_id = @@SPID THEN N'••'
WHEN ttCD.is_user_process = 0 THEN N'•'
END), N'') AS spid
,STUFF ((
SELECT
N', ' + CONVERT (NVARCHAR (MAX), ttXCD.session_id) AS [text()]
FROM
dbo.#temp_core_data ttXCD
WHERE
ttXCD.blocking_session_id = ttCD.session_id
ORDER BY
ttXCD.session_id
FOR
XML PATH (N'')
), 1, 2, N'') AS blocking
,(CASE
WHEN ttCD.status_session NOT IN (N'dormant', N'sleeping') THEN UPPER (ttCD.status_session)
ELSE ttCD.status_session
END) AS status_session
,(CASE
WHEN ttCD.status_request <> N'sleeping' THEN UPPER (ttCD.status_request)
ELSE ttCD.status_request
END) AS status_request
,CONVERT (BIGINT, (CASE
WHEN ttCD.wait_time < 0 THEN 2147483647 + (2147483649 + ttCD.wait_time)
ELSE ttCD.wait_time
END)) AS wait_time
,CONVERT (BIGINT, (CASE
WHEN ttCD.total_elapsed_time < 0 THEN 2147483647 + (2147483649 + ttCD.total_elapsed_time)
ELSE ttCD.total_elapsed_time
END)) AS total_elapsed_time
,(CASE
WHEN ttCD.login_time_sessions = N'1900-01-01 00:00:00.000' THEN ttCD.login_time_processes
ELSE ttCD.login_time_sessions
END) AS login_time
,(CASE
WHEN ttCD.last_request_start_time = N'1900-01-01 00:00:00.000' THEN NULL
ELSE ttCD.last_request_start_time
END) AS last_request_start_time
,(CASE
WHEN ttCD.last_request_end_time = N'1900-01-01 00:00:00.000' THEN ttCD.last_batch
ELSE ttCD.last_request_end_time
END) AS last_request_end_time
,ISNULL (NULLIF (ttCD.nt_user_name, N''), ttCD.login_name) AS login_id
) caFL
CROSS APPLY
(
SELECT
N'[ Deadlocking ] : ' + CONVERT (NVARCHAR (6), ttD.blocking_session_id) AS deadlocking_spid
,(CASE
WHEN (caFL.blocking IS NOT NULL OR ttCD.blocking_session_id IS NOT NULL OR ttP.session_id IS NOT NULL) THEN N' •• '
ELSE N''
END) AS separator_01
,N'< Blocking > : ' + caFL.blocking AS blocking_spids
,(CASE
WHEN (ttCD.blocking_session_id IS NOT NULL OR ttP.session_id IS NOT NULL) THEN N' •• '
ELSE N''
END) AS separator_02
,N'> Blocked By < : ' + CONVERT (NVARCHAR (6), ttCD.blocking_session_id) AS blocked_by_spid
,(CASE
WHEN ttP.session_id IS NOT NULL THEN N' •• '
ELSE N''
END) AS separator_03
,N'( Parallelism ) : ' + CONVERT (NVARCHAR (6), ttP.session_id) AS parallelism_spid
,ISNULL ((CASE
WHEN ttCD.[text] IS NULL THEN N''
WHEN caFL.status_session = N'sleeping' AND caFL.status_request IS NULL THEN N''
WHEN ttCD.stmt_start < 1 AND ttCD.stmt_end = -1 THEN N'<< Single Statement >>'
WHEN (DATALENGTH (ttCD.[text]) - ttCD.stmt_start) / 2 < 0 AND ttCD.stmt_end = -1 THEN N'<< Derived Statement >>'
WHEN ttCD.stmt_end = -1 THEN SUBSTRING (ttCD.[text], (ttCD.stmt_start / 2) + 1, ((DATALENGTH (ttCD.[text]) - ttCD.stmt_start) / 2) + 1)
ELSE SUBSTRING (ttCD.[text], (ttCD.stmt_start / 2) + 1, ((ttCD.stmt_end - ttCD.stmt_start) / 2) + 1)
END), N'') AS sql_statement_current
) caCP
ORDER BY
(CASE
WHEN ttD.blocking_session_id IS NOT NULL AND caFL.blocking IS NULL THEN 1
WHEN ttD.blocking_session_id IS NOT NULL AND caFL.blocking IS NOT NULL THEN 2
WHEN caFL.blocking IS NOT NULL AND ttCD.blocking_session_id IS NULL THEN 3
WHEN caFL.blocking IS NOT NULL AND ttCD.blocking_session_id IS NOT NULL THEN 4
WHEN ttCD.blocking_session_id IS NOT NULL THEN 5
WHEN ttP.session_id IS NOT NULL THEN 6
ELSE 7
END)
,ttCD.session_id
-----------------------------------------------------------------------------------------------------------------------------
-- Cleanup: Drop Any Remaining Temp Tables
-----------------------------------------------------------------------------------------------------------------------------
IF OBJECT_ID (N'tempdb.dbo.#temp_core_data', N'U') IS NOT NULL
BEGIN
DROP TABLE dbo.#temp_core_data
END
IF OBJECT_ID (N'tempdb.dbo.#temp_databases', N'U') IS NOT NULL
BEGIN
DROP TABLE dbo.#temp_databases
END
IF OBJECT_ID (N'tempdb.dbo.#temp_deadlocking', N'U') IS NOT NULL
BEGIN
DROP TABLE dbo.#temp_deadlocking
END
IF OBJECT_ID (N'tempdb.dbo.#temp_lock_details', N'U') IS NOT NULL
BEGIN
DROP TABLE dbo.#temp_lock_details
END
IF OBJECT_ID (N'tempdb.dbo.#temp_log_details', N'U') IS NOT NULL
BEGIN
DROP TABLE dbo.#temp_log_details
END
IF OBJECT_ID (N'tempdb.dbo.#temp_parallelism', N'U') IS NOT NULL
BEGIN
DROP TABLE dbo.#temp_parallelism
END
GO
