May 12, 2009 at 1:21 pm
Hi,
We have Separate Drive For Temdb in Clustered SQL Sevrer 2005. Right now we have only one mdf file and one ldf file.
We have 8 processors(Intel(R) Xeon(R) CPU E5440 @2.83Ghz).I do not know whether they are core 2 duo or not(how to find this?).
Could you please tell me the best way to create no.of multiple data files for this tempdb? Right now tempdb size is just 400MB.
thanks
May 12, 2009 at 2:13 pm
Database looks fairly small, are you experiencing TempDB bottleneck?
The rule of thumbs for databases experiencing TempDB bottleneck is to allocate one tempdb datafile for each core, each datafile in a separate -dedicated disk.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.May 12, 2009 at 2:46 pm
If you are on SQL Server 2005 or 2008, you should not need multiple Tempdb data files unless you are observering latch contention on your temp table cache. If you are then the current reccomendation from MS is a number of TempDB data files equal to 1/4 to 1/2 the number of CPU cores. This is all explained in detail in this article by Paul Randall about TF1118 (one of the trace flags for alleviating this problem).
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
May 12, 2009 at 2:51 pm
Nice article RBarr, Thank you!
I'll revise my rule of thumbs about this matter 😉
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.May 12, 2009 at 3:24 pm
Yeah that's a 5-star article that I only wish someone had written 3 years ago. I am so tired of hearing the "Number of Tempdb files" argument everywhere I go. And although these answers were mostly "out there" in pieces from Microsoft, they were not well publicized and not cooordinated at all. As such, folks, including some MVPs, have been constantly quoting outdated info from 3-5 years ago.
I've tried to show them that the situation obviously changed with SQL Server 2005 (or shortly thereafter), but MS's own material ahs been so ambivelent and non-clarifying that it has been impossible to settle it. Thankfully, Paul's execellent and definitve write-up should end that ceaseless babble once and for all.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply