Freigeben über

Mini-Lab - Part 2 - Backup Compression with Resource Governor

  • Artikel

[Prior Post in Series]

In Part 1 of this 2 part Mini-Lab series we looked at a new feature that was introduced in SQL Server 2008 called Backup Compression.  We were able compress the backups of our test database ( 2008FeaturesDB ) to roughly 10% of the size of the uncompressed full backup while also cutting our overall backup time in half.  A side effect that we found while doing this was that the CPU took a much bigger hit during the compressed backup vs. the uncompressed backup.  In Part 2 of this Mini-Lab we're going to utilize another new feature that was introduced in SQL Server 2008 called Resource Governor.

Resource Governor

Resource Governor allows to you manage SQL Server workload and resources by creating resource pools and specifying limits on resource consumption (CPU and Memory) to incoming requests.  In Part 1 of our Mini-Lab we found that Backup Compression added a significant increase in CPU activity (over 40%). 

What if this load is too much? 

What if there are too many other processes running at the same time and we need to make sure that the backup doesn't utilize more than 25% of the CPU?

Here is where Resource Governor shines and we'll walk through how you would set this up (based on the database and information from Part 1 of the Mini-Lab)

The first thing we're going to do is enabled the Resource Governor:

-- ENABLE RESOURCE GOVERNOR
USE master
GO
ALTER RESOURCE GOVERNOR RECONFIGURE
GO

OK now that we've enabled Resource Governor we need to create our Resource Pool.  You can think of Resource Pool almost like running an instance of SQL Server within your instance.  Here is where you can specify the min and max CPU and Memory percentage that this resource pool can consume.

-- CREATE RESOURCE POOL
CREATE RESOURCE POOL [LowCPUPool]
WITH( min_cpu_percent=0,
max_cpu_percent=25,
min_memory_percent=0,
max_memory_percent=100)
GO

Let's go ahead and create our "LowCPUPoolUser" which will be the user that runs our queries that we only want to be able to consume 25% of the CPU.

CREATE LOGIN [LowCPUPoolUser] WITH PASSWORD=N'p@ssw0rd',
DEFAULT_DATABASE=[master],
DEFAULT_LANGUAGE=[us_english]
GO
USE [2008FeaturesDB]
GO
CREATE USER [LowCPUPoolUser] FOR LOGIN [LowCPUPoolUser]
GO
USE [2008FeaturesDB]
GO
EXEC sp_addrolemember N'db_backupoperator', N'LowCPUPoolUser'
GO
USE [2008FeaturesDB]
GO
EXEC sp_addrolemember N'db_datareader', N'LowCPUPoolUser'
GO

Next we need to create our Workload group.  A workload group is like a container for session requests that are passed through from the Classifier Function.

-- CREATE WORKLOAD GROUP
CREATE WORKLOAD GROUP [LowCPUGroup]
USING [LowCPUPool]; 

We mentioned the "Classifier Function" above.  This function is what determines what Workload Group a session gets assigned to.  Any session that is not assigned in the classifer session gets put into the "default" workload group.

-- CREATE CLASSIFIER FUNCTION

CREATE FUNCTION dbo.fn_WorkloadClassifier() RETURNS sysname
WITH SCHEMABINDING
AS
BEGIN
DECLARE @workload_group_name AS sysname
IF (SUSER_NAME() = 'LowCPUPoolUser')
SET @workload_group_name = 'LowCPUGroup'
RETURN @workload_group_name
END

Now that we have our classifer function we need to tell the Resource Governor to use this function for incoming sessions and run the reconfigure statement to apply all of our changes:

 -- REGISTER THE CLASSIFIER FUNCTION

ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION = [dbo].[fn_WorkloadClassifier])

-- RECONFIGURE THE RESOURCE GOVERNOR TO APPLY THE CHANGES

ALTER RESOURCE GOVERNOR RECONFIGURE

OK we are ready to go. 

