Do stolen pages (as in sys.dm_os_performance_counters) report anything that sys.dm_os_memory_clerks does not?
0
votes
1
answer
141
views
I have been under the misconception that
SELECT * FROM sys.dm_os_performance_counters WHERE counter_name LIKE 'Stolen Server Memory%';
shows how much memory the OS is stealing from SQL Server. The truth, according to [the documentation](https://learn.microsoft.com/en-us/sql/relational-databases/performance-monitor/sql-server-memory-manager-object?view=sql-server-ver16#memory-manager-counters) , is
> Specifies the amount of memory the server is using for purposes other than database pages.
which I take to mean "the amount of memory used by SQL Server (i.e. not the OS) for anything other than the buffer pool".
With this in mind, is there any point in checking sys.dm_os_performance_counters
if I am already checking SELECT * FROM sys.dm_os_memory_clerks ORDER BY pages_kb DESC
? Given that the buffer pool is always at the top of that list (your server is doomed if it isn't), determining how much memory isn't being used by it should be a simple matter of arithmetic.
I suspect that I am wrong about this because these two numbers do not match on my server. However, I do not know how to find out why.
-- Total stolen memory.
SELECT top (1) cntr_value
FROM sys.dm_os_performance_counters
WHERE counter_name LIKE 'Stolen Server Memory%'
ORDER BY cntr_value desc;
-- Total memory not in buffer pool.
-- Should be the same as the above but is always less on my machine.
SELECT SUM(pages_kb)
FROM sys.dm_os_memory_clerks
WHERE [Type] 'MEMORYCLERK_SQLBUFFERPOOL';
Asked by J. Mini
(1237 rep)
Feb 25, 2024, 05:05 PM
Last activity: Mar 7, 2024, 10:45 PM
Last activity: Mar 7, 2024, 10:45 PM