December 15, 2008 at 1:11 pm
I just ran into an interesting little problem.
SQL Server 2005 SP2 (9.0.3054) x64 8CPU (2 quad cores) 32GB memory (28GB allocated to SQL Server).
We have one index that when we try to rebuild the index using ONLINE=ON - the process will cause an unrecoverable deadlock. The interesting thing is that the process is deadlocking on itself.
My questions is: has anyone ever seen anything like this? Is this a known issue that could be corrected by upgrading to a later CU?
Any other ideas?
Thanks
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
December 15, 2008 at 1:16 pm
Hmm - haven't heard of this and it shouldn't happen. It can deadlock with user updates but then it should be chosen as the deadlock victim. I'd call Product Support as this seems like a bug.
Thanks
Paul Randal
CEO, SQLskills.com: Check out SQLskills online training!
Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005
December 15, 2008 at 1:36 pm
By unrecoverable, do you mean the deadlock detector's not picking it up? Does having traceflag 1222 on result in a deadlock graph in the error log?
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 15, 2008 at 1:37 pm
Thanks - that is what I was afraid of. I guess we are going to open a ticket with Microsoft.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
December 15, 2008 at 1:43 pm
GilaMonster (12/15/2008)
By unrecoverable, do you mean the deadlock detector's not picking it up? Does having traceflag 1222 on result in a deadlock graph in the error log?
By unrecoverable, I mean the deadlock detector is picking it up and giving me a stack dump. Specifically stating that this deadlock cannot be recovered.
Date12/15/2008 12:18:14 PM
LogSQL Server (Archive #1 - 12/15/2008 12:18:00 PM)
Sourcespid11s
Message
Deadlock monitor failed to resolve this deadlock.
Server may require restart to recover from this condition
Reproducible on a copy of the live database - by issuing an ALTER INDEX on the clustered index for that table. When I use ONLINE=ON, the process immediately deadlocks. Without it - the index rebuild happens in less than a second.
The next thing I am going to try is forcing MAXDOP to 1 for this process and see what happens.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
December 15, 2008 at 1:47 pm
Ok...... Never seen that before.
If it's stack dumping, you need to call customer support.
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 15, 2008 at 1:48 pm
For anybody who is interested, setting MAXDOP to 1 allowed the process to complete successfully, even with ONLINE=ON.
So, it definitely looks like an issue with parallelism on this index. I so hope it is only this index and not any others. 🙂
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
December 15, 2008 at 1:56 pm
GilaMonster (12/15/2008)
Ok...... Never seen that before.If it's stack dumping, you need to call customer support.
Yeah, that is what I figured was going to happen. For the short term, I will just modify my re-indexing procedure to not reindex this table/index online. I really don't like hard-coding in my procedures, but that is better than the alternative.
Thanks.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
December 17, 2008 at 10:15 am
I thought I would come back here and give you all an update. I was able to actually reproduce the same issue on a newly created table. I created the table exactly the same as the original table, then loaded the new table with data from the first table.
Ran the alter index online (parallel process) on the new table and it deadlocked.
Here is how the table was created:
CREATE TABLE dbo.Deadlock_Table (
ID numeric(16,0) IDENTITY(1,1) NOT NULL,
VisitID numeric(16,0) NOT NULL,
ProviderID numeric(16,0) NOT NULL
)
GO
CREATE CLUSTERED INDEX IX_Deadlock_Table ON dbo.Deadlock_Table
(VisitID ASC,
ProviderID ASC
)WITH (PAD_INDEX = off
,STATISTICS_NORECOMPUTE = off
, SORT_IN_TEMPDB = off
, IGNORE_DUP_KEY = off
, DROP_EXISTING = off
, ONLINE = off
, ALLOW_ROW_LOCKS = On
, ALLOW_PAGE_LOCKS = On
, FILLFACTOR = 95
)
GO
As you will notice, we have an IDENTITY created - but not indexed. A clustered index - but it is not unique, no primary key (is it really a table?). I have already talked to the vendor about this - still waiting to hear back from them on why the table was created this way.
With the above table and almost 1.25 million rows - running the alter index online in parallel will cause the deadlock. I changed the clustered index to a non-clustered index and could not reproduce the problem. Added the clustered index on the identity column and made it the primary key - no problem.
But, leave it setup as above - deadlocks every time.
I have Microsoft looking at this issue, just to see if we can figure out exactly what is causing it. For now, I am avoiding the issue by not re-indexing this table online. I hope to have some changes from the vendor on the table structure that will also avoid the problem.
It is still very strange that this table will deadlock like this.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
December 17, 2008 at 10:25 am
And one final update - adding a new non-clustered index on the ID column and making it the primary key fixed the problem. I could not reproduce the deadlock.
Removed the above index - tried to rebuild the clustered index and it deadlocked again.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
December 18, 2008 at 11:26 am
interesting, in testing have you let it run for hours just to see if it ever stops or finishes?
December 18, 2008 at 11:42 am
We first noticed the issue on a Sunday morning, when we found that our nightly maintenance from Friday night was still running.
So, yeah - it ran for a long time :w00t:
It would never have completed because of the deadlock.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply