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!

SQL Server 2019 Build Versions, Release Dates, and KB Articles

Summary: Quickly identify your SQL Server build using @@VERSION and reference the comprehensive list of SQL Server 2019 release versions from CTP to RTM and GDR.

Determining Your SQL Server 2019 Build and Version

Ever wonder about the specific build and version of your SQL Server instance? Knowing your exact build number is essential for applying the correct security patches and cumulative updates. You can easily find this out using a simple T-SQL global variable:

-- Identify Server Name and Full Version Info
SELECT 
    @@SERVERNAME AS [Server Name], 
    @@VERSION AS [SQL Server and OS Version Info];
        

SQL Server 2019 Build History

Below is a detailed look at the various builds of SQL Server 2019 (Internal Version 15.0). This list includes the early Community Technology Previews (CTP), Release Candidates (RC), and General Distribution Releases (GDR).

Build Number	Description / Milestone	Release Date
15.0.1000.34	CTP 2.0	9/24/2018
15.0.1100.94	CTP 2.1	11/7/2018
15.0.1200.24	CTP 2.2	12/6/2018
15.0.1300.359	CTP 2.3	3/1/2019
15.0.1400.75	CTP 2.4	3/26/2019
15.0.1500.28	CTP 2.5	4/23/2019
15.0.1600.8	CTP 3.0	5/22/2019
15.0.1700.37	CTP 3.1	6/26/2019
15.0.1800.32	CTP 3.2	7/24/2019
15.0.1900.25	RC1 / Refresh	8/29/2019
15.0.2000.5	RTM (General Availability)	11/4/2019
15.0.2070.41	GDR1	11/4/2019

Official Microsoft Resources

• Servicing Update for SQL Server 2019 RTM
• Determine Version and Update Level of SQL Components
• KB4518398 - Full SQL Server 2019 Build List

Is your SQL Server out of date? Regularly checking your build number against official KB articles ensures you are protected against known vulnerabilities and performance bugs!

Monitoring In-Memory OLTP Index Performance with sys.dm_db_xtp_index_stats

Summary: Audit Hekaton index efficiency using the sys.dm_db_xtp_index_stats DMV to track scans, retries, and row-level operations for In-Memory SQL Server tables.

Analyzing In-Memory OLTP Index Statistics

Understanding how your SQL Server indexes are performing is crucial for database optimization, especially when using In-Memory OLTP (Hekaton). Because memory-optimized tables utilize different data structures (like Hash and Range indexes), standard index DMVs often won't provide the full picture.

The following query leverages sys.dm_db_xtp_index_stats to provide deep insights into your in-memory index usage and health.

In-Memory Index Usage Query

-- Monitor In-Memory OLTP index activity
SELECT 
    OBJECT_NAME(i.[object_id]) AS [Object Name],
    i.index_id,
    i.[name] AS [Index Name],
    i.[type_desc] AS [Index Type],
    xis.scans_started,
    xis.scans_retries,
    xis.rows_touched,
    xis.rows_returned
FROM sys.dm_db_xtp_index_stats AS xis WITH (NOLOCK)
INNER JOIN sys.indexes AS i WITH (NOLOCK) 
    ON i.[object_id] = xis.[object_id]
    AND i.index_id = xis.index_id
ORDER BY [Object Name], i.index_id
OPTION (RECOMPILE);
        

Key Metrics Explained

• scans_retries: A high number here can indicate excessive transaction collisions or "interference" in the lock-free data structures, suggesting a need for better bucket counts in Hash indexes.
• rows_touched vs. rows_returned: If rows_touched is significantly higher than rows_returned, your index may be scanning through many irrelevant rows (common in poorly tuned Hash indexes with high collision rates).
• scans_started: Reflects the actual utilization frequency of the index by the query optimizer.

For further technical details, refer to the official Microsoft documentation for sys.dm_db_xtp_index_stats.

---

Related Performance Resources

• Retrieving SQL Server Memory Dumps
• Understanding Memory Clerk Usage
• Detecting Server Memory Pressure
• Analyzing Memory Grants Pending
• Resource Governor & Pool Configuration

Optimizing In-Memory performance? Ensure your Hash Index bucket counts are roughly 1x to 2x the number of unique values in your indexed column for maximum efficiency!

How to Monitor SQL Server Query Store Settings and Health via T-SQL

Summary: Use the sys.database_query_store_options view to audit your Query Store configuration, monitor storage limits, and verify the current capture and cleanup modes.

Monitoring SQL Server Query Store Settings

The Query Store is often described as a "flight data recorder" for your database. To ensure it is functioning correctly and not hitting storage limits, you need to monitor the sys.database_query_store_options system view. This is essential for troubleshooting scenarios where the Query Store may have automatically switched to a Read-Only state.

Query Store Configuration Audit Script

The following query provides a quick breakdown of your current and desired Query Store states, storage consumption, and cleanup policies.

-- Audit Query Store health and configuration settings
SELECT
    actual_state_desc,               -- Current state (e.g., READ_WRITE or READ_ONLY)
    desired_state_desc,              -- The state you have requested
    interval_length_minutes,         -- Data aggregation interval
    current_storage_size_mb,         -- Current footprint on disk
    max_storage_size_mb,             -- Max allowed size before cleanup/read-only
    query_capture_mode_desc,         -- Capture policy (ALL, AUTO, or NONE)
    size_based_cleanup_mode_desc,    -- Automatic cleanup behavior
    readonly_reason                  -- Why the Query Store is currently read-only
FROM sys.database_query_store_options WITH (NOLOCK)
OPTION (RECOMPILE);
        

Key Metrics & Optimization Hints:

• actual_state_desc vs desired_state_desc: If the actual state is READ_ONLY while the desired is READ_WRITE, check the readonly_reason. It is likely that the max_storage_size_mb has been reached.
• WITH (NOLOCK): This hint performs a "dirty read." While it prevents locking contention on high-traffic systems, always use it carefully if precise consistency is required.
• OPTION (RECOMPILE): This forces a fresh execution plan. For system views like this, it ensures you aren't looking at stale cached metadata from a previous run.

---

For a complete deep dive into Query Store maintenance and best practices, refer to the official Microsoft Query Store Documentation.

Is your Query Store filling up? Consider changing your QUERY_CAPTURE_MODE to AUTO to capture only the most relevant queries and save significant disk space!