question

Blackhawk-17 avatar image
Blackhawk-17 asked

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?

Regards;
Greg

performancetempdbdatabase-designhard-disks
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

3 Answers

·
Grant Fritchey avatar image
Grant Fritchey answered

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.

1 comment
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Blackhawk-17 avatar image Blackhawk-17 commented ·
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?
1 Like 1 ·
Fatherjack avatar image
Fatherjack answered

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
1 comment
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Grant Fritchey avatar image Grant Fritchey ♦♦ commented ·
+1 I should have said the same thing, test, test, test
1 Like 1 ·
sp_lock avatar image
sp_lock answered

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!

10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.