Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Scripts          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Use SQL Server 2014 Resource Governor to Throttle Processes for IO Usage


By:   |   Last Updated: 2014-10-01   |   Comments (6)   |   Related Tips: More > Resource Governor

Problem

Microsoft introduced Resource Governor in SQL Server 2008 Enterprise Edition with the capability to control CPU and memory resource consumption by setting limits through workload groups. By assigning a workload group to processes that share the same characteristics (reporting, maintenance, certain users, etc.), SQL Server can throttle resources usage as required. However, there was no way to limit or throttle IO resources usage; something that is important in cases like Table Scans, Database maintenance tasks, etc.

Solution

SQL Server 2014 addressed the gap of throttling IO resources by adding support for IO resource throttling in Resource Governor. We can now specify minimum and maximum IOPS (Input/Output Operations per Second) per disk volume in the resource pool definition. MIN_IOPS_PER_VOLUME and MAX_IOPS_PER_VOLUME are the minimum and maximum physical IO operations per second (IOPS) per disk volume for a resource pool. Related monitoring goodies have also been added including : new Perfmon counters, the sys.dm_resource_governor_resource_pool_volumes DMV to return IO usage stats about volumes used by the instance, new Extended Events in addition to adding extra columns to some existing DMVs.

As stated before, throttling IO resources usage can become handy in cases where the IO subsystem is used heavily and not necessarily something like memory consumption such as with index maintenance, DBCC's , etc. where they can have lower priority than some business processes. For the record, there are some alternatives for running integrity checks against databases such as running on a standby instance or running against secondary replica, but it is beyond the scope of this tip and it is STILL important to run integrity checks on primary databases to catch any problems with the respective IO subsystem.

Basic Steps to Setup SQL Server Resource Governor

Let's say we have a weekly database maintenance process that we are not very picky about the duration it takes.  Instead we are more concerned about user sessions and we do not want the database maintenance to affect user performance. Now with resource governor we can limit the max IOS per volume for something like database maintenance tasks.

Here is the basic process to configure Resource Governor:

1: Create a resource pool that defines the limit of resources to be used by database maintenance task.

2: Create a workload group that will use the resource(s) constraints defined in the resource pool.

3: Create a classifier function that will "route" the processes to the respective workload groups based on conditions like the application name or login. Warning:  You really want to make your classifier function as simple as possible since it will be evaluated for every new session, even when connection pooling is enabled. A complex or badly written one can slow your applications or even bring SQL Server to a halt.

4: Alter Resource Governor to use the classifier function for incoming connections then enable Resource Governor.

Step by Step SQL Server Resource Governor Example

We will be using sample database AdventureWorks2014 to demonstrate throttling CHECKDB. The database backup is small so we want to enlarge database a bit. You can use the method described here, use your own method or a 3rd party tool.

To follow the basic rule of making our classifier as simple as possible, we will be creating a login to use exclusively for running DBCC CHECKDB. The classifier will be able to "route" the connection to a respective workload group based on the login used. With our example, make sure you have SQL Server authentication enabled.

CREATE LOGIN checkdb_maintenance WITH PASSWORD = 'checkdb_maint0!';
GO
ALTER SERVER ROLE [sysadmin] ADD MEMBER [checkdb_maintenance]
GO

Create the resource pool and workload group without any specifications.

CREATE RESOURCE POOL checkdb_maintenance;
GO
CREATE WORKLOAD GROUP checkdb_maintenance USING checkdb_maintenance;
GO

Create the classifier function that will route the incoming connections to the respective workload groups and pools. The function will simply filter anything using loginname checkdb_maintenance to a group of the same name and anything else to the default pool

USE MASTER;
GO
CREATE FUNCTION dbo.Restrict_checkdb_IO()
RETURNS SYSNAME WITH SCHEMABINDING
AS
BEGIN
       DECLARE @GroupName SYSNAME
          IF SUSER_NAME() = 'checkdb_maintenance'
            BEGIN
              SET @GroupName = 'checkdb_maintenance'
           END
       ELSE
       BEGIN
              SET @GroupName = 'default'
       END
       RETURN @GroupName;
END

Assign the classifier to Resource Governor.

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

As of this moment, the new pool is not yet enabled and all user connections will be routed to the default pool even if they are using login checkdb_maintenance.

Assign the classifier to resource governer.

Let's reconfigure Resource Governor with the new changes.

ALTER RESOURCE GOVERNOR RECONFIGURE;

Now we can see the new pool in Perfmon and also listed in the sys.dm_resource_governor_resource_pools DMV.

Now we can see the new pool in perfmon AND also listed by sys.dm_resource_governor_resource_pools.


SELECT pool_id, name, min_iops_per_volume, max_iops_per_volume, read_io_queued_total,
read_io_issued_total, read_io_completed_total,read_io_throttled_total, read_bytes_total,
read_io_stall_total_ms, read_io_stall_queued_ms, io_issue_violations_total,io_issue_delay_total_ms
FROM   sys.dm_resource_governor_resource_pools
WHERE  name <> 'internal'; 

Let's run DBCC CHECKDB using the login checkdb_maintenance and observe if the process will be throttled. Kindly note that we have not setup any limits on the new resource pool, it just exists. I'm using login checkdb_maintenance as I run the code below.

DBCC DROPCLEANBUFFERS
GO
DBCC CHECKDB (AdventureWorks2014 ) WITH NO_INFOMSGS; 
GO

Using perfmon, I'm watching three counters under Resource pool stats objects. Once we run the DBCC CHECKDB statement, we will see that the classifier function redirects the connection to checkdb_maintenance pool. This is a small database so the usage is not that high though.

Once we run the CHECKDB statement, we will see that the classifier function redirects the connection to checkdb_maintenance pool

Using DMVs, we can also get more information about the running processes and associated resource pools.

SELECT
 r.session_id, 
 r.request_id as session_request_id,   
 s.group_id,rg.name as pool_name,  
 r.status, 
 s.host_name,   
 CASE WHEN s.login_name = s.original_login_name THEN s.login_name 
 ELSE s.login_name + ' (' + s.original_login_name + ')' 
 END as login_name,
 s.program_name, 
 db_name(r.database_id) as database_name, 
 r.command, 
 substring(st.text,r.statement_start_offset/2 ,
 (CASE WHEN r.statement_end_offset = -1 THEN len(convert(nvarchar(max), st.text)) * 2 
 ELSE r.statement_end_offset 
 END - r.statement_start_offset)/2) as statement,
 r.start_time,
 r.total_elapsed_time as total_elapsed_time_ms,
 r.cpu_time as cpu_time_ms,
 r.wait_type as current_wait_type,
    r.wait_resource as current_wait_resource,
    r.wait_time as current_wait_time_ms,
    r.last_wait_type,
    r.blocking_session_id 
FROM   sys.dm_exec_requests r      
LEFT OUTER JOIN sys.dm_exec_sessions s 
 ON s.session_id = r.session_id         
LEFT OUTER JOIN sys.dm_resource_governor_resource_pools rg 
 ON s.group_id=rg.pool_id             
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) st 
WHERE r.session_id<>@@spid 


Let's assume we don't want Checkdb to suprass 500 IOPS per volume.

Now, let's assume we don't want DBCC CHECKDB to surpass 600 IOPS per volume. We can do this by specifying a value for Max_IOPS_PER_VOLUME in the resource pool definition then reconfigure Resource Governor to honor the new definition.

In the code and screen shot below, we are retrying DBCC CHECKDB with the Max_IOPS_PER_VOLUME configuration of 600 and 1500 then validating the results in PerfMon.

WAITFOR DELAY '00:00:15'
GO
ALTER RESOURCE POOL checkdb_maintenance WITH (Max_IOPS_PER_VOLUME=600);
ALTER RESOURCE GOVERNOR RECONFIGURE;
GO
WAITFOR DELAY '00:00:15'
GO
ALTER RESOURCE POOL checkdb_maintenance WITH (Max_IOPS_PER_VOLUME=1500);
ALTER RESOURCE GOVERNOR RECONFIGURE;
GO

 

We are going to "change gear" here and throttle CHECKDB down and up again to see how this works.

 

The DMV sys.dm_resource_governor_resource_pools will also show the throttled IO values.

DMV sys.dm_resource_governor_resource_pools will also show Throttled IO values.

Same values can be shown in DMV sys.dm_resource_governor_resource_pool_volumes.

SELECT * 
FROM  sys.dm_resource_governor_resource_pool_volumes


Same values can be shown in DMV sys.dm_resource_governor_resource_pool_volumes
Next Steps


Last Updated: 2014-10-01


get scripts

next tip button



About the author
MSSQLTips author Mohamed Mawla Mohamed Mawla works as a Database Consultant for The Pythian Group in Ottawa. His expertise spans SQL Server and MySQL.

View all my tips
Related Resources




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Tuesday, July 07, 2015 - 2:15:46 PM - Burt Back To Top

In perfmon where is the counter you're selecting from which shows the IO?


Tuesday, October 21, 2014 - 8:47:53 AM - W Back To Top

@Joe

use case 1: shared storage e.g. SAN with volumes shared between either multiple SQL server instances, or volumes shared between an SQL instance and a wider virtualised environment.  Throttling SQL IO means its IO demands have a ceiling, making them more predictable (for expansion planning etc.) and protects the rest of the virtual envirnment against a rogue SQL process eating all available IOPS.

use case 2: protecting resources for critical/immediate processes within an instance against heavy IO use by necessary processes that don't require immdiacy.


Friday, October 10, 2014 - 7:23:37 AM - Joe Back To Top

Interesting but I can't see a use case --slow IO is the number one blocker in my experience -- I can't think of a scenario where I'd want to limit IO.


Wednesday, October 01, 2014 - 4:29:15 PM - Brain2000 Back To Top

Great article.  Good to know how the CPU and I/O throttling work.

Unfortunately this is still only supported by the enterprise edition of SQL 2014.  I'm a little disappointed, as we cannot afford the increased cost for this great feature.


Wednesday, October 01, 2014 - 12:02:16 PM - Greg Robidoux Back To Top

Hi Mohamed,

Welcome to MSSQLTips and great job on your first tip!

Greg


Wednesday, October 01, 2014 - 9:46:14 AM - Jeremy Kadlec Back To Top

Mohamed,

Congrats on your first tip and welcome to the team!

Thank you,
Jeremy Kadlec
MSSQLTips.com Co-Leader


Learn more about SQL Server tools