How TempDB should be setup is a question that comes up a lot, mostly because there is a lot of bad information out there. So here’s how I typically setup TempDB for the bulk of my clients, and these settings will probably work for you as well.
Number of Files
The number of files that you need for tempdb, in most cases is 8. There’s plenty of websites and blogs out there which say that you should have one tempdb database file for each CPU core that you have. And that was true, for SQL 2000 because we only had 4-8 cores in the machines. For 99.9%+ of SQL Server instances 8 tempdb database files will do the trick.
You’ll need to add more files for the tempdb database when you see waits on the PFS data pages (the easiest way to see this is by using sp_whoisactive as it’ll say PFS if the waits are on a PFS page).
File Size
The size of the tempdb database should be what ever size it grows to normally. If you’ve got one database file today, and that gets up to 15 Gigs, when you break that out to 8 files, make each one about 2 Gigs in size. This way when SQL starts up the space is already allocated and you don’t need to worry about the database growing.
Auto Growth Settings
Typically I set the auto growth settings for the data files for the tempdb database at 1 Gig. For systems which are a lot larger I might go with a bigger number, but for most clients we set a 1 Gig auto-growth setting.
Transaction Log
For the transaction log things are a little big different. I’ll typically start with an 8000 Meg file. If I think that it’s going to grow a lot (based on experience for this instance) I’ll set the growth for 8000 Megs. If I don’t think it’ll grow much or at all I’ll set it for 1000 Megs. These numbers look a little funky because of some auto-growth bugs which have existed in the past.
So that’s it. That’s how I’ll typically setup the tempdb for my clients.
Denny