Quick TempDB configuration sanity check

TempDB misconfiguration still shows up constantly in health checks.

SELECT
    name,
    type_desc,
    size * 8 / 1024 AS size_mb,
    CASE is_percent_growth
        WHEN 1 THEN CAST(growth AS varchar(10)) + '%'
        ELSE CAST(growth * 8 / 1024 AS varchar(10)) + ' MB'
    END AS growth_setting,
    physical_name
FROM tempdb.sys.database_files
ORDER BY type_desc, name;

Rules of thumb:

• Multiple data files (often 4–8 to start; validate under load) on modern versions
• Equal size files; avoid one giant and several tiny files
• Fixed MB growth, not tiny percent growth on large volumes
• Instant file initialisation enabled for the service account (data files)
• Monitor version store / spills when TempDB is the wait hotspot

After changing file counts, measure PAGELATCH waits under real workload — configuration is not one-size-fits-all.