SQLTeam.com | Weblogs | Forums

How many TempDB should be created using old / legacy processor?



Quick question: how many TempDB should I create when SQL Server is installed on old / legacy system?

Many best practice advice says to create TempDBs based on how many processors on the server machine, including each processor's cores are accounted for.

FYI: the processor I'm using is old Xeon processor (it's a 64 bit Intel Xeon Processor 3.2GHz 2M Cache 800MHz FSB 90nm), and there are two of that Xeon processor installed on the server machine. And the SQL Server itself is also old version: 2008 (no, not the R2 version).

I can't determine which one is the correct way to know how many processors being detected by the Windows Server OS:

  1. Look it up on System Information, it shows only two Xeon processors.
  2. Look it up on Device Manager, it shows there are four Xeon processors.

Which one is correct?


Likely both are accurate in a sense. You have 2 physical cores, each with hyperthreading of 2.

In that case, I'd suggest only 2 tempdb data files. If tempdb contention becomes an issue -- it shouldn't -- I guess you could increase it.


Thanks Scott, that’s what I’ve done so far on the TempDB setting, just wanted to make sure I’ve made the right decision.