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

Blackhawk-17 gravatar image

11.9k 28 31 37

(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

Grant Fritchey gravatar image

Grant Fritchey ♦♦
103k 19 21 74

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
Pretty defnitive, yes. Everything I've read by people I trust (Kalen Delany, Paul Randal, those guys) pretty much suggests that you're not going to see benefits until you have multiple controllers and multiple drives. That's in line with what MS says. I'm not smart enough on that low level stuff to argue.
Jan 28, 2010 at 11:04 PM Grant Fritchey ♦♦
(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

Fatherjack gravatar image

Fatherjack ♦♦
42.8k 75 79 108

+1 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

sp_lock gravatar image

9.3k 26 28 31

(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: 2633 times

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