Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
    
You may also be interested in this: sqlblog.com/blogs/sqlos_team/archive/2012/07/30/… –  Aaron Bertrand Jul 31 '12 at 0:11
add comment

1 Answer

up vote 9 down vote accepted

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.

share|improve this answer
    
what does this mean (Othen than special cases like buffer pool)? –  SQL Learner Jul 13 '12 at 15:14
1  
It means Resource Governor can't prevent you from pulling 10GB into the buffer pool, since the buffer pool isn't just for you, and the presumption is that if you need 10GB in buffer, someone else may need it as well. Surely you don't want Resource Governor to force a user to read 10GB directly from disk, just because it exceeds the artificial limit you implemented with RG? –  Aaron Bertrand Jul 13 '12 at 15:15
    
Aaron Bertrand, I could be mistaken but you are the one who wrote white paper related to Resource Governor in SQL 2008? –  SQL Learner Jul 13 '12 at 15:19
    
That was me, yes. Along with Boris Baryshnikov, who owned the feature. –  Aaron Bertrand Jul 13 '12 at 15:20
    
Thank you Sir!! Hats Off... –  SQL Learner Jul 13 '12 at 15:21
show 4 more comments

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.