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.
Any progress on stolen pages? I would love to read up on and/or collaborate on the topic!
ReplyDeleteNarrowing down the trends and relationships in "\SQLServer:Memory Manager\Stolen Server Memory (KB)" has been a little trickier. I've been working in SQL Server 2012 almost exclusively for this, so I don't have to worry about the multi-page allocator vs single page allocator. My T-SQL is nowhere near as good as most folks :)
ReplyDeleteI had hoped that other counters in the '\SQLServer:Memory Manager' object could be summed to get 'Stolen Server Memory (KB)'... subtracting the reserved query memory since that's granted memory that hasn't been stolen yet. But even after I added in the plan cache pages as below, the number is way too small. Accounting based on the clerks and brokers comes closer. So my attempts to reconcile numbers within perfmon against perfmon reported 'stolen server memory' aren't going too well yet. But reconciling against the brokers and clerks is going a little better.
SELECT
cntr_value AS [perfmon_Stolen_Server_Memory_kb]
FROM
sysperfinfo
WHERE
object_name='SQLServer:Memory Manager'
AND
counter_name ='Stolen Server Memory (KB)'
DECLARE @add_parts BIGINT
DECLARE @subtract_part BIGINT
DECLARE @cachedplan_kb BIGINT
SELECT @cachedplan_kb =
cntr_value * 8
FROM
sysperfinfo
WHERE
object_name = 'SQLServer:Plan Cache'
AND
Counter_name = 'Cache Pages'
AND
instance_name = '_Total'
SELECT @add_parts =
SUM(cntr_value)
FROM
sysperfinfo
WHERE
object_name='SQLServer:Memory Manager'
AND
counter_name IN (
'Connection Memory (KB)',
'Lock Memory (KB)',
'Optimizer Memory (KB)',
'SQL Cache Memory (KB)',
'Log Pool Memory (KB)',
'Granted Workspace Memory (KB)'
)
SELECT @subtract_part =
cntr_value
FROM
sysperfinfo
WHERE
object_name='SQLServer:Memory Manager'
AND
counter_name='Reserved Server Memory (KB)'
SELECT
@cachedplan_kb + @add_parts - @subtract_part AS [stolen_Memory_Manager_accounting_kb]
SELECT
SUM(allocations_kb) AS [stolen_broker_accounting_kb]
FROM SYS.DM_OS_MEMORY_BROKERS
DECLARE @bpool_commit BIGINT
DECLARE @nonbpool_stolen BIGINT
SELECT @bpool_commit=
SUM(virtual_memory_committed_kb)
+ SUM(shared_memory_committed_kb)
FROM
SYS.DM_OS_MEMORY_CLERKS
WHERE
type = 'MEMORYCLERK_SQLBUFFERPOOL';
SELECT @nonbpool_stolen=
SUM(pages_kb)
+ SUM(virtual_memory_committed_kb)
+ SUM(shared_memory_committed_kb)
FROM
SYS.DM_OS_MEMORY_CLERKS
WHERE
type != 'MEMORYCLERK_SQLBUFFERPOOL'
SELECT
@bpool_commit + @nonbpool_stolen AS [stolen_clerk_accounting]
Aha! I forgot to accommodate the 128 transaction log buffers per database, at 64k each. Lets see how that helps.
ReplyDelete