x

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

ebzm gravatar image

ebzm
423 29 33 35

(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

Tim gravatar image

Tim
36.4k 38 41 139

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

AaronBertrand gravatar image

AaronBertrand
905 1 3

(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][2]

You will find very useful information here related to tempdb.

[2]: http://www.sqlskills.com/BLOGS/PAUL/post/Comprehensive-tempdb-blog-post-series.aspx
more ▼

answered Jul 01, 2011 at 12:19 AM

Pavel Pawlowski gravatar image

Pavel Pawlowski
22.3k 9 11 21

(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.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

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

By RSS:

Answers

Answers and Comments

SQL Server Central

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

Topics:

x50

asked: Jun 30, 2011 at 09:14 PM

Seen: 1786 times

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