DBA Sensation

January 4, 2012

How to configure resource governor in sql 2008 to seperate the classified work load

Filed under: 3. MS SQL Server — Tags: , , , — zhefeng @ 5:30 pm

On our server some big apps always eat up all resource which cause other apps get hang as well.
Try to seperate the traffic between big apps and normal apps on a shared sql instance by implementing resource governor.

Here is the plan for pools. pBigApp takes maxium 60% resource and other apps by default using default pool.

Pool_name MIN% MAX% Calculated_Effective_Max% Calculated_Shared% Comment
internal 0 100 100 0 not applicable to internal pool
default 0 100 100 100 Calculated_Effective_Max%= min(MAX%,100-sum(min%)), calculated_shared%= Calculated_Effective_Max% – Min%
pBigapp 0 60 60 60

Configuration:
1. make sure the resource governor is enabled
select is_enabled from sys.resource_governor_configuration
–if it returns “0”, then you need to enable it
ALTER RESOURCE GOVERNOR RECONFIGURE;

2. Issue a CREATE RESOURCE POOL statement to create a resource pool
USE master;
— Create a resource pool “pBigApp” that sets the MAX_CPU_PERCENT to 60%.
CREATE RESOURCE POOL pBigApp WITH (MAX_CPU_PERCENT = 60);
GO

3. Create a workload group to use new pool “pBigApp”
CREATE WORKLOAD GROUP gASTEC USING pBigApp;
GO

4. Create a classifier function that maps the workload group created in the preceding step to the user of the low-priority login
–Note that any request that does not get classified goes into the ‘Default’ group.
USE master;
CREATE FUNCTION dbo.rgclassifier_MAX_CPU() RETURNS sysname
WITH SCHEMABINDING
AS
BEGIN
DECLARE @workload_group_name AS sysname
IF (SUSER_NAME() = ‘ASTEC’)
SET @workload_group_name = ‘gASTEC’
RETURN @workload_group_name
END;
GO

–another function example with application name classfied
CREATE FUNCTION dbo.rgclassifier_v1() RETURNS sysname
WITH SCHEMABINDING
AS
BEGIN
DECLARE @grp_name sysname
IF (SUSER_NAME() = ‘sa’)
SET @grp_name = ‘GroupAdmin’
IF (APP_NAME() LIKE ‘%MANAGEMENT STUDIO%’)
OR (APP_NAME() LIKE ‘%QUERY ANALYZER%’)
SET @grp_name = ‘GroupAdhoc’
IF (APP_NAME() LIKE ‘%REPORT SERVER%’)
SET @grp_name = ‘GroupReports’
RETURN @grp_name
END;
GO

5. Register the classifier function with Resource Governor.
ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION= dbo.rgclassifier_MAX_CPU);

6. Verify the classification of specific sessions
log in as the user that you specified in your classifier function, and verify the session classification by issuing the following SELECT statement
USE master;
SELECT sess.session_id, sess.login_name, sess.group_id, grps.name
FROM sys.dm_exec_sessions AS sess
JOIN sys.dm_resource_governor_workload_groups AS grps
ON sess.group_id = grps.group_id
WHERE session_id > 50;
GO

Useful scripts:
1. what workload group and resource pool in Resource Governor was assigned to each session
SELECT session_id as ‘Session ID’,
[host_name] as ‘Host Name’,
[program_name] as ‘Program Name’,
nt_user_name as ‘User Name’,
SDRGWG.[Name] as ‘Group Assigned’,
DRGRP.[name] as ‘Pool Assigned’
FROM sys.dm_exec_sessions SDES
INNER JOIN sys.dm_resource_governor_workload_groups SDRGWG
ON SDES.group_id = SDRGWG.group_id
INNER JOIN sys.dm_resource_governor_resource_pools DRGRP
ON SDRGWG.pool_id = DRGRP.pool_id

2. Assigns all new sessions to the default workload group by removing any existing classifier function from the Resource Governor configuration.
ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION = NULL);
GO
ALTER RESOURCE GOVERNOR RECONFIGURE;

3.Example of store the classifier function in the master database.
USE master;
GO
SET ANSI_NULLS ON;
GO
SET QUOTED_IDENTIFIER ON;
GO
CREATE FUNCTION dbo.rgclassifier_v1() RETURNS sysname
WITH SCHEMABINDING
AS
BEGIN
— Declare the variable to hold the value returned in sysname.
DECLARE @grp_name AS sysname
— If the user login is ‘sa’, map the connection to the groupAdmin
— workload group.
IF (SUSER_NAME() = ‘sa’)
SET @grp_name = ‘groupAdmin’
— Use application information to map the connection to the groupAdhoc
— workload group.
ELSE IF (APP_NAME() LIKE ‘%MANAGEMENT STUDIO%’)
OR (APP_NAME() LIKE ‘%QUERY ANALYZER%’)
SET @grp_name = ‘groupAdhoc’
— If the application is for reporting, map the connection to
— the groupReports workload group.
ELSE IF (APP_NAME() LIKE ‘%REPORT SERVER%’)
SET @grp_name = ‘groupReports’
— If the connection does not map to any of the previous groups,
— put the connection into the default workload group.
ELSE
SET @grp_name = ‘default’
RETURN @grp_name
END
GO
— Register the classifier user-defined function and update the
— the in-memory configuration.
ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION=dbo.rgclassifier_v1);
GO
ALTER RESOURCE GOVERNOR RECONFIGURE;
GO

4. system views and dm views
sys.resource_governor_configuration: Returns the stored Resource Governor state.
sys.resource_governor_resource_pools: Returns the stored resource pool configuration. Each row of the view determines the configuration of a pool.
sys.resource_governor_workload_groups: Returns the stored workload group configuration.

sys.dm_resource_governor_workload_groups: Returns workload group statistics and the current in-memory configuration of the workload group.
sys.dm_resource_governor_resource_pools: Returns information about the current resource pool state, the current configuration of resource pools, and resource pool statistics.
sys.dm_resource_governor_configuration: Returns a row that contains the current in-memory configuration state for Resource Governor.

Reference:
1. Managing SQL Server Workloads with Resource Governor
http://msdn.microsoft.com/en-us/library/bb933866.aspx

2. Part 1: Anatomy of SQL Server 2008 Resource Governor CPU Demo
http://blogs.technet.com/b/sqlos/archive/2007/12/14/part-1-anatomy-of-sql-server-2008-resource-governor-cpu-demo.aspx

3. Part 2: Resource Governor CPU Demo on multiple CPUs
http://blogs.technet.com/b/sqlos/archive/2008/01/18/part-2-resource-governor-cpu-demo-on-multiple-cpus.aspx

4. How to: Use Resource Governor to Limit CPU Usage by Backup Compression (Transact-SQL)
http://msdn.microsoft.com/en-us/library/cc280384.aspx

5. Resource Governor DDL and System Views
http://msdn.microsoft.com/en-us/library/bb895339.aspx

Create a free website or blog at WordPress.com.