TempDB configuration

I need help on configuring multiple files for TempDB. We have 2 instance on a physical server with 2 Numa (8 processors) CPU. These instances are high performance OLTP databases. I didn’t think it would be right to configure by creating 8 multiple files for TempDB on each instances, but I might be wrong. Any advice? I would highly appreciate it.

more ▼

asked Jun 30, 2011 at 09:14 PM in Default

avatar image

434 33 33 38

(comments are locked)
10|1200 characters needed characters left

3 answers: sort voted first

From my experience I would start out with 4 files on both instances and would have a dedicated RAID group for TEMPDB. With multiple instances and both having high performance OLTP databases you are bound to have contention with TEMPDB if it is configured by default out of the box. If this system has been up and running for awhile you are in much better luck because you have some data to go on with the size of TEMPDB that you need and possible work load that has been put on it. Just make sure you are following as many of the best practices as possible. Things like moving the ldf and mdf to dedicated drives, sizing it appropriately, splitting the data file into multiple equal size files, turning off auto close, etc. There are lots of resources about TEMPDB out there.

more ▼

answered Jul 01, 2011 at 01:58 AM

avatar image

40.9k 39 94 168

I agree with Tim here. Start with four and evaluate your workload/look for contention problems.

Jul 01, 2011 at 07:59 AM SirSQL
(comments are locked)
10|1200 characters needed characters left

Do you have a tempdb issue to begin with? Typical wisdom (once you've established that you have a tempdb bottleneck) is to start slowly - try # of files = half the number of cores the instance can see. Make sure they are all exactly the same size.

more ▼

answered Jun 30, 2011 at 10:58 PM

avatar image

905 3 6

(comments are locked)
10|1200 characters needed characters left

Take a look on Paul Randal's posts:

A SQL Server DBA myth a day: (12/30) tempdb should always have one data file per processor core
Comprehensive tempdb blog post series

You will find very useful information here related to tempdb.

more ▼

answered Jul 01, 2011 at 12:19 AM

avatar image

Pavel Pawlowski
22.7k 10 15 26

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Jun 30, 2011 at 09:14 PM

Seen: 4326 times

Last Updated: Jun 30, 2011 at 09:14 PM

Copyright 2018 Redgate Software. Privacy Policy