SQL Server 2008 – Resource Governor – Part II


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

Comments (0)