Pending query memory grant requests; Resource Governor to increase query concurrency

Today while reviewing information from sys.dm_exec_query_memory_grants I noticed information for a query that listed ideal memory 278622251861496 - many petabytes.  The query cost was 42431012814.  Historically, SQL Server query cost was tied to an estimate of query execution time on a particular system.  Good thing that query wasn't running on that system - the cost estimate is for 1,345 years of execution time.  :)  That query wasn't alone - there were lots of queries that submitted excessive query memory grants.  And there were lots of pending query memory grants.  CPU utilization on the server was low - a brief peak at approximately 60% CPU busy was as high as it reached.  For much of the time, more pending query memory grants were registered than outstanding grants.  In order to improve performance, we had to get queries to stop just standing around.

The following is from the followup email I sent after discussing perfmon metrics and metrics from sys.dm_exec_query_memory_grants dmv on a conference call.


****

This afternoon we looked at perfmon metrics and sys.dm_exec_query_memory_grants information indicating extended periods of low server CPU utilized, few outstanding query memory grants (as low as three) together with many pending query memory grants (sometimes approaching 100 pending grants).

Some queries on the system currently have such large query cost and "ideal memory" (the sum of the required memory for the plan operators and the estimated intermediate and final query results) that increasing the server RAM would simply increase the query grant requests these queries would make.

Large query memory grant requests might actually be used by the requesting queries.  However, often large query memory grant requests are examples of "eyes bigger than stomach".  In these cases, the query only uses a small portion of the large query memory grant request.

Buffer pool pages are stolen as needed for query memory as the query progresses, up to the amount of the query memory grant.  "Reserved memory" accounts for the portion of query memory grants that have not yet been stolen.  Subtracting the reserved memory from the granted workspace memory yields the pages stolen for query memory.  This number is less than the number of stolen pages indicated by perfmon, because pages are also stolen for uses other than query workspace.  (The number of stolen pages while there are 0 outstanding query memory grants fluctuates a bit, but gives a pretty good idea of the number of stolen pages used for other purposes.)  The ratio of reserved memory to granted query workspace memory is consistently very high when there are pending query memory grants - never lower than 88% - indicating a low likelihood that most of the granted memory will ever be stolen for query memory.

There are two options for decreasing pending query memory grants and increasing query concurrency in that context:
1.    Identifying and tuning problem queries to reduce maximum query memory grant request size. 
2.    Using Resource Governor to reduce REQUEST_MAX_MEMORY_GRANT_PERCENT.   If this option is used, it should be a stopgap solution to allow additional time for identifying and tuning problem queries.  Once the offending queries have been corrected, REQUEST_MAX_MEMORY_GRANT_PERCENT should return to its default value.

The Resource Governor default MAX_MEMORY_PERCENT value for the default workgroup is the same as the SQLServer:Memory Manager\Maximum Workspace Memory perfmon metric. The Resource Governor REQUEST_MAX_MEMORY_GRANT_PERCENT value, the largest allowed individual query memory grant request, is by default 25% of MAX_MEMORY_PERCENT. 

In this case, lowering the REQUEST_MAX_MEMORY_GRANT_PERCENT to 5% would allow 19 or 20 (depending on other consumption of stolen pages) concurrent queries to execute with the maximum allowed query memory grant.

Any reduction of REQUEST_MAX_MEMORY_GRANT_PERCENT should be accompanied by monitoring of the use of query memory grants and tempdb; queries will spill remaining results to tempdb if they have already exhausted their query memory grant and haven't completed.  The idea of reducing REQUEST_MAX_MEMORY_GRANT_PERCENT is to increase query concurrency without causing excessive spills to tempdb by "right-sizing" query memory grant requests.   

The following link hints at this type of intervention.
http://technet.microsoft.com/en-us/library/cc627395(v=sql.105).aspx
From this page:
"… increasing request_max_memory_grant_percent has a side effect of reducing concurrency of large queries. For example, users can expect to run three large queries with the default 25 percent setting, but only two large queries with a 40 percent setting."

Conversely, reducing request_max_memory_grant_percent has a side effect of increasing concurrency of queries with large ideal memory estimates.