Tempdb files

Question

Tempdb files

ramyours2003

SSChampion

Points: 12268
More actions
April 6, 2016 at 7:05 am

#306251

I have 32 processors in my server , can we create more than 8 temp files , if yes how ?

Viewing 7 posts - 1 through 6 (of 6 total)

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

jeff.mason SSCrazy Eights Points: 9585 More actions · Answer 1

Are you sure you need more than 8? Are you seeing slowdowns in tempdb with 8 files anyway? I have yet to see an install that truly benefited from more than 8 files. If so, though, there should be no difficulty in adding additional files exactly the same way as you would add any other files. There isn't a special way to add files when you go over 8.

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 2

You should not WANT to do this unless you have identified PFS/SGAM allocation issues (and haven't done numerous other things first to address that). This is ESPECIALLY true if you are on rotating disk storage and don't have a LOT of dedicated spindles to server up tempdb. You will often make IO SLOWER due to head thrashing and disk latency by creating more files.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

ramyours2003 SSChampion Points: 12268 More actions · Answer 3

ramyours2003

SSChampion

Points: 12268

April 6, 2016 at 9:59 am

#1870241

Thanks...

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

Why do you want to create more 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

Jeff Moden SSC Guru Points: 1004412 More actions · Answer 5

ramyours2003 (4/6/2016)
I have 32 processors in my server , can we create more than 8 temp files , if yes how ?

To be honest, this is an advanced subject that can make or break your machine. You need to Google for Paul Randal's article on the subject.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Perry Whittle SSC Guru Points: 234013 More actions · Answer 6

ramyours2003 (4/6/2016)
I have 32 processors in my server , can we create more than 8 temp files , if yes how ?

monitor for allocation contention, if it's an issue add more files

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