SQL Server TempDB files on server with NUMA processors

Posted on 2012-08-16
Last Modified: 2012-08-16
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.

Question by:SQLMuse
    LVL 68

    Expert Comment

    Please post the results of this query:

    select *
    from sys.dm_os_sys_info

    Author Comment

    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
    LVL 68

    Expert Comment

    OK, you're only got 4 physical CPUs, so I would create 4 tempdb data files.

    Author Comment

    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?
    LVL 25

    Accepted Solution

    You can go with 16, but yeah make sure do test with 8 and 24.

    Author Closing Comment

    Thanks for you help.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Highfive + Dolby Voice = No More Audio Complaints!

    Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

    Suggested Solutions

    This is basically a blog post I wrote recently. I've found that SARGability is poorly understood, and since many people don't read blogs, I figured I'd post it here as an article. SARGable is an adjective in SQL that means that an item can be fou…
    In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
    Hi everyone! This is Experts Exchange customer support.  This quick video will show you how to change your primary email address.  If you have any questions, then please Write a Comment below!
    how to add IIS SMTP to handle application/Scanner relays into office 365.

    759 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    10 Experts available now in Live!

    Get 1:1 Help Now