• We had a similar problem in the "early days" of VM. I'm not a VM kinda guy. Someone else always set this up in the environment. That means that I'm not going to get the lingo right so forgive that, please.

    It turned out to be some sort of a setting in VM that allowed VM to swap resources in and out to support other things like othe SQL Servers. Once they changed the setting to not allow that to happen (think "dedicated VM box"), those flat-line/timeout problems vanished. I would appear that the system was spending more time trying to reallocate system resources than it was actually doing work. One of the biggest problems (apparently) was that it also reallocated memory and cause the work to "spool to disk" instead of staying in memory. I don't know if it was actually hitting the "swap file" in the classic sense because I didn't (at the time) have the time to do a deep dive on the problem with the folks that fixed it, but those are the sketchy details that I can remember.

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