|
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;
(comments are locked)
|
|
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. 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 '10 at 01:34 PM
Blackhawk-17
I guess these guys would know: http://blogs.msdn.com/psssql/archive/2007/02/21/sql-server-urban-legends-discussed.aspx
Jan 28 '10 at 06:27 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 '10 at 11:04 PM
Grant Fritchey ♦♦
(comments are locked)
|
|
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:
+1 I should have said the same thing, test, test, test
Jan 28 '10 at 12:38 PM
Grant Fritchey ♦♦
(comments are locked)
|
|
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!
(comments are locked)
|
