tempDB data file

Question

tempDB data file

Gangadhara MS

Hall of Fame

Points: 3377
More actions
May 29, 2014 at 10:21 am

#307773

Microsoft recommends For the TEMPDB database, create 1 data file per CPU. but We have 4 processors with 16 cores each in new server. So do we need to have 64 tempdb data files or 4 ? please clarify.
-Junior DBA

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

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

dbafromthecold@gmail.com SSChampion Points: 10120 More actions · Answer 1

Have a read here:-

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

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

Gangadhara MS (5/29/2014)
Microsoft recommends For the TEMPDB database, create 1 data file per CPU.

That recommendation belongs in the garbage.

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

SQLRNNR SSC Guru Points: 281324 More actions · Answer 3

GilaMonster (5/29/2014)
Gangadhara MS (5/29/2014)
Microsoft recommends For the TEMPDB database, create 1 data file per CPU.
That recommendation belongs in the garbage.

And burned!!

:hehe:

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Rudyx - the Doctor SSC-Forever Points: 43695 More actions · Answer 4

agreed as it is written - however there are relevant truisms that do enhance performance on truly large systems.

We have found that on NUMA architected 'physical' systems one file per socket (not core) more evenly distributes things. Trace flag 1118 also helps with allocations/deallocations on' truly busy' tempdb databases. One however must allocate the tempdb data files exactly the same size with no growth.

NOTES:

- big NUMA architected 'physical systems = 8 sockets by 10 cores/socket with 1 TB+ of RAM

- 'truly busy' tempdb databases translates to read/write volumes between 1-3 TB per day (peaks of 4-6 TB) with all of the associated allocations/deallocations

for our big iron we have had to double the 8 files (1 file per NUMA socket) to 16 files (a 'special' case suggested by Microsoft).

We have 16 - 50 GB tempdb data files (800 GB total) and the t-log is 384 GB with 2 128 GB growths allowed.

When it comes to VMware hosted SQL Server instances we apply the 1 file per core maxim to a limit of 8 along with -T1118.

We are now also growing the VMware capabilities so we will be moving to TN sized application databases and 23-64 CVPU and 128/256 GB RAM configurations - so tempdb willbe huge also !!!

RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."