Deadlock victim for CREATE INDEX

Jan Vávra 341 Reputation points
2022-07-11T18:14:55.96+00:00

I have a production table on Azure SQL Database of size 140 GB.
I was creating a index with ONLINE=ON for 9 hours and I got "You have been choosen as a deadlock victim".

In the index list the index is shown. But can I trust it was created properly?

Azure SQL Database
0 comments No comments
{count} vote

Accepted answer
  1. Alberto Morillo 34,456 Reputation points MVP
    2022-07-11T19:49:06.35+00:00

    When creating an index with online = on, the create index process will not block when creating the index object itself, but when it comes to near the end of the process, it will acquire a schema modification Schema Modification lock (SCH-M) for a period of time in order to actually add the index to the table, this lock type will block all outside operations until the lock is released, which could account for your blocking issues.

    You can drop the index and recreate it with the WAIT_AT_LOW_PRIORITY option and MAX_DURATION = 0 MINUTES.

    ALTER INDEX IX_Index_TableName1  
    ON dbo.[TableName1]  
    REBUILD WITH (FILLFACTOR = 90, ONLINE = ON ( WAIT_AT_LOW_PRIORITY  
       ( MAX_DURATION = 0 MINUTES, ABORT_AFTER_WAIT = NONE ) ));  
    

    Let me know if that works for you, because we have the option to specify if we want to kill processes that are blocking the index creation.

    Hope this helps.

    0 comments No comments

1 additional answer

Sort by: Most helpful
  1. Jan Vávra 341 Reputation points
    2022-07-13T06:47:34.283+00:00

    Well, the index was created.
    I've used

    CREATE NONCLUSTERED INDEX [indexName] ON [dbo].[tableName]
    (
    cols
    )
    INCLUDE(cols)
    WITH ( ONLINE = ON ( WAIT_AT_LOW_PRIORITY ( MAX_DURATION = 0 MINUTES, ABORT_AFTER_WAIT = NONE ))

    Thanks a lot.

    0 comments No comments

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.