Insufficient data from Andrew Fryer

The place where I page to when my brain is full up of stuff about the Microsoft platform

SQL Server 2008 Resource Governor

SQL Server 2008 Resource Governor

  • Comments 7
  • Likes

The feature of SQL Server 2008 that seems to get  the most attention from DBA's is the Resource Governor.  It basically does what it says on the tin; for example you may want to reserve a portion of CPU or other resource for a user, process etc.

At the top level Resource Governor has Resource Pools, and there is always  default resource pool

Below this you create Workload groups:

CREATE WORKLOAD GROUP groupAdhoc
CREATE WORKLOAD GROUP groupReports
CREATE WORKLOAD GROUP groupAdmin

These workload groups will be belong to the default resource pool, and for this introduction I will keep it simple, by leaving it like that. It is then a matter of assigning whatever you want to the those groups by using a function like this:

CREATE FUNCTION rgclassifier_v1() RETURNS SYSNAME
WITH SCHEMABINDING
AS
BEGIN
    DECLARE @grp_name AS SYSNAME
      IF (SUSER_NAME() = 'sa')
          SET @grp_name = 'groupAdmin'
      IF (APP_NAME() LIKE '%MANAGEMENT STUDIO%')
          OR (APP_NAME() LIKE '%QUERY ANALYZER%')
          SET @grp_name = 'groupAdhoc'
      IF (APP_NAME() LIKE '%REPORT SERVER%')
          SET @grp_name = 'groupReports'
    RETURN @grp_name
END

Notice that you can use any rule you like to create an association with a workload group e.g. users or the application.

This function is then needs to be applied to the resource governor like this:

ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION= dbo.rgclassifier_v1) 

You are now ready to configure which workload group gets which resources with this syntax:

ALTER WORKLOAD GROUP groupAdhoc
WITH (REQUEST_MAX_CPU_TIME_SEC = 30)

or

WITH (MAX_CPU_PERCENT = 50)

Finally the changes need to be applied to the resource governor process running in memory

ALTER RESOURCE GOVERNOR RECONFIGURE

Changes can be applied to the Resource Governor at will and take effect immediately.  The function can be changed to move objects in to different workload groups as required and all of this will affect processes already running.

To try this you will need CTP of SQL server 2008 which is available here.

Comments
  • I got back from vacation ( more here if you want to read about where we go and what we do every other

  • I know this post is dated but I just want to clarify a few salient points.

    You cannot re-classify (new workload group) a session that is already classified.  You also cannot change the classifier function in flight (you need to dis-associate it from the resource governor first).  Finally, not all changes take effect immediately (e.g. memory changes will not take effect until the next memory grant request).

  • Aaron thanks for the clarification

  • What database is the function created in?

  • Liz

    My aplogies you need to create in the master database!

    Normally something I only do by accident becuase I haven't set my conneciton properly watch my webcast on it here..

    http://blogs.technet.com/andrew/archive/2008/02/06/sql-server-2008-resource-governor-in-action.aspx

    Andrew

  • Very nice article!  Thank you!

    I do have a qustion, though, that I've researched without success so far.

    Are we able to route to Workload Groups on the basis of Application Roles?  In other words, can Resource Manager manage on the basis of Application Roles?

    Many thanks for any help you can provide on this!

  • Timothy,

    I'm afraid not. All you can use is what's in the connection string.  sp_setapprole is run post login so all resource governor will know about is the login used by the applicatio itself.

    Andrew

Your comment has been posted.   Close
Thank you, your comment requires moderation so it may take a while to appear.   Close
Leave a Comment