Find Blocking in SQL Server and use it to send an alert:
As a DBA, I am always interested to find whether blocking is going on, in my environment or not? How long blocking exists? Which are the queries/SPs creating blocking etc.
Blocking generally occurs when one SQL Server connection (user process or application process) places a lock on a table or a number of rows and a second connection attempts to read or modify the data under the lock by first connection. Depending on the type of the lock, this can cause the second connection to wait until the first connection releases its lock. A blocked connection waits indefinitely for the blocking connection to release its lock.
Sometime It may happens that blocking gets converted into deadlock situation where neither of both connection able to finish its job and one of the connection identified as deadlock victim by SQL Server. In short, Locking, Blocking and deadlocking is not good to keep. This is the reason I always eager to know the details about blocking happening in my system.
Few days back, I have created one SQL Server View “WhatIsGoingOn” which was using sys.dm_exec_requests and sys.dm_exec_sessions DMVs to find information about currently running processes in SQL Server instance along with sessionID and blocking Session ID, if any. I am going to use “WhatIsGoingOn” SQL Server View in one SP to find only blocked session and insert its detail into following SQL Server 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 )
After having above table in my database, I will create following Stored Procedure 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, I can schedule SP “uspInsertBlockingRecords” every few minutes for business hours in SQL Server Job Agent so that I can see details of blocking anytime I wish from table “tblBlockingDetails”. I can even set email schedule either to get information about blocking, if I wish.
If you want to create DeadLock situation forcefully to check its behavior, you can use the script given in one of my past article here.
Reference: Ritesh Shah
Note: Microsoft Books online is a default reference of all articles but examples and explanations