Correctly Setting Up tempdb

SQL Server stores data in tempdb for many activities that happen behind the scenes such as: spooling data for queries, sorting, row versioning, as well as holding temporary tables and table variables. Since this data is physically materialized on disk, tempdb becomes an obvious bottleneck forcing us to make special considerations.

Any system with heavy use should have tempdb on its own disk array, seperately from where user databases are located. Obviously you'll want to use as many spindles as possible using striping in raid 10. (raid 1 can be used for the log)

Everytime SQL Server is restarted, tempdb is recreated and it size reverts to the effective defines size, which defaults to 8MB. Since this will likely be too small for most databases, it will grow at 10% per growth creating small files that will likely be fragmented within the file system. Since processes will need to wait around for the file to grow, it is suggested that you set this to an appropriate size for your database and workload.

In order to determine what is an appropriate size you can observe the size of tempdb when the system is under load. You can then alter the SIZE parameter so that the effective defined size is more apropriate. Once this is set you won't have to worry about autogrowth until the tempdb gets full which ideally would only happen during irregular activity.