How to Audit and Optimize SQL Server Configurations using sys.configurations

Summary: Use the sys.configurations view to audit server-wide settings in SQL Server. Learn which critical performance options, such as Max Memory and Parallelism, require immediate attention.

Auditing SQL Server Instance Configurations

Understanding your SQL Server configuration settings is crucial for maintaining optimal performance and stability. The sys.configurations system catalog view provides a wealth of information about all server-wide options. Here is how to view them all at once:

-- Retrieve all server-wide configurations
SELECT 
    name,
    value,
    value_in_use,
    minimum,
    maximum,
    [description],
    is_dynamic,
    is_advanced
FROM sys.configurations WITH (NOLOCK)
ORDER BY name
OPTION (RECOMPILE);
        

This query gives you a comprehensive list, including the value_in_use (what is currently running) versus the value (what is saved in the metadata). It also flags is_dynamic settings—which take effect immediately—and is_advanced settings, which require sp_configure 'show advanced options', 1 to be modified.

Critical Settings for Performance Tuning

While reviewing all configurations is helpful, you should prioritize these specific settings during a health check. Below are common configurations and their typical best-practice starting points:

-- Check specific performance-critical settings
SELECT * FROM sys.configurations WITH (NOLOCK) 
WHERE name IN (
    'automatic soft-NUMA disabled',        -- Should be 0 in most modern cases
    'backup checksum default',             -- Should be 1 (Best practice)
    'backup compression default',          -- Should be 1 in most cases
    'clr enabled',                         -- Only enable if strictly required
    'cost threshold for parallelism',      -- Default of 5 is usually too low; try 50+
    'lightweight pooling',                 -- Should be 0
    'max degree of parallelism',           -- Should be set based on NUMA/Cores
    'max server memory (MB)',              -- Set a cap to leave room for the OS
    'optimize for ad hoc workloads',       -- Should be 1 to reduce plan cache bloat
    'priority boost',                      -- Should be 0
    'remote admin connections',            -- Should be 1 (Enables DAC)
    'tempdb metadata memory-optimized'     -- 0 by default; check for TempDB contention
)
ORDER BY name;
        

🧩 Important Considerations

• Context Matters: Settings like cost threshold for parallelism and MAXDOP vary significantly depending on your specific hardware and workload (OLTP vs. DW).
• Testing: Always test configuration changes in a non-production environment. Some changes can significantly alter query plan generation.
• Persistence: For many of these settings, you must run RECONFIGURE after an sp_configure command for the change to take effect.

---

For more in-depth details on every available option, refer to the official Microsoft documentation for sys.configurations.

Is your server properly capped? Failing to set 'max server memory' is the #1 cause of OS instability on SQL Server hosts. Check your settings today!