SQL Server 2012 Resource Governor Enhancement

sql serversql-server-2008sql-server-2012

In SQL Server 2012 what specific enhacement was made regarding controlling memory usage by using Resource Governor. Up until SQL Server 2008 R2 resource governor was only able to control memory related to query memory grant.

I looked in SQL Server 2012 BOL however it does not specifically states anything clearly.

Best Answer

This is a good question, because there is a lot of misleading information out there.

The changes to what memory Resource Governor can manage in SQL Server 2012 roughly coincide with the changes to what memory the Max Server Memory setting can manage. Prior to SQL Server 2012, this was just buffer pool / single-page allocations. In SQL Server 2012 this is extended to multi-page allocations. I lifted this diagram from Memory Manager surface area changes in SQL Server 2012:

enter image description here

And while the statement is a little over-promising IMHO, the SQL OS team also stated in New SQLOS features in SQL Server 2012:

Resource Governor governs all SQL memory consumption (other than special cases like buffer pool)

When I first started doing research on the changes to this feature, the indication I had was that it could manage all memory allocations except buffer pool and column store cache. Prior to SQL Server 2012, Resource Governor only had control over query grant memory.

There are of course other important enhancements to Resource Governor in SQL Server 2012, that I thought I'd mention for other readers who get here because of your question title. You can now:

  • have 64 resource pools instead of the previous limit of 20
  • use real CPU capping (even with no contention) - the previous model relied on concurrency before implementing a cap
  • implement scheduler / NUMA node affinity (e.g. tie a resource pool to a specific NUMA node)

Also note there are some differences with how memory manager changes have been implemented in 32-bit vs. 64-bit. Some relevant info here.

Related Question