SQL Server Resource Governor

SQL Server’s Resource Governor feature, first available in SQL Server 2008, provides the ability to limit the resources consumed by SQL Server database connections. This functionality permits database administrators to regulate the CPU usage and memory consumption of various workloads.

Resource Governor Overview

Each 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 Pool

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

Obviously, if you want to have any control over SQL Server resources, you’ll need to create one or more user-defined resource pools to limit session consumption. You may do this with the following SQL statement:

CREATE RESOURCE POOL resource_pool_name

WITH ( [ MIN_CPU_PERCENT = value ] )

             [ [ , ] MAX_CPU_PERCENT = value ]

             [ [ , ] MIN_MEMORY_PERCENT = value ]

             [ [ , ] MAX_MEMORY_PERCENT = value ] )

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

Creating a Workload Group

SQL 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)

USING PayrollPool

Creating a Classifier Function

User-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()

RETURNS SYSNAME

WITH SCHEMABINDING

AS

BEGIN

   DECLARE @wkldgroup SYSNAME

   IF (APP_NAME() = ‘Payroll App’)

      SET @wkldgroup = ‘PayrollWorkload’

   ELSE

      RETURN NULL

   RETURN @wkldgroup

END

Activating Resource Governor

Once 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 GOVERNOR

WITH (CLASSIFIER_FUNCTION = dbo.PayrollClassifier)

You then activate Resource Governor with:

ALTER RESOURCE GOVERNOR RECONFIGURE

Deactivating Resource Governor

If you wish to later disable Resource Governor, simply issue the command:

ALTER RESOURCE GOVERNOR DISABLE