How to Monitor SQL Server Resource Governor Pools using T-SQL

Summary: Use the sys.dm_resource_governor_resource_pools DMV to monitor memory utilization, IOPS limits, and target memory for your SQL Server resource pools.

Monitoring SQL Server Resource Governor Pools

Ever wondered about the current status of your SQL Server resource pools? When using Resource Governor to manage multi-tenant workloads or to limit internal background processes, it is critical to monitor how much memory and I/O each pool is actually consuming.

This quick query provides valuable insights into performance and memory utilization across all configured pools.

Resource Pool Utilization Script

SELECT
    pool_id,
    [Name],
    statistics_start_time,
    min_memory_percent,
    max_memory_percent,
    max_memory_kb / 1024 AS [max_memory_mb],
    used_memory_kb / 1024 AS [used_memory_mb],
    target_memory_kb / 1024 AS [target_memory_mb],
    min_iops_per_volume,
    max_iops_per_volume
FROM sys.dm_resource_governor_resource_pools WITH (NOLOCK)
OPTION (RECOMPILE);
        

Understanding the Metrics

This query leverages the sys.dm_resource_governor_resource_pools dynamic management view. Key columns to watch include:

• used_memory_mb: The actual amount of memory currently held by the pool.
• target_memory_mb: The amount of memory SQL Server is currently attempting to allocate to the pool based on workload demand and settings.
• max_iops_per_volume: Helps you identify if I/O capping is actively restricting a specific resource pool's performance.

Optimizing your SQL Server resources? Check out our other guides on monitoring CPU trends and TempDB usage!

How to Audit SQL Server Service Accounts and Status using T-SQL

Summary: Use the sys.dm_server_services DMV to quickly audit SQL Server service accounts, startup types, cluster status, and Instant File Initialization (IFI) settings.

Retrieve SQL Server Service Details and Configurations

Ever wondered about the nitty-gritty details of your SQL Server services? Instead of opening the Windows Services console (services.msc) or Configuration Manager, you can pull a comprehensive overview directly from a query window.

SQL Server Service Audit Script

This query provides a quick overview of essential information for each service associated with your current SQL Server instance.

-- Audit SQL Server services and configuration
SELECT 
    servicename AS [Service Name],
    process_id AS [PID],
    startup_type_desc AS [Startup Type],
    status_desc AS [Current Status],
    last_startup_time AS [Last Start Time],
    service_account AS [Account Name],
    is_clustered AS [Is Clustered?],
    cluster_nodename AS [Active Node],
    [filename] AS [Binary Path],
    instant_file_initialization_enabled AS [IFI Enabled]
FROM sys.dm_server_services WITH (NOLOCK)
OPTION (RECOMPILE);
        

Key Insights from this Query

• Service Account: Quickly verify if your services are running under the correct least-privileged service accounts.
• Instant File Initialization (IFI): Ensure IFI is enabled to speed up data file allocations and restores (SQL Server 2016+).
• Startup Type: Identify services that might fail to start after a reboot because they are set to 'Manual'.
• Cluster Details: Confirm which node in a Failover Cluster Instance (FCI) is currently hosting the service.

For more in-depth information regarding the columns in this DMV, refer to the official Microsoft documentation.

---

Performing a Health Check? Checking your service accounts and IFI status is a foundational step in any SQL Server performance audit!

How to Audit SQL Server NUMA Node Configuration and Scheduler Load

Summary: Use the sys.dm_os_nodes DMV to inspect NUMA node health, verify balanced CPU scheduler distribution, and monitor active worker loads across your hardware architecture.

Monitoring NUMA Node Configuration and Load Balance

This query provides valuable insights into your NUMA (Non-Uniform Memory Access) nodes, showing their composition and how much load they are currently handling. Understanding the distribution of resources and activity across your system's memory architecture is critical for high-performance database environments.

NUMA Node and Scheduler Audit Script

-- Check NUMA node health and scheduler distribution
SELECT 
    node_id,
    node_state_desc,
    memory_node_id,
    processor_group,
    cpu_count,
    online_scheduler_count,
    idle_scheduler_count,
    active_worker_count,
    avg_load_balance,
    resource_monitor_state
