Thursday, March 15, 2012

SQL DBA Best Practices - Why separate tempdb?

I've had discussions with server/system architects attempting to justify not only separating the database and log files, but also the tempdb to separate disks. 

This is one of those that more applies to SQL Server 2008 and beyond.

Did you know that tempdb not only stores temporary tables, but SQL Server also utilizes it for grouping and sorting operations, cursors, the version store supporting snapshot isolation level, and overflow for table variables? 

Hopefully knowing these other objects that can be highly utilized even if you do not believe your databases utilize a large amount of temporary objects will arm you with ammunition to vote for more and different physical disks for tempdb.

Disk cost cannot dwarf the speed benefit you will have, especially in data warehouse situations where some BI solutions create very large temp tables to join on before returning results.

No comments: