With the on-boarding of the resource governor in SQL Server with SQL Server 2008 we were able to start throttling applications and DBA processes that consumed large amounts of CPU and Memory. This was a great improvement that allowed DBAs to better manage machine resources. With the introduction of SQL Server 2014 the Microsoft team has enhanced the Resource Governor to now cover I/O. With this new version you are now able to throttle your I/O intensive processes, should you need to. In this article I will explore how to setup Resource Governor to limit the resources for those large I/O operations.
What New Settings are Available to Control I/O?
With the introduction of SQL Server 2014, Microsoft introduced two new resource pool options that allow you to control the I/O threshold setting. These two 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 resource governor will allow a disk volume to have.
On a busy IO instance where you want to make sure your process gets all the IO it needs to perform well you can use the MIN_IOPS_PER_VOLUME setting. By setting this parameter to number of the minimum IOs your process needs, SQL Server will make sure your process is allowed that number of IOs 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 IOs by using the MAX_IOPS_PER_VOLUME setting.
Let me show you how to use these parameters to control the amount of I/O per second a given resource pool can use by volume.
Configuring Resource Governor to Restrict I/O Usage
In order to allow SQL Server 2014 to restrict I/O you need to setup 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 setup for limiting I/O. The last thing you need to do is reconfigure Resource Governor to use the new settings.
The code below 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
Here I have created a Resource Pool named “RestrictedIOPool” that sets the maximum I/Os per second (IOPS) to 30 and the minimum IOPS to 1.
To associate a Resource Group to the Resource Pool I created above I will run this code:
USE master; GO CREATE WORKLOAD GROUP RestrictedIOGroup USING RestrictedIOPool; GO
In this code I created a Resource Group named “RestrictedIOGroup” and associated it with my Resource Pool named “RestrictedIOPool”.
Next I need to develop a classifier function that will identify when to use my RestrictedIOGroup. The following function code does that:
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 I check to see if the SUSER_NAME() associated with a sessions is “RestrictMyIO”. If it is then I set the @GroupName variable to the name of the Resource Group that is restricting I/O, which in my example is “RestrictedIOGroup”. This group name is returned from this 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. I need to alter Resource Governor to use my new classifier function and then reconfigure it to use all my 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 login “RestrictMyIO”.
Setting up Testing Environment
In order to test my new Resource Pool and Group I first need to establish a database and my new login for testing purposes. To accomplish this I 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
This code first creates a database named “MyTestIODB”. Next I create a login name “RestrictMyIO” that has access to the “MyTestIODB” database. Lastly I gave this new login DBO access so it could have all the rights it might need while I test my new Resource Group.
The last thing I need for testing is some test data in my new database. To create that test data I will 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 100
This code creates a table name “IOTest” that has 5,000,000 rows worth of data.
Now that I have my testing database named “MyTestIODB”, my login named “RestrictMyIO” that I want to control I/O, and a test table named “IOTest”, I am all ready to test the I/O throttling I’ve setup, but first I need to set up a Performance Monitor session to monitor the READ IO activity.
Setting up Performance Monitor
To monitor my test to verify that Resource Governor is throttling IO I will use a few counters from the Windows Performance Monitor tool. To setup Performance Monitor I will add two different counters to a Performance Monitoring session. The two counters I want to monitor are shown below:
By reviewing the screenshot above you can see I added two SQL Server:Resource Pool Stats counters. One counter watches the Disk Read IO/sec for the RestrictedIOPool, and the other one watches the Disk Read IO/sec for the default Resource Governor pool.
I will use these different counters to verify how SQL Server is controlling I/O to my disk subsystem while I run my IO test using different logins.
Reading Data from a Table
Before I start testing I need to do a little house keeping. This house keeping is to get a true test of reading from disk before each test. To do this house keeping I will be running the following code:
--Flush dirty pages to disk, and clean out buffer pool CHECKPOINT DBCC DROPCLEANBUFFERS
This code writes all data pages to disk using the CHECKPOINT command, and then removes all pages from the buffer pool by performing the DBCC DROPCLEANBUFFERS. By running this code before each test I can be assured that all pages will be read from disk instead of the buffer pool.
For my IO test I will be running the following code:
USE MyIOTestDB; GO IF OBJECT_ID ('tempdb..#IOTest') is not null DROP TABLE #IOTest; GO SELECT * INTO #IOTest FROM IOTest; GO
This code reads the 5,000,000 from my IOTest table in the MyIOTestDB database and places it in tempdb. I will be running this code using two different accounts. One account is the SQL Server Authenticated login I created earlier named “RestrictMyIO”. The other account will be my normal Windows account that I use to manage my server. After each test run of the above code I will be examining the Performance Monitor counters to see what kind of Read IO load I put on the different Resource Pool statistics.
For my first execution of the code above I will be using my Window login. Below is a Performance Monitor graph that shows the two different counters:
First Test – Performance Monitor Graph
By reviewing this graph you can see all the IO are associated with the default Resource Governor pool. This is the green line. My test does a maximum of 67 I/Os per second. Note that the red line doesn’t move above zero. That line represents the I/Os that go against the RestrictedIOPool.
If you go back and review the classifier function you can see it sets the return value to “default” for all logins that are not equal to “RestrictMyIO”. This is why all my I/O for this first test associated with the default counter.
Now I will run the same test showing the same counters, after I’ve cleaned the buffer pool using the SQL Server Login “RestrictMyIO”. Here is the Performance Monitor Graph for my second test:
Second Test – Performance Monitor Graph
By reviewing this graph you can now see that the default Resource Pool (green) is flat-lined and the Disk Read IO/sec for the RestrictedIOPool (red) is showing all the IO. Also note that SQL Server is throttling these I/Os trying to keep them under 30. If you look closely, SQL Server kept the I/Os close to 30 but it did exceed it slightly with the maximum value of 32.
My example here was extremely simple, but shows you how to setup and use the Resource Governor to throttle IO.
Throttle Those Big IO Resource Hogs
With the release of SQL Server 2014 you now have new knobs to turn that can be used to set the minimum and maximum IO allowed for a Resource Pool. These new settings should help you restrict those big I/O consumers from hogging all the I/O resources. If you need to throttle I/O then hurry up and install SQL Server 2014 and start restricting those big IO resource hogs on your system.