Post reply

deadlocks with a non clustered index on uniqueidentifier

  • February 5, 2009 at 6:47 am

    #199907

    Hello

    I have a table with a unique Identifier column which is the pk (Guid created by a c# application).

    for a long time I used a clustered index on this column. last year we changed the index to be a non clustered one as inserts are supposed to be easier but since than there are deadlocks on that table about once a week which I didn't have before. (there are many databases with about 20-40 users on each one)

    most of the activity in this table is read and update and the delete \ inserts are in a lower rate.

    on a few servers I returned the index to be clustered and it seems it solved the problem but I am not sure why and obviously I cannot be sure the problem is resolved just because it didn't happen since a few weeks ago.

    Thanks in advance.

  • February 5, 2009 at 7:42 am

    #938854

    To give you a complete answer, you'd need to post the code, deadlock graphs, database structures, etc.

    To make a few suggestions, Does the table have any other clustered index? If not, it's a heap table. It's possibly developing a hot spot at the end of the table, a page or a few pages where all the insert action is occuring. A clustered index is going to distribute the data across a larger number of pages, especially when dealing with random data like a GUID, so that inserts are distributed all over the place and less likely to contend with one another. That's probably what's happening, but, again, I'd need to see a lot more detailed information to be sure.

    "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

  • February 5, 2009 at 8:06 am

    #938881

    Thanks for your answer. it gives me a reasonable explanation for the problem.

    I am pretty sure it is the reason as that is the only change in that area for a long time.

    as you wrote , the table has no other clustered index. the only index it has is on the Guid column.

    thanks & all the best

    Uri

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply