How Many TempDB Files Can be added to SQL Server 2008 with 32 CPU's

  • 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.

  • 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

  • Some feel it is a happy medium to go with a number of tempdb files to match the number of processor chips not cores...

  • 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]

  • 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.

  • 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.

  • 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