[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1321
  • Last Modified:

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
0
SQLMuse
Asked:
SQLMuse
  • 3
  • 2
1 Solution
 
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
 
Scott PletcherSenior DBACommented:
OK, you're only got 4 physical CPUs, so I would create 4 tempdb data files.
0
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
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
 
TempDBACommented:
You can go with 16, but yeah make sure do test with 8 and 24.
0
 
SQLMuseAuthor Commented:
Thanks for you help.
0

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now