tempDB, size it right; it is important.

use -T1118.

1 data file for every logical processor, 1 log file.  This seems excessive for a 4 way Quad core machine, 16 temporary database data files, each one at 2gb.  We'll see how this runs today.

Read the Microsoft white paper, it's got all the information.

http://www.microsoft.com/technet/prodtechnol/sql/2005/workingwithtempdb.mspx 

WorkingWithTempDB.doc (301.5 KB) 

One Unexpected side effect of this is uncontrolled log file growth.  We have a 12gb log file and it was filling up quick, 2gb an hour.  While monitoring this you could see the log file growing continuously, if the log file were to fill up, it could cause an aotogrowth, an autogrowth in tempDB when you doing 1,000's of transactions per seconds will crash the server (ask me how i know!).  To keep this from happenning we implemented a job to checkpoint the tempDB Database every 30 minutes.  Hopefully when we ren't under such load it will ease up and begin checkpointing properly on it's own.  We did verify there are no uncommitted or long running transactions.

A table variable is created in memory, and so performs slightly better than #temp tables (also because there is even less locking and logging in a table variable). A table variable might still perform I/O to tempdb (which is where the performance issues of #temp tables make themselves apparent), though the documentation is not very explicit about this.

Table variables are automatically cleared when the procedure or function goes out of scope, so you don't have to remember to drop or clear the data (which can be a good thing or a bad thing; remember "release early"?). The tempdb transaction log is less impacted than with #temp tables; table variable log activity is truncated immediately, while #temp table log activity persists until the log hits a checkpoint, is manually truncated, or when the server restarts.

In an OLTP system that uses tempDB extensively it is important to monitor both the user and tempDB very closely.