Number of TempDbs for certain types of processors.

Question

Number of TempDbs for certain types of processors.

Duran

SSChampion

Points: 13259
More actions
January 24, 2011 at 5:39 am

#233855

Hello,
I understand that it is good practice to create the same number of Tempdb's as you have processors, but what I was wondering was if you have 2 quad core processors would a person create 2 tempdb's or 4 tempdb's?
Thanks in advance for any advice.
D.

Viewing 4 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: 1004514 More actions · Answer 1

Duran (1/24/2011)
I understand that it is good practice to create the same number of Tempdb's as you have processors

Not necessarily.

http://sqlskills.com/BLOGS/PAUL/post/A-SQL-Server-DBA-myth-a-day-%281230%29-tempdb-should-always-have-one-data-file-per-processor-core.aspx

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

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 2

I wanted to clarify something, in MS SQL Server there is NEVER more than one tempdb. Now, based on your question it appears you are talking about number of data files for tempdb. The 1 file per core has been thrown out there but is hardly a rule. I have seen some empirical testing showing that 1 file, cores/2, and cores count for files *CAN* all perform well in specific instances. However, without testing you cannot be sure. Those same tests I saw indicated that sometimes 1 file/core actually performed WORSE than 1 file.

So without testing and blindly just doing 1 file/core you could be shooting yourself in the foot.

Test it, find the right number for your environment.

CEWII

Duran SSChampion Points: 13259 More actions · Answer 3

Thanks for getting back, Guys. Gail, thank you for the link I'll have a read through. Elliot, will definatly be testing, but needed to make sure I'd be testing it correctly, apologies you are correct, I meant datafiles not several databases.

Thanks,

D.