[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 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:
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
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