December 8, 2015 at 5:43 am
If theres one thing I particularly suck at with SQL its recursive hierarchies.. my brain shuts down....
So I have this table that captures blocking details
CREATE TABLE [dbo].[Blocking](
[spid] [int] NULL,
[waitresource] [varchar](512) NULL,
[typ_of_block] [varchar](20) NULL,
[capdt] [datetime] NULL,
[blocked_spid] [int] NULL
)
GO
A typical row would be
spid waitresource typ_of_block capdt blocked_spid
3842:9:78831 Blocking 2015-12-07 09:13:29.43002
407KEY: 21:72067595954855944 (d167984bf49a)Blocked 2015-12-07 09:57:11.140612
I want to be able to display this in hierarchical format so it displays each lead blocker then blocked records for that lead in hierarchical fashion down to the bottom of the tree ... Ive tried and failed and have vanished down the rabbit hole ....
help !!!
December 8, 2015 at 6:24 am
Might not be the answer that you are looking for, but I do think that this might help you. I'm using the procedure sp_blocked_process_report_viewer that was written by Michael J Swart and can be found at http://michaeljswart.com/2011/04/a-new-way-to-examine-blocked-process-reports/. I strongly recommend using this procedure to analyze blocking report done by server side trace or profiler.
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
December 17, 2015 at 10:42 pm
Recursive CTEs aren't bad once you get your head wrapped around them... If you want to post some consumable test data, I'll attempt a solution.
December 18, 2015 at 6:35 pm
Jason A. Long (12/17/2015)
Recursive CTEs aren't bad once you get your head wrapped around them... If you want to post some consumable test data, I'll attempt a solution.
See thee first link in my signature line below for how to properly create readily consumable test data as Jason suggested above. It'll really help us help you much more quickly and accurately.
--Jeff Moden
Change is inevitable... Change for the better is not.
December 19, 2015 at 8:25 am
Adi Cohn-120898 (12/8/2015)
Might not be the answer that you are looking for, but I do think that this might help you. I'm using the procedure sp_blocked_process_report_viewer that was written by Michael J Swart and can be found at http://michaeljswart.com/2011/04/a-new-way-to-examine-blocked-process-reports/. I strongly recommend using this procedure to analyze blocking report done by server side trace or profiler.Adi
The problem I have with that fine article is that you have to know the name of the trace as well as having to set up an SQL Profiler trace even if it is a server side trace. Additionally...
The alternative that I used was to setup an alert for blocking (Object = SQLServer:General Statistics, Counter = Processes blocked) which not only sends me an immediate email when extended blocking occurs but also runs a job to run a proc to capture the information from the sys.dm_exec_requests view and sys.dm_exec_sql_text functions into a permanent table in my "DBA database" for further analysis. That also means that I don't have to mess with any bloody XML or remember that I have a file on the server growing in the background. I also don't have to remember to start SQL Profiler after a server bounce for upgrades or whatever.
--Jeff Moden
Change is inevitable... Change for the better is not.
December 21, 2015 at 9:32 am
Thanks Jeff for the handy hint on the sort of data required and how to post it .. heres a very small subset of data, one chain with a single lead blocker and one with a depth of three
CREATE TABLE [dbo].[Blocking](
[spid] [int] NULL,
[typ_of_block] [varchar](20) NULL,
[capdt] [datetime] NULL,
[blocked_spid] [int] NULL
)
GO
insert blocking
values ('84','Blocking','2015-12-15 05:56:44.677','0')
insert blocking
values ('85','Blocked','2015-12-15 05:56:44.677','84')
insert blocking
values ('106','Blocked','2015-12-15 07:46:25.107','158')
insert blocking
values ('158','Blocking','2015-12-15 07:46:25.107','0')
insert blocking
values ('253','Blocked','2015-12-15 07:46:25.107','106')
insert blocking
values ('299','Blocked','2015-12-15 07:48:33.710','106')
December 21, 2015 at 9:40 am
simon_s (12/21/2015)
Thanks Jeff for the handy hint on the sort of data required and how to post it .. heres a very small subset of data, one chain with a single lead blocker and one with a depth of three
CREATE TABLE [dbo].[Blocking](
[spid] [int] NULL,
[typ_of_block] [varchar](20) NULL,
[capdt] [datetime] NULL,
[blocked_spid] [int] NULL
)
GO
insert blocking
values ('84','Blocking','2015-12-15 05:56:44.677','0')
insert blocking
values ('85','Blocked','2015-12-15 05:56:44.677','84')
insert blocking
values ('106','Blocked','2015-12-15 07:46:25.107','158')
insert blocking
values ('158','Blocking','2015-12-15 07:46:25.107','0')
insert blocking
values ('253','Blocked','2015-12-15 07:46:25.107','106')
insert blocking
values ('299','Blocked','2015-12-15 07:48:33.710','106')
Bit confused by the data and the titles of the columns. In the last to inserts above, is looks like SPID 106 is blocking SPIDs 253 and 299 rather than the other way around as the column names you provided would suggest. I'm pretty sure that it's not possible for two SPIDs to block a given SPID.
Heh... never mind. Not enough coffee. That's answers my own question.
--Jeff Moden
Change is inevitable... Change for the better is not.
December 21, 2015 at 10:36 am
Jeff will no doubt follow up with something super cool... but in the mean time, this should give you the basic idea...
IF OBJECT_ID('tempdb..#Blocking', 'U') IS NOT NULL
DROP TABLE #Blocking;
CREATE TABLE #Blocking(
[spid] [int] NULL,
[typ_of_block] [varchar](20) NULL,
[capdt] [datetime] NULL,
[blocked_spid] [int] NULL
);
GO
insert #Blocking (spid,typ_of_block,capdt,blocked_spid)
values ('84','Blocking','2015-12-15 05:56:44.677','0');
insert #Blocking (spid,typ_of_block,capdt,blocked_spid)
values ('85','Blocked','2015-12-15 05:56:44.677','84');
insert #Blocking (spid,typ_of_block,capdt,blocked_spid)
values ('106','Blocked','2015-12-15 07:46:25.107','158');
insert #Blocking (spid,typ_of_block,capdt,blocked_spid)
values ('158','Blocking','2015-12-15 07:46:25.107','0');
insert #Blocking (spid,typ_of_block,capdt,blocked_spid)
values ('253','Blocked','2015-12-15 07:46:25.107','106');
insert #Blocking (spid,typ_of_block,capdt,blocked_spid)
values ('299','Blocked','2015-12-15 07:48:33.710','106');
WITH
cte_Blocks AS (
SELECT
b.spid,
b.typ_of_block,
b.capdt,
b.blocked_spid,
blocked_level = CAST(1 AS INT),
blocking_chain = CAST(b.spid AS VARCHAR(8000))
FROM
#Blocking b
WHERE
b.blocked_spid = 0
UNION ALL
SELECT
b.spid,
b.typ_of_block,
b.capdt,
b.blocked_spid,
blocked_level = cb.blocked_level + 1,
blocking_chain = CAST(CONCAT(cb.blocking_chain, '>', CAST(b.spid AS varchar(8000))) AS VARCHAR(8000))
FROM
#Blocking b
JOIN cte_Blocks cb
ON cb.spid = b.blocked_spid
)
SELECT
cb.spid,
cb.typ_of_block,
cb.capdt,
cb.blocked_spid,
cb.blocked_level,
cb.blocking_chain
FROM
cte_Blocks cb
ORDER BY
cb.capdt,
cb.blocking_chain;
Results:
spid typ_of_block capdt blocked_spid blocked_level blocking_chain
----------- -------------------- ----------------------- ------------ ------------- -----------------------
84 Blocking 2015-12-15 05:56:44.677 0 1 84
85 Blocked 2015-12-15 05:56:44.677 84 2 84>85
158 Blocking 2015-12-15 07:46:25.107 0 1 158
106 Blocked 2015-12-15 07:46:25.107 158 2 158>106
253 Blocked 2015-12-15 07:46:25.107 106 3 158>106>253
299 Blocked 2015-12-15 07:48:33.710 106 3 158>106>299
December 21, 2015 at 7:21 pm
Jason A. Long (12/21/2015)
Jeff will no doubt follow up with something super cool...
Not so much as the hierarchical query goes. You pretty much topped that off quite nicely. The only thing that you might want to add is a check to see if a SPID has already been added to the hierarchical path so that when a deadlock happens (a loop of spids blocking each other), you don't get an error in the code. You could actually use that detector to add a marker to the path to indicate that a deadlock had occurred.
If the OP also does an APPLY between the sys.dm_exec_requests that I believe he may be using to capture the information and the sys.dm_exec_sql_text([sql_handle]) function, you can also get a copy of the offending code for each line. If that's of interest, let me know and I'll also show you how to make the code "click-to-view" as it's actually written.
--Jeff Moden
Change is inevitable... Change for the better is not.
December 21, 2015 at 7:52 pm
Minor change to deal with deadlocks:
WITH
cte_Blocks AS (
SELECT
b.spid,
b.typ_of_block,
b.capdt,
b.blocked_spid,
blocked_level = CAST(1 AS INT),
blocking_chain = CAST(b.spid AS VARCHAR(8000))
FROM
#Blocking b
WHERE b.blocked_spid = 0
UNION ALL
SELECT
b.spid,
b.typ_of_block,
b.capdt,
b.blocked_spid,
blocked_level = cb.blocked_level + 1,
blocking_chain = CAST(cb.blocking_chain + '>' + CAST(b.spid AS varchar(8000)) AS VARCHAR(8000))
FROM
#Blocking b
JOIN cte_Blocks cb
ON cb.spid = b.blocked_spid
),
cte_Deadlocks AS (
SELECT
b.spid,
b.typ_of_block,
b.capdt,
b.blocked_spid,
blocked_level = CAST(1 AS INT),
blocking_chain = CAST(b.spid AS VARCHAR(8000))
FROM
#Blocking b
WHERE b.spid NOT IN (SELECT spid FROM cte_Blocks )
UNION ALL
SELECT
b.spid,
b.typ_of_block,
b.capdt,
b.blocked_spid,
blocked_level = cb.blocked_level + 1,
blocking_chain = CAST(cb.blocking_chain + '>' + CAST(b.spid AS varchar(8000)) AS VARCHAR(8000))
+ CASE WHEN cb.blocking_chain LIKE CAST(b.spid AS varchar(8000)) + '>%' THEN '!! DEADLOCK !!' ELSE '' END
FROM
#Blocking b
JOIN cte_Deadlocks cb
ON cb.spid = b.blocked_spid AND cb.blocking_chain NOT LIKE '%DEADLOCK%'
)
SELECT
cb.spid,
cb.typ_of_block,
cb.capdt,
cb.blocked_spid,
cb.blocked_level,
cb.blocking_chain
FROM
cte_Blocks cb
UNION ALL
SELECT
cb.spid,
cb.typ_of_block,
cb.capdt,
cb.blocked_spid,
cb.blocked_level,
cb.blocking_chain
FROM
cte_Deadlocks cb
ORDER BY
capdt,
blocking_chain;
And yes, CONCAT for some reason is not recognised by my SQL2008 server.
Any idea why?
:hehe:
_____________
Code for TallyGenerator
December 21, 2015 at 8:48 pm
Jeff Moden
Not so much as the hierarchical query goes. You pretty much topped that off quite nicely. The only thing that you might want to add is a check to see if a SPIC has already been added to the hierarchical path so that when a deadlock happens (a loop of spids blocking each other), you don't get an error in the code. You could actually use that detector to add a marker to the path to indicate that a deadlock had occurred.
I like the idea... I was able to successfully break a loop by rejecting the "chain duplicate" row... But that's also rejecting the "indicator" row. (It's provably just late and I'm not wrapping my head all the way around it... At least I'm going to use it as my excuse...)
In any case here's an updated test harness and my 1st stab at a solution... Aside from breaking up the infinite loop, I'm not in love with what I have so far...
IF OBJECT_ID('tempdb..#Blocking', 'U') IS NOT NULL
DROP TABLE #Blocking;
CREATE TABLE #Blocking(
[spid] [int] NULL,
[typ_of_block] [varchar](20) NULL,
[capdt] [datetime] NULL,
[blocked_spid] [int] NULL
);
GO
insert #Blocking (spid,typ_of_block,capdt,blocked_spid)
values ('84','Blocking','2015-12-15 05:56:44.677','0');
insert #Blocking (spid,typ_of_block,capdt,blocked_spid)
values ('85','Blocked','2015-12-15 05:56:44.677','84');
insert #Blocking (spid,typ_of_block,capdt,blocked_spid)
values ('106','Blocked','2015-12-15 07:46:25.107','158');
insert #Blocking (spid,typ_of_block,capdt,blocked_spid)
values ('158','Blocking','2015-12-15 07:46:25.107','0');
insert #Blocking (spid,typ_of_block,capdt,blocked_spid)
values ('253','Blocked','2015-12-15 07:46:25.107','106');
insert #Blocking (spid,typ_of_block,capdt,blocked_spid)
values ('299','Blocked','2015-12-15 07:48:33.710','106');
-- new "looping" data rows ----------------------------
insert #Blocking (spid,typ_of_block,capdt,blocked_spid)
values ('300','Blocked','2015-12-15 07:48:34.000','299');
insert #Blocking (spid,typ_of_block,capdt,blocked_spid)
values ('301','Blocked','2015-12-15 07:48:35.000','300');
insert #Blocking (spid,typ_of_block,capdt,blocked_spid)
values ('302','Blocked','2015-12-15 07:48:36.000','301');
insert #Blocking (spid,typ_of_block,capdt,blocked_spid)
values ('299','Blocked','2015-12-15 07:48:37.000','302');
insert #Blocking (spid,typ_of_block,capdt,blocked_spid)
values ('300','Blocked','2015-12-15 07:48:38.000','299');
insert #Blocking (spid,typ_of_block,capdt,blocked_spid)
values ('301','Blocked','2015-12-15 07:48:39.000','300');
WITH
cte_Blocks AS (
SELECT
b.spid,
b.typ_of_block,
b.capdt,
b.blocked_spid,
blocked_level = CAST(1 AS INT),
blocking_chain = CAST(b.spid AS VARCHAR(8000))
FROM
#Blocking b
WHERE
b.blocked_spid = 0
UNION ALL
SELECT
b.spid,
b.typ_of_block,
b.capdt,
b.blocked_spid,
blocked_level = cb.blocked_level + 1,
blocking_chain = CAST(CONCAT(cb.blocking_chain, '>', CAST(b.spid AS varchar(8000))) AS VARCHAR(8000))
FROM
#Blocking b
JOIN cte_Blocks cb
ON cb.spid = b.blocked_spid
WHERE
NOT EXISTS (SELECT 1 FROM dbo.DelimitedSplit8K(cb.blocking_chain, '>') sc WHERE b.spid = CAST(sc.Item AS INT))
)
SELECT
cb.spid,
cb.typ_of_block,
capdt = MIN(cb.capdt),
cb.blocked_spid,
cb.blocked_level,
cb.blocking_chain,
dl_loops = COUNT(*)
FROM
cte_Blocks cb
GROUP BY
cb.spid,
cb.typ_of_block,
cb.blocked_spid,
cb.blocked_level,
cb.blocking_chain
ORDER BY
capdt,
cb.blocking_chain
;
Needless to say I'm interested to see your solution...
Jeff Moden
If the OP also does an APPLY between the sys.dm_exec_requests that I believe he may be using to capture the information and the sys.dm_exec_sql_text([sql_handle]) function, you can also get a copy of the offending code for each line. If that's of interest, let me know and I'll also show you how to make the code "click-to-view" as it's actually written.
I can't speak for the OP, but I'm interested to see that as well...
December 21, 2015 at 9:00 pm
Sergiy (12/21/2015)
Minor change to deal with deadlocks:
WITHcte_Blocks AS (
SELECT
b.spid,
b.typ_of_block,
b.capdt,
b.blocked_spid,
blocked_level = CAST(1 AS INT),
blocking_chain = CAST(b.spid AS VARCHAR(8000))
FROM
#Blocking b
WHERE b.blocked_spid = 0
UNION ALL
SELECT
b.spid,
b.typ_of_block,
b.capdt,
b.blocked_spid,
blocked_level = cb.blocked_level + 1,
blocking_chain = CAST(cb.blocking_chain + '>' + CAST(b.spid AS varchar(8000)) AS VARCHAR(8000))
FROM
#Blocking b
JOIN cte_Blocks cb
ON cb.spid = b.blocked_spid
),
cte_Deadlocks AS (
SELECT
b.spid,
b.typ_of_block,
b.capdt,
b.blocked_spid,
blocked_level = CAST(1 AS INT),
blocking_chain = CAST(b.spid AS VARCHAR(8000))
FROM
#Blocking b
WHERE b.spid NOT IN (SELECT spid FROM cte_Blocks )
UNION ALL
SELECT
b.spid,
b.typ_of_block,
b.capdt,
b.blocked_spid,
blocked_level = cb.blocked_level + 1,
blocking_chain = CAST(cb.blocking_chain + '>' + CAST(b.spid AS varchar(8000)) AS VARCHAR(8000))
+ CASE WHEN cb.blocking_chain LIKE CAST(b.spid AS varchar(8000)) + '>%' THEN '!! DEADLOCK !!' ELSE '' END
FROM
#Blocking b
JOIN cte_Deadlocks cb
ON cb.spid = b.blocked_spid AND cb.blocking_chain NOT LIKE '%DEADLOCK%'
)
SELECT
cb.spid,
cb.typ_of_block,
cb.capdt,
cb.blocked_spid,
cb.blocked_level,
cb.blocking_chain
FROM
cte_Blocks cb
UNION ALL
SELECT
cb.spid,
cb.typ_of_block,
cb.capdt,
cb.blocked_spid,
cb.blocked_level,
cb.blocking_chain
FROM
cte_Deadlocks cb
ORDER BY
capdt,
blocking_chain;
Just ran this against my updated test harness... It didn't break the loop and got the "maximum recursion" error.
Sergiy (12/21/2015)
And yes, CONCAT for some reason is not recognised by my SQL2008 server.Any idea why?
:hehe:
I know exactly why... I'm a big dumb animal who wasn't paying attention to which sub-forum I was in... :doze: Mia Cupla...
December 21, 2015 at 9:01 pm
-- removing duplicate post --
December 22, 2015 at 3:26 pm
Jason A. Long (12/21/2015)
Just ran this against my updated test harness... It didn't break the loop and got the "maximum recursion" error.
I must admin - I have quite limited experience with deadlocking.
I always try to prevent deadlocks from happening with an appropriate design and coding.
So, when I face a deadlock situation there are no non-blocked transactions (the ones with Blocked=0) anymore.
Therefore I did not consider an option of a deadlock within a tree growing from a non-blocked transaction.
Well, it's easy to put the deadlock catching code into cte_Blocks too:
WITH
cte_Blocks AS (
SELECT
b.spid,
b.typ_of_block,
b.capdt,
b.blocked_spid,
blocked_level = CAST(1 AS INT),
blocking_chain = CAST(b.spid AS VARCHAR(8000))
FROM
#Blocking b
WHERE b.blocked_spid = 0
UNION ALL
SELECT
b.spid,
b.typ_of_block,
b.capdt,
b.blocked_spid,
blocked_level = cb.blocked_level + 1,
blocking_chain = CAST(cb.blocking_chain + '>' + CAST(b.spid AS varchar(8000)) AS VARCHAR(8000))
+ CASE WHEN cb.blocking_chain LIKE CAST(b.spid AS varchar(8000)) + '>%' or cb.blocking_chain LIKE '%>' + CAST(b.spid AS varchar(8000)) + '>%' THEN '!! DEADLOCK !!' ELSE '' END
FROM
#Blocking b
JOIN cte_Blocks cb
ON cb.spid = b.blocked_spid AND cb.blocking_chain NOT LIKE '%DEADLOCK%'
),
cte_Deadlocks AS (
SELECT
b.spid,
b.typ_of_block,
b.capdt,
b.blocked_spid,
blocked_level = CAST(1 AS INT),
blocking_chain = CAST(b.spid AS VARCHAR(8000))
FROM
#Blocking b
WHERE b.spid NOT IN (SELECT spid FROM cte_Blocks )
UNION ALL
SELECT
b.spid,
b.typ_of_block,
b.capdt,
b.blocked_spid,
blocked_level = cb.blocked_level + 1,
blocking_chain = CAST(cb.blocking_chain + '>' + CAST(b.spid AS varchar(8000)) AS VARCHAR(8000))
+ CASE WHEN cb.blocking_chain LIKE CAST(b.spid AS varchar(8000)) + '>%' or cb.blocking_chain LIKE '%>' + CAST(b.spid AS varchar(8000)) + '>%' THEN '!! DEADLOCK !!' ELSE '' END
FROM
#Blocking b
JOIN cte_Deadlocks cb
ON cb.spid = b.blocked_spid AND cb.blocking_chain NOT LIKE '%DEADLOCK%'
)
SELECT
cb.spid,
cb.typ_of_block,
cb.capdt,
cb.blocked_spid,
cb.blocked_level,
cb.blocking_chain
FROM
cte_Blocks cb
UNION ALL
SELECT
cb.spid,
cb.typ_of_block,
cb.capdt,
cb.blocked_spid,
cb.blocked_level,
cb.blocking_chain
FROM
cte_Deadlocks cb
ORDER BY
capdt,
blocking_chain;
On another hand, you code, Jason, fails to catch the simplest classic case of a deadlock: when 2 or more transactions lock each other:
insert #Blocking (spid,typ_of_block,capdt,blocked_spid)
values (1084,'Blocking','2015-12-15 05:56:44.677',1085);
insert #Blocking (spid,typ_of_block,capdt,blocked_spid)
values (1085,'Blocked','2015-12-15 05:56:44.677',1186);
insert #Blocking (spid,typ_of_block,capdt,blocked_spid)
values (1186,'Blocked','2015-12-15 05:56:44.677',1084);
insert #Blocking (spid,typ_of_block,capdt,blocked_spid)
values (1010,'Blocking','2015-12-15 05:56:44.677',1011);
insert #Blocking (spid,typ_of_block,capdt,blocked_spid)
values (1011,'Blocked','2015-12-15 05:56:44.677',1010);
Your code omits them completely.
Which is not quite good, I think. 😎
P.S. And you still used CONCAT - must be too hard to refuse. 🙂
_____________
Code for TallyGenerator
December 22, 2015 at 3:59 pm
Yea... I'm right there with you. My latest test harness was based solely on my perception of what Jeff was talking about. I'm not certain that it's actually representative of a real, possible situation.
Hopefully, Jeff will chime back in.
Viewing 15 posts - 1 through 15 (of 22 total)
You must be logged in to reply to this topic. Login to reply