How to solve blocking issue

  • after finding blocking spid.

    How to solve that problem.

    deadlock and blocking both are same are different.

  • You can always kill one spid or the other, but you should know the impact of that first. As to what you meant by "deadlock and blocking both are same are different.", I have no idea what you're trying to say.

  • Here for blocking we need to kill the sp id.

    how to solve the deadlock issue.

  • Deadlock is a condition where two or more queries go after resources that the others are using, and in at least 2 cases, an "other query" needs an exclusive lock and can't get it because it's already in use, and because the other query holds an exclusive on something the 1st query needs, neither query can EVER succeed. It's your basic "catch 22" situation. Read the following page, and it does a far better job of describing it than I can:

    http://msdn.microsoft.com/en-us/library/ms178104(v=sql.105).aspx

    Solving such a problem is often a performance related problem. Get the queries that deadlock to run faster, and there's less opportunity for this to occur. Also, running queries that don't actually need an exclusive lock with query hints that provide one or in ways that cause one can be things to look for and eliminate, if possible. It's not always possible. As SQL Server detects deadlock and randomly chooses a victim, another way to solve the problem is to reschedule things, if that's an option.

  • sgmunson (12/10/2012)


    You can always kill one spid or the other, but you should know the impact of that first. As to what you meant by "deadlock and blocking both are same are different.", I have no idea what you're trying to say.

    Don't do it. Killing a SPID may leave it in a never ending rollback that will consume the better part of an entire CPU. Far better to find the person causing the blocking and have them cancel whatever they're running.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • gantavasu (12/10/2012)


    Here for blocking we need to kill the sp id.

    how to solve the deadlock issue.

    It's normally not a simple thing and it's far too complex to explain on a forum post. Please do the following... From SSMS, press the {f1} key and search for DEADLOCKS.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • http://www.simple-talk.com/books/sql-books/troubleshooting-sql-server-a-guide-for-the-accidental-dba/ Chapters 6 and 7

    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
  • Jeff Moden (12/10/2012)


    sgmunson (12/10/2012)


    You can always kill one spid or the other, but you should know the impact of that first. As to what you meant by "deadlock and blocking both are same are different.", I have no idea what you're trying to say.

    Don't do it. Killing a SPID may leave it in a never ending rollback that will consume the better part of an entire CPU. Far better to find the person causing the blocking and have them cancel whatever they're running.

    Thanks for the correction. I was not aware of that particular gotcha, and it's always good to learn BEFORE it bites you as opposed to after.

  • Here is a 3-part blog post about deadlocking from Bart Duncan: http://blogs.msdn.com/b/bartd/archive/2006/09/09/deadlock-troubleshooting_2c00_-part-1.aspx

    Best,
    Kevin G. Boles
    SQL Server Consultant
    SQL MVP 2007-2012
    TheSQLGuru on googles mail service

Viewing 9 posts - 1 through 8 (of 8 total)

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