Greetings!
As a DBA, make sure you monitor the 11 VitalSigns used by SQLRx to debug performance bottlenecks. |
 SQL Server Administration: Monitor blocking and blocking chains with a handy CTE.
Blocking occurs when one process has records locked that another process is attempting to also lock.
The first process will cause the second process to wait until it is done, causing blocking. Since blocking can last a long time without deadlocking, you may notice slowdowns as many users wait because of the blocking. It is important to find the root cause of the blocking, and approach the user who's causing it before simply killing a blocking SPID.
To find the root blocking SPID in the CTE below, look for a zero in the BlockedBySPID column.
SET NOCOUNT ON;
IF OBJECT_ID('tempdb..#Processes') IS NOT NULL
DROP TABLE #Processes;
-- get current processes
SELECT s.spid, BlockedBySPID = s.blocked, DatabaseName = DB_NAME(s.dbid),
s.program_name, s.loginame, ObjectName = OBJECT_NAME(objectid, s.dbid),
Definition = CAST(text AS VARCHAR(MAX)), SecWaitTime = s.waittime/1000, s.waitresource,
s.hostname, LoginName = s.loginame, LastBatch = s.last_batch, NumOpenTrans = s.open_tran
INTO #Processes
FROM sys.sysprocesses s
CROSS APPLY sys.dm_exec_sql_text (sql_handle)
WHERE s.spid > 50;
-- Recursive CTE looks for blocking
WITH Blocking(SPID, BlockedBySPID, SecWaitTime, WaitResource, BlockingStatement, DatabaseName,
HostName, LoginName, LastBatch, NumOpenTrans, RowNo, LevelRow )
AS
( SELECT s.SPID, s.BlockedBySPID, s.SecWaitTime, s.WaitResource, s.Definition, s.DatabaseName,
s.hostname, s.LoginName, s.LastBatch, s.NumOpenTrans,
ROW_NUMBER() OVER(ORDER BY s.SPID), 0 AS LevelRow
FROM #Processes s
JOIN #Processes s1 ON s.SPID = s1.BlockedBySPID
WHERE s.BlockedBySPID = 0
UNION ALL
SELECT r.SPID, r.BlockedBySPID, r.SecWaitTime, r.WaitResource, r.Definition, r.DatabaseName,
r.hostname, r.LoginName, r.LastBatch, r.NumOpenTrans,
d.RowNo, d.LevelRow + 1
FROM #Processes r
JOIN Blocking d ON r.BlockedBySPID = d.SPID
WHERE r.BlockedBySPID > 0
)
SELECT * FROM Blocking
ORDER BY RowNo, LevelRow;
SET NOCOUNT OFF;
__________________________________________________________________ | |
SQLRx is now a part of www.msdev.com, Microsoft's training site for solution providers! Check the site periodically to view new training videos.
If you have a SQL question you'd like us to answer, and possibly use for the next SQLRx Tip of the Month or msdev video, email it to us! If you missed any of our previous tips, you can view them here.
For assistance or more information on optimizing your SQL Server Environment, visit us at www.sqlrx.com or email Lori Brown at lorib@isi85.com.
Sincerely,
Lori Brown | SQLRx Senior Consultant | Integrated Services, Inc.
4144 N. Central Expwy, Suite 430 | Dallas, TX 75204
Phone: 214.526.7680 x 113 | lorib@isi85.com
Integrated Services, Inc. 
Turning Data Into Profit
Over 20 Years of Credibility & Trust
Join our email list Check out our events SQLRx Downloads |
|
