Have you ever heard of stolen memory in SQL Server? I’ve never had any significant issues with stolen memory myself, but I thought it would make for a good blog post. Let’s take a look at what stolen memory is and what can be done about it.
What is Stolen Memory?
When you specify the max amount of memory that SQL Server can use, you may think that’s all the memory to be used for the buffer pool. That’s not the case. SQL Server will use memory not only for the buffer pool but also in tasks such as sorts, memory grants, and other internal tasks. These tasks can swoop in and “steal” memory from the buffer pool, causing more work for SQL Server.
Why is that a problem?
Stolen memory can be an indication of memory pressure and lead to a less useful buffer pool. Since memory is stolen from the buffer pool, that stolen data is no longer available to be used, meaning the next time that data is needed, SQL Server will need to go to disk yet again instead of grabbing it from the buffer pool.
Memory Considerations
When evaluating memory needs, you’ll want to evaluate more than just what data resides in the buffer pool.
Consider this: if you’re on SQL Server Standard Edition 2019, which is capped at a 128 GB memory limit, that limit applies only to the buffer pool. Total Server Memory is not limited by 128 GB, so consider setting this value higher than 128 GB to account for the buffer pool plus other parts of SQL Server that utilize memory.
Tracking Stolen Memory
If you have stolen memory concerns, you can track it with this query:
SELECT object_name, counter_name, cntr_value
FROM sys.dm_os_performance_counters
WHERE counter_name = 'Stolen Server Memory (KB)';
GO
I haven’t come across a specific number to indicate that you either do have stolen memory problems or don’t have stolen memory problems, only to use this query as an option in gathering a baseline that can be tracked.
Stolen memory can be an indication of memory pressure and hinder your buffer pool. Keep it in mind as a potential issue with your SQL Servers.
Thanks for reading!
Chad Callihan 