2

It is clear that Resource Governor monitors how much work each group is doing. sys.dm_resource_governor_workload_groups makes this utterly obvious.

Suppose that:

  • I don't yet trust my ability to classify my users into groups
  • I want to begin by grouping my users without Resource Governor limiting their resources
  • I want to monitor how much resources each group users

How would I achieve this?

My hunch is that this will work, but I don't trust myself with the Resource Governor yet. Did I miss anything?

CREATE WORKLOAD GROUP Group
USING [default];
GO

CREATE FUNCTION dbo.Classifier() <Do Whatever>

ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION = dbo.Classifier);
GO

ALTER RESOURCE GOVERNOR RECONFIGURE;
GO

1 Answer 1

2

Yes, I believe you have the needed minimum configuration set.

I don't yet trust my ability to classify my users into groups

This is an example function with a few possible items you might find useful to classify your requests into Workload Groups:

CREATE FUNCTION [dbo].[RGClassifierFunction]() RETURNS SYSNAME
WITH SCHEMABINDING
AS
BEGIN
    
    RETURN (SELECT CASE
                    WHEN (APP_NAME() LIKE N'Microsoft SQL Server Management Studio%') THEN N'WorkloadGroup1'
                    WHEN (IS_MEMBER('DOMAIN\DomainGroup') = 1) THEN N'WorkloadGroup2'
                    WHEN (HOST_NAME() LIKE N'ComputerName') THEN N'WorkloadGroup3'
                    WHEN (SUSER_NAME() LIKE N'userName') THEN N'WorkloadGroup4'
                    ELSE N'default' 
            END);

END

One of the Resource governor best practices is to keep that function as simple as possible:

The classifier function extends login processing time. Avoid complex logic and long-running or resource-intensive queries in the classifier, particularly if queries use large tables. An overly complex function can cause login delays or connection timeouts.


I want to monitor how much resources each group uses

I find it very useful to monitor some counters available on the built-in Windows system tool Perfmon, like comparing the total use of CPU against the use for each Workload Group with these counters:

Perfmon Counters

Perfmon Graph


Useful material: Tutorial: Resource governor configuration examples and best practices

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.