Freigeben über

SQL Server 2008 Resource Governor

  • Artikel

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.

Technorati Tags: SQL Server 2008,Resource Governor,SQL

Comments

  • Anonymous
    November 26, 2007
    I got back from vacation ( more here if you want to read about where we go and what we do every other

  • Anonymous
    April 21, 2009
    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).

  • Anonymous
    April 23, 2009
    Aaron thanks for the clarification

  • Anonymous
    April 28, 2009
    What database is the function created in?

  • Anonymous
    April 29, 2009
    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

  • Anonymous
    June 02, 2009
    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!

  • Anonymous
    June 04, 2009
    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