IO resource governor in SQL Server 2014

In this article, we'll look at what new features SQL Server 2014 adds to resource governor in the future. Resource governor (Resource Governor) is a feature that appears starting with SQL Server 2008. It is a feature used to manage SQL Server workloads and system resource usage. Before SQL Server 2014, resource Governor could only limit the CPU bandwidth, memory resources that some users use to access SQL Server. But with the development of virtualization and cloud technology, the control of IO has a great demand. IaaS (Infrastructure as a service), that is, infrastructure service providers need to control the resources required by each customer by setting the resources of the IO resource pool. This ensures that performance is not impacted by other customers, and that different SLAs or database services can be set based on the user's resource request. Another requirement is IT administrators or database administrators who want to isolate OLTP workloads from maintenance operations. Rebuilding an index, for example, is a common operation because scanning an entire index or table can result in a large number of IO requests. By using IO resource management, you can limit the IO load on these operations to ensure that the concurrency and performance of the OLTP are unaffected. Make the entire server environment more secure.

In SQL Server 2014, the control of IO resources has been increased to address these issues, based on customer requests.

Resource pools, workload groups, and classification concepts are the same as before.

The following are the new features added by SQL Server 2014 :

• The Resource pools increases IOPS control on the volume (per volume Disk partition logical partition) based on the control of Cpu/memory, and can set maximum and minimum iops for the volume for more complex resource control.
• You can set maximum outstanding IO (at the instance level) on a single disk partition. Use this feature to better adjust the load on the disk subsystem.
• Added fields in DMV Sys.dm_resource_governor_resource_pools and sys.dm_resource_governor_configuration to query the use and configuration of IO. In addition the new DMV was added
  Sys.dm_resource_governor_resource_pool_volumes, you can capture the use of Io across different partitions.

For details, please refer to:

Http://msdn.microsoft.com/en-us/library/bb934023.aspx

Http://msdn.microsoft.com/en-us/library/bb934099.aspx

Http://msdn.microsoft.com/en-us/library/dn358348.aspx

• A new two new xevents (file_write_enqueued, file_read_enqueued) can be added to the IO request for the IO Resource management queue.
• Finally we added performance monitoring metrics Sqlserver:resource Pool stats including disk read io/sec, disk read bytes/sec, AVG disk read IO (MS), disk Write I O/sec, Disk Write
  BYTES/SEC, AVG Disk write Io (ms), disk Read io throttled/sec, disk write Io throttled/sec, etc.

When a user successfully logs on to SQL Server, if IO resource regulation is set, the database engine will call the classification function to determine which workload group the current logged-on user belongs to. When this user executes an SQL statement that generates read/write to IO, the database engine does not send this IO read-write request directly to the OS, instead of putting the IO request in a queue, SQL IO resource management will remove the corresponding IO request from the queue based on the current user's settings and usage of the existing resources to the OS for execution, to achieve the management and control of the IO resources.

Let's take the following example to illustrate how to use the IO resource governor in an instance of SQL Server.

Assuming that we have a database host or a consolidated database running on a private cloud, we need to place multiple databases according to the requirements of multiple customers, which enables efficient use of resources while saving costs. If a client's database runs IO-intensive workloads, this can result in IO performance for the entire disk, affecting other users ' operations.

To simplify our presentation, let's say that there are two users who need access to this database, one of whom is an IT manager who needs to regularly back up the database to take care of the data security, and a user who is a financial person who needs to read the data to generate the report. We create two resource pools for these two users and a classifier function that maps user sessions to the corresponding resource pool. The session for user backup is mapped to the resource pool Groupbackup, and the session maps the user report to the resource pool Groupreport. To make the presentation clear, we set the resource pool groupbackup minimum and maximum IOPS to 1 and set the resource pool Groupreport minimum and maximum IOPS to 2.

1  UseMaster;2 3 Go4 5 --Create 3 workload groups for different category of users or application6 7 CREATEWORKLOADGROUP8 groupadmin;9 Ten CREATEWORKLOADGROUP One Groupbackup; A  - CREATEWORKLOADGROUP - Groupreports; the  - --Create classifier function -  - CREATE FUNCTIONDbo.rgclassifier ()RETURNSsysname +  withSCHEMABINDING -  as + BEGIN A  at     DECLARE @grp_namesysname -  -       IF(Suser_name()= 'Admin') -  -           SET @grp_name = 'Groupadmin' -  in       IF(Suser_name()= 'Backup') -  to          SET @grp_name = 'Groupbackup' +  -       IF(Suser_name()= ' Report')--or app_name () like '%report theSERVER%') *  $ SET @grp_name ='Groupreports'Panax Notoginseng  - RETURN @grp_name the  + END; A  the GO +  - --Register the classifier function with Resource Governor $  $ ALTER RESOURCE GOVERNOR with (classifier_function - = dbo.rgclassifier); - GO the  - --Start Resource GovernorWuyi  the ALTER RESOURCE GOVERNOR RECONFIGURE; - GO Wu  - --Create resource pools and map it to workload groups About  $ CREATE RESOURCE POOL - Pooladmin - With ( - Min_iops_per_volume =. A Max_iops_per_volume = +  ); the  - CREATE RESOURCE POOL poolbackup $ With ( the Min_iops_per_volume = 1, the Max_iops_per_volume = 1 the  ); the  - CREATE RESOURCE POOL poolreports in With ( the Min_iops_per_volume = 2, the Max_iops_per_volume = 2 About  ); the  the ALTER WORKLOAD GROUP the Groupadmin +  - USING pooladmin; the Bayi ALTER WORKLOAD GROUP the Groupbackup the  - USING poolbackup; -  the ALTER WORKLOAD GROUP the Groupreports the  the USING poolreports; -  the ALTER RESOURCE GOVERNOR RECONFIGURE; the  the GO

With this configuration, SQL Server will limit the load on the IO resource pool, thus satisfying 2 iops to the user report,1 ioPS to the user backup. By setting the IO Resource Governor to set the maximum workload limit on the customer, the predicted performance can be achieved while protecting other user performance from the impact.

Limit:

The IO Resource Governor is primarily limited to physical IO (disk read-write) and does not work with logical IO (memory). In addition, the IO resource Governor restricts the read-write IO generated by the user to complete the task. For some SQL
The Server-initiated IO is a background process originating from the SQL itself, for example: checkpoint, lazy writer. This part of the IO is not limited in the default internal workload group. So the IO Resource Governor's control over Io is primarily two scenarios that limit the amount of disk overhead that is associated with rebuilding indexes and backups.

Original link: http://blogs.msdn.com/b/apgcdsd/archive/2014/12/13/sql-2014-5-io.aspx

IO resource governor in SQL Server 2014