tempdb configuration for 24 logical cores

Question

tempdb configuration for 24 logical cores

Benki Chendu

SSCrazy Eights

Points: 8051
More actions
August 27, 2015 at 7:32 pm

#302682

Hi,
I have a server with 2 processors, 12 cores each making it a 24 core logical system.
My tempdb has 4 datafiles and 4 logfiles created.
I know creating more number of log files in tempdb may not make sense, but how many tempdb data files are supposed to be created?
Should it be 24?

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

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

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

Benki Chendu (8/27/2015)
Hi,
I have a server with 2 processors, 12 cores each making it a 24 core logical system.
My tempdb has 4 datafiles and 4 logfiles created.
I know creating more number of log files in tempdb may not make sense, but how many tempdb data files are supposed to be created?
Should it be 24?

You shouldn't have 4 log files for any database because the logfile is always used as a serial loop. There is no advantage to having more than one log file.

Unless you continue to have contention in TempDB, you probably shouldn't have more than 8 data files because too many can also cause performance problems. If you do continue to have contention after that, increase by 4 files at a time and check for contention again.

Please see the following article for how to check TempDB contention and much more detail on the subject than I could ever come up with.

http://www.sqlskills.com/blogs/paul/a-sql-server-dba-myth-a-day-1230-tempdb-should-always-have-one-data-file-per-processor-core/

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

The one thing you should do is remove the extra log files. There is no benefit from multiple log files, SQL uses them in sequence.

Second, how many files depends on whether you're seeing allocation contention in TempDB. If you are, try going to 8 files. If you're not, leave it at 4.

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

Benki Chendu SSCrazy Eights Points: 8051 More actions · Answer 3

GilaMonster (8/28/2015)
The one thing you should do is remove the extra log files. There is no benefit from multiple log files, SQL uses them in sequence.
Second, how many files depends on whether you're seeing allocation contention in TempDB. If you are, try going to 8 files. If you're not, leave it at 4.

How do I remove the extra log files?

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

https://technet.microsoft.com/en-us/library/ms191433%28v=sql.105%29.aspx

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