IO Resource Governance in SQL Server 2014

November 7, 2013

Resource Governor was introduced in SQL Server 2008 to achieve predictable performance in a multi-tenant environment, to support mixed workload patterns, to provide mechanisms and tools to deal with runaway queries, and to enforce resource limits for CPU and memory. It enables customers to implement database consolidation or to configure their own database as a service. Since then, we’ve been incrementally enhancing this feature in major releases to address the top customer requests in this area. In SQL Server 2014, we are excited to add support for IO resource governance.

What is new?

Resource Pools now support configuration of minimum and maximum IOPS per volume in addition to CPU/Memory settings enabling more comprehensive resource isolation controls. See syntax details for Create and Alter resource pool with IO support.
Ability to configure the maximum outstanding IO per volume (at instance scope). This knob is meant to calibrate the resource governor feature for your storage subsystem.
As a note of clarification, by volume we refer to the disk volume as identified by Windows File system APIs
We’ve added new columns in the sys.dm_resource_governor_resource_pools and sys.dm_resource_governor_configuration dynamic management views to surface the IO usage and configuration. In addition, we’ve also added a new DMV called sys.dm_resource_governor_resource_pool_volumes that captures IO usage stats across different volumes used by the instance.
Two new XEvents (file_write_enqueued, file_read_enqueued) were added to monitor IO requests to the IO Resource Manager queues. These XEvents follow the conventions of the existing IO related events, such as issuing IO and IO completion
Last but not least, we’ve added relevant performance counters such as Disk Read IO/sec, Disk Read Bytes/sec, Avg Disk Read IO (ms), Disk Write IO/sec, Disk Write Bytes/sec, Avg Disk Write IO (ms), Disk Read IO Throttled/sec, Disk Write IO Throttled/sec to the SQLServer:Resource Pool Stats.

How to use it?

Let’s take the following scenario as an example of how IO Resource Governance can be used to control the resource usage in a SQL Server instance:

Let’s suppose we are a Database hoster or running a Private cloud for database consolidation and we need to host multiple databases from different tenants (or customers) within a single SQL Server instance to achieve better density and COGS. If one of the tenants is running a very IO intensive workload, this can saturate the IO subsystem, causing performance problems to concurrent workloads that need to perform IO.

The first step would be to create a Resource Pool for each tenant/database and a classifier function that will map the sessions from each tenant to the corresponding Resource Pool. For example, sessions for Customer 1 can be mapped to Resource Pool 1 and sessions for Customer 2 to Resource Pool 2.

If you want to use IO Resource Governance, it is important to set the min or max IOPS setting for every Resource Pool so that the IO requests are redirected to the governance subsystem and minimum reservations can be honored. In the example below, we set the MAX_IOPS_PER_VOLUME to the maximum value for every Pool:

-- Create 2 resource pools & 2 workload groups.
CREATE RESOURCE POOL Customer1Pool;
CREATE RESOURCE POOL Customer2Pool;
 GO

CREATE WORKLOAD GROUP Customer1Group USING Customer1Pool;
CREATE WORKLOAD GROUP Customer2Group USING Customer2Pool;
GO

 -- Create classifier function
CREATE FUNCTION fnUserClassifier()
RETURNS SYSNAME
WITH SCHEMABINDING
 AS
 BEGIN
 IF ORIGINAL_DB_NAME() = 'Customer1DB'
BEGIN
RETURN 'Customer1Group'
END

 IF ORIGINAL_DB_NAME() = 'Customer2DB'
BEGIN
 RETURN 'Customer2Group'
END

 RETURN 'default'
 END;
 GO

-- Set the classifier function and enable RG.
 ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION = dbo.fnUserClassifier);
ALTER RESOURCE GOVERNOR RECONFIGURE;
 GO

 -- Set default values for the resource pools so that IO RG is enabled.
 ALTER RESOURCE POOL Customer1Pool WITH (MIN_IOPS_PER_VOLUME=0, MAX_IOPS_PER_VOLUME=2147483647);
ALTER RESOURCE POOL Customer2Pool WITH (MIN_IOPS_PER_VOLUME=0, MAX_IOPS_PER_VOLUME=2147483647);
ALTER RESOURCE GOVERNOR RECONFIGURE;
 GO

Classifying each workload to a different Resource Pool allows us to configure the resource limits we want to provide for each tenant and also monitor the resource usage generated by their workload. The graph below (Performance Monitor) shows that the workload from Customer 1 is issuing too many IO requests causing a performance drop for Customer 2:

In order to protect Customer 2 and guarantee that he gets consistent performance, regardless of the activity from other tenants, we can set the MIN_IOPS_PER_VOLUME setting for the corresponding Resource Pool. From the graph above, it seems that the system can handle around 1300 IOPS, so we decide to reserve 650 IOPS for Customer 2:

ALTER RESOURCE POOL Customer2Pool WITH (MIN_IOPS_PER_VOLUME=650);
ALTER RESOURCE GOVERNOR RECONFIGURE;
 GO

With this configuration, SQL Server will try to throttle the workloads that are running in other Resource Pools, in order to satisfy the 650 IOPS reservation for Customer 2. In the graph below, we can see that the IOPS of the system are now fairly distributed among the tenants and that the performance for Customer 2 is back to normal despite its noisy neighbor:

The MIN_IOPS_PER_VOLUME setting will make a reservation for the Resource Pool, but it won’t set any limit for the maximum IOPS it can use. This means that the tenants will still get performance variation depending on how active the rest of the tenants on the instance are. To avoid this problem and guarantee predictable performance, we can set the MAX_IOPS_PER_VOLUME setting for each of the tenants. This will set a hard limit for the maximum IOPS the tenant’s workload can use, guaranteeing predicable performance for the tenant, but also protecting the rest of the tenants on the instance:

ALTER RESOURCE POOL Customer2Pool WITH (MAX_IOPS_PER_VOLUME=750)
ALTER RESOURCE GOVERNOR RECONFIGURE
 GO

By configuring the IO settings on the Resource Pools we can control the resources we want to provide for each tenant. This allows us to guarantee predictable performance regardless of the activity from other tenants or even provide differentiation in SLA for the Database Service based on the amount of the resources customers sign up to reserve.

Another scenario that many of you might find applicable is isolating your OLTP workload from any maintenance operations that need to run in the database. Rebuilding an index, for example, is a common operation that can trigger a large number of IO requests, as it needs to scan the whole index or table. By using IO Resource Governance we can limit the number of IO operations these tasks can perform and guarantee predictable performance for concurrent OLTP workload.

In this case, we need a Resource Pool dedicated for maintenance operations and a classifier function that will map maintenance sessions to the corresponding Resource Pool. Running these operation as a different user might be a good way to distinguish between regular and maintenance sessions. By configuring the MAX_IOPS_PER_VOLUME setting on the “maintenance” Resource Pool, we can limit the number of IO operations these tasks can perform and protect concurrent OLTP workload from being impacted.

I hope the example scenarios above demonstrate the core value of this feature.

Call to Action

Please try this feature right away by downloading the SQL Server 2014 CTP2. Even more easily test it in on the SQL Server 2014 CTP2 images that are now available in Windows Azure. We look forward to hearing your feedback.