Resource governor – Making VM’s a safer place to be

Hey,

Apologies for the continued absence of posting, work is a harsh mistress… Anyway, something I wanted to share with you as part of my ongoing virtualization learnings (AKA Hiccups).

When you have limited resources you may find yourself needing to get deeper with how you dole out the resources to your SQL instance. Setting Max memory correctly doesn’t mean a single user/query won’t eat every available byte. Limiting the instance to 4 vCPU’s doesn’t mean running DBCC checks won’t leave your system crying. In a lot of cases purchasing licences to allocate 8 vCPU’s (or the bribes to convince your Sys admins to allocate that many) is cost prohibitive, so you have to work with what you’ve got.

Enter the resource governor. Obviously this can be used on any system, not just VM’s, however this is where I’ve found it to be a very useful ally.

The resource governor was introduced in SQL 2008, so unfortunately if you are on a prior version, you’re out of luck with this one. To summarize what it does, it provides the ability to limit memory and CPU within the instance, on a per user basis, without impacting the overall resources available to the instance. So if you have a report which hammers your database, crippling everything else, you can assign the report user only a slice of the overall resources to protect the rest of the operations. In this example I have created a maintenance user to run my CHECKDB commands, and have limited it to use only 2 of the 4 available vCPU’s. Here is the link to the Microsoft guide on Resource Governor, which is far more detailed, but below is a little example to get you started.

So there are 3 key steps to setting up resource governor (apart from turning it on!).

Ok, quickly, how to turn it on.

ALTER RESOURCE GOVERNOR RECONFIGURE;
GO

Next!

Step 1 – Create your Resource Pool – http://msdn.microsoft.com/en-us/library/hh510189.aspx


The resource pool gives you 4 options. MIN/MAX CPU and MIN/MAX Memory. Both of these values are percentage based. Steps 1 and 2 can be carried out through Management Studio, but I find the scripting a little easier personally. So, a script to create the Maintenance resource pool:

CREATE RESOURCE POOL [Maintenance] WITH(min_cpu_percent=0,
max_cpu_percent=50,
min_memory_percent=0,
max_memory_percent=50)

GO

So now you have your pool (master resource management level for lack of a better term), now it’s time to allow people to swim in it. Workload groups can be assigned different priorities, different portions of the already restricted resources etc. This is where we will create the Maintenance workload group.

CREATE WORKLOAD GROUP [Maintenance] WITH(group_max_requests=0,
importance=Medium,
request_max_cpu_time_sec=0,
request_max_memory_grant_percent=25,
request_memory_grant_timeout_sec=0,
max_dop=2) USING [Maintenance]
GO

The options here (in order of appearance above) are maximum amount of requests users in this group can make (0 being unrestricted), their importance (think windows priority levels), max amount of CPU time, max amount of memory, max amount of time to wait for a memory grant, other options. In this case other options only allows a max degree of parallelism of 2. This means that even though there are 4 cores in the machine, it is only allowed to use 2 to execute the query.

Step 3 – Create your classifier function – http://msdn.microsoft.com/en-us/library/hh510208.aspx

Unfortunately this one doesn’t have a GUI option to set it up, so here’s a script :

Create FUNCTION [dbo].[MaintenanceClassifier]() RETURNS sysname
WITH SCHEMABINDING
AS
BEGIN
    DECLARE @grp_name sysname
          IF (SUSER_NAME() = ‘Maintenance’)
          SET @grp_name = ‘Maintenance’
    RETURN @grp_name
END;

GO

What this does is check when a user logs on, if it has a user name of “Maintenance”, then it should be assigned to the Maintenance workload group. Simples!

Step 3b


Ok so I’m cheating a bit, there is technically a 4th step. You have to tell Resource Governor to use your new Classifier Function. So here you go:

ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION=dbo.MaintenanceClassifier)

ALTER RESOURCE GOVERNOR RECONFIGURE
GO
Now finally, you want to see if your user is using the workload group properly. The following query will show you all sessions, and the workload groups they are using
SELECT s.group_id, CAST(g.name as nvarchar(20)), s.session_id, s.login_time, CAST(s.host_name as nvarchar(20)), CAST(s.program_name AS nvarchar(20))
          FROM sys.dm_exec_sessions s
     INNER JOIN sys.dm_resource_governor_workload_groups g
          ON g.group_id = s.group_id
ORDER BY g.name
Give it a try and see how you can apply it to your systems. Resource Governor isn’t trumpeted enough in my opinion and is an under rated feature of SQL 2008.
Here’s a link to the full Microsoft example of how to set up and test a classifier function in case you want to go a bit deeper.
http://msdn.microsoft.com/en-us/library/hh510208.aspx
I’m still hopeful I’ll get back blogging regularly soon, but as always any questions/feedback please don’t hesitate to contact me.
Stephen