I recently had to deal with an environment with a lot of blocked processes. I was tiered of resolving the lock-chains with sp_who2.
With the following ingredients I made a receipt called “sp_blocked6”, in Sql Server, we speak about a stored procedure.
Ingredients:
- I concentrated on sys.sysprocesses, the compatible view on which sp_who2 is based, as well as on dynamic views sys.dm_exec_connections, sys.dm_exec_sessions and sys.dm_exec_requests.
(Wo)man! Now, we can get a lot more information on the connections being made to a Sql Server 2005 instance!
- I used the new T-Sql construction “WITH common_table_expression” to deal with recursivity, because I could not predict how long the blocking-chain is/was, read how many sessions are blocking each other
- And I discovered another new T-Sql operator CROSS APPLY which allows you to invoke a table-valued function for each row returned by another select, to show the statement(s) being send by each session.
The resulting stored procedure shows an output which I could not catch in previous versions of Sql Server.
So move to Sql Server 2005 and you can use the following stored procedure:
-- START OF SCRIPT ------------------------------------------------------------
USE MASTER
GO
CREATE PROCEDURE sp_Blocked6
AS
/*
Created by Peter De Seranno
Last changed on 20061018
Version 1.1
Running on: Sql Server 2005
Usage: list all blocked processes with minimal info
*/
WITH List (blocked, spid, BlockLevel, sql_handle) AS
(
SELECT blocked, spid, 0 AS BlockLevel, sql_handle
FROM sys.sysprocesses
WHERE blocked = 0
UNION ALL
SELECT sp.blocked, sp.spid, BlockLevel + 1, sp.sql_handle
FROM sys.sysprocesses sp
INNER JOIN List L
ON sp.blocked = L.spid
)
SELECT List.spid
, CASE WHEN blocked = 0 THEN '-' ELSE blocked END AS BlockedBy
, BlockLevel
, st.text AS spidSQL
, CASE ISNULL(dms.transaction_isolation_level, -1) WHEN 0 THEN 'Unspecified'
WHEN 1 THEN 'ReadUncomitted'
WHEN 2 THEN 'ReadCommitted'
WHEN 3 THEN 'Repeatable'
WHEN 4 THEN 'Serializable'
WHEN 5 THEN 'Snapshot'
WHEN -1 THEN NULL
ELSE 'not valid'
END AS IsolationLevel
, dmr.last_wait_type AS LastWaitType
, wait_resource AS WaitResource
FROM List
LEFT JOIN sys.dm_exec_requests dmr ON List.spid = dmr.session_id
LEFT JOIN sys.dm_exec_sessions dms ON List.spid = dms.session_id
CROSS APPLY sys.dm_exec_sql_text( List.sql_handle) AS st
WHERE BlockLevel > 0 OR spid IN ( SELECT blocked FROM List)
ORDER BY BlockLevel, spid
GO
-- END OF SCRIPT ------------------------------------------------------------
Hopefully, you do not need this one or it does not return any rows, meaning you are not dealing with blocking.
Good luck!