Use of resource governor to limit CPU usage for backup compression

Resource Governor

As many of you have gone through my previous article on compress backup
Since backup compression takes higher cpu it affect other processes

Here Resource Governor comes as a useful option , where we can configure use cpu for the selected
session.

Resource governor is a new feature in SQL Server 2008 which we can use to manage cpu usage/memory usage by different  operations.

Resource governor can be helpful in many cases we will look into it one by one

Today we will look into use of resource governor in backup compression


First we will create a user who is supposed to take backup named bkp_operator.
Then  we will have to enable resource governor.

Alter resource governor reconfigure

or in Management Studio

right click on resource governor and enable it




For resource governor we will have to create a resource pool

Right click on Resource pools select create new pool
create a new pool named p_40
set value of MAXIMUM  CPU% to 40



Or using TSQL

CREATE resource pool p_40

   WITH

(max_cpu_percent = 40);

create a workgroup g_40 that use this pool p_40

CREATE WORKLOAD GROUP g_40

USING p_40

Now create a classifier function that will use this group

CREATE FUNCTION dbo.Cpu_compression_for_backup()

returns SYSNAME

WITH schemabinding

AS

    BEGIN

        DECLARE @worklod_group AS SYSNAME

        IF ( Suser_name() = 'bkp_operator' )

            SET @worklod_group = 'g_40'

        RETURN @worklod_group

    END;

go

ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION= dbo.Cpu_compression_for_backup);

-- Start Resource Governor

ALTER RESOURCE GOVERNOR RECONFIGURE;

Now when the user bkp_operator will backup database resource governor will assign only 40% cpu to this user .

Below are the query to system tables for resource governor

SELECT *

FROM   sys.resource_governor_resource_pools

SELECT *

FROM   sys.resource_governor_workload_groups

SELECT *

FROM   sys.resource_governor_configuration

SELECT Object_schema_name(classifier_function_id) AS [schema],

       Object_name(classifier_function_id)        AS [function]

FROM   sys.dm_resource_governor_configuration