Find Blocking in SQL Server and use it to send an alert

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.

[sourcecode language=”sql”]
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

)[/sourcecode]

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.

[sourcecode language=”sql”]
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

[/sourcecode]

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

http://Extreme-Advice.com

http://www.sqlhub.com

Quick Learn

Note: Microsoft Books online is a default reference of all articles but examples and explanations

Find locked table name with duration and count in SQL Server

Finding LOCK table in SQL Server is one of essential task of DBA. Locked table for long time in SQL Server adversary affect on performance of the application, especially in pick business hours.

I have one SQL Server VIEW which help us to find all the table which are being locked currently along with duration since it is being locked.

[sourcecode language=”sql”]
CREATE VIEW WhatIsLocked
AS
SELECT
Locks.request_session_id AS SessionID,
Obj.Name AS LockedObjectName,
DATEDIFF(second,ActTra.Transaction_begin_time, GETDATE()) AS Duration,
ActTra.Transaction_begin_time,
COUNT(*) AS Locks
FROM    sys.dm_tran_locks Locks
JOIN sys.partitions Parti ON Parti.hobt_id = Locks.resource_associated_entity_id
JOIN sys.objects Obj ON Obj.object_id = Parti.object_id

JOIN sys.dm_exec_sessions ExeSess ON ExeSess.session_id = Locks.request_session_id
JOIN sys.dm_tran_session_transactions TranSess ON ExeSess.session_id = TranSess.session_id
JOIN sys.dm_tran_active_transactions ActTra ON TranSess.transaction_id = ActTra.transaction_id
WHERE   resource_database_id = db_id() AND Obj.Type = ‘U’
GROUP BY ActTra.Transaction_begin_time,Locks.request_session_id, Obj.Name
[/sourcecode]

You can use this view to keep automated watch. you can query this view every few minutes in database in business hours and if find lock bigger then certain duration, send email with detail. You can even keep watch by putting this query in web/windows application which keeps refreshing every few seconds and show details of locking.

I have written one more article in past which displays lock table, table with  ‘SCH-M’ lock and which command causing the lock. read article in detail, click here.

Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations