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.
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.
sp_configure 'show advanced', 1
-- Use only 1 CPU (Laptop)
sp_configure 'affinity mask', 1
-- 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
sp_configure 'max server', 512
-- 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
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
CREATE RESOURCE POOL PoolExec
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
CREATE WORKLOAD GROUP GrpGeneric
CREATE WORKLOAD GROUP GrpExec
In the next step we will create SQL User Logins.
Now we will create 3 user logins for 3 different class
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
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
CREATE FUNCTION RGClassifier()
RETURNS SYSNAME WITH SCHEMABINDING
DECLARE @val varchar(32)
if 'UserExec' = SUSER_SNAME()
SET @val = 'GrpExec';
else if 'UserGeneric' = SUSER_SNAME()
SET @val = 'GrpGeneric';
else if 'UserMarketing' = SUSER_SNAME()
-- Make this function
-- known to the Resource Governor
ALTER RESOURCE GOVERNOR
WITH (CLASSIFIER_FUNCTION = dbo.RGClassifier)
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)
-- Make the changes effective
ALTER RESOURCE GOVERNOR RECONFIGURE
-- Adjust PoolExec to not consume more than 30% of CPU
ALTER RESOURCE POOL PoolExec
WITH (MAX_CPU_PERCENT = 30)
-- Alter importance of GrpGeneric
ALTER WORKLOAD GROUP GrpGeneric
WITH (IMPORTANCE = Low)
-- make the changes effective
-- Alter importance of GrpMarketing
ALTER WORKLOAD GROUP GrpMarketing
WITH (IMPORTANCE = High)
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.