In systems where tempdb is heavily used (explicitly or implicitly), consider placing tempdb on its own disk array, and not on the same drives where other databases are located. Also, stripe the data portion to multiple drives to increase I/O throughput. The more spindles, the better. Ideally, use RAID 10 for the data portion and RAID 1 for the log.

Every time you restart SQL Server, tempdb is re-created, and its size reverts to the effective defined size. If you made no changes to the original size configuration after installing SQL Server, tempdb’s size will default to 8 MB and its growth increment will default to 10 percent. In most production environments, these values might not be practical. Whenever a process needs to store data in tempdb and tempdb is full, SQL Server will initiate an autogrow operation. The process will have to wait for the space to be allocated. Also, when the database is small, 10 percent is a very small unit. The small fragments will most probably be allocated in different places on disk, resulting in a high level of file-system fragmentation. And if that’s not enough, remember that every time SQL Server restarts, tempdb’s size will revert to its defined size (8 MB). This means that the whole process will start again, where tempdb will keep on autogrowing until it reaches a size appropriate to your environment’s workload. Until it reaches that point, processes will suffer as they wait while tempdb autogrows.

You can figure out the appropriate size for tempdb by observing its actual size after a period of activity with no restarts. You then alter the database and change the SIZE parameter of tempdb’s files so that tempdb’s size will be appropriate. Whenever SQL Server is restarted, tempdb will just start out at the defined size. If you do this, there won’t be a need for autogrowth until tempdb gets full, which should occur only with irregular and excessive tempdb activity.

Remember that logically tempdb is re-created whenever SQL Server restarts. Like any other new database, tempdb is created as a copy of the model database. This means that if you create permanent objects in tempdb (permanent tables, user defined types, database users, and so on), they’re erased in the next restart. If you need objects to exist in tempdb after restarts, you have two options. One is to create them in model. They will appear in tempdb after a restart. However, this option will also affect new user databases you create. Another option is to encapsulate code that creates all objects in a startup procedure. (See information on startup procedures earlier in the chapter in the "Global Temporary Tables" section.) Remember that a startup procedure is invoked whenever SQL Server is restarted. Essentially, the objects will be re-created every time upon restart, but this will be transparent to users.

With regard to temporary tables, obviously dealing with very large volumes of data can cause performance problems. However, you might face performance problems with tempdb even when working with small temporary tables. When many concurrent sessions create temporary tables, SQL Server might experience contention when it tries to allocate pages. This was mainly an issue with SQL Server 2000 SP3 and earlier service packs, but some aspects of the problem are still relevant in later versions. I urge you to read Microsoft Knowledge Base article 328551, "FIX: Concurrency enhancements for the tempdb database," which discusses the problem and suggests solutions. You can access it at the following URL: http://support.microsoft.com/default.aspx?scid=kb;en-us;328551.