2

I have a question about the tempdb files and hyperthreading. Here are the sw/hw specs.

Server 2008 SP2 Standard X64 SQL 2005 Standard Sp3 x64 Dual Quad Core Xeon's E5540's (HT enabled) 12 GB Ram

My first question is how many tempdb files should I create? The guides say 16, but I've read that SQL Standard will only support 4 physical CPU's. How does sql distinguish between what is physcial / logical or cores versus sockets?

If I can get this question answered, there may be others, so first things first I suppose.

Thanks to anyone who can help.

Cian
  • 5,777
  • 1
  • 27
  • 40

4 Answers4

2

how many tempdb files should I create?

The standard answer to this is: One per CPU Core. This is the "benchmark" for SQL 2005 and 2008.

Jonesome Reinstate Monica
  • 5,347
  • 9
  • 54
  • 80
1

Disable Hyperthreading. SQL and HT don't mix.

Standard Edition supports only 4 CPUs, indeed: SQL Server 2005 Features Comparison. But, just as licensing, the Express/Workgroup/Standard CPU limitation (1/2/4) are per physical processor, so your system will use all 8 cores. See KB914278 (although is for Express, the algorithm applies to other editions too afaik).

So you should create 8 equal size files for tempdb. After you disable HT, of course.

Remus Rusanu
  • 8,253
  • 1
  • 19
  • 22
  • I think the recommendation with these new CPU's from MS is to leave HT enabled – Nick Kavadias Oct 28 '09 at 06:42
  • Do you hae nay ref on that Nick? I'm not saying is not so, but I'd like to know how do the new CPUs solve the L2 cache trashing and the capacity planning issues around HT that SQL was exposing. – Remus Rusanu Oct 28 '09 at 07:01
  • i *heard* it on good authority, but will try to look for a reference to it. Should be somewhere on sqlcat.com – Nick Kavadias Oct 28 '09 at 10:07
  • I have read several benchmarks about SQL and the 5500, and they all list 2x as many threads as cores. So I have to believe that you are suppose to leave it enabled. http://www.h-online.com/newsticker/news/item/New-Xeon-CPUs-with-Nehalem-core-in-SAP-SD-benchmark-739411.html – ITGuy24 Oct 28 '09 at 21:19
1

For 2005, here are my rules of thumb:

In TEMP, create .25 to 1 data file per filegroup per CPU. Dual Core (superscalar) counts as additional CPU - hyperthreading does not.

Total TEMP, at a minimum, should be around 25% of the size of the largest table in any database. Be TEMP generous. TEMP is completely whacked and rebuilt on SQL Server bounce. TEMP can only utilize the SIMPLE database recovery model.

Look here also: http://www.microsoft.com/technet/prodtechnol/sql/2005/workingwithtempdb.mspx

cheers

Allen
  • 79
  • 3
0

In my opinion:

'# threads = # tempdb's'

ITGuy24
  • 1,576
  • 1
  • 15
  • 29