2 core machine with 4 Tempdb files

  • AlphaTangoWhiskey

    SSChampion

    Points: 10788

    Just curious if there is a performance hit by having 4 tempdb files with a 2 core machine?

    running SQL 2016 (SP2 CU7 +GDR)

     

  • Grant Fritchey

    SSC Guru

    Points: 395586

    How are your waits? Are you seeing lags in tempdb? If not, leave it alone. If so, make a change. Guidance on the number of files for tempdb is all over the map. I generally fall to "more than one is good, more than eight is WAY too many". It's simplistic, but keeping things simple and only adjusting as evidence suggests adjustments are needed has worked really well in the past. Others suggest one file per core. Others say that, but a limit of 8. It varies. You have more than one per core. PANIC! Kidding. I doubt you're seeing anything major. Plus, is tempdb management your worst performance headache? For most, it's not. It's all the bad code, horrid structures, and out of date statistics that are causing issues.

    Measure, then decide.

    ----------------------------------------------------
    The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
    Theodore Roosevelt

    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

Viewing 2 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply