August 17, 2010 at 1:49 pm
Our company is planning to upgrade the present Cluster to an advanced hardware with SQL 2008 Cluster (A/P), as a DBA i have to confiure and install and mange the SQL Box. For the better performance company is plannig to buy 32 Core Procssor Box, is it idle to add 32 tempdb files as SQL Server performance stand point. Adding too many tempdb files can reduce performance and too much management as well, so what is best configuration for this Box.
August 17, 2010 at 2:24 pm
It all depends on how your tempDB is being used. One thing to keep in mind is that the size of each of the tempDB files should be the same.
There are also lots of blogs telling you that having multiple tempDB files is not going to help performance. So this is a very open ended question.
Just my 2 cents
-Roy
August 18, 2010 at 12:46 pm
Some feel it is a happy medium to go with a number of tempdb files to match the number of processor chips not cores...
August 23, 2010 at 12:38 am
sundeep.maddi (8/17/2010)
Our company is planning to upgrade the present Cluster to an advanced hardware with SQL 2008 Cluster (A/P), as a DBA i have to confiure and install and mange the SQL Box. For the better performance company is plannig to buy 32 Core Procssor Box, is it idle to add 32 tempdb files as SQL Server performance stand point. Adding too many tempdb files can reduce performance and too much management as well, so what is best configuration for this Box.
Generally speaking, You should add number of files equal to physical processor you have on your server? My recommendation will be to start with 8 files and make sure every file has same size defined with no growth. So analyze your tempdb load before you decide about setting tempdb data file size.
[font="Verdana"]--www.sqlvillage.com[/size][/font]
August 23, 2010 at 3:01 am
Request you to read the article http://sqlskills.com/blogs/paul/post/Search-Engine-QA-12-Should-you-create-multiple-files-for-a-user-DB-on-a-multi-core-box.aspx by Paul Randal.. Number of files actually depends on how you are going to utilize your tempdb.
Regards..Vidhya Sagar
SQL-Articles
August 23, 2010 at 7:50 am
And just as an aside keep in mind also that SQL 2008 R2 is only going to support 32 CPUs in the Datacenter Edition; if you get R2 Enterprise your max number of CPUs is 8.
August 23, 2010 at 4:52 pm
Thanks for the valued suggestions, i am still looking into the different articles on number of tempdb files.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply