SQL Server Workspace Memory - with a Twist

In my recent blog post 'SQL Server Granted/Reserved/Stolen Workspace Memory in Perfmon' I hinted at a twist in the story of perfmon accounting of workspace memory.

Here's the stolen/free/database cache account of memory from perfmon on 'system B'.  Its a big memory system - a terabyte of RAM.

 

 At times more than 250 GB of RAM is stolen memory.  Wonder how much of that is used workspace memory?  How about inserting an Excel column for the difference between 'SQLServer:Memory Manager\Granted Workspace Memory (KB)' and 'SQLServer:Memory Manager\Reserved Server Memory (KB)'?

Whoa... reserved memory should be a subset of granted memory?!?




The amount of granted memory is so small I can't even see it on the same scale as the reserved memory. Adjust the scale and I can see that 'SQLServer:Memory Manager\Granted Workspace Memory (KB)' rarely has much memory accounted at all.






Well, I know that there are multiple Resource Governor workload groups on this server.  Here's a graph of their CPU activity during this observation time.



WrkGrp_4 is the main source of activity during the observation period. I wonder how these workgroups are distributed among Resource Governor resource pools?  Each resource pool added to the RG results in a new set of memory brokers added to the system, including a new memory broker to account for memory grants within the pool.

Sure - I could show the RG configuration but I'm a charts-n-graphs kinda guy :-)  Comparing this graph of Resource Pool CPU utilization to the previous graph, its clear each of the 4 workload groups is associated with its own resource pool.



With additional resource pools and their brokers in the mix,  the accounting of  'SQLServer:Memory Manager\Granted Workspace Memory (KB)' and 'SQLServer:Memory Manager\Reserved Server Memory (KB)' is expected to change.

We can see that 'SQLServer:Memory Manager\Granted Workspace Memory (KB)' only reports the granted query memory for the default resource pool.



With that being the case,  consistency would predict that 'SQLServer:Memory Manager\Reserved Server Memory (KB)' only report the unused portion of memory grants within the default resource pool.  If that were the case, 'SQLServer:Memory Manager\Reserved Server Memory (KB)' values would always be less than 'SQLServer:Memory Manager\Granted Workspace Memory (KB)' - we've already seen that's not the case.

Below is a transparent red overlay of  'SQLServer:Memory Manager\Reserved Server Memory (KB)' on top of a stacked graph of 'Active memory grant amount (KB)' for each of the resource pools.
That's more like it! 


So there's a fundamental difference between perfmon values for 'SQLServer:Memory Manager\Granted Workspace Memory (KB)' and 'SQLServer:Memory Manager\Reserved Server Memory (KB)': the granted memory values are ONLY for the default resource pool, while the reserved memory values are an aggregate across all resource pools.  Without that important tidbit, memory accounting can get pretty weird if there are active added resource pools to the Resource Governor configuration.

With that piece of knowledge, I add a column to Excel that sums the active memory grant amount for each of the resource pools, then subtract 'SQLServer:Memory Manager\Reserved Server Memory (KB)'.  The result is the total stolen/used workspace memory across all resource pools.  I wish perfmon had a counter for reserved memory per resource pool to accompany its granted per pool counter.  Alas - if you want that you're gonna hafta reap it from sys.dm_exec_query_memory_grants or sys.dm_exec_query_resource_semaphores.

Anyway - with my new computed column I can see that stolen/used query memory is that largest contributor to stolen memory during the observation, and based on the counters for granted per pool I know that the vast majority of the used/stolen query memory during the observation was against grants for ResPool_4.



There are three big reasons I like to track used query memory, especially in the context of Resource Governor:
1. Often query concurrency is limited by the amount of granted memory - but used query memory may be a small fraction of granted throughout.  If there's CPU to spare, using Resource Governor to limit the maximum query grant size can increase system performance by decreasing resource_semaphore waits and increasing query concurrency.
2. Sorting and hashing activity in workspace memory is not counted in the perfmon 'buffer page lookups' counter.  So when there is massive amounts of sorting, it can bend the relationship of CPU utilization to logical IO on the system.  Important to know when modeling system behavior.
3. If there are multiple resource pools active at the same time (other than the pre-existing default and internal pools), the system is vulnerable to out-of-memory exceptions when granted workspace memory becomes overcommitted and non-workspace memory steal/allocations fail.

But those stories will have to wait for yet another day... 


***Update***
Once you graph the used query memory on a given system, if you see that its just a fraction of granted memory and are experiencing pending memory grants/resource_semaphore waits - you may want to use Resource Governor workload group max query memory grant to increase query concurrency.  An older blog post where I address that.

Resource Governor to restrict Max Query Memory Grant
http://sql-sasquatch.blogspot.com/2015/03/resource-governor-to-restrict-max-query.html

   

SQL Server Granted/Reserved/Stolen Workspace Memory in Perfmon

In order to understand memory utilization at the server layer, the memory must be accounted.  That’s not always easy.  When analyzing perfmon for SQL Server, I recommend the following relationship as a starting point.

SQLServer:Memory Manager\Database Cache Memory (KB)

+

SQLServer:Memory Manager\Stolen Server Memory (KB)

+

SQLServer:Memory Manager\Free Memory (KB)

=

SQLServer:Memory Manager\Total Server Memory (KB)

Here' s a graph of 'system A' that shows this memory relationship.  When 'SQLServer:Memory Manager\Total Server Memory (KB)' achieves the SQL Server max server memory setting and the server remains free of server-level memory pressure, such graphs look pretty.

Here's a similar graph of 'system B'. Graphs like this are extremely useful to me.

Many systems experience a condition where some queries incur significant resource_semaphore waits.  These waits occur when the current sum of granted workspace memory prevents the waiting queries from receiving their memory grant.

In cases like that, it may seem useful to graph the relationship between granted workspace memory and the memory utilization above.

Well – shoot.  The 'system A'  graph including 'SQLServer:Memory Manager\Granted Workspace Memory (KB)'  doesn' t look extremely useful.  Sometimes stolen memory is way less than granted memory.  Sometimes granted memory is way more than stolen memory.  The relationship between granted memory and stolen memory is rather mysterious.  (Without spoiling too much later fun, I’ll state here that 'system A' is not using Resource Governor resource pools outside of the included default and internal pools.)

  

Perfmon also includes 'SQLServer:Memory Manager\Reserved Server Memory (KB)'.  This measure is an important part of this story. 

When a plan is selected for a query,  'ideal memory' has been calculated.  This amount of RAM is estimated to accommodate all sort and hash for the query to take place in memory without using tempdb.  If the 'ideal memory' is higher than the maximum query memory grant allowed for the workload group or the system, the grant request will instead be for the maximum grant.  When the request is granted, no action against the total amount of memory is taken immediately.  Rather, memory is 'stolen' against the grant and allocated to the query as needed over time.  Any portion of the grant not used by the query is still reserved for the query grant – but if that particular piece of memory had database buffer pool contents in, the contents remained and could be accessed by queries throughout.

This gives a clue to the meaning of 'SQLServer:Memory Manager\Reserved Server Memory (KB)'.   Some amount of memory has been granted (or promised) to queries for workspace.  Within that amount at any given time is memory which has been stolen against the grants, and reserved memory which may be stolen against the grants as the queries continue their execution.  So the difference on system A between 'SQLServer:Memory Manager\Granted Workspace Memory (KB)'  and 'SQLServer:Memory Manager\Reserved Server Memory (KB)' is the amount of used, or stolen, workspace memory.  That amount is visible as the darker blue in the graph below, peeking out from behind the lighter blue of the reserved server memory.

Since stolen/used workspace memory is not directly available from perfmon, I just add a column in Excel with the formula for the difference between 'SQLServer:Memory Manager\Granted Workspace Memory (KB)'  and 'SQLServer:Memory Manager\Reserved Server Memory (KB)' . 

Now its clear that although the queries on system A in aggregate don’t come close to using their entire memory grants, workspace memory is the main contributor to stolen memory most of the time.

There’s a big twist to this story on system B.  That’ll hafta wait for my next blog post.

***Update***

Wow!  Two days and 2 blog posts!  Unheard of for me :-)

SQL Server Workspace Memory - with a Twist

 http://sql-sasquatch.blogspot.com/2015/08/sql-server-workspace-memory-with-twist.html


******Update******
Once you graph the used query memory on a given system, if you see that its just a fraction of granted memory and are experiencing pending memory grants/resource_semaphore waits - you may want to use Resource Governor workload group max query memory grant to increase query concurrency.  An older blog post where I address that.

Resource Governor to restrict Max Query Memory Grant
http://sql-sasquatch.blogspot.com/2015/03/resource-governor-to-restrict-max-query.html 

SQL Server PLE with 8 NUMA nodes

I posted earlier about PLE as a tricky performance metric - showing how a drop in PLE in some cases is a sign of performance improvement rather than performance drop :-)
http://sql-sasquatch.blogspot.com/2015/04/sql-server-ple-is-very-tricky.html

Here's another quick set of thoughts about PLE - this time about the PLE on "home NUMA node" being more relevant to a given query than the overall PLE, or the PLEs of other NUMA nodes.

Because each NUMA node by default manages its own portion of the buffer pool cache, the number reported as the overall page life expectancy of SQL Server on an 8 NUMA node server is a composite of the 8 NUMA node-local PLEs.  I haven't been able to figure out how the overall PLE is calculated from node PLEs; I'm typically more interested in the amount of database cache and free memory per node anyway.  But here are some of the more interesting numbers. 





Note: these perfmon captures are from a SQL Server version where SQLOS still swapped to call NUMA node 0 "Buffer Node 1", and NUMA node 1 "buffer node 0".  SQL Server doesn't still do that in SQL Server 2014 :-)

Lets zoom in a little.  Here are the 8 NUMA node PLEs, and the dotted red line is the overall PLE.


The difference in PLE across the nodes is easy to understand once you notice the variation across NUMA nodes in CPU busy.  All of the CPU consumption on this server was from SQL Server.


Database page reads will insert the page into the buffer pool associated with the NUMA node that is home to the worker thread performing the read.  First writes, resulting in a newly allocated page, will also go into the NUMA node that is home for that worker.  Busier NUMA nodes(in terms of CPU utilization) can be expected to have higher insert rates.  Insert rate and database cache size are important contributors to PLE calculation.  But, there are other important considerations, too: rate of stealing, and rate of freeing - since these rates will determine the change in size of the database cache.  (Also, the rate of growth of the database cache - especially during rampup but also during other periods when other cache allotments such as procedure cache shrink resulting in lots of free pages.)






***** Update *****

Yeah, just wanted to throw in one more close-up :-)