August 22, 2008 at 8:00 am
I have the following query that returns blocking and blocked SPID info, when blocking persists for over a minute.
The problem is that the query does not return exactly what I want.
I want it to return no records if blocking is under a minute, and all records - both blocking and blocked SPIDs - when blocking lasts for over a minute.
Right now, the query below returns the blocking-SPID record(s) for blocking under a minute, and everything when blocking is over a minute.
How can I change the query so it does what I want, without using a temp table?
SELECT
R.session_id AS SPID
,R.blocking_session_id AS BlockingSPID
,R.wait_time AS WaitTime
,S.login_time AS Login_Time
,S.[host_name] AS HostName
,S.[program_name] AS ProgramName
,left(H.text,2000) AS SQL_Text
FROM
sys.dm_exec_requests R
INNER JOIN
sys.dm_exec_sessions S
ON
R.session_id = S.session_id
CROSS APPLY
sys.dm_exec_sql_text(R.sql_handle) H
WHERE
R.session_id > 50
AND
(
(R.blocking_session_id <> 0 AND R.wait_time > 60000) -- 1 min
OR
(R.blocking_session_id = 0 AND R.session_id IN (SELECT blocking_session_id FROM sys.dm_exec_requests WHERE blocking_session_id <> 0 ))
);
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
August 22, 2008 at 10:10 am
I noticed in the second half of your OR condition, you check:
[font="Courier New"]R.blocking_session_id = 0 AND R.session_id IN ...[/font]
It looks like your query will show the blocked processes, but only show the blocking processes if they themselves are not blocked. If you have long running transactions, it's very possible that the blocking could be 3 or 4 levels deep, so I wouldn't want to restrict the query from only showing blocking processes that are not blocked.
If what you were trying to do is find the first blocker that is starting a chain reaction, you might need a recursive CTE.
August 22, 2008 at 10:55 am
I'm interested in this too.
Is there any script that can log any "root cause" blocking SPIDs to a table so I can answer questions about whether or not there was blocking during a service outage? Or a profile trace template that does the same thing?
I'm getting a little frustrated with running sp_who2 - it is great for interactively showing whether there is blocking when you run it, but I want something more comprehensive that can keep an eye out even when I am not at the computer looking.
I'm happy to try to build something myself, but I have a sense that someone else has probably solved this issue so I don't want to "reinvent the wheel."
I have seen aba_lockinfo, but it logs almost too much information. I'm trying to get at what processes are causing the trouble - maybe someone can give me pointers for how to interpret the aba_lockinfo data.
Thanks for any help!
webrunner
-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html
August 22, 2008 at 11:04 am
charshman (8/22/2008)
I noticed in the second half of your OR condition, you check:[font="Courier New"]R.blocking_session_id = 0 AND R.session_id IN ...[/font]
It looks like your query will show the blocked processes, but only show the blocking processes if they themselves are not blocked. If you have long running transactions, it's very possible that the blocking could be 3 or 4 levels deep, so I wouldn't want to restrict the query from only showing blocking processes that are not blocked.
If what you were trying to do is find the first blocker that is starting a chain reaction, you might need a recursive CTE.
Here is the updated query, using a temp table. Again, I would like to be able to do this without a temp table, if possible, and I will explore the CTE option (when time permits 🙂 )
Please note that the query as it stands below, returns blocking SPIDs, even if they are themselves blocked, so the result is correct:
-- Script to return blocking and blocked spids with wait times > 1 min
-- SQL 2005
SET NOCOUNT ON;
CREATE TABLE #blocks
(
SPID SMALLINT
,BlockingSPID SMALLINT
,WaitTime BIGINT
,LastWaitType VARCHAR(32)
,WaitResource VARCHAR(32)
,DBName VARCHAR(100)
,CPU_Time INT
,Total_Elapsed_Time INT
,Physical_IO BIGINT
,Logical_Reads BIGINT
,Memory_Usage INT
,Login_Time DATETIME
,[Status] VARCHAR(30)
,HostName VARCHAR(128)
,ProgramName VARCHAR(128)
,Command VARCHAR(16)
,NT_User_Name VARCHAR(128)
,Login_Name VARCHAR(128)
,SQL_Text VARCHAR(1000)
);
INSERT INTO
#blocks
SELECT
R.session_id
,R.blocking_session_id
,R.wait_time
,R.last_wait_type
,R.wait_resource
,DB_NAME(R.database_id)
,R.cpu_time
,R.total_elapsed_time
,R.reads + R.writes
,R.logical_reads
,S.memory_usage
,S.login_time
,R.status
,S.[host_name]
,S.[program_name]
,R.command
,S.nt_user_name
,S.login_name
,LEFT(H.text,2000)
FROM
sys.dm_exec_requests R
INNER JOIN
sys.dm_exec_sessions S
ON
R.session_id = S.session_id
CROSS APPLY
sys.dm_exec_sql_text(R.sql_handle) H
WHERE
R.session_id > 50
AND
(
(R.blocking_session_id <> 0 AND R.wait_time > 60000) -- 1 min
OR
(R.blocking_session_id = 0 AND R.session_id IN (SELECT blocking_session_id FROM sys.dm_exec_requests WHERE blocking_session_id <> 0 ))
)
ORDER BY
R.blocking_session_id;
IF EXISTS (SELECT * FROM #blocks WHERE BlockingSPID > 0)
BEGIN
SELECT * FROM #blocks -- return everything
WHERE BlockingSPID > 0
OR SPID IN (SELECT BlockingSPID FROM #blocks WHERE BlockingSPID > 0);
END
ELSE
BEGIN
TRUNCATE TABLE #blocks;
SELECT * FROM #blocks; -- return nothing
END
DROP TABLE #blocks;
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply