Resource Governor was introduced in SQL Server 2008 in order to allow us have better control over our system resource consumption. With Resource Governor, we can divide our users to groups, when each group gets a certain percent of our resources. That way, we can reduce the risk of a crazy query consuming all of the server resources and hurting other processes.

The problem is that up and including SQL Server 2012, we could limit the amount of CPU and memory resources a certain group can consume, but couldn’t do it for IO. With SQL Server 2014, we can finally do that.

Let’s see how it’s done on SQL Server 2014 CTP1:

Configuration

First, let’s use a code section by Tibot Karazi to create a database, a table with data, and a user to be throttled (thanks Tibor):

/********************************
Create the buffer pool extension
*******************************/EXEC sp_configure 'max server memory', 200
RECONFIGURE
GO
/***************************************
Create Resource Governor needed objects
***************************************/use master
GO
IF DB_ID('ResourceGovernorDemo') IS NOT NULL DROP DATABASE ResourceGovernorDemo
GO
CREATE DATABASE ResourceGovernorDemo
ON PRIMARY
(NAME = N'ResourceGovernorDemo', FILENAME = N'C:\Demos\ResourceGovernorDemo.mdf' , SIZE = 250MB , FILEGROWTH = 50MB )
LOG ON
( NAME = N'ResourceGovernorDemo_log', FILENAME = N'C:\Demos\ResourceGovernorDemo_log.ldf' , SIZE = 30MB , FILEGROWTH = 50MB)
ALTER DATABASE ResourceGovernorDemo SET RECOVERY SIMPLE
GO
use ResourceGovernorDemo
GO
--Create tables and populate with data, approx 250 MB each
CREATE TABLE DemoTable(col1 INT IDENTITY PRIMARY KEY CLUSTERED, col2 INT, col3 CHAR(100))
GO
INSERT INTO DemoTable (col2, col3)
SELECT TOP (90000) ROW_NUMBER() OVER(ORDER BY a.OBJECT_ID), 'hello'
FROM sys.columns AS a CROSS JOIN sys.columns AS b
GO 25
--Create login
IF SUSER_ID('BadUser') IS NOT NULL DROP LOGIN BadUser
GO
CREATE LOGIN BadUser WITH PASSWORD = 'Aloha1'
--Create user
USE ResourceGovernorDemo
CREATE USER BadUser
GO
GRANT SELECT ON DemoTable TO BadUser
GO

Now comes the fun part. We will create a resource pool and disallow it from performing more than 50 IO operations per second (per volume):

--Create group and pool
CREATE RESOURCE POOL LimitedIOPool WITH (MAX_IOPS_PER_VOLUME = 50);
CREATE WORKLOAD GROUP LimitedIOGroup USING LimitedIOPool;

And to finish the configuration, we’ll add a resource governor classifier function:

USE MASTER
IF OBJECT_ID ('dbo.ResourceGovernorClassifier') IS NOT NULL DROP FUNCTION dbo.ResourceGovernorClassifier;
GO
CREATE FUNCTION dbo.ResourceGovernorClassifier ()
RETURNS SYSNAME WITH SCHEMABINDING
AS
BEGIN
 DECLARE @GroupName SYSNAME;
 IF SUSER_SNAME() = 'BadUser'
 SET @GroupName = 'LimitedIOGroup';
 ELSE SET @GroupName = 'Default';
 RETURN @GroupName;
END;
GO
ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION = dbo.ResourceGovernorClassifier);
GO
ALTER RESOURCE GOVERNOR RECONFIGURE;
GO

Execution Tests

Now, from the same window, let’s execute a query:

USE ResourceGovernorDemo
GO
--Clear Cache
CHECKPOINT
DBCC DROPCLEANBUFFERS
GO
USE ResourceGovernorDemo
GO
IF OBJECT_ID ('tempdb..#temp') is not null
drop table #temp
GO
SELECT * INTO #temp
FROM DemoTable
GO

On Performance Monitor, the Disk Reads/sec and Disk Writes/sec counters look like this:

Now, let’s open another windows and connect with the “BadUser” login. When executing the same query from the new window, the same Performance Monitor counters now look like this:

During Read Activity

During Write Activity

Obviously, this query took much longer to execute.

When I presented this to my local user group, the guys noticed that reads are indeed throttled, but writes can pass the limit (50 in our case). It can make sense, since a user session doesn’t directly write to disk but to memory buffers that are later written to disk on checkpoint or by the lazy writer. It’s interesting to see if writes are indeed not throttled, and if so, whether or not that’s the reason.