Thursday, September 26, 2013
Perfmon: SQL Server Database pages + Stolen pages + Free pages = Total pages
I stare at a lot of perfmon numbers, and I stare for a really, really long time.
I like to understand the relationship of utilization among related resources, and how variable limits are set in response to system conditions. I don't often easily find resources which explain such things.
The max server memory attribute should be set to govern the size of SQL Server shared memory regions - the value should allow enough server RAM for Windows, SQL Server processes themselves, a small bit of filesystem cache, and other memory needs of the server*. If that value is set to a value that can be achieved by SQL Server, it'll appear in perfmon as the value for "SQLServer:Buffer Manager\Target pages" (among other places). As the SQL Server page allocator strives toward the target memory allotment, its progress will be noted in "SQLServer:Buffer Manager\Total pages".
There's a lot of stuff about SQL Server memory management and utilization that I haven't figured out yet... but this one's written in ink:
Database pages + Stolen pages + Free pages = Total pages
And, if a server has achieved max server memory, total pages will be equal to target pages. If it maintains its target pages value in total pages, then you can create a nice stacked area graph like the one above to show how database pages, stolen pages, and free pages fit together within max server memory. If total pages grows and shrinks - well maybe your max server memory setting is a bit too high :) You can still make a stacked graph - the top edge will just be ragged and it will indicate the total pages in the struggle of trying to achieve the target pages on your server.
Now, understanding what makes up stolen pages is a bit more complicated. So its for another day.
*In versions previous to SQL Server 2012, among the "other memory needs of the server" are any allocations made by the multi-page allocator. Be careful to consider that when setting max server memory for SQL Server 2008 R2 or before. For SQL Server 2012 and beyond, the previous single page allocator (governed by max server memory) and multi-page allocator (scoffs at max server memory) have given way to the multi-page allocator (governed by max server memory). Thus the memory uses previously using the multi-page allocator is now governed by max server memory and overall memory management has become a little more predictable.