Share via

SQL Server 2008 - Resource Governor - Part II

  • Article

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.

ResourceGovernor.sql