Denali – Day 29: Resource Governor Enhancements (Database Engine)
Introduction to Resource Governor:
Resource Governor is a feature introduced in sql server 2008. It requires Enterprise and developer edition.
As name implies It controls the resources allocation for users, resources can be controlled are “Memory and CPU” no IO can be controlled.
We have observed most of the time our sql server goes down or perform very slow due to some user has run a very heavy query due to which top management team is not able to work, now with Resource governor we can control the resource to be allocated to the each individual/group in such a way that priority users works uninterruptable.
Enable/Disable Resource Governor:
SSMS ->Management -> Resource Governor ->Enable/Disable
or using T-sql
ALTER RESOURCE GOVERNOR RECONFIGURE
GO
ALTER RESOURCE GOVERNOR DISABLE;
GO
Steps to configure Resource Governor:
- Use Master Database for T-sql — to make it server wide
- Create Resource pool(s) give properties .
- Create Workload groups using Resource Pool
- Create Classified Function Return Sysname with schemabinding
- Alter Resource governor with Classified function
- Alter Resource governor with reconfigure — same as sp_configure requires reconfigured to effect the status.
Add resource governor counters.
–resource pool stats
Login with user to validate the same.
SSMS -> Management -> Resource Pool
Can change the configuration for the Resource pool we have created, just go to properties.
This is not a hard limit. Also limits should be valid like min should be below 100 or less.
With resource governor we can prioritize one user to have more resource and lower for other.
What’s New in Denali:
- Now you could have more Resource Pool it was 20 Resources earlier now we could have 64 Resource pool available but it’s for x64 system. This is good for Enterprise large system.
-
If you wanted to allocated specific set of Affinity scheduler we could able to do it now for Resource pool
e.g. Alter resource pool xxx with (Affinity scheduler =(4-8)).
-
Now you can setup a hard cap on CPU limit, so that even if your system is not busy enough than also you can restrict the resource pool to use only specific upper CAP on CPU limit which will ignore MAX_CPU_PERCENT
e.g. Alter resource pool xxx with (CAP_CPU_PERCENT = 25) – this will restrict the resource pool to use only 25% of CPU.
- More Memory configuration – now you could configure almost all the memory, earlier it was limited to query grants.
Ref:
http://msdn.microsoft.com/en-us/library/hh710024.aspx
http://mssqldude.wordpress.com/2012/03/07/resource-governor-updates-in-sql-server-2012/
http://dbfriend.blogspot.com/2012/04/resource-governor-in-sql-server-2012.html
Vinay Thakur 