Resource Governor Resource Pool information

Ever wondered about the current status of your SQL Server resource pools? This quick query provides valuable insights into their performance and memory utilization.

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);

This query leverages the sys.dm_resource_governor_resource_pools dynamic management view to give you a clear picture of how your resource governor is managing your SQL Server resources.