Add TempDB data files

Question

Add TempDB data files

jralston88

SSCommitted

Points: 1931
More actions
September 16, 2014 at 11:46 am

#310947

We've been asked to add 6 tempdb data files to our SQL Server 2008 EE cluster (6 physical cores). Looking at the add tempdb data file process seems very straight forward but there are concerns. I've read if you add TempDB datafiles improperly, it can slow down query response time.
My questions:
Should you place each new tempdb data file in its own directory? From what I've seen - give it's own directory.
Do you need to do anything to assign one data file for one processor or will SQL server automatically assign.
Are there any concerns with a clustered environment?
Any comments / URLs would be appreciated - thanks.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Let's start with 'why'?

There is no truth to the myth that only one core can write to a particular file. All threads, no matter where they are running can read/write any file they wish. There's no affinity between cores and files.

7 files is a bit of an odd number (start with 1, add 6). Normally it would be 4 files total, 8 if there are signs of tempDB allocation contention, then add files in groups of 4 or 8 until the allocation contention disappears.

As for folders, SQL doesn't care. Put em in one, put em in separate, no difference if they're on the same drive.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

jralston88 SSCommitted Points: 1931 More actions · Answer 2

Thanks Gail,

I've read about the 'myth', and the increments suggested. We'll try it.

We also had discussions about where to place the data files, each on separate phyical drives or just one with separate directories. Right now with one, we've had no issues (I'll be checking again). Future growth - who knows...

Thanks for your comments...

Igor Micev SSC-Dedicated Points: 33109 More actions · Answer 3

If you haven't had issues with one tempdb file and you have the possibility to put extra files on separate physical disks (better option) then you shouldn't worry. I think it's no necessary to add more tempdb files until you detect there exists allocation contention.

Igor Micev,My blog: www.igormicev.com

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

jralston88 (9/16/2014)
We also had discussions about where to place the data files, each on separate phyical drives or just one with separate directories. Right now with one, we've had no issues (I'll be checking again).

It depends whether you're splitting for IO contention (in which case multiple drives) or allocation contention (just multiple files)

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass