Resource governor is a useful feature in SQL Server 2008 which can provide instance-level throttling of CPU and memory resources. I will not dwell into the basic details and architecture, as they are very well documented in Books Online and also in the reference links mentioned at the end of this post. However, I do want to briefly discuss the difference between two different settings which control memory allocations:

  • The REQUEST_MAX_MEMORY_GRANT_PERCENT setting which is a property of the workload group
  • The MAX_MEMORY_PERCENT which is a property of the resource pool

From the available resources on this topic it may not be totally apparent as to what is the difference between these two, apart from the fact that they are applied to different entities: one to the workload group and one to the resource pool. However, the more important thing is that they actually control 2 different aspects of SQL Server memory.

MAX_MEMORY_PERCENT

This controls the total amount of memory (allocated through the Single Page Allocator) used by a resource pool. Here is the expected result if your request running in workload group ‘mygroup’ and therefore in resource pool ‘mypool’ exceeds this value:

Msg 701, Level 17, State 123, Line 2
There is insufficient system memory in resource pool 'mypool' to run this query.

It is also interesting to see that setting this parameter effectively affects plan cache size. The resource governor will aggressively evict plans from cache if the above limit is reached. I arrived at this finding based on a very interesting Microsoft Connect feedback item involving Boris Baryshnikov, and the same is documented at the link at the end of the references section. You can easily test the same by providing a lot of adhoc queries to the engine, such as what the sample script below does:

declare @i int
select @i  = 1
declare @s nvarchar(500)
while @i < 100000
begin
select @s = N'select * from Person.Contact where ContactID = ' + convert(varchar, @i)
exec sp_executesql @s
select @i = @i + 1
end

If you run this query without resource governor enabled, you will find that sys.dm_exec_cached_plans is quickly filled with a lot of ‘junk’ plans, one for each iteration above:

select plan_handle, text from sys.dm_exec_cached_plans
cross apply sys.dm_exec_sql_text(plan_handle)

In my simple test, with resource governor enabled, the plan cache is kept small as older plans are more aggressively evicted from cache. So in my opinion this is a very interesting ‘side effect’ of the MAX_MEMORY_PERCENT setting.

REQUEST_MAX_MEMORY_GRANT_PERCENT

This controls the amount of workspace memory granted to the workload group. Workspace memory is required for operations such as hash join, sorts etc. which need active memory to work within. If you exceed this value, here is the typical error message you can expect:

Msg 8657, Level 17, State 1, Line 2
Could not get the memory grant of 1736 KB because it exceeds the maximum configuration limit in workload group 'mygroup' (256) and resource pool 'mypool' (256).  Contact the server administrator to increase the memory usage limit.

Now, there is a small but important point regarding REQUEST_MAX_MEMORY_GRANT_PERCENT. Reproducing text from Books Online: If the query memory requirements exceed the limit that is specified by this parameter, the server does the following:

  • For user-defined workload groups, the server tries to reduce the query degree of parallelism until the memory requirement falls under the limit, or until the degree of parallelism equals 1. If the query memory requirement is still greater than the limit, error 8657 occurs.
  • For internal and default workload groups, the server permits the query to obtain the required memory.

The important thing to note is that the internal and default workload groups will NOT prevent the query from running.

That’s it! I recommend you dig further with some useful reference links:

If you liked this post, please take a minute to rate it and if you can provide some comments, I’d appreciate it very much!