Resource Governor OverviewEach time SQL Server creates a new session, Resource Governor runs a classifier function to assign it to a workload group based upon characteristics defined by the administrator (e.g. name of the application creating the session). The sessions in that workload group are then granted access to a specific shared resource pool that limits the workload group’s use of system resources.
Creating a Resource PoolWhen you install SQL Server, you’ll find that it comes with two predefined resource pools:
- The internal resource pool is used for SQL Server’s own administrative tasks. This resource pool has unrestricted access to resources and the administrator may not modify this setting, as doing so may jeopardize the stability of SQL Server.
- The default resource pool is for SQL Server sessions not assigned to a workload group. It has no maximum CPU or memory constraints, but the administrator may modify the allocations to this pool.
CREATE RESOURCE POOL resource_pool_namewhere you supply the resource pool name as well as the minimum and maximum commitment of system resources. The sum of minimum percentages for all resource pools across the server cannot exceed 100 percent.
WITH ( [ MIN_CPU_PERCENT = value ] )
[ [ , ] MAX_CPU_PERCENT = value ]
[ [ , ] MIN_MEMORY_PERCENT = value ]
[ [ , ] MAX_MEMORY_PERCENT = value ] )
Creating a Workload GroupSQL Server also has two predefined workload groups: internal and default. These two workload groups have access to the resource pools of the same name, as described in the previous section. You may create your own workload group using the CREATE WORKLOAD GROUP statement. You may assign the workload group an importance rating of low, medium, or high, which provides Resource Governor with a priority to use when allocating resources with competing demands. You may specify a resource pool for each workload group with the USING clause. For example, you may create a new workload group called PayrollWorkload that has access to a resource pool named PayrollPool with the following SQL command:
CREATE WORKLOAD GROUP PayrollWorkload
WITH (IMPORTANCE = MEDIUM)
Creating a Classifier FunctionUser-defined classifier functions help Resource Governor assign new sessions to the appropriate workload group based upon attributes of the connection. These functions should be created in the master database and must return the name of a workload group. If the classifier function returns anything other than the name of a valid workload group, the session will be assigned to the default group. You may only have one active classifier function at any time.
For example, you might create a classifier function that monitors for the application name “Payroll App” and assigns those connections to the PayrollWorkload group while all other sessions flow through to the default pool. This can be done with the following SQL statement:
CREATE FUNCTION dbo.PayrollClassifier()
DECLARE @wkldgroup SYSNAME
IF (APP_NAME() = ‘Payroll App’)
SET @wkldgroup = ‘PayrollWorkload’
Activating Resource GovernorOnce you’ve created the workload group, resource pool and classifier function, you can turn Resource Governor on with two simple SQL statements. The first sets the name of the classifier function you wish to use (remember, you can only have one active!). Here’s the command:
ALTER RESOURCE GOVERNORYou then activate Resource Governor with:
WITH (CLASSIFIER_FUNCTION = dbo.PayrollClassifier)
ALTER RESOURCE GOVERNOR RECONFIGURE
Deactivating Resource GovernorIf you wish to later disable Resource Governor, simply issue the command:
ALTER RESOURCE GOVERNOR DISABLE