For databases that contain memory-optimized data, it’s considered a best practice to create a separate resource pool that limits memory consumption for memory-optimized objects. If you don’t use use Resource Governor to constrain the amount of memory allocated to memory-optimized objects, it’s possible that the buffer pool will respond to memory pressure, shrink, and performance for traditional/on-disk tables will be impacted (using Resource Governor also allows you to track memory consumption specific to that database). We must carefully monitor the amount of memory used by the resource pool, and respond when it goes beyond a certain threshold. If we don’t, then we can reach an out-of-memory condition (OOM).
This blog post details the various approaches I considered.
Option 1
SQL Agent Performance Condition Alerts can respond to changes on the “Resource Pool Stats” object. This gets us pretty close to the stated goal, but unfortunately you have to hard code a threshold value for “Used memory (KB)”. If you needed to deploy this type of monitoring on many servers, and over time some of them upgrade physical memory, you have to remember to go back and adjust the hard-coded value for the new threshold. I was hoping to be able to evaluate this as a percentage of “Target Memory (KB)”, and then alert above x%, but that doesn’t seem to be possible with Performance Condition Alerts.
Option 2
SQL Server Event Alerts can take action when a specific error number occurs. I checked sys.sysmessages for references to the word ‘pool’, but as far as I can tell, none of them allow you to proactively respond to memory conditions before you run out of memory. We have errors like:
- Disallowing page allocations for database ‘%.*ls’ due to insufficient memory in the resource pool ‘%ls’.
- There is insufficient memory in the resource pool ‘%ls’ to run this operation on memory-optimized tables.
- There is insufficient system memory in resource pool ‘%ls’ to run this query.
Option 3
Another possibility would be to have a TSQL Agent job that constantly polls sys.dm_os_performance_counters:
|
1 2 3 4 5 6 7 8 9 |
SELECT object_name ,counter_name ,instance_name ,cntr_value ,cntr_type FROM sys.dm_os_performance_counters WHERE object_name = 'SQLServer:Resource Pool Stats' AND instance_name = '<your_pool_name>' AND counter_name IN ('Target memory (KB)', 'Used memory (KB)') |
This would allow you to calculate the ratio you want to alert on, and then send an email. I like this approach for its simplicity, but you’d have to compare the value of cntr_value for two different rows to calculate the percentage.
Option 4
I looked into WMI, and the documentation is pretty bad about exactly what’s supported for SQL Server. I was surprised to find that the class I needed was part of root\cimv2, not in the SQL Server branch. Note that there can be security issues with accessing WMI, so that might be a concern for your environment (NOTE: WMI is pretty old, so it’s not recommended to keep using it. Instead, we should CIM, Common Information Model).
|
1 2 3 4 5 6 |
$FreePoolSpace = Get-CimInstance -namespace root\cimv2 ` -class Win32_PerfFormattedData_MSSQLSERVER_SQLServerResourcePoolStats ` -Filter "Name = 'Pool_InMem' " | select UsedmemoryKB, TargetmemoryKB $FreePoolSpace.UsedmemoryKB /$FreePoolSpace.TargetmemoryKB |
This does exactly what we need:
No matter how much memory is assigned to the pool, it calculates the percentage that’s used. If the value of Used memory exceeds a pre-defined percentage of Target memory, you might send an email, write to the SQL Errorlog (and then notify based on that error number), or use other options for logging and notification.