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!