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.

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

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.

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 %)

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