Deadlocks due bad fragmentation?

  • I maybe wrong about this, but wanted to check or revise my own concepts.

    I thought deadlocks (most of them) were due poor or bad code; a process pinning other one, and the other one, waiting for 1st one to finish.

    Had a very large databases, with code behind that needs to be improved, that usually generates bunch of deadlocks all Monday mornings. I decided to enable the 1222 flag plus create ReIndex jobs (this db never had one, incredible but true) But to my surprise, got no deadlocks today. Knock on wood, but nothing so far.

    Could be that the ReIndex job helped somehow? and if that was the case, why ... because is reading pages faster creating less locking, maybe?

    I should also say that I moved ldf to different drive, was on same one.

  • No, fragmentation will not cause deadlocks, neither will removing fragmentation resolve deadlocks.

    Most likely the reason the deadlocks have gone away is that the rebuild also updates stats, which invalidates execution plans. Hence you got different plans, so no deadlocks for now.

    That said, deadlocks are most of the time due to code and/or indexes, so worth looking into that.

    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

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • GilaMonster (10/22/2012)


    No, fragmentation will not cause deadlocks, neither will removing fragmentation resolve deadlocks.

    Most likely the reason the deadlocks have gone away is that the rebuild also updates stats, which invalidates execution plans. Hence you got different plans, so no deadlocks for now.

    That said, deadlocks are most of the time due to code and/or indexes, so worth looking into that.

    That is what I thought!

    Thanks for reply, appreciate it! I certainly will keep the 1222 flag enable for a while, and see if it can capture something, just in case.

    Our developers are also working hard in fixing those code issues based on my recommendations. We have had lot of improvements, but still there is a long road ahead ....

  • sql-lover (10/22/2012)


    I maybe wrong about this, but wanted to check or revise my own concepts.

    I thought deadlocks (most of them) were due poor or bad code; a process pinning other one, and the other one, waiting for 1st one to finish.

    Gail has already answered your question, but I want to be sure you're clear on what a deadlock actually is. What you describe here is how I would describe a block. That's very different from a deadlock.

    A deadlock is when two different processes each have a resource the other needs, but neither can release the resources it has until it gets the resources the other has. The nickname for a deadlock is the deadly embrace.

    It'll help to understand what's happening in your system if you're more sure of the difference between deadlocks and blocks.

    ----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software

  • Grant Fritchey (10/22/2012)


    sql-lover (10/22/2012)


    I maybe wrong about this, but wanted to check or revise my own concepts.

    I thought deadlocks (most of them) were due poor or bad code; a process pinning other one, and the other one, waiting for 1st one to finish.

    Gail has already answered your question, but I want to be sure you're clear on what a deadlock actually is. What you describe here is how I would describe a block. That's very different from a deadlock.

    A deadlock is when two different processes each have a resource the other needs, but neither can release the resources it has until it gets the resources the other has. The nickname for a deadlock is the deadly embrace.

    It'll help to understand what's happening in your system if you're more sure of the difference between deadlocks and blocks.

    Yep,

    That's why I meant, Grant 🙂 ... English is my second language, so maybe I did not use the right words. But I am clear on what a deadlock is.

    Thanks for the follow up, though!

  • sql-lover (10/22/2012)


    Grant Fritchey (10/22/2012)


    sql-lover (10/22/2012)


    I maybe wrong about this, but wanted to check or revise my own concepts.

    I thought deadlocks (most of them) were due poor or bad code; a process pinning other one, and the other one, waiting for 1st one to finish.

    Gail has already answered your question, but I want to be sure you're clear on what a deadlock actually is. What you describe here is how I would describe a block. That's very different from a deadlock.

    A deadlock is when two different processes each have a resource the other needs, but neither can release the resources it has until it gets the resources the other has. The nickname for a deadlock is the deadly embrace.

    It'll help to understand what's happening in your system if you're more sure of the difference between deadlocks and blocks.

    Yep,

    That's why I meant, Grant 🙂 ... English is my second language, so maybe I did not use the right words. But I am clear on what a deadlock is.

    Thanks for the follow up, though!

    No worries. Just wanted to make sure things were clear.

    ----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software

Viewing 6 posts - 1 through 5 (of 5 total)

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