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

Advertisements

8 Comments »

  1. Good way of telling, and good piece of writing
    to take facts regarding my presentation subject,
    which i am going to present in institution of higher
    education.

    Comment by grand theft auto san andreas ps2 cheats — June 13, 2013 @ 3:00 am

  2. This maybe a weird question. But is it okay if
    I ask which web hosting firm you’re having? I’d like to know for the future to have quick loading speeds
    for my personal blog as well, thanks a lot in advance!

    Comment by Sarah — July 20, 2013 @ 5:20 pm

  3. No se bastante muy bien la agudza quue has intentado elaborar.

    Aunque me interesa tu web.

    Comment by gta v cheats — September 24, 2014 @ 3:01 am

  4. Me presento plenamente de acuerdo con el total de los Info que has posteado, a pesar de que no en absolutamente todos.
    Un internet web flipante.

    Comment by juegos de ps3 para descargar gratis para pc — October 3, 2014 @ 1:03 pm

  5. Impresionante. Me agrada de que manera has planteado el
    topico. ¿Me puedes presentar maas posteo del argumento?
    Me encuentro extremadamente interesado en el topico.

    Muchas Gracias.

    Comment by donde comprar juegos para xbox 360 baratos — October 3, 2014 @ 8:07 pm

  6. Me enloquece mucho los aspectos que redactas aca, a pesar de que el diseño y estilo del blog
    me parece poco atractivo. De todas maneras , Gran escrito.

    Comment by trucos para grand theft auto san andreas pc trucoteca — October 4, 2014 @ 9:28 am

  7. Es eel momento perfecto para realizar ciertos planteamientos paara el tiempo futuro y ser
    una persona feliz. He releido este opinion y si pudiera me gustaria recomendarte sobree unos fantasticas temas o sugerencias.
    Puede ser tu puedas escribir los siguinetes articulos relacionados
    con este post. Desearia descubrir mas cosas en relacion a este tema

    Comment by como conseguir dinero en gta 5 — October 8, 2014 @ 4:09 am

  8.  Un sorprendente juego no obstante recurrente

    Comment by trucos de videojuegos — April 1, 2017 @ 9:34 am


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Create a free website or blog at WordPress.com.

%d bloggers like this: