• The whole idea is to maximize bandwidth that can be used to read/write in tempdb. Keep that in mind on everything you read about it, and it'll help.

    For example, your question about 1 disk for data, 1 for logs. If they can be on separate I/O channels, yes, it makes sense, and will usually get better performance. Why? Because it can write to both at the same time without having to take turns going through a single connection.

    Same for multiple files. It will often create zero advantage to have multiple tempdb data files, if they're all on the same disk. Why? Because it'll still have to take turns reading and writing. Hard drives read and write sequentially. They only do one thing at a time.

    Also, on systems with adequate RAM and a good buffer on a SAN, disk-optimization for tempdb may not matter at all. Why? Because everything is going to RAM and to the SAN cache (which is effectively RAM in terms of speed), and thus actual disk I/O sometimes doesn't even matter on those servers.

    Another thing to keep in mind is that this kind of optimization is only necessary on servers that hit tempdb hard. That typically means either lots of large datasets being joined, sorted, etc., in tempdb, or lots of temp tables being used by lots of queries. You should make sure that's an actual bottleneck before you spend a lot of time (and money) on optimizing tempdb.

    If, for example, most of your slow queries are slow because of poor index use, then optimizing tempdb won't get you very far compared to optimizing the queries and indexes. If you see a lot of CPU wait-states and very few I/O wait-states, in your server stats, then tempdb isn't the problem there either. Lots of cursors? Not likely to be fixed via tempdb. And so on through the list of SQL Server optimization steps.

    But if you do find you need to optimize tempdb, just keep in mind that the key to it is parallel I/O channels, and the number of files is just a piece of that.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon