SQL Server 2014使用资源调控器对IOPS控制

系统管理员 2021-04-23 16:14 513阅读 0赞

在SQLServer2008中引入了[ResourceGovernor][]，通过对一些语句的CPU和内存的资源限制瞻性的对多租户的数据库环境进行有效管理。这个特性允许客户实现数据库的整合或者把数据库配置为服务管理。但是随着虚拟化和云技术的发展，IO的控制成为一个难题。在SQLServer2014中，我们根据客户的请求,增加了对IO资源的控制来解决客户遇到的问题。

**新特性：**

*  [ResourcePools][]在对CPU/Memory的控制基础上增加了对卷(pervolumeDiskPartition逻辑分区)的IOPs控制，可以针对卷设置最大和最小的IOPS，从而实现更复杂的资源控制。
 *  可以对单个磁盘分区设置[maximumoutstandingIO][](在实例级别).使用这个特性可以更好的调整磁盘子系统的负载。
 *  澄清一下byvolume指的是[diskvolume][]被windows文件系统识别的APIs.
 *  我们在DMV[sys.dm\_resource\_governor\_resource\_pools][sys.dm_resource_governor_resource_pools]和[sys.dm\_resource\_governor\_configuration][sys.dm_resource_governor_configuration]中新增了栏位可以查询IO的使用和配置.另外我们新增加了DMV[sys.dm\_resource\_governor\_resource\_pool\_volumes][sys.dm_resource_governor_resource_pool_volumes],可以捕获IO跨不同分区的使用情况。
 *  新增加了两个新的XEvents(file\_write\_enqueued,file\_read\_enqueued)，可以对于IO资源管理队列的IO请求。
 *  最后我们增加了性能监视指标[SQLServer:ResourcePoolStats][SQLServer_ResourcePoolStats]包括DiskReadIO/sec,DiskReadBytes/sec,AvgDiskReadIO(ms),DiskWriteIO/sec,DiskWriteBytes/sec,AvgDiskWriteIO(ms),DiskReadIOThrottled/sec,DiskWriteIOThrottled/sec等。
 *

**如何使用：**

让我们以下面的场景为例说明如何使用IO资源治理在一个SQL服务器实例上控制资源。

假设我们有一台数据库主机或者运行在私有云上的整合数据库，我们需要根据多个客户的要求放多个数据库，这样可以实现资源的有效利用同时节省成本。如果客户的一个数据库运行IO密集型的工作负载，这样会导致整个磁盘的IO性能，从而影响其他用户的操作。

第一个我们会为每个客户/数据库创建一个资源池和一个可以将客户会话映射到对应资源池的分类器函数。例如,会话为客户1可以被映射到资源池1,会话为客户2到资源池2。

如果你想使用IO资源治理，重要的是对每一个资源池设定最小或大IOPS,以便IO请求定向到治理子系统。在下面的例子中,我们对每个资源池都设置MAX\_IOPS\_PER\_VOLUME:

\--Create2resourcepools&2workloadgroups.  
CREATERESOURCEPOOLCustomer1Pool;  
CREATERESOURCEPOOLCustomer2Pool;  
GO  
  
CREATEWORKLOADGROUPCustomer1GroupUSINGCustomer1Pool;  
CREATEWORKLOADGROUPCustomer2GroupUSINGCustomer2Pool;  
GO  
  
\--Createclassifierfunction  
CREATEFUNCTIONfnUserClassifier()  
RETURNSSYSNAME  
WITHSCHEMABINDING  
AS  
BEGIN  
IFORIGINAL\_DB\_NAME()='Customer1DB'  
BEGIN  
RETURN'Customer1Group'  
END  
  
IFORIGINAL\_DB\_NAME()='Customer2DB'  
BEGIN  
RETURN'Customer2Group'  
END  
  
RETURN'default'  
END;  
GO  
  
\--SettheclassifierfunctionandenableRG.  
ALTERRESOURCEGOVERNORWITH(CLASSIFIER\_FUNCTION=dbo.fnUserClassifier);  
ALTERRESOURCEGOVERNORRECONFIGURE;  
GO  
  
\--SetdefaultvaluesfortheresourcepoolssothatIORGisenabled.  
ALTERRESOURCEPOOLCustomer1PoolWITH(MIN\_IOPS\_PER\_VOLUME=0,MAX\_IOPS\_PER\_VOLUME=2147483647);  
ALTERRESOURCEPOOLCustomer2PoolWITH(MIN\_IOPS\_PER\_VOLUME=0,MAX\_IOPS\_PER\_VOLUME=2147483647);  
ALTERRESOURCEGOVERNORRECONFIGURE;  
GO

将每个工作负载分类到不同的资源池有助于我们根据每个用户的使用情况配置资源限制，同时可以监控用户的工作复杂。下面的图(性能监视器)表明由于客户1的大量IO请求导致了客户2的性能下降：

![SouthEast][]

为了保护客户2的性能不受其他客户的影响，我们对客户2的资源池设置MIN\_IOPS\_PER\_VOLUME。从上面的图表,看起来,系统可以处理大约1300IOPS,所以我们决定保留650IOPS给客户2:

ALTERRESOURCEPOOLCustomer2PoolWITH(MIN\_IOPS\_PER\_VOLUME=650);  
ALTERRESOURCEGOVERNORRECONFIGURE;

通过这个配置，SQLServer将会限制其他资源池的负载，从而满足650IOPS给客户2.下面的图中,我们可以看到系统的IOPS被公平的分配到客户，客户2的性能没有受其他客户的影响。

![SouthEast][SouthEast 1]

MIN\_IOPS\_PER\_VOLUMEsetting虽然可以让资源池保留资源，但是不会限制最大的IOPS.这就意味着性能仍然会根据其他客户的使用情况变化，无法获得稳定性能。为了避免这个问题,保证可预测的性能，我们需要对每个客户设置MAX\_IOPS\_PER\_VOLUME。这将对客户设置最大的工作负载限制，一方面可以达到预测的性能，同时还会保护其他的客户性能不受影响：

ALTERRESOURCEPOOLCustomer2PoolWITH(MAX\_IOPS\_PER\_VOLUME=750)  
ALTERRESOURCEGOVERNORRECONFIGURE  
GO

通过对IO资源池的资源设定，我们可以控制每个客户需要的资源。这可以保证性能不会受其他客户的影响，同时也可以基于用户的资源请求设定不同的SLA或者数据库服务。

另一个场景是可以将OLTP工作负载与维护操作进行隔离。比如重建索引是一种常见的操作,因为要扫描整个索引或者表，会导致大量的IO请求。通过使用IO资源治理我们可以限制这些操作的IO复杂，从而保证OLTP的并发和性能不受影响。

可以提供下载[downloadingtheSQLServer2014CTP2][]测试这个功能。

[ResourceGovernor]: http://msdn.microsoft.com/en-us/library/bb933866.aspx?WT.mc_id=Blog_SQL_InMem_SQL2014
[ResourcePools]: http://msdn.microsoft.com/en-us/library/hh510189.aspx?WT.mc_id=Blog_SQL_InMem_SQL2014
[maximumoutstandingIO]: http://msdn.microsoft.com/en-us/library/bb895329%28v=sql.120%29.aspx?WT.mc_id=Blog_SQL_InMem_SQL2014
[diskvolume]: http://msdn.microsoft.com/en-us/library/windows/desktop/aa365728%28v=vs.85%29.aspx?WT.mc_id=Blog_SQL_InMem_SQL2014
[sys.dm_resource_governor_resource_pools]: http://msdn.microsoft.com/en-us/library/bb934023%28v=sql.120%29.aspx?WT.mc_id=Blog_SQL_InMem_SQL2014
[sys.dm_resource_governor_configuration]: http://msdn.microsoft.com/en-us/library/bb934099%28v=sql.120%29.aspx?WT.mc_id=Blog_SQL_InMem_SQL2014
[sys.dm_resource_governor_resource_pool_volumes]: http://msdn.microsoft.com/en-us/library/dn358348%28v=sql.120%29.aspx?WT.mc_id=Blog_SQL_InMem_SQL2014
[SQLServer_ResourcePoolStats]: http://msdn.microsoft.com/en-us/library/cc645958%28v=sql.120%29.aspx?WT.mc_id=Blog_SQL_InMem_SQL2014
[SouthEast]: /images/20210424/1619194474926.png
[SouthEast 1]: /images/20210424/1619194409207.png
[downloadingtheSQLServer2014CTP2]: http://www.microsoft.com/en-us/sqlserver/sql-server-2014.aspx?WT.mc_id=Blog_SQL_InMem_SQL2014