SQL Server with Mr. Denny

Apr 2 2014   5:00PM GMT

How I Setup TempDB

Denny Cherry Denny Cherry Profile: Denny Cherry

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.


 Comment on this Post

There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when other members comment.

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

Share this item with your network: