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