WhatIsGoingOn?

# joined DMV view useful for looking at current blocks  # step 1 create view CREATE VIEW WhatIsGoingOn

AS

SELECT

OBJECT_NAME(objectid) as ObjectName

,SUBSTRING(stateText.text, (statement_start_offset/2)+1,

((CASE statement_end_offset

WHEN -1 THEN DATALENGTH(stateText.text)

ELSE statement_end_offset

END - statement_start_offset)/2) + 1) AS statement_text

,DB_Name(database_id) as DatabaseName

,req.cpu_time AS CPU_Time

,DATEDIFF(minute, last_request_start_time, getdate()) AS RunningMinutes

,req.Percent_Complete

,sess.HOST_NAME as RunningFrom

,LEFT(CLIENT_INTERFACE_NAME, 25) AS RunningBy

,sess.session_id AS SessionID

,req.blocking_session_id AS BlockingWith

,req.reads

,req.writes

,sess.[program_name]

,sess.login_name

,sess.status

,sess.last_request_start_time

,req.logical_reads

FROM

sys.dm_exec_requests req

INNER JOIN sys.dm_exec_sessions sess ON sess.session_id = req.session_id

AND sess.is_user_process = 1

CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS stateText

# step 2 create logging table

CREATE TABLE tblBlockingDetails
(
ID INT IDENTITY(1,1),
ObjectName SYSNAME NULL,
BlockingObjectName SYSNAME NULL,
StatementText VARCHAR(MAX),
BlockingStatementText VARCHAR(MAX),
SessionID INT,
BlockingSessionID INT,
Status VARCHAR(50),
BlockingStatus VARCHAR(50),
DateCreated DATETIME
)


# step3 create procedure to insert rows into the logging table
#After creating tblBlockingDetails, create the Stored Procedure uspInsertBlockingRecords below which will capture #details of any session being locked and insert that detail in “tblBlockingDetails” table.

CREATE PROC uspInsertBlockingRecords
AS
INSERT INTO tblBlockingDetails
SELECT
W1.ObjectName AS ObjectName,
W2.ObjectName AS BlockingObjectName,
W1.Statement_Text AS StatementText,
W2.Statement_Text AS BlockingStatementText,
W1.sessionid AS SessionID,
W2.sessionid AS BlockingSessionID,
W1.status AS Status,
W2.status AS BlockingStatus,
GETDATE() AS DateCreated
FROM
WhatIsGoingOn W1 INNER JOIN WhatIsGoingOn W2
ON
W1.BlockingWith > 0
AND W1.BlockingWith = W2.sessionid

Now, you can schedule SP uspInsertBlockingRecords every few minutes during business hours in SQL Server Job Agent so you can view the details of blocking processes anytime from table tblBlockingDetails. Its for example also possible to create an email alert to notify a dba group if blocking processes are active (e.g. RunningMinutes > 10 minutes). In this way its possible for a DBA to be proactive in resolving blocking issues. Also useful for performance tuning with some minor tweaks.