2016 SP2 CU6 & tempdb

Question

2016 SP2 CU6 & tempdb

Mick Opalak

SSCommitted

Points: 1631
More actions
June 11, 2019 at 3:25 pm

#3652369

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.

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

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

Jeff Moden SSC Guru Points: 1003871 More actions · Answer 1

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.

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)

curious_sqldba SSC-Dedicated Points: 36494 More actions · Answer 2

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 SSCoach Points: 16020 More actions · Answer 3

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: 1631 More actions · Answer 4

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: 36494 More actions · Answer 5

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: 1631 More actions · Answer 6

We have 96 physical cores and 192 logical processors.

curious_sqldba SSC-Dedicated Points: 36494 More actions · Answer 7

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