NOTE: The server I'm using for my testing has a quad core.  In order to highlight Resource Governor at work I set my affinity mask to 1.  What this means is that when I'm showing CPU Usage % and my affinity mask is 1 that 25% is my 1 CPU pinned at 100%

To best show Resource Governor at work we're going to do the following:

  • Open a Management Studio Query Window using the LowCPUPoolUser
  • Open a Management Studio Query Window using a Different User
  • Open up Performance Monitor and add the following counters:
    • SQLServer:Resource Pool Stats:CPU Usage % (default and LowCPUPool)
  • Run a large CPU intensive query using the Different User (this should spike at 25% which is 1 CPU out of the quadcore at 100%)
  • Run the Full Database Backup command with compression using the LowCPUPoolUser

If you're interested the CPU intensive query I'm using is:

USE [2008FeaturesDB]
GO
DECLARE @LoopCounter int
SELECT @LoopCounter = 1000000
DECLARE @CustomerID int

WHILE @LoopCounter <> 0
BEGIN
SELECT @CustomerID = CustomerID
FROM dbo.Customers
WHERE FirstName like '%whatever%'

SELECT @LoopCounter = @LoopCounter -1
END

And if you don't have the backup script handly from Part 1:

-- BACKUP DATABASE WITH COMPRESSION
BACKUP DATABASE [2008FeaturesDB]
TO DISK = N' <<BackupPath>> \2008FeaturesDB_Compressed.bak'
WITH NOFORMAT, INIT, COMPRESSION, NAME = N'2008FeaturesDB-Full Database Backup w/Compression',
SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO

OK so we're first going to run the CPU intensive query using the other user (NOT LowCPUPoolUser)

As expected we see our 1 CPU pinned at 100% which is represented by 25% (on a quadcore)

Now we're going to kick off the backup query using our LowCPUPoolUser

You can see that the first query we were running with the other user is put into the "default" workgroup and the LowCPUPoolUser is in the LowCPUPool workgroup and is only consuming approximately 6.25% (or 25% of 1 CPU).

Now obviously your hardware will probably be different then my hardware so your chart might not look EXACTLY like the one shown above but you should see something very similar (assuming you're using a quadcore and set your affinity mask to 1)

In Part 1 we had a chart comparing a full database backup without compression and with compression.  We're going to add another row to this chart and that is With Compression and Resource Governor

  DATABASE NAME DATABASE SIZE BACKUP SIZE DURATION
WITHOUT COMPRESSION 2008FeaturesDB 12.28 GB 2.54 GB 76 seconds
WITH COMPRESSION 2008FeaturesDB 12.28 GB 0.22 GB 34 seconds
WITH COMPRESSION AND RESOURCE GOVERNOR 2008FeaturesDB 12.28 GB 0.22 GB 53 seconds

You can see that because we limited our CPU usage to only 25% that our duration increased a bit (but still faster than an uncompressed backup) and our compressed backup size remained the same.

Backup Compression and Resource Governor are just two out of the many new features that were introduced in SQL Server 2008.  Hopefully this 2 part mini-lab series gave you some motivation to investigate these features further for your environment.

For More Information on Resource Governor please check out Managing SQL Server Workloads with Resource Governor on MSDN
_____________________________________________________________________________________________

 Follow Tier1OnSQL on Twitter

Comments

  • Anonymous
    September 19, 2014
    By Rachael Rettner

    belstaff gangster leather jacket
    Very obese children and teens may be at risk for m Belstaff Hemley Vent ultiple sclerosis, a new study suggests.

    In the study, very obese girls (those who had a body mass index (BMI) of 35
  • Anonymous
    September 19, 2014
    The first American woman belstaff brad leather jacket sale to go into space, Sally Ride, died Monday after a 17-month battle against pancr belstaff hero prcie eatic cancer,belstaff brad leather jacket salehttp://www.belstaffsonline.com/belstaff-brad-black
  • Anonymous
    March 01, 2015
    The comment has been removed
  • Anonymous
    March 26, 2015
    The comment has been removed
  • Anonymous
    May 01, 2015
    The comment has been removed