Microsoft has introduced an interesting and exciting new feature in SQL Server 2008; it is named the Resource Governor. It allows the user to limit the CPU and memory usage by a specific application or user. It would help in preventing any particular user / application to eat up all the resources of the SQL Server.
Terminologies:
Resource Pool
It is the collection of physical resources of the database management system. It is viewed as a virtual SQL Server instance within the SQL Server as visible by the application. It is defined with minimum and maximum values of resource (memory and CPU) utilization. SQL Server 2008 allows a maximum of 18 user defined pools and two built-in pools. There are following built-in resource pools in SQL Server 2008:
Internal: Used solely by the database engine. This cannot be re-configured by the user.
Default: Used by all those workloads that have not been assigned to any specific resource pool.
Workload Group
To make the management of users assigned to a Resource Pool for database administrator easier, workload groups are introduced. All workloads are assigned to some specific resource pool. A resource pool may have zero or more workload groups assigned to it.
Like built-in resource pools, there are built-in workload groups as well. They are as follows:
Internal: Assigned to the internal resource pool. Used by database engine.
Default: All those sessions that do not classify to any other workload group, are assigned to it.
Classifier function
Now for dividing each user session that would be assigned to any particular workload group, a Classifier function is used. It is a normal user defined function (UDF) defined in SQL Server (scalar valued). Any property may be used to do this job like IP Address, Application Name and Username etc.
Assigning Classifier function to Resource Governor:
ALTER RESOURCE GOVERNOR
WITH (CLASSIFIER_FUNCTION = dbo.My_Classifer_func)
Demo:
Creating Resource Pools:
create resource pool one
go
create resource pool two
go
Create Workload Groups:
create workload group oneWG
using one
go
create workload group twoWG
using two
go
Creating a Classification Function:
Alter function classifier()
returns sysname with schemabinding
begin
declare @retval sysname
if SUSER_NAME()='scott'
set @retval ='oneWG'
else if SUSER_NAME()='reports'
set @retval='twoWG'
return @retval
end
go
Set Resource Governor to use the Classifier Function declared.
alter resource governor with (classifier_function=dbo.classifier)
go
Reconfigure the Resource Governor for changes to take effect.
alter resource governor reconfigure
go
Code that be used to generate CPU Spikes(Do not use in Production).
–generating CPU Spikes to test
set nocount on
declare @i int=0
declare @s nvarchar(500)
while @i<10000000000000
begin
select @s=SUSER_NAME()+DB_NAME()+@@VERSION
set @i+=1
end
Monitor the resource pools using performance monitor.
On Resource Pool properties we can adjust the Min CPU and Max CPU, and also Min Memory and Max memory allocated for that Resource Pool from GUI or TSQL. Monitor the resource pools using performance monitor again after tweaking CPU and Memory limits
Dynamic Management Views (DMVs) for Resource Governor:
There are following DMVs introduced to support resource governor.
1. sys.dm_resource_governor_workload_groups
2. sys.dm_resource_governor_configuration
3. sys.dm_resource_governor_resource_pools
Resource Governor Limitations
There are also some limitations to the Resource Governor as below:
1. It is only limited to the Database engine. But there are some services which are out of the database engine like Analysis, Integration and Reporting services. For these, we still have to rely on other solutions to manage the resource problem i.e. installing separate SQL Server instances.
2. Only a single instance can be managed through this. An organization may have more than a single instance, but must manage each separately.
3. Limited to only two resources i.e. CPU bandwidth and memory management.