Where Waits coming from?


  • Appteam complains about Timeouts connecting to sql server. No errors- eitherEventViewer or Sql server error log. Checked most "popular"waits (used query from Paul Randal site) - got this:   

    WaitType

    Wait_S

    WaitCount

    Percent

    AvgWait_S

    PREEMPTIVE_OS_AUTHENTICATIONOPS

    705730.94

    21,999,839

    63.3

    0.0321

    BACKUPIO

    118039.45

    13,748,352

    10.6

    0.0086

    ASYNC_IO_COMPLETION

    117950.15

    18,258

    10.6

    6.4602

    BACKUPBUFFER

    108957.83

    8,039,889

    9.8

    0.0136

    MSQL_XP

    13037.04

    1,735,074

    1.2

    0.0075


    Trying to identify- where Waitscoming from?
    Or can we correlate Waits and Applications or Users app running under?
    Or there is no way we can distribute total Waits by ... something?
    Thanks

  • Yuri55 - Tuesday, May 15, 2018 6:24 PM


    Appteam complains about Timeouts connecting to sql server. No errors- eitherEventViewer or Sql server error log. Checked most "popular"waits (used query from Paul Randal site) - got this:   

    WaitType

    Wait_S

    WaitCount

    Percent

    AvgWait_S

    PREEMPTIVE_OS_AUTHENTICATIONOPS

    705730.94

    21,999,839

    63.3

    0.0321

    BACKUPIO

    118039.45

    13,748,352

    10.6

    0.0086

    ASYNC_IO_COMPLETION

    117950.15

    18,258

    10.6

    6.4602

    BACKUPBUFFER

    108957.83

    8,039,889

    9.8

    0.0136

    MSQL_XP

    13037.04

    1,735,074

    1.2

    0.0075


    Trying to identify- where Waitscoming from?
    Or can we correlate Waits and Applications or Users app running under?
    Or there is no way we can distribute total Waits by ... something?
    Thanks

    Do the connection strings enable "Multiple Active Result Sets"?  If they do, or they have nothing about M.A.R.S. in them, change your connection strings to explicitly turn it off.  We went through this a year ago and it was a humdinger of a problem.  Not only did it start to cause rampant timeouts, but it also causes KILL/ROLLBACKs in SQL Server which seriously exacerbate the problem.  It was causing 36 CPUs to jump to 80% CPU along with massive blocking where no one could login in via any means (which is one of the many reasons why I always keep and RDC session open on my production boxes) that would last anywhere from 30 to 40 minutes and, sometimes, would be followed in just another couple of minutes by another such episode.

    Even if M.A.R.S. enable connections don't turn out to be your problem, turn it off to be sure for the future.  We operated for about 3 years with no noticeable issues and then, one day, it reached a tipping point and crushed us.

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

  • Thanks Jeff- will double check with Dev team. Good to know about your real life example-
    kind of "Curious case of MARS enabled" 🙂
    Do you have any comments regarding my Waits related question- can we correlate sql server Waits and ...something like app, user, dates, etc or it's server general info and no distribution is available?
    Thanks

  • Yuri55 - Wednesday, May 16, 2018 10:18 AM

    Thanks Jeff- will double check with Dev team. Good to know about your real life example-
    kind of "Curious case of MARS enabled" 🙂
    Do you have any comments regarding my Waits related question- can we correlate sql server Waits and ...something like app, user, dates, etc or it's server general info and no distribution is available?
    Thanks

    One really decent method is to download "sp_WhoIsActive" by Adam Machanic.  You'll have to search for it because I wrote my own and so don't use his but it will help you find the code causing the waits.  And, yeah... the M.A.R.S. problem caused a lot of waits even if it wasn't in a KILLED/ROLLBACK state but other code in need of a bit of tuning is also a major problem.  On reasonable quality servers, it's usually not a hardware problem and a whole lot of people end up chasing ghosts.  We went from a 16 cpu box with 128 GB of RAM to a 32 CPU box with 256 GB of RAM and then later upped it to 48 CPUs and 384 GB of RAM.  The first upgrade only helped a couple of large batch jobs run only about 2 times as fast and did nothing for most of the code.  The second upgrade did nothing for any of it.

    As I've been known to say, "Performance is in the code" and Adam's sp_WhoIsActive will help you find which code to bring guns to bear on.  A lot of it will be very low effort high ROI fixes and fixing the top 10 will normally make the code 10 to 1000 times faster and less resource intensive.  Don't stop there though because the 10 after that are just waiting to become performance problems as the data continues to grow.

    Again, make sure you keep your stats up to date and make sure you keep up with CUs/SPs on your boxes.

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

  • Thanks again, Jeff for your time and tips. This "chasing ghosts" is what I try to avoid therefore trying to understand what/who contribute to this not very common wait type "PREEMPTIVE_OS_AUTHENTICATIONOPS" (as per info- can be Controller/DNS/Active Directory). Anyhow I have Adam script handy and will use it.
    Best, Yuri

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

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