Back to basics with Resource Governor IO in SQL Server 2014

By: Pinal Dave


SQL Server Resource Governor concepts have been in existence since the SQL Server 2008 versions. With every version, there have been new additions, and it has steadily gotten better and better. It started with CPU throttling and memory throttling; now, with SQL Server 2014, they introduced IO throttling.

There are three important concepts related to Resource Governor:

  • Resource Pools – represents a virtual container or physical resource inside a SQL Server instance
  • Workload Groups – serves as a container for session requests based on the classification function defined.
  • Classification function – is a user-defined function which helps in routing the user sessions based on a criteria defined.

IO throttling delivers a long requested and competitive upgrade to the SQL Server Resource Governor, and enables service providers to effectively deliver workload isolation within a SQL Server instance.

Why Resource Governor for IO?

As VM sizes in public and private Infrastructure as a Service (IaaS) architectures increase, multi-workload and multi-user scenarios become increasingly popular. Within an instance multiple workloads will need to be isolated in order for the critical tasks to have the resources they need and to provide an SLA to workload tenants. With memory and CPU isolation available through the SQL Server resource governor, IO is the primary challenge to workload multi-tenancy. With these challenges, this capability:

  • Enables SQL Server customers in IaaS and multitenant environments to set a value for minimum / maximum IOPS per disk volume for workloads and groups of workloads.
  • Enables setting the maximum number of outstanding IOPS per disk volume in the system.

NOTE

This applies to both READ and WRITE activities done ONLY by user tasks.  Most write operations take place from background processes (checkpoint, lazy writer) and background/system processes are not part of the RGIO throttling – they are considered part of the INTERNAL group/pool. Common write operations that gets throttled are – IO activities (like DB checkpoint) that occur before actual backup and sparse file/transient replica creation during DBCC checkdb.

Syntax additions

There are a number of additions that are notable. For example, CREATE/ALTER RESOURCE POOL has two additional options:

  1. MIN_IOPS_PER_VOLUME = value
  2. MAX_IOPS_PER_VOLUME = value

The allowed range for value is from 0 through 2^31-1 (2,147,483,647). Specify 0 to indicate no minimum threshold for the pool in case of MIN and 0 us unlimited threshold in cased of MAX values.

Performance counters for Resource Governor IO

Some of the PerfMon counters that were added to help know about Resource Governor IO include:

Disk Read IOs/sec Number of read operations from the disk in the last second
Disk Read Bytes/sec Number of bytes read from the disk in the last second
Avg Disk msec/Read IO Average time, in milliseconds, of a read operation from the disk
Disk Read IO Throttled/sec Number of read operations throttled in the last second
Disk Write IOs/sec Number of write operations to the disk in the last second
Disk Write Bytes/sec Number of bytes written to the disk in the last second
Avg Disk msec/Write IO Average time, in milliseconds, of a write operation to the disk
Disk Write IO Throttled/sec Number of write operations throttled in the last second

These are in addition to those already available for standard IO counters.

Extended Events additions

SQL Server 2014 added two new XEvents to trace enqueueuing 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:

Name: file_read_enqueued

Data:

  • mode
  • file_handle
  • offset
  • database_id
  • file_id
  • filegroup_id
  • size
  • path

Name: file_write_enqueued

Data:

  • mode
  • file_handle
  • offset
  • database_id
  • file_id
  • filegroup_id
  • size
  • path
  • io_data

Using these XEvents together with the events that trace issuing IO (file_read/file_written) and IO completion (file_read_completed/file_write_completed), we can identify when the IO was enqeued, dequeued and completed and investigate issues related to the IO subsystem.

Final notes

Resource Governor IO has been a fine addition to existing SQL Server instances as they upgrade to SQL Server 2014. With bigger servers and consolidation of servers happening, features such as these often go unnoticed and unknown to many. This article highlights some of the capabilities of SQL Server 2014 with Resource Governor.

 

Leave a Reply