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,878

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,595
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,363
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
0

In my opinion:

'# threads = # tempdb's'

ITGuy24
  • 1,596