SQL Server 2008 Resource Governor

I’ve yet to look at the resource governor because, for the most part, I haven’t had a reason to deploy it to production. The Resource Governor is definitely a worthwhile venture, if for no other reasons than 1) Oracle has had something similar forever, and 2) the old query timeout option (for every query run on the server) was cheesy for the most part.

So I ran the following code – a typical deployment – to create a pool, a group. and a classifier function to get this thing rolling.

USE MASTER;
GO
CREATE RESOURCE POOL RGPool_SQLServerApps
WITH 
    (MAX_MEMORY_PERCENT = 1);
GO
alter WORKLOAD GROUP RGGroup_SQLServerApps
    WITH (IMPORTANCE = low) USING RGPool_SQLServerApps;
GO
CREATE FUNCTION dbo.rgClassifier()
RETURNS SYSNAME
WITH SCHEMABINDING
BEGIN
    DECLARE @GroupName SYSNAME = '';
    IF (APP_NAME() LIKE '%MANAGEMENT STUDIO%')
      OR (APP_NAME() LIKE '%QUERY ANALYZER%')
        SET @GroupName =  'RGGroup_SQLServerApps';
    
    RETURN @GroupName;

END;
GO
ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION = dbo.rgClassifier);
GO
ALTER RESOURCE GOVERNOR RECONFIGURE;
GO

SELECT pool_id as Pool
    , name as Name
    , min_cpu_percent as MinCPU 
    , max_cpu_percent as MaxCPU
    , min_memory_percent as MinRAM
    , max_memory_percent as MaxRAM    
FROM sys.resource_governor_resource_pools;
GO

To test this, I opened six SSMS applications, and for one I opened somewhere in the neighborhood of 50 query windows. Here’s what I get from task manager:

Now, I’m not a math genius, though I took through (trig based) calculus III, I find it difficult to come up with how 57MB is 1% of 2048MB, the amount of RAM that I have on my workstation. The others are happily consuming 12MB RAM, or .5% of the total amount of RAM on the machine. What gives? Right off, the Resource Governor didn’t work in this situation, and more importantly, brings up a point that probably wasn’t even addressed: the fact that it doesn’t account for multiple instances of the Management Studio opened on a given number of workstations!

I hope to experiment further with this; if you have anything to share on its use please drop me a line.

Lee

--------------------------------------

What we need is something to govern devenv.exe. Every time I look it’s sucked down 250MB RAM!