Temp dB, CPU Cores and Disks - oh my!

I know the conventional wisdom is to create Temp dB with the same number of equally sized files as CPU cores.

Ideally I would assume that having these files spread across multiple disks, separate from any dBs, would be the optimum design.

What if I don't have the number of drives to support that?

Will multiple Temp dB files still aide performance if they all reside on the same disk, or on fewer disks than the number of files?

What if they have to share disk resources with the data files? Are multiple Temp dB files still a valid approach?


more ▼

asked Jan 28, 2010 at 11:58 AM in Default

avatar image

12.1k 30 36 42

(comments are locked)
10|1200 characters needed characters left

3 answers: sort voted first

To get the best performance out of tempdb, you really do need to seperate tempdb storage from the data storage. That much is a must. After that, it's just methods of tuning, not so much requirements. Multiple files will probably not help much, if at all, when you you're not spreading them across multiple drives.

more ▼

answered Jan 28, 2010 at 12:20 PM

avatar image

Grant Fritchey ♦♦
137k 20 47 81

My understanding was that multiple files were suggested to enhance CPU operations as only one thread/CPU can be accommodated. Not only to improve disk I/O.

Do your recommendations change if talking about a VLDB Data Warehouse?

Jan 28, 2010 at 01:34 PM Blackhawk-17
(comments are locked)
10|1200 characters needed characters left

I have seen demos that show that adding tempDBs causes performance drops in some instances, my advice is test, test and test all possible/plausible configurations. I think microsoft still recommend >1 in certain circumstances.

Are you installing/speccing for a virtualised environment?

My preference for a small/med hardware spec:

  • C:\ 2xHDD - mirrored drives for OS, sys databases and possibly ldfs
  • D:\ 2xHDD - stripe set for ldfs and possibly OLAP data
  • E:\ 4xHDD - RAID5 for OLTP data
more ▼

answered Jan 28, 2010 at 12:22 PM

avatar image

Fatherjack ♦♦
43.8k 79 102 118

  • I should have said the same thing, test, test, test

Jan 28, 2010 at 12:38 PM Grant Fritchey ♦♦
(comments are locked)
10|1200 characters needed characters left

Def with Grant on this one... Even in a medium environment I make provisions for tempdb on seperate storage, even more so when using XML data type... Our dev's seem to be using these alot for our translation business.

KevinK also has a good article around tempdb.. A good read!

more ▼

answered Jan 28, 2010 at 12:39 PM

avatar image

10.9k 27 37 37

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Jan 28, 2010 at 11:58 AM

Seen: 2926 times

Last Updated: Jan 28, 2010 at 11:58 AM

Copyright 2018 Redgate Software. Privacy Policy