2016 SP2 CU6 & tempdb

  • Mick Opalak

    SSCommitted

    Points: 1629

    I haven't been able to find an answer for this via Google.  Hopefully the forum can help.

    We recently upgraded a SQL 2016 SP2 instance from CU3 to CU6 and we're seeing more TempDB contention.  Does anyone know of a change in CU6 that would explain different behavior in TempDB?

     

    Thanks in advance.

  • Jeff Moden

    SSC Guru

    Points: 993779

    Haven't made it to CU6, yet (we try to stay off the bleeding edge) so thanks for the heads up.  Hopefully, this will also bump your post back close to the top because this seems to be a wee-bit important.

    --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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems

  • curious_sqldba

    SSC-Dedicated

    Points: 36262

     

    Define contention. Are you seeing lot of pfs/sgam wait types? Are any on your custom temdb settings still there post applying CU6?

  • frederico_fonseca

    SSChampion

    Points: 14037

    and how many files you got on tempdb?

    and can you go to CU7 just in case it fixes the issue?

  • Mick Opalak

    SSCommitted

    Points: 1629

    Yes, we are seeing sporadic pfs/sgam wait types.  We currently have 40 TempDB data files.  We are thinking about going up to 48.

  • curious_sqldba

    SSC-Dedicated

    Points: 36262

    How many logical and physical cores do you have?  We have a 48 physical core and 96 logical cores box,  we found 48 files for us seems to be helpful. Be cautious about adding files, adding too many files can also cause issues.  Sporadic pgs/sgam  is normal,  in past i sampled wait types every 5 secs and got an aggregate total on tempdb waits, when you add more files your goal should be to reduce that number.

  • Mick Opalak

    SSCommitted

    Points: 1629

    We have 96 physical cores and 192 logical processors.

  • curious_sqldba

    SSC-Dedicated

    Points: 36262

    You can start with 48 and keep adding 4 files until you see acceptable improvement.

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

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