Thursday, September 26, 2013

Perfmon: SQL Server Memory Manager\Maximum Workspace Memory (KB)

I'm back for a little more SQL Server perfmon fun. 
Today lets look at the "Memory Manager\Maximum Workspace Memory (KB)" counter.
The SQL Server terms "workspace memory" and "query memory" are largely interchangeable - one or the other might be preferable when the context is perfmon, dmvs, or another area that tends to favor one term over another.
This is my favorite reference for high-level understanding of this memory use - its a 90+ minute video. :)
Query Tuning Mastery: Zen and the Art of Workspace Memory - Adam Machanic

Usually, the workspace memory maximum, or the limit for total outstanding memory grants, is fairly stable when SQL Server "total memory" is at the "target memory" level.  But not always - sometimes this limit can be quite volatile even when overall shared memory allotment to SQL Server is very stable.  I'm hoping to track down the resource that is shrinking the workspace memory maximum in the environments I work with - probably have to do it by using a DMV query every 5 minutes, maybe summing the memory amounts for the memory object types.

Here are two graphs - the first shows the database, stolen and free pages on a given system.
SQL server version: Microsoft SQL Server 2008 R2 (SP1) - 10.50.2769.0 (X64)
Windows version: Windows NT 6.1 <X64> (Build 7601: Service Pack 1)

Just like the graph in the previous blog post, total memory is stable at target memory level: max server memory has been achieved and is being maintained.  But, the workspace memory maximum is volatile, and seems to have a correlation to the number of database pages in "total memory".

Why do I care?  On this system there are pending query memory grants.  When do they happen?  Well, they tend to congregate during times that the workspace memory limit is lower.  From 6 am to 11 am the pending query memory grants are a significant performance concern.  So the lower limit on query memory grants is contributing to a query concurrency issue on this system (although the lower query memory limit is far from the only culprit in this crime story).

No comments:

Post a Comment