Simon Worth’s SQL Server Weblog

SQL Server and its many complexities

Resource Governor – Part 03 – More on Classifier Functions

with one comment

image_thumb14

In my second post on Resource Governor for SQL Server 2008, I briefly went over some key concepts to understand how the Resource Governor works.  We reviewed Resource Pools, Workload Groups, and looked at the Classifier Function for RG.  In this post we will dig deeper into classifier functions, how to write them, and how they assign connection requests to resource pools.

Classifier Functions are subject to timeout limitations set by connection properties – so if your function is long running or poorly performing you could have timeout issues with users trying to connect and may experience orphaned connections on the server.   These timeouts could cause the server to be unusable and the only way to fix it at that point is to use the DAC to login and fix issues.  You can also use single user mode to fix the issue, you won’t really be able to do anything about performance issues.

Classifier functions categorize what group to place connections into.  There are some system functions available to your classifier function in order to figure out where connections should be placed.  The following functions can aid in figuring out what rules need to be applied to the connection.  The descriptions for the following system functions are taken directly from BOL.

APP_NAME() Returns the application name for the current session if set by the application
HOST_NAME() Returns the workstation name
SUSER_SNAME() Returns the login name associated with a security identification number (SID)
IS_SVROLEMEMBER() Indicates whether a SQL Server login is a member of the specified fixed server role
IS_MEMBER() Indicates whether the current user is a member of the specified Microsoft Windows group or Microsoft SQL Server database role

 

You can use lots of different logic to figure out where the connection should be grouped.  And along the lines of different types of logic you may choose to implement a table driven solution to figure out where connections should be grouped.  With a table driven solution you can still use the system functions available as well as other custom defined business rules to apply rules to the login – but a table driven solution offers a little more flexibility when it comes to the logic used.

For example, you may have many users logging into the system using SQL Server authentication – as well others using Windows Authentication. You can create a table to hold these logins – both SQL Server logins and windows logins.

We’ll create a couple different tables to accomplish this solution.  The tables are very basic and not really the best design – we’ll expand on this later, but for now lets just rough something out.

First, we create a Resource Governor Groups table.  This table will hold the various groups we want to assign connections to.  Once that is complete we’ll add some data to work with.

 

CREATE TABLE dbo.RGGroups(
       GroupKey INT IDENTITY(1,1) NOT NULL
      ,GroupName VARCHAR(50) NOT NULL
      ,CONSTRAINT [PK_RGGroups] PRIMARY KEY CLUSTERED ([GroupKey]))

 

Now we’ll create a table to hold users
CREATE TABLE [dbo].[RGUsers](
    [UserKey] [int] IDENTITY(1,1) NOT NULL,
    [UserName] [varchar](100) NOT NULL,
    [ResourceGov] [bit] NOT NULL,
    [GroupKey] [int] NOT NULL,
 CONSTRAINT [PK_RGUsers_UserKey] PRIMARY KEY CLUSTERED
([UserKey] ASC) ON [PRIMARY])
 

 

Now we have a table that will hold various workload groups as well as a table to hold users (both SQL Logins and Windows accounts).

The next step is to create some data so we can create a classifier function.  This SQL adds 10 different Workload Groups to the RGGroups table.

WITH NumbersTable (Number)
AS (SELECT 1 UNION ALL
    SELECT 2 UNION ALL
    SELECT 3 UNION ALL
    SELECT 4 UNION ALL
    SELECT 5 UNION ALL
    SELECT 6 UNION ALL
    SELECT 7 UNION ALL
    SELECT 8 UNION ALL
    SELECT 9 UNION ALL
    SELECT 10)
 
INSERT INTO dbo.RGUsers (UserName, GroupKey)
SELECT
       REPLICATE(CASE rg.GroupKey WHEN 1 THEN 'a'
                                  WHEN 2 THEN 'b'
                                  WHEN 3 THEN 'c'
                                  WHEN 4 THEN 'd'
                                  WHEN 5 THEN 'e'
                                  WHEN 6 THEN 'f'
                                  WHEN 7 THEN 'g'
                                  WHEN 8 THEN 'h'
                                  WHEN 9 THEN 'i'
                                  WHEN 10 THEN 'j'
                                  ELSE 'z' END, nt.Number) UserName
      ,rg.GroupKey
  FROM
       NumbersTable nt
 CROSS JOIN
       dbo.RGGroups rg
 

 

OK, so now we have 100 different logins assigned to 10 different workload groups.  Now we can start to write the classifier function to handle these connection requests.  NOTE – we haven’t enabled RG yet, so this classifier function won’t really be doing anything right now.  It will just be returning a Workload Group – and once RG is turned on, this will be passed to it for resource allocation.

CREATE FUNCTION fnTimeClassifier()
       RETURNS SYSNAME WITH SCHEMABINDING
AS
BEGIN
     DECLARE @UserGroupName SYSNAME
      SELECT
             @UserGroupName = GroupName
        FROM
             dbo.RGGroups rg
       INNER JOIN
             dbo.RGUsers ru
          ON ru.GroupKey = rg.GroupKey
       WHERE
             ru.UserName = SUSER_SNAME()
     IF(@UserGroupName IS NOT NULL)
     BEGIN
          RETURN @UserGroupName
     END
     RETURN N'Unknown Group'
END
GO

 

 

This is about as basic as you can get using table driven solution.  We’ll expand on this in a later post.  But this gives you an idea how the function is created.  As you can see the last statement in the script returns “Unknown Group” to the resource governor, and as I said in the second post – if the group cannot be classified then RG assigns the connection to the DEFAULT resource pool.

Up next, we’ll look at creating resource pools and workload groups.  Then we can start applying what we’ve seen so far to the Resource Governor using actual logins and business requirements.

Advertisements

Written by Simon Worth

January 23, 2009 at 4:54 pm

One Response

Subscribe to comments with RSS.

  1. Hello
    I need a complete example described step by step through the creation
    and use of
    SQLServer 2008 Resource Governor to MAXDOP = 1 in one or more database.
    Example of the database name: XYZ.

    Has something to send as described above?

    Thanks.

    Best regards
    José Júlio Duarte

    Jose Julio Duarte

    May 4, 2011 at 8:03 am


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: