SQL Server TempDB files on server with NUMA processors

Hi everyone,

We are setting up a new server for SQL Server 2008 R2.  The server has 128 GB RAM.  When I look in Task Manager, it shows 80 processors.  I looked in resource monitor and it shows 4 NUMA sockets with 19 nodes in each socket.

So my question is, how many TempDB files should we configure?  Currently we have it at 4 but I'm not sure how to configure with this many / type of CPU.  I've not worked with a machine with this type processors before.  Since MS basically recommends 1 tempdb file per core, how many should be use?  What is the definition of a core in this scenario?

Let me know if you need more information.

Thanks,
Brett
SQLMuseAsked:
Who is Participating?
 
TempDBAConnect With a Mentor Commented:
You can go with 16, but yeah make sure do test with 8 and 24.
0
 
Scott PletcherSenior DBACommented:
Please post the results of this query:

select *
from sys.dm_os_sys_info
0
 
SQLMuseAuthor Commented:
Here you go.  Thanks for looking at this for me.  Let me know if you have trouble reading it.

cpu_ticks      ms_ticks      cpu_count      hyperthread_ratio      physical_memory_in_bytes      virtual_memory_in_bytes      bpool_committed      bpool_commit_target      bpool_visible      stack_size_in_bytes      os_quantum      os_error_mode      os_priority_class      max_workers_count      scheduler_count      scheduler_total_count      deadlock_monitor_serial_number      sqlserver_start_time_ms_ticks      sqlserver_start_time      affinity_type      affinity_type_desc      process_kernel_time_ms      process_user_time_ms      time_source      time_source_desc      virtual_machine_type      virtual_machine_type_desc
10421611619980869      3704845082      24      6      25766510592      8796092891136      2176000      2176000      2176000      2093056      4      5      32      832      24      30      14658      3654654821      2012-08-16 02:59:50.927      2      AUTO      12484416      232740153      1      MULTIMEDIA_TIMER      0      NONE
0
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
Scott PletcherSenior DBACommented:
OK, you're only got 4 physical CPUs, so I would create 4 tempdb data files.
0
 
SQLMuseAuthor Commented:
OK.  Thanks.  So each CPU (NUMA core 0, 1, 2, and 3) are physical sockets; each of which has 20 cores?  Since the total CPU's reported by Task Manager is 80, is this how the math works out?
0
 
SQLMuseAuthor Commented:
Thanks for you help.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.