December 16, 2011 at 7:23 am
Grant Fritchey (12/16/2011)
Anyone have a script that will reliably generate a deadlock from a single process? I need to be able to repeatedly generate a parallelism deadlock for some internal testing. I won't be publishing anything from it, so your trade secrets are safe.
Hi Grant,
-- Test table
CREATE TABLE dbo.Test
(
id INTEGER IDENTITY (1, 1) NOT NULL,
value INTEGER NOT NULL,
padding CHAR(999) NOT NULL,
CONSTRAINT [PK dbo.Test (id)]
PRIMARY KEY CLUSTERED (id),
)
;
GO
-- 1,000,000 rows
INSERT dbo.Test WITH (TABLOCKX)
(value, padding)
SELECT TOP (1000000)
value = CONVERT(INTEGER, Data.n),
padding = REPLICATE(CHAR(65 + (Data.n % 26)), 999)
FROM
(
SELECT TOP (1000000)
n = ROW_NUMBER() OVER (ORDER BY (SELECT 0))
FROM master.sys.columns C1
CROSS JOIN master.sys.columns C2
CROSS JOIN master.sys.columns C3
ORDER BY
n ASC
) AS Data
ORDER BY
Data.n ASC
OPTION (RECOMPILE)
GO
-- Parallel deadlock and exchange spills
-- Trace: Lock Deadlock Chain (note resource type exchange) and Exchange Spill Event
DECLARE
@a INTEGER,
@b-2 INTEGER
SELECT
@a = a,
@b-2 = b
FROM
(
SELECT TOP (2000000)
a = T1.id % 80,
b = CHECKSUM(REVERSE(T1.padding))
FROM dbo.Test AS T1
JOIN dbo.Test AS T2 ON
T2.id = T1.id
WHERE
T1.id BETWEEN 1 AND 200000
ORDER BY
a, b
UNION ALL
SELECT TOP (2000000)
a = T1.id % 80,
b = CHECKSUM(REVERSE(T1.padding))
FROM dbo.Test AS T1
JOIN dbo.Test AS T2 ON
T2.id = T1.id
WHERE
T1.id BETWEEN 1 AND 200000
ORDER BY
a, b
) AS x
ORDER BY
x.a
OPTION (RECOMPILE, MAXDOP 0)
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
December 16, 2011 at 7:38 am
SQL Kiwi (12/16/2011)
Grant Fritchey (12/16/2011)
Anyone have a script that will reliably generate a deadlock from a single process? I need to be able to repeatedly generate a parallelism deadlock for some internal testing. I won't be publishing anything from it, so your trade secrets are safe.Hi Grant,
-- Test table
CREATE TABLE dbo.Test
(
id INTEGER IDENTITY (1, 1) NOT NULL,
value INTEGER NOT NULL,
padding CHAR(999) NOT NULL,
CONSTRAINT [PK dbo.Test (id)]
PRIMARY KEY CLUSTERED (id),
)
;
GO
-- 1,000,000 rows
INSERT dbo.Test WITH (TABLOCKX)
(value, padding)
SELECT TOP (1000000)
value = CONVERT(INTEGER, Data.n),
padding = REPLICATE(CHAR(65 + (Data.n % 26)), 999)
FROM
(
SELECT TOP (1000000)
n = ROW_NUMBER() OVER (ORDER BY (SELECT 0))
FROM master.sys.columns C1
CROSS JOIN master.sys.columns C2
CROSS JOIN master.sys.columns C3
ORDER BY
n ASC
) AS Data
ORDER BY
Data.n ASC
OPTION (RECOMPILE)
GO
-- Parallel deadlock and exchange spills
-- Trace: Lock Deadlock Chain (note resource type exchange) and Exchange Spill Event
DECLARE
@a INTEGER,
@b-2 INTEGER
SELECT
@a = a,
@b-2 = b
FROM
(
SELECT TOP (2000000)
a = T1.id % 80,
b = CHECKSUM(REVERSE(T1.padding))
FROM dbo.Test AS T1
JOIN dbo.Test AS T2 ON
T2.id = T1.id
WHERE
T1.id BETWEEN 1 AND 200000
ORDER BY
a, b
UNION ALL
SELECT TOP (2000000)
a = T1.id % 80,
b = CHECKSUM(REVERSE(T1.padding))
FROM dbo.Test AS T1
JOIN dbo.Test AS T2 ON
T2.id = T1.id
WHERE
T1.id BETWEEN 1 AND 200000
ORDER BY
a, b
) AS x
ORDER BY
x.a
OPTION (RECOMPILE, MAXDOP 0)
I'm not even marginally surprised that you have one of these sitting around.
Thank you!
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
December 16, 2011 at 7:44 am
Grant Fritchey (12/16/2011)
SQL Kiwi (12/16/2011)
Grant Fritchey (12/16/2011)
Anyone have a script that will reliably generate a deadlock from a single process? I need to be able to repeatedly generate a parallelism deadlock for some internal testing. I won't be publishing anything from it, so your trade secrets are safe.Hi Grant,
I'm not even marginally surprised that you have one of these sitting around.
Thank you!
My thoughts exactly.
---------------------------------------------------------
How best to post your question[/url]
How to post performance problems[/url]
Tally Table:What it is and how it replaces a loop[/url]
"stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."
December 16, 2011 at 7:46 am
Grant Fritchey (12/16/2011)
I'm not even marginally surprised that you have one of these sitting around. Thank you!
Clearly I will need to work harder at surprising you in future 🙂
You're welcome.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
December 16, 2011 at 7:47 am
SQL Kiwi (12/16/2011)
Grant Fritchey (12/16/2011)
I'm not even marginally surprised that you have one of these sitting around. Thank you!Clearly I will need to work harder at surprising you in future 🙂
You're welcome.
Well I was surprised it took you so long to reply if it helps! 😛
December 16, 2011 at 7:51 am
SQL Kiwi (12/16/2011)
Grant Fritchey (12/16/2011)
I'm not even marginally surprised that you have one of these sitting around. Thank you!Clearly I will need to work harder at surprising you in future 🙂
You're welcome.
It generated an ugly parallel execution plan, but I'm not getting a deadlock. What did I do wrong?
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
December 16, 2011 at 7:56 am
SQL Kiwi (12/16/2011)
Grant Fritchey (12/16/2011)
I'm not even marginally surprised that you have one of these sitting around. Thank you!Clearly I will need to work harder at surprising you in future 🙂
You're welcome.
Oops. Sorry. I'm wrong, wrong, wrong. It did get the deadlocks. Thanks again.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
December 16, 2011 at 7:58 am
jcrawf02 (12/16/2011)
By the way, if anyone feels so inclined, prayers and happy thoughts would be appreciated for my family today. No more info at the moment, I might share in a few weeks if certain things happen. Little nervous at the moment.
Many happy wishes coming your way.
December 16, 2011 at 8:14 am
Grant Fritchey (12/16/2011)
It generated an ugly parallel execution plan, but I'm not getting a deadlock. What did I do wrong?
Replying to the wrong post here (I see you worked it out already), but I just want to add: if you get chance, try it on SQL Server 2012 - you get a nice warning triangle on the Gather Streams (which is where the spill occurs). This is such a nice new feature - I doubt many people routinely run a trace looking for Exchange Spill events.
Getting a repro for an undetected/unresolved parallel deadlock is much, much harder; these are bugs, and tend to get fixed quite rapidly.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
December 16, 2011 at 8:17 am
SQL Kiwi (12/16/2011)
Grant Fritchey (12/16/2011)
It generated an ugly parallel execution plan, but I'm not getting a deadlock. What did I do wrong?Replying to the wrong post here (I see you worked it out already), but I just want to add: if you get chance, try it on SQL Server 2012 - you get a nice warning triangle on the Gather Streams (which is where the spill occurs). This is such a nice new feature - I doubt many people routinely run a trace looking for Exchange Spill events.
Getting a repro for an undetected/unresolved parallel deadlock is much, much harder; these are bugs, and tend to get fixed quite rapidly.
Excellent point. All the documentation I keep finding everywhere is on various fixes. Again, thanks.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
December 16, 2011 at 8:22 am
Ninja's_RGR'us (12/16/2011)
Ninja's_RGR'us (8/19/2011)
Issue posted on connectAs of today, closed as won't fix!!!
You know, it's not like it causes corruption and kills your prod db or anything that serious :sick:
Time to speak up if you're as disgusted as I am.
Wow, that is, um, disappointing.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
December 16, 2011 at 8:27 am
Ninja's_RGR'us (12/16/2011)
That URL migh actually work 😉http://icanhascheezburger.com/2011/12/15/funny-pictures-http-status-cats/[/url]
hahahahahahahahaha
all the way to...
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
December 16, 2011 at 8:29 am
jcrawf02 (12/16/2011)
By the way, if anyone feels so inclined, prayers and happy thoughts would be appreciated for my family today. No more info at the moment, I might share in a few weeks if certain things happen. Little nervous at the moment.
Good luck and you got 'em
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
December 16, 2011 at 8:38 am
jcrawf02 (12/16/2011)
By the way, if anyone feels so inclined, prayers and happy thoughts would be appreciated for my family today. No more info at the moment, I might share in a few weeks if certain things happen. Little nervous at the moment.
Will keep your family in our prayers. Hope everything turns out fine.
-Roy
December 16, 2011 at 8:43 am
jcrawf02 (12/16/2011)
By the way, if anyone feels so inclined, prayers and happy thoughts would be appreciated for my family today. No more info at the moment, I might share in a few weeks if certain things happen. Little nervous at the moment.
I kind of know how that feels!
+1M on the help, whereever it comes from.
Viewing 15 posts - 32,656 through 32,670 (of 66,712 total)
You must be logged in to reply to this topic. Login to reply