Implementing Resource Governor to limit the CPU usage by Backup Compression

HI SQL Folks : As we all know that backup compression is resource intensive operation, to get rid of this problem we can implement Resource Governor (RG) in our environment and RG can take care of job to limit the CPU on it. I’m showing this demo on m\c which is having multiple CPU’s on it. I’m using SQL Server 2008 R2 build 10.50.4266 on win 2008 R2 SP1.

1. If we run the backup compression w\o RG then it consumes 90 % + of total CPU. I ran it and below is the Perfmon output (You need to select two counters in permon name as SQL Server : resource Pool stats and SQL Server : Workload group stats) As you can see in below that a Blue Line (CPU Usage by default group) is consuming most of the CPU.

             RG0

2.  Now I’m going to create and configure Resource Governor by using below scripts :

———————————————–

 Step 1: Create Resource Pool

———————————————–

CREATE RESOURCE POOL pSlow

WITH (MIN_CPU_PERCENT=0,

MAX_CPU_PERCENT= 30,

MIN_MEMORY_PERCENT=0,

MAX_MEMORY_PERCENT=30)

GO

———————————————–

Step 2: Creating Workload Group

———————————————–

CREATE WORKLOAD GROUP gSlow

USING pSlow

—————————————————

Step 3: Creating user defined function which routes the workload to the appropriate workload group.

—————————————————

/*In this example we are checking SUSER_NAME() and making the decision of Workgroup selection.

We can use other functions such as HOST_NAME(), APP_NAME(), IS_MEMBER() etc. */

CREATE FUNCTION f1()

RETURNS SYSNAME WITH SCHEMABINDING

BEGIN

 

DECLARE @val sysname

if ‘UserSlow’ = SUSER_SNAME()

SET @val = ‘gSlow’;

else

SET @val = ‘default’;

return @val;

END

GO

—————————————————

Create logins, assign function and make changes effective

—————————————————

CREATE LOGIN UserSlow WITH PASSWORD=’UserSlowPwd’ , CHECK_POLICY=OFF

GO

ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION = dbo.f1)

GO

ALTER RESOURCE GOVERNOR RECONFIGURE

3. Resource Governor has been setup at server level, Now I need to make a job that will take a backup with compression option :

Before taking a backup of database, we need to add that login as user in peculiar database (for which we’re taking the backup) and need to assign the permission :

Use <db_name>

go

sp_addrolemember  ‘db_backupoperator’, ‘UserSlowPwd’

go

 

Backup database <db_name>

Go

to disk = ‘D:\abc.bak’

with compression

Save this script say as SQLQuery2.sql and make a SQL job and put below command in job to execute this script

sqlcmd -S <Server_Name>-U UserSlow -P UserSlowPwd -i “I:\Perf Data By Gaurav\SQLQuery2.sql”

4. Since I assigned 30 % CPU to this RG i.e. whenever any query running by this peculiar login (UserSlow) then engine will only assign 30 % of single CPU to it.

Beauty of the RG is, if no workload is there on the system then it can maximum utilize a single CPU that is assigned to it (i.e. if 4 CPU’s are there and out of them 1 CPU is assigned to RG then it an use 90 % of that single CPU, it’ll not touch other 3 CPU’s), if workload is there then it will use only 30 % of single CPU that is assigned to it.

To prove this point I ran a backup compression job (now job is using RG that’s having limit on CPU) and created an initial small load on system. As you can in below perfmon values, default load that I created; highlighted in blue line ; using 30 % of CPU but since load is less, my RG is consuming 70 % of total CPU.

RG1

5. Now I increased the load on system by running below script in query analyzer :

set nocount on

declare @i int

declare @s varchar(100)

 set @i = 100000000

 while @i > 0

begin 

select @s = @@version;

set @i = @i – 1;

 end

and as you can see in below reading that RG started to release CPU to other queries. Now RG (highlighted with yellow) is consuming less than 60 % CPU (pre. was using 70 %) and other load started to consuming 45 % CPU (pre. was using 30 %)

RG2

6. To prove a point that RG is using a separate CPU, I fired below query :

select r.session_id, CONVERT(NCHAR(20), wg.name) as group_name, t.scheduler_id,r.status

from sys.dm_exec_requests r join sys.dm_os_tasks t on r.task_address = t.task_address

join sys.dm_resource_governor_workload_groups wg on r.group_id = wg.group_id

where r.session_id > 50

In below output of the query you can see, RG group “gSlow” is using different scheduler (CPU no. 3) and other workload are using CPU 0\1\2.

RG4

Hope it’ll  help someone. Thanks.

Advertisements

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

%d bloggers like this: