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
CREATE CLUSTERED INDEX IX_Deadlock_Table ON dbo.Deadlock_Table
( VisitID 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
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.