With the release of SQL Server 2014, Microsoft introduced an enhanced version of the Resource Governor that now covers I/O. This means that you can now throttle your I/O intensive processes, giving you more control over your machine resources. In this article, we will explore how to set up the Resource Governor to limit the resources for those large I/O operations.

What New Settings are Available to Control I/O?

SQL Server 2014 introduced two new resource pool options that allow you to control the I/O threshold setting. These settings are MIN_IOPS_PER_VOLUME and MAX_IOPS_PER_VOLUME. Each of these parameters can be set to a value between zero (0) and 2,147,483,647. If the parameter MIN_IOPS_PER_VOLUME is set to zero(0), it means that there is no minimum threshold for I/Os. If the MAX_IOPS_PER_VOLUME is set to zero(0), this means that the upper bounds of I/Os per second is unlimited. If these parameters are set to a number, then that is the minimum or maximum number of I/O operations per second that the Resource Governor will allow a disk volume to have.

On a busy I/O instance where you want to make sure your process gets all the I/O it needs to perform well, you can use the MIN_IOPS_PER_VOLUME setting. By setting this parameter to the number of the minimum I/Os your process needs, SQL Server will make sure your process is allowed that number of I/Os per second. If you have other processes that you don’t want to use all the I/O bandwidth, then you can throttle those processes by setting the maximum number of I/Os using the MAX_IOPS_PER_VOLUME setting.

Configuring Resource Governor to Restrict I/O Usage

In order to allow SQL Server 2014 to restrict I/O, you need to set up four things, assuming that Resource Governor is already enabled. The first thing you need to set up is a Resource Pool that sets the maximum and/or minimum I/O limits. The second thing you need to do is associate a Resource Group with the Resource Pool. The third thing you need to do is write some code in the Resource Governor Classifier function to associate a session to the Resource Group you set up for limiting I/O. The last thing you need to do is reconfigure Resource Governor to use the new settings.

Here is an example code that establishes a Resource Pool that restricts I/O:

USE master;
GO
CREATE RESOURCE POOL RestrictedIOPool WITH (MAX_IOPS_PER_VOLUME = 30, MIN_IOPS_PER_VOLUME = 1);
GO

In this code, a Resource Pool named “RestrictedIOPool” is created with a maximum I/Os per second (IOPS) of 30 and a minimum IOPS of 1.

To associate a Resource Group to the Resource Pool, you can run the following code:

USE master;
GO
CREATE WORKLOAD GROUP RestrictedIOGroup USING RestrictedIOPool;
GO

In this code, a Resource Group named “RestrictedIOGroup” is created and associated with the Resource Pool named “RestrictedIOPool”.

Next, you need to develop a classifier function that will identify when to use the RestrictedIOGroup. Here is an example of the function code:

USE master;
GO
CREATE FUNCTION dbo.RestrictedIO() RETURNS SYSNAME WITH SCHEMABINDING AS
BEGIN
    DECLARE @GroupName SYSNAME
    IF SUSER_NAME() = 'RestrictMyIO'
    BEGIN
        SET @GroupName = 'RestrictedIOGroup'
    END
    ELSE
    BEGIN
        SET @GroupName = 'default'
    END
    RETURN @GroupName;
END
GO

In this code, the function checks if the SUSER_NAME() associated with a session is “RestrictMyIO”. If it is, then the @GroupName variable is set to the name of the Resource Group that is restricting I/O, which in this example is “RestrictedIOGroup”. This group name is returned from the function when it exits.

At this point, Resource Governor is all set up to control I/O for any sessions where the session user is named “RestrictMyIO”. But there is one last thing to do. You need to alter Resource Governor to use the new classifier function and then reconfigure it to use all the new settings. This can be done by running the following code:

USE master;
GO
ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION = dbo.RestrictedIO);
ALTER RESOURCE GOVERNOR RECONFIGURE;

Now all that is left is to test our new Resource Group to see if it throttles READ IO for the login “RestrictMyIO”.

Setting up Testing Environment

In order to test the new Resource Pool and Group, you first need to establish a database and a new login for testing purposes. To accomplish this, you can run the following code:

USE master;
GO
-- Create Test DB
CREATE DATABASE MyIOTestDB ON PRIMARY (NAME = N'MyIOTestDB', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\MyIOTestDB.mdf', SIZE = 100MB, FILEGROWTH = 100MB)
LOG ON (NAME = N'MyIOTestDB_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\MyIOTestDB_log.ldf', SIZE = 20MB, FILEGROWTH = 20MB);
ALTER DATABASE MyIOTestDB SET RECOVERY SIMPLE;
GO

-- Setup RestrictMyIO login
CREATE LOGIN RestrictMyIO WITH PASSWORD = N'RestrictMyIO', DEFAULT_DATABASE = MyIOTestDB, CHECK_EXPIRATION = OFF, CHECK_POLICY = OFF;
GO

USE MyIOTestDB;
GO
CREATE USER RestrictMyIO FOR LOGIN RestrictMyIO;
GO
ALTER ROLE db_owner ADD MEMBER RestrictMyIO;
GO

In this code, a database named “MyIOTestDB” is created, and a login named “RestrictMyIO” is created with access to the “MyIOTestDB” database. Lastly, DBO access is given to this new login so it has all the rights it might need while testing the new Resource Group.

The last thing you need for testing is some test data in the new database. To create that test data, you can use the following code:

USE MyIOTestDB;
GO
DROP TABLE IOTest;
GO
CREATE TABLE IOTest (
    ID INT IDENTITY CONSTRAINT [PK_IOTest_ID] PRIMARY KEY CLUSTERED (ID ASC),
    AnotherNumber INT,
    AString CHAR(50)
);
GO

SET NOCOUNT ON;
INSERT INTO IOTest (AnotherNumber, AString)
SELECT TOP (50000) ROW_NUMBER() OVER (ORDER BY a.OBJECT_ID), REPLICATE('A', 50)
FROM sys.columns AS a
CROSS JOIN sys.columns AS b;
GO

In this code, a table named “IOTest” is created with 5,000,000 rows of data.

Setting up Performance Monitor

To monitor the test and verify that Resource Governor is throttling I/O, you can use the Windows Performance Monitor tool. To set up Performance Monitor, you can add two different counters to a Performance Monitoring session. The two counters you want to monitor are “SQL Server: Resource Pool Stats” counters for Disk Read IO/sec for the RestrictedIOPool and the default Resource Governor pool.

By reviewing the Performance Monitor graph, you can see the I/O load on the different Resource Pool statistics. This will help you verify how SQL Server is controlling I/O to your disk subsystem while running the IO test using different logins.

Conclusion

With the release of SQL Server 2014, you now have new settings available to control the minimum and maximum I/O allowed for a Resource Pool. These settings can help you restrict those big I/O consumers from hogging all the I/O resources. If you need to throttle I/O, then installing SQL Server 2014 and utilizing the Resource Governor can greatly benefit your system.

By following the steps outlined in this article, you can effectively set up and use the Resource Governor to throttle I/O and better manage your machine resources.

See all articles by Greg Larsen