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
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’
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.