Resource Governor – Getting started

One of the databases I’m responsible for is a fairly large OLTP system.  For the majority of the time, it runs great.  Unfortunately, there’s a select group of users who insist on using it as an OLAP system, running very large reports or queries during business hours and consuming a lot of system resources in the process.  There have been a few occasions where these reports have brought this very robust server to a crawl.  (We’ve gone over several options for offloading this reporting, but nothing was “real-time” enough for their use, so here we are.)

This year we’ll be upgrading this database to SQL Server 2008 and I’m very excited to make use of the Resource Governor feature.  I figure if I can’t get them off the system I can at least prevent them from killing the system, right?  But how do I know what resource limit(s) to set?  Pick some arbitrary number?  Probably not a good idea.  I want to control them, not strangle them.  So what I did was something recommended here, in Scenario 1.  I tweaked the code a little for my own use.

BEGIN TRAN;
-- Create 3 workload groups based on the nature of their workload.
-- One handles ad hoc requests, the second handles reports, and the
-- third handles admin requests. These groups all use the default
-- settings for workload groups.
-- These workloads are divided into groups that cover ad hoc queries,
-- reports, and administration jobs.
CREATE WORKLOAD GROUP GroupAdhoc;
CREATE WORKLOAD GROUP GroupReports;
CREATE WORKLOAD GROUP GroupAdmin;
GO
COMMIT TRAN;
GO
-- Create a classification function.
-- Note that any request that does not get classified goes into
-- the 'default' group.
CREATE FUNCTION dbo.rgclassifier_v1() RETURNS sysname
WITH SCHEMABINDING
AS
BEGIN
 DECLARE @grp_name sysname
 IF (APP_NAME() LIKE '%MANAGEMENT STUDIO%')
 OR (APP_NAME() LIKE '%QUERY ANALYZER%')
 SET @grp_name = 'GroupAdhoc'
 IF (SUSER_NAME() = 'sa')
 SET @grp_name = 'GroupAdmin'
 IF (APP_NAME() LIKE '%REPORT SERVER%' OR APP_NAME() LIKE '%COGNOS%' OR APP_NAME() LIKE '%Net SqlClient Data Provider%')
 SET @grp_name = 'GroupReports'
 RETURN @grp_name
END;
GO
-- Register the classifier function with Resource Governor
ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION= dbo.rgclassifier_v1);
GO
-- Start Resource Governor
ALTER RESOURCE GOVERNOR RECONFIGURE;
GO

Notice I’m not setting any resource limits here.  The purpose is to categorize sessions into the appropriate group and monitor the normal resource usage.  I can see this usage by querying sys.dm_resource_governor_workload_groups.

USE master;
SELECT * FROM [sys].[dm_resource_governor_workload_groups]
GO

In particular, I’m going to be keeping an eye on the max_request_cpu_time_ms and max_request_grant_memory_kb columns.  Once I get an idea of what they’re currently using, I’ll be better able to decide what limits to set.  At that point I can set resource restrictions within Resource Governor or I can choose to have an alert triggered if a limit is exceeded.  I know – very exciting stuff!

(But I’m even more excited about the prospect of AlwaysOn mirroring in SQL Server Denali for offloading these reports.  I mean, what’s more “real-time” than “real-time”??)

Also recommended: