December 16, 2011 at 8: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,
-- 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!
Yeah - it seems Paul has everything or knows enough about everything that he can make it look like he has everything.
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:52 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.
Thoughts and prayers coming your way. Hope everything turns out well.
December 16, 2011 at 9:19 am
SAP = Blech
--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams
December 16, 2011 at 9:22 am
SQLRNNR (12/16/2011)
Yeah - it seems Paul has everything or knows enough about everything that he can make it look like he has everything.
Not at all, sadly 🙁
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
December 16, 2011 at 9:37 am
Thanks all, sincerely appreciated 🙂 I feel a #SQLfamily post coming on...
---------------------------------------------------------
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 10:46 am
SQL Kiwi (12/16/2011)
Ninja's_RGR'us (12/16/2011)
Connect items are often closed as Won't Fix before an explanation or more details are provided.
Yes, which quite frankly is a problem in and of itself. Great impression for customers/users - 'We care so little about the issue that we won't tell you why we close the item'
Most users aren't going to see (or care about) complexities of the internal process.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 16, 2011 at 10:51 am
GilaMonster (12/16/2011)
SQL Kiwi (12/16/2011)
Ninja's_RGR'us (12/16/2011)
Connect items are often closed as Won't Fix before an explanation or more details are provided.Yes, which quite frankly is a problem in and of itself. Great impression for customers/users - 'We care so little about the issue that we won't tell you why we close the item'
Most users aren't going to see (or care about) complexities of the internal process.
Ironically enough I was going to post a connect item about that as well. Once the decision is made, the explaination should be available as well.
If it's not, it shouldn't be much of an issue to leave the connect item alone untill the info is available. I know there's hidden complexity, but it's not my problem as your customer.
"We don't care about corruption" is not great PR for any RDBSM.
December 16, 2011 at 11:22 am
GilaMonster (12/16/2011)
SQL Kiwi (12/16/2011)
Connect items are often closed as Won't Fix before an explanation or more details are provided.Yes, which quite frankly is a problem in and of itself. Great impression for customers/users - 'We care so little about the issue that we won't tell you why we close the item'
Most users aren't going to see (or care about) complexities of the internal process.
I agree - it's not a good look at all. My comment was aimed at informing; I am not defending it.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
December 16, 2011 at 11:26 am
SQL Kiwi (12/16/2011)
GilaMonster (12/16/2011)
SQL Kiwi (12/16/2011)
Connect items are often closed as Won't Fix before an explanation or more details are provided.Yes, which quite frankly is a problem in and of itself. Great impression for customers/users - 'We care so little about the issue that we won't tell you why we close the item'
Most users aren't going to see (or care about) complexities of the internal process.
I agree - it's not a good look at all. My comment was aimed at informing; I am not defending it.
MS replied on connect already explaining the lack of explaining. It's won't fix now because there are bugs in the current fix, so it's now in the next release.
Hell of a difference!
Still shows as won't fix last time I checked! :hehe:
December 16, 2011 at 11:31 am
Ninja's_RGR'us (12/16/2011)
SQL Kiwi (12/16/2011)
GilaMonster (12/16/2011)
SQL Kiwi (12/16/2011)
Connect items are often closed as Won't Fix before an explanation or more details are provided.Yes, which quite frankly is a problem in and of itself. Great impression for customers/users - 'We care so little about the issue that we won't tell you why we close the item'
Most users aren't going to see (or care about) complexities of the internal process.
I agree - it's not a good look at all. My comment was aimed at informing; I am not defending it.
MS replied on connect already explaining the lack of explaining. It's won't fix now because there are bugs in the current fix, so it's now in the next release.
Hell of a difference!
Still shows as won't fix last time I checked! :hehe:
I better idea would have been to assign "scheduled to be fixed" instead of "won't fix"
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 11:33 am
SQLRNNR (12/16/2011)
Ninja's_RGR'us (12/16/2011)
SQL Kiwi (12/16/2011)
GilaMonster (12/16/2011)
SQL Kiwi (12/16/2011)
Connect items are often closed as Won't Fix before an explanation or more details are provided.Yes, which quite frankly is a problem in and of itself. Great impression for customers/users - 'We care so little about the issue that we won't tell you why we close the item'
Most users aren't going to see (or care about) complexities of the internal process.
I agree - it's not a good look at all. My comment was aimed at informing; I am not defending it.
MS replied on connect already explaining the lack of explaining. It's won't fix now because there are bugs in the current fix, so it's now in the next release.
Hell of a difference!
Still shows as won't fix last time I checked! :hehe:
I better idea would have been to assign "scheduled to be fixed" instead of "won't fix"
Next RC is in February, and I bet it will be fixed.
December 16, 2011 at 11:43 am
Revenant (12/16/2011)
SQLRNNR (12/16/2011)
Ninja's_RGR'us (12/16/2011)
SQL Kiwi (12/16/2011)
GilaMonster (12/16/2011)
SQL Kiwi (12/16/2011)
Connect items are often closed as Won't Fix before an explanation or more details are provided.Yes, which quite frankly is a problem in and of itself. Great impression for customers/users - 'We care so little about the issue that we won't tell you why we close the item'
Most users aren't going to see (or care about) complexities of the internal process.
I agree - it's not a good look at all. My comment was aimed at informing; I am not defending it.
MS replied on connect already explaining the lack of explaining. It's won't fix now because there are bugs in the current fix, so it's now in the next release.
Hell of a difference!
Still shows as won't fix last time I checked! :hehe:
I better idea would have been to assign "scheduled to be fixed" instead of "won't fix"
Next RC is in February, and I bet it will be fixed.
I know this is not a small bug. After all it's in the rollback command, so this needs more than proper attention.
Worse than that it has to be fixed in all supported version (2008, R2, 2012).
On top of that, each version had slightly different behaviors and error messages. Yes message(s), because there was more than 1 errors in all cases.
On top of that the behavior re-change again depending on the amount of data updated.
I'm just glad to hear that they are taking care of it!
December 16, 2011 at 12:07 pm
Revenant (12/16/2011)
Next RC is in February, and I bet it will be fixed.
It's not a Denali bug. It's been reproduced in everything from SQL 2000 right up to SQL 2012 CTP 3.
Now SQL 2000 and 2005 are out of support, so it may not get fixed there, but that leaves SQL 2008, SQL 2008 R2 and SQL 2012, all of which need the bug fixed.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 16, 2011 at 12:23 pm
GilaMonster (12/16/2011)
Revenant (12/16/2011)
Next RC is in February, and I bet it will be fixed.It's not a Denali bug. It's been reproduced in everything from SQL 2000 right up to SQL 2012 CTP 3.
Now SQL 2000 and 2005 are out of support, so it may not get fixed there, but that leaves SQL 2008, SQL 2008 R2 and SQL 2012, all of which need the bug fixed.
Yes; however, as 2012 goes RC, I will push the guys to have it fixed. There may be a fix for 2008 and R2, but I do not know those schedules.
December 16, 2011 at 1:13 pm
Stefan Krzywicki (12/16/2011)
SAP = Blech
SAP = a lot worse than just Blech
Tom
Viewing 15 posts - 32,671 through 32,685 (of 66,712 total)
You must be logged in to reply to this topic. Login to reply