The Essential Guide to SQL Server 2014: The Resource Governor

In this Essential Guide to SQL Server 2014 series I’ve been covering some of the most important new features in SQL Server 2014. In the last column I discussed SQL Server 2014’s Buffer Pool Extensions which enable you to expand SQL Server’s Buffer Pool using SSD storage. In this column I’m going to explore the enhancements to another important SQL Server 2014 performance feature: the Resource Governor.

The Resource governor was first introduced back in SQL Server 2008 and its purpose is to make application response time more predictable – especially in multi-tenant environments. The original implementation of the Resource Governor enabled you to limit the CPU and memory that a database request could consume thereby preventing resource intensive queries from consuming too many system resources. This can be particularly important as tools like Reporting Services, Report Builder and Power BI enable end users to create and run their own reports which might be less than optimal from a system resource standpoint.

SQL Server 2014 extends the Resource Governor with the ability to specify minimum and maximum IOPS per volume in addition to controlling CPU and memory. This can be useful in hosting and other database consolidation scenarios where you need to achieve higher database density and more predictable application performance. If one of the applications has a very IO intensive workload that workload can potentially overload the storage subsystem impacting other applications. IO demands can impact other workloads just as much or more as memory and CPU demands.

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

SQL Server 2014’s Resource Governor adds two new resource pool options that allow you to control the I/O usage: MIN_IOPS_PER_VOLUME and MAX_IOPS_PER_VOLUME. To take advantage of these new settings you would need to create a resource pool, workload group and a classifier function that will map the incoming connections to the corresponding resource pool. For example, the following T-SQL code shows how you can create a resource pool, workload group and classifier function that limits the IO for the specified database to 50 IOPS.

USE master;
GO
CREATE RESOURCE POOL MyDBPool WITH
(
       MAX_IOPS_PER_VOLUME = 50,
       MIN_IOPS_PER_VOLUME = 1
);
GO

CREATE WORKLOAD GROUP MyDBGroup
USING MyDBPool;
GO

CREATE FUNCTION dbo.MyDBFunction()
RETURNS SYSNAME WITH SCHEMABINDING
AS
BEGIN
IF ORIGINAL_DB_NAME() = 'MyDB'
 BEGIN
 RETURN 'MyDBGroup'
 END

 RETURN 'default'
END;
GO

ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION = dbo.MyDBFunction);
ALTER RESOURCE GOVERNOR RECONFIGURE;

You can find more information about SQL Server 2014’s updated Resource Governor at MSDN’s Resource Governor and the SQL Server Blog.