• FritsD - Tuesday, October 17, 2017 3:16 AM

    Hi Jeff, thanls for the reply. No we didn't use EF and don't have MARS in our connections strings.
    We also had nother occurance of the issue yesterday afternoon which means my prime suspect of tempdb contention was incorrect as the additional files didn't help.

    Ok... the next logical suspect is a "runaway query" that has at least one accidental many-to-many join (also known as a data driven Cross Join).  You need to capture the code that's running when one of these incidents occurs along with some cpu and IO usage stats so that you can figure out which code it is that needs to be fixed.

    If it was bad enough to overwhelm resource usage of "normal" code over time, you might be able to find it in the "standard" instance level reports that are built into the "Reports" pulldown when you right click on the instance in the Object Explorer window.  If not, you need something like Adam Machanic's sp_WhoIsActive to find such a thing while it's 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)