During my recent trip to the SQLPass conference in the US, I had the opportunity to meet many attendees and engage in some interesting conversations. One particular conversation during lunch caught my attention, as it revolved around how InMemory databases utilize memory and whether there is a way to determine which Resource Governor pool is using how much memory.

In response to this query, I wanted to share some valuable information about the DMVs (Dynamic Management Views) that can help us find this information quickly and easily.

Resource Governor Pools

The first DMV we can use is sys.dm_resource_governor_resource_pools. This DMV provides details about the Resource Governor pools and their memory configuration. By executing the following query, we can retrieve information such as the pool ID, name, minimum and maximum memory percentages, as well as the current memory usage:

SELECT pool_id, NAME, min_memory_percent, max_memory_percent, max_memory_kb / 1024 AS max_memory_in_MB, used_memory_kb / 1024 AS used_memory_in_MB, target_memory_kb / 1024 AS target_memory_in_MB
FROM sys.dm_resource_governor_resource_pools;

This query will give us insights into how the Resource Governor pools are configured and the current memory allocation for each pool.

Database and Pool Binding

If we have InMemory enabled databases, it is important to understand which databases are associated with which resource pools and how memory is allocated. The following query can provide us with this information:

SELECT d.database_id, d.NAME AS DbName, d.resource_pool_id AS PoolId, p.NAME AS PoolName, p.min_memory_percent, p.max_memory_percent
FROM sys.databases d
LEFT OUTER JOIN sys.resource_governor_resource_pools p ON p.pool_id = d.resource_pool_id;

By executing this query, we can see the mapping of databases to resource pools, along with the associated memory configuration.

These DMVs offer valuable insights into the memory usage in SQL Server, allowing us to monitor and optimize resource allocation. If you have any similar experiences or insights to share, please feel free to leave a comment below.