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)
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.
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?
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..
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!
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.