Using the SQL Server Resource Governor to Manage I/O

In large enterprises and multi-tenant environments controlling the resources that are used by a given workload or application can be an important factor in meeting your SLAs. If one workload consumes too large a chuck of your system’s resources then all of the other workloads running on that host could be adversely affected. That’s where the SQL Server Resource Governor comes in.  The Resource Governor was first introduced in the SQL Server 2008 Enterprise edition. It was designed to make SQL Server response times more predictable. The original SQL Server 2008 implementation of the Resource Governor enabled you to limit the CPU and memory resources that a given workload could consume. This ability can be particularly important in controlling the resource consumption of ad-hoc queries and end user reporting tools. End users like the ability to create and run their own reports using tools like Report Builder, Power BI and other reporting tools. However, they can and often do create reports that use terrible queries and can be system resource hogs — dragging down the performance of the entire system when they run.  The Resource Governor can limit the effect these type of workloads can have on the system.

Watch a video about this here:

 

The original implementation of the Resource Governor was limited to capping CPU and memory utilization but didn’t do anything about I/O utilization. SQL Server 2014 Enterprise edition extended the capabilities of the Resource Governor to include the ability to limit minimum and maximum IOPS per volume.  IOPs are one of the biggest factors that can impact other applications. The ability to limit IOPs is particularly important in database consolidation scenarios where you have higher database density and still require predictable application performance.

Resource Governor Components

The Resource Governor is composed of three main components: resource pools, workload groups, and a classifier.

• Resource Pools – Resource pools represents the physical resources of an instance of the Database Engine. By default, the Resource Governor uses two pools: the Internal Pool and the Default Pool. The Internal Pool is used by SQL Server itself. All user activity takes place in the Default Pool. To limit the resources used by an application you would create your own user-defined resource pools.
• Workload Groups – Workload groups represent the jobs running on the system. Each workload group describes application requests that are similar. Like the Resource Governor, by default there are two workload groups: the Internal workload group and the Default workload group. All internal SQL Server system activity is grouped into the Internal workload group. All user activity is grouped into the Default workload group. To limit the resources used by one or more applications you would create your own user-defined workload groups.
• Classifier Function – The Classifier Function evaluates incoming session requests and routes them to the appropriate workload group based on the characteristics of the session that you supply to the function.

Limiting I/O with the Resource Governor

When a session is started, the Resource Governor classifier assigns the session to a specific workload group. That session must run according to the policies assigned to the workload group and the resources defined in the associated resource pool. To limit IOPs SQL Server 2014’s Resource Governor provides two resource pool options: MIN_IOPS_PER_VOLUME and MAX_IOPS_PER_VOLUME. To use the Resource Governor to limit IOPs you need to create a resource pool, workload group and a classifier function that will map the incoming connections to the corresponding resource pool. The following T-SQL code shows how you can create a resource pool, workload group and lassifier function that limits the IOPs for the specified database.

First you use the CREATE REOURSE POOL command to create the resource pool. The following example shows the creation of MyResourcePool which will limit IOPS to 500.

USE master;

GO



CREATE RESOURCE POOL MyResourcePool WITH

(

       MAX_IOPS_PER_VOLUME = 500,

       MIN_IOPS_PER_VOLUME = 1

);

GO

After the resource pool has been created you can use the CREATE WORKLOAD GROUP command like you can see in the following listing. Here the workload group is named MyWorkLoadGroup and it is associated with MyResourcePool.

 

CREATE WORKLOAD GROUP MyWorkloadGroup

USING MyResourcePool;

GO

Next you need to create the classifier function that will be used to route the appropriate workloads to the correct workload group. In the listing below you can see how the MyClassifierFunction is used to route all requests from the AdventureWorks database to MyWorkLoadGroup. All other requests will go the Default workload group.

 

CREATE FUNCTION dbo.MyClassifierFunction()

RETURNS SYSNAME WITH SCHEMABINDING

AS

BEGIN

IF ORIGINAL_DB_NAME() = 'AdventureWorks'

BEGIN

RETURN 'MyWorkloadGroup'

END



RETURN 'default'

END;

GO

Finally, you need to use the ALTER RESOURCE GOVERNOR command to associate the classifier function with the Resource Governor.

ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION = dbo.MyClassifierFunction);

ALTER RESOURCE GOVERNOR RECONFIGURE;

The Resource Governor can be a powerful tool that can help you achieve more predict performance for all of your workload. In this article you’ve seen how to use the new SQL Server 2014 capabilities to limit IOPs but you can also use the Resource Governor to limit CPU and memory usage as well.