Hi Friends, I was a bit caught up during these days so I could not update my blogs since a long time. Today again it is a weekend and I have some time so I am writing the article on Resource Governor Part II which is overdue since a long time.

In my first post you would have seen what is the advantage of using Resource Governor and how it works. In this post I will provide you with some sample code. The scenario is that you have one SQL Server 2008 installation and it is used by all the people in the organization. Sales people use it for querying historical sales data, senior executives use it for taking out reports and other useful data and at the same time other people use it for data entry, ad-hoc queries etc. Eventually the DBA finds that the server gets into run-away situation during peek hours. So now the DBA decides to allocate bandwidth each of these user groups.

Let's see a demo script how a DBA will do that.

NOTE : This script was written to execute well on my Laptop so please avoid copy + paste and execute on your production server. This script is provided just to help users understand the concept.

Sample Code

We will do some settings that needs to be done only once per installation. It is not same for all types of server so please refer to the Books Online before performing these settings.

--ONE-TIME SETTINGS

 

sp_configure 'show advanced', 1

 

GO

 

RECONFIGURE

 

GO

 

-- Use only 1 CPU (Laptop)

 

sp_configure 'affinity mask', 1

 

GO

 

RECONFIGURE

 

GO

 

-- NOTE : It is a best practice to set min & max when using Resource Governor

 

-- 512 MB is suitable for a Laptop Demo

 

sp_configure 'min server', 512

 

GO

 

sp_configure 'max server', 512

 

GO

 

RECONFIGURE

 

GO

 

-- END of ONE-TIME settings

 

We can find out the information on the Resource Governor by querying the DMVs (Dynamic Management View). These DMVs provide us with the information on the resource pools, workload groups and the classifier function.

/*

 

There will be 2 resource pools and 2 workload groups

 

already existing in the SQL Server when you will run this

 

query. Please don't change any of these.

 

*/

 

--Get the information on existing Workload Groups

 

SELECT * FROM sys.dm_resource_governor_workload_groups

 

--Get the informayion on exissting Resource Pools

 

SELECT * FROM sys.dm_resource_governor_resource_pools

 

--Get the information on existing classifier function (if any)

 

SELECT * FROM sys.dm_resource_governor_configuration

 

GO

 

Now we will create two Resource Pools first.

/*

 

We will create 2 Resource Pools for the Users

 

1) PoolAdhoc for Generic Users

 

2) PoolExec for Company Executives

 

*/

 

-- Create User pools

 

CREATE RESOURCE POOL PoolAdhoc

 

GO

 

CREATE RESOURCE POOL PoolExec

 

GO

 

After creating the Resource Pools we will create the Workload Groups and these Workload Groups will be assigned to respective Resource Pools.

/*

 

We will create 3 Workload Groups

 

1) GrpMarketing for Marketing Department Users

 

2) GrpGeneric for Generic Ad-Hoc query users

 

3) GrpExec for the Top Level Executives

 

..and assign them to respective resource pools

 

1) GrpMarketing to PoolAdhoc

 

2) GrpGeneric to PoolAdhoc

 

3) GrpExec to PoolExec

 

*/

 

CREATE WORKLOAD GROUP GrpMarketing

 

USING PoolAdhoc

 

GO

 

CREATE WORKLOAD GROUP GrpGeneric

 

USING PoolAdhoc

 

GO

 

CREATE WORKLOAD GROUP GrpExec

 

USING PoolExec

 

GO

 

In the next step we will create SQL User Logins.

/*

 

Now we will create 3 user logins for 3 different class

 

of users.

 

1) UserMarketing for Marketing Department

 

2) UserGeneric for Generic Users

 

3) UserExec for Senior Executives

 

*/

 

-- create logins to separate users into different groups

 

CREATE LOGIN UserMarketing WITH PASSWORD = 'UserMarketing1', CHECK_POLICY = OFF

 

CREATE LOGIN UserGeneric WITH PASSWORD = 'UserGeneric1', CHECK_POLICY = OFF

 

CREATE LOGIN UserExec WITH PASSWORD = 'UserExec1', CHECK_POLICY = OFF

 

GO

 

Now when the user logins are created our next task is to create a Classifier Function. Classifier function tells the Resource Governor on how to handle the incoming request. The classifier function has to be created in the Master database. After the creation of the classifier function we have to make the Resource Governor aware of this function.

/*

 

We would create classifier function now.

 

This classifier function has to be created in the

 

MASTER Database. Please make sure to select the

 

Master Database

 

*/

 

USE master

 

GO

 

CREATE FUNCTION RGClassifier()

 

RETURNS SYSNAME WITH SCHEMABINDING

 

BEGIN

 

       DECLARE @val varchar(32)

 

       if  'UserExec' = SUSER_SNAME()

 

              SET @val = 'GrpExec';

 

       else if 'UserGeneric' = SUSER_SNAME()

 

              SET @val = 'GrpGeneric';

 

       else if 'UserMarketing' = SUSER_SNAME()

 

              SET @val = 'GrpGeneric';

 

       return @val;

 

END

 

GO

 

-- Make this function

 

-- known to the Resource Governor

 

ALTER RESOURCE GOVERNOR

 

WITH (CLASSIFIER_FUNCTION = dbo.RGClassifier)

 

GO

 

Now we can set the priority of the Workload Group or the CPU usage by any Resource Pools etc. as demonstrated in the query below.

-- Adjust PoolAdhoc to not consume more than 50% of CPU

 

ALTER RESOURCE POOL PoolAdhoc

 

WITH (MAX_CPU_PERCENT = 50)

 

GO

 

-- Make the changes effective

 

ALTER RESOURCE GOVERNOR RECONFIGURE

 

GO

 

-- Adjust PoolExec to not consume more than 30% of CPU

 

ALTER RESOURCE POOL PoolExec

 

WITH (MAX_CPU_PERCENT = 30)

 

GO

 

-- Make the changes effective

 

ALTER RESOURCE GOVERNOR RECONFIGURE

 

GO

 

-- Alter importance of GrpGeneric

 

ALTER WORKLOAD GROUP GrpGeneric

 

WITH (IMPORTANCE = Low)

 

GO

 

-- make the changes effective

 

ALTER RESOURCE GOVERNOR RECONFIGURE

 

GO

 

-- Alter importance of GrpMarketing

 

ALTER WORKLOAD GROUP GrpMarketing

 

WITH (IMPORTANCE = High)

 

GO

 

-- Make the changes effective

 

ALTER RESOURCE GOVERNOR RECONFIGURE

 

GO

 

That is all in this post from my side. If you have any feedbacks then please feel free to share it with me over email.