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:
- MIN_IOPS_PER_VOLUME = value
- 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