TEMPDB files

Question

TEMPDB files

adisql

SSCrazy

Points: 2845
More actions
March 23, 2018 at 2:00 pm

#336829

Hi,
Please suggest to configure tempdb files in SQLserver2016 Enterprise edition.
cpu_count is 96.
current tempdb files are 24
is it good configuration ?
do i need to add more tempdb files ?
Thanks

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

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

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 1

To be honest, I wouldn't configure more than 8 without some evidence of allocation contention. The only time I'd have 24, is if, with 20 files, there was still evidence of severe allocation contention.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Jeffrey Williams SSC Guru Points: 89773 More actions · Answer 2

Review the following articles:

https://www.brentozar.com/archive/2016/01/cheat-sheet-how-to-configure-tempdb-for-microsoft-sql-server/
https://www.sqlskills.com/blogs/paul/a-sql-server-dba-myth-a-day-1230-tempdb-should-always-have-one-data-file-per-processor-core/

Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.

How to post questions to get better answers faster
Managing Transaction Logs

Perry Whittle SSC Guru Points: 233792 More actions · Answer 3

GilaMonster - Friday, March 23, 2018 2:24 PM
To be honest, I wouldn't configure more than 8 without some evidence of allocation contention. The only time I'd have 24, is if, with 20 files, there was still evidence of severe allocation contention.

+1
although, I wouldn't even use 8, probably 2 - 4 and if there's evidence of contention then increase it

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 4

Perry Whittle - Monday, March 26, 2018 6:04 AM
GilaMonster - Friday, March 23, 2018 2:24 PM
To be honest, I wouldn't configure more than 8 without some evidence of allocation contention. The only time I'd have 24, is if, with 20 files, there was still evidence of severe allocation contention.
+1
although, I wouldn't even use 8, probably 2 - 4 and if there's evidence of contention then increase it

+1

I'll also add that a whole lot of contention in TempDB can actually be (and should be) fixed by finding and fixing problems in code.

--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)
Intro to Tally Tables and Functions

Perry Whittle SSC Guru Points: 233792 More actions · Answer 5

Jeff Moden - Monday, March 26, 2018 7:33 AM
Perry Whittle - Monday, March 26, 2018 6:04 AM
GilaMonster - Friday, March 23, 2018 2:24 PM
To be honest, I wouldn't configure more than 8 without some evidence of allocation contention. The only time I'd have 24, is if, with 20 files, there was still evidence of severe allocation contention.
+1
although, I wouldn't even use 8, probably 2 - 4 and if there's evidence of contention then increase it
+1
I'll also add that a whole lot of contention in TempDB can actually be (and should be) fixed by finding and fixing problems in code.

I concur Jeff, fix the root of the issue rather than putting sticky plaster over it

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 6

Perry Whittle - Monday, March 26, 2018 6:04 AM
GilaMonster - Friday, March 23, 2018 2:24 PM
To be honest, I wouldn't configure more than 8 without some evidence of allocation contention. The only time I'd have 24, is if, with 20 files, there was still evidence of severe allocation contention.
+1
although, I wouldn't even use 8, probably 2 - 4 and if there's evidence of contention then increase it

I usually go for 4 as a default, because that's not likely to cause problems with too many files, and it'll pretty much remove any chance of contention showing up except in extreme cases, which get addressed on a case-by-case basis.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass