Blocking

  • We were getting constant blocking on the db server. However, after stopping the process blocking went away. Just wanted to check automatic SQL table locking seems to be messed up. So the process is trying to access the locked table and would never finish and cause the use the CPU. Process hasn't been changed. What could be the issue?

  • Heh... you're the one touting the handle of "sqlguru".  You should be teaching us how you fixed the issue. 😀

    If you want help with a performance issue, we need more information.  Please Read'n'Heed the article at the second link in my signature line below.

    To SWAG a several of the more common answers for the "What could be the issue?" question...

    1. Crap code that reached a tipping point in the data.
    2. Someone did some index maintenance that changed execution plans or the rebuilds removed the wrong kind of space and all inserts and updates are now causing massive page splits, which easily take 43 times longer (or worse) than normal.
    3. Someone added an index.
    4. Underpowered hardware reached a tipping point and started hitting the "swap file".
    5. Someone changed a server setting.
    6. Someone added an anti-virus and forgot to exclude SQL Server files.
    7. Statistics no longer represent the truth of the data and need to be rebuilt.
    8. Process hasn't been changed?  Famous last words.
    9. Someone changed a system or database setting.
    10. Someone added another "process" that uses most of the same things and has similar problems as above.

    Without the things listed in the article I talked about, though, any recommendations or suggestions are basically a crap shoot.

     

    --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.

    Dear Lord... I'm a production DBA. Please grant me patience because, if you grant me strength, I'm gonna need bail money to go with it.


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

  • Make sure you turn on blocked process report in Extended Events. It'll be a lot easier to KNOW what's going on rather than relying on some random people on the internet guessing based on thoroughly incomplete information.

    ----------------------------------------------------
    The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore Roosevelt
    The Scary DBA
    Author of: SQL Server 2022 Query Performance Tuning, 6th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

  • Yep.  Get a Blocked Process report and feed your findings back to us.  We're willing to help you out.  It can be a lot of things.

  • As a bit of a sidebar, check out Adam Machanic's sp_WhoIsActive at the following link.  Click on the "Documenation" tab and look for "blocking" on the next list of links.  Add that kind of check to a blocking alert and have it write the output to a table and you'll have an awesome tool to figure out what's going on with blocking.

    http://whoisactive.com/

     

    --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.

    Dear Lord... I'm a production DBA. Please grant me patience because, if you grant me strength, I'm gonna need bail money to go with it.


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

  • SQLGuru any update on your findings?.

    Regards
    Durai Nagarajan

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

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