11 September, 2010

Resource Governor

Today I’ll demonstrate how to create and control Resource Governor.

But before I do that, i need to say what is Resource Governor.

Well Resource Governor is a new feature in SQL 2008 that lets you control how the resources available to SQL are distributed to applications.

for now on I’ll use RG for Resource Governor.

To do this RG have the following concepts:

Resource Pools: which are configurations that are bound to a amount of resource on SQL server.

And.

Workload Groups: which are groups where applications will be classified and grouped.

and to finish up there’s the function that defines what Workload Group the applications (done for every connection) will belong to.

To better illustrate this you can check this diagram from TechNet.

rg_basic_funct_components(en-us,SQL.105)

taken from: http://technet.microsoft.com/en-us/library/bb934084.aspx

With the Resource Governor you can set how much CPU or Memory a Resource Pool will have, and on the Workload Group you can say how much will the apps in there will request.

It works like this:

You create a Resource Pool that says it will use a minimum of 10% CPU and a maximum of 70% and for memory you say minimum 0% and maximum 50%. Now you create a workload group that says it will use the before mentioned resource pool and that request’s a maximum of 30 seconds of CPU Time.

All that you can do through SSMS 2008, however the tricky part is how you say an application is part of this Group?

There’s something called Classifier Function.

On every connection to SQL there will be an evaluation that says on which group the connection belongs to.

So What is this Classifier Function and how do you create one?

Well its a User Defined Function, a normal function that returns the name of the group.

How do you create one?

Well only through Transact SQL, this is not even visible on SSMS.

So here’s an example of the full transact SQL.

BEGIN TRANSACTION
USE master
-- Create a resource pool that sets:
--MIN_CPU_PERCENT to 10%.
--MAX_CPU_PERCENT to 70%.
--MIN_MEMORY_PERCENT to 0%.
--MAX_MEMORY_PERCENT to 50%.
CREATE RESOURCE POOL SSMS_RESOURCE_POOL
WITH
(MAX_CPU_PERCENT = 70
, MIN_CPU_PERCENT = 10,
MIN_MEMORY_PERCENT = 0
, MAX_MEMORY_PERCENT = 50);
GO
-- Create a workload group to use this pool
-- with its maximum request of CPU Time being 30s
CREATE WORKLOAD GROUP SSMS_WORKLOAD_GROUP
WITH
(REQUEST_MAX_CPU_TIME_SEC = 30)
USING SSMS_RESOURCE_POOL;
GO
-- Create a classification function.
-- test the application name on the application
-- assign a workload group based on the application
-- otherwise redirect to the default group (NULL)

CREATE FUNCTION
dbo.resource_governor_classification_function()
returns sysname
WITH SCHEMABINDING
AS
BEGIN
IF APP_NAME() LIKE
'Microsoft SQL Server Management Studio%'
BEGIN
RETURN 'SSMS_WORKLOAD_GROUP'
END
return NULL
END
GO
ALTER RESOURCE GOVERNOR WITH
(CLASSIFIER_FUNCTION =
dbo.resource_governor_classification_function);
COMMIT TRANSACTION
ALTER RESOURCE GOVERNOR RECONFIGURE;


Hope that this clarifies it a bit, and after testing it yourselves you get to understand it better.