FROM sys.dm_os_nodes WITH (NOLOCK)
WHERE node_state_desc <> N'ONLINE DAC'
OPTION (RECOMPILE);
        

Why This Matters for Performance

After running this, you should verify that an equal number of online schedulers are assigned to each NUMA node. This is especially important for physical or virtual machines with more than four sockets or over 24 physical cores.

• Balanced Load: An uneven distribution of schedulers can lead to "hot" NUMA nodes where specific CPUs are overworked while others remain idle.
• License Alignment: On standard edition servers with core limits, ensure your limited cores are spread evenly across nodes to maximize memory bandwidth.
• Resource Monitor State: This indicates if the internal thread responsible for memory management is healthy on that specific node.

---

For more detailed information on NUMA tuning and hardware alignment, explore these resources:

• Official Documentation: sys.dm_os_nodes
• SQLskills: Balancing Core Licenses Across NUMA Nodes

Troubleshooting performance bottlenecks? Proper NUMA alignment is often the "hidden" fix for scaling issues on large multi-processor servers!

How to use SERVERPROPERTY to Retrieve SQL Server Instance Details

Summary: Use the SERVERPROPERTY function in T-SQL to extract essential metadata about your SQL Server instance, including versioning, edition, patch levels, and feature support.

Discovering SQL Server Instance Details with SERVERPROPERTY

Ever needed to quickly grab essential information about your SQL Server instance? The SERVERPROPERTY function is a powerful tool for DBAs and developers alike. It allows you to retrieve a wide range of details—from network names and versioning to specific engine configurations like collation and default file paths—all without needing access to the physical host.

The Comprehensive Instance Audit Script

Below is a comprehensive SQL query that leverages SERVERPROPERTY to pull numerous valuable insights about your SQL Server environment into a readable vertical list.

-- Retrieve a detailed snapshot of SQL Server instance properties
SELECT SERVERPROPERTY('MachineName') AS [Value], 'Machine Name' AS [Description]
UNION ALL SELECT SERVERPROPERTY('ServerName'), 'Server Name'
UNION ALL SELECT SERVERPROPERTY('InstanceName'), 'Instance'
UNION ALL SELECT SERVERPROPERTY('IsClustered'), 'IsClustered'
UNION ALL SELECT SERVERPROPERTY('ComputerNamePhysicalNetBIOS'), 'NetBIOS Physical Name'
UNION ALL SELECT SERVERPROPERTY('Edition'), 'Edition'
UNION ALL SELECT SERVERPROPERTY('ProductLevel'), 'Product Level (RTM/SP/CU)'
UNION ALL SELECT SERVERPROPERTY('ProductUpdateLevel'), 'CU Level'
UNION ALL SELECT SERVERPROPERTY('ProductVersion'), 'Full Product Version'
UNION ALL SELECT SERVERPROPERTY('ProductMajorVersion'), 'Major Version'
UNION ALL SELECT SERVERPROPERTY('ProductMinorVersion'), 'Minor Version'
UNION ALL SELECT SERVERPROPERTY('ProductBuild'), 'Build Number'
UNION ALL SELECT SERVERPROPERTY('ProductBuildType'), 'Build Type (GDR/OD/CU)'
UNION ALL SELECT SERVERPROPERTY('ProductUpdateReference'), 'KB Article Reference'
UNION ALL SELECT SERVERPROPERTY('ProcessID'), 'OS Process ID'
UNION ALL SELECT SERVERPROPERTY('Collation'), 'Server Collation'
UNION ALL SELECT SERVERPROPERTY('IsFullTextInstalled'), 'Full-Text Search Installed'
UNION ALL SELECT SERVERPROPERTY('IsIntegratedSecurityOnly'), 'Windows Auth Only Mode'
UNION ALL SELECT SERVERPROPERTY('FilestreamConfiguredLevel'), 'Filestream Level'
UNION ALL SELECT SERVERPROPERTY('IsHadrEnabled'), 'Always On (HADR) Enabled'
UNION ALL SELECT SERVERPROPERTY('HadrManagerStatus'), 'HADR Manager Status'
UNION ALL SELECT SERVERPROPERTY('InstanceDefaultDataPath'), 'Default Data Path'
UNION ALL SELECT SERVERPROPERTY('InstanceDefaultLogPath'), 'Default Log Path'
UNION ALL SELECT SERVERPROPERTY('BuildClrVersion'), 'CLR Version'
UNION ALL SELECT SERVERPROPERTY('IsXTPSupported'), 'In-Memory OLTP Supported'
UNION ALL SELECT SERVERPROPERTY('IsPolybaseInstalled'), 'Polybase Installed'
UNION ALL SELECT SERVERPROPERTY('IsAdvancedAnalyticsInstalled'), 'Machine Learning / R Services'
OPTION (RECOMPILE);
        

Why Use This?

This query provides a detailed overview of your SQL Server setup, making it incredibly useful for:

• Inventory Audits: Quickly gathering version and edition details across multiple servers.
• Troubleshooting: Verifying if a specific Cumulative Update (CU) or KB patch has been applied.
• Configuration Checks: Ensuring default paths and security modes align with company standards.

---

Further Reading

For a complete and in-depth understanding of all properties available with this function, refer to the official Microsoft documentation.

Need to automate your environment discovery? This script is a perfect starting point for building a dynamic server inventory dashboard!

How to Find SQL Server Socket and Core Counts for Licensing

Summary: Use the xp_readerrorlog procedure to extract CPU socket, physical core, and logical core counts directly from the SQL Server startup logs for accurate licensing verification.

Find Socket and Core Counts in the SQL Server Error Log

This post will show you how to find out the socket, physical core, and logical core counts directly from your SQL Server Error log. This information is crucial for verifying your SQL Server licensing model, especially when moving between physical and virtual environments.

Querying the Error Log for CPU Metadata

During the SQL Server startup sequence, the engine detects and logs the available hardware topology. You can use the following command to filter the current log for these specific hardware entries:

-- Identify CPU hardware topology for licensing
EXEC sys.xp_readerrorlog 0, 1, N'detected', N'socket';
        

Why This Information is Vital

Running the command above helps you quickly confirm your SQL Server's hardware configuration and ensures it aligns with your licensing requirements:

• Physical Core Count: Essential for Core-based licensing (Standard and Enterprise).
• Socket Count: Useful for checking host-level hardware limits.
• Hyperthreading: Confirming the ratio of logical vs. physical cores to ensure SQL Server is utilizing the available hardware efficiently.

---

Performing a Licensing Audit? This simple command is the fastest way to pull hardware stats without needing server-level permissions!

How to Check and Enable Global Trace Flags in SQL Server

Summary: Use DBCC TRACESTATUS to identify active global trace flags and learn how to enable performance-tuning flags like TF 3226, 6534, and 7745.

Managing Global Trace Flags in SQL Server

Ever wondered which global trace flags are active in your SQL Server environment? Trace flags are frequently used to temporarily set specific server characteristics or to switch off a particular behavior. You can easily discover which flags are currently active using the following command:

-- Check status of all global trace flags
DBCC TRACESTATUS (-1);
        

This command reveals all currently enabled global trace flags. For instance, you might encounter some common ones used in production environments:

• TF 3226: Suppresses "backup successful" messages in the SQL Server Error Log. This is a must-have for servers with frequent log backups to keep the error log readable.
• TF 6534: Enables native code execution for spatial data types, which can significantly boost performance for GIS-heavy workloads.
• TF 7745: Prevents Query Store data from being written to disk during a failover or shutdown, prioritizing availability over the persistence of the latest runtime stats.

How to Enable a Trace Flag

To enable a specific trace flag globally (the -1 parameter is essential for global scope), use the DBCC TRACEON command:

-- Enable a trace flag globally (e.g., 3226)
DBCC TRACEON (3226, -1);
        

Note: While DBCC TRACEON enables a flag immediately, it will be lost upon restart. To make a trace flag persistent, you must add it as a Startup Parameter (e.g., -T3226) in the SQL Server Configuration Manager.

---

For a comprehensive list of all available trace flags, refer to the official Microsoft documentation.

Optimizing your instance? Always test trace flags in a development environment first, as some can significantly change the behavior of the Query Optimizer!

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!

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!