当前位置：Gxlcms > 数据库问题 > SQL Server 2014里的IO资源调控器

SQL Server 2014里的IO资源调控器

时间：2021-07-01 10:21:17 帮助过：3人阅读

在SQL Server 2014中，根据客户的请求，增加了对IO资源的控制来解决这些问题。

资源调控器中资源池，工作负荷组，分类的概念和之前无异。

技术分享

 

下面是SQL Server 2014增加的新特性：

  • Resource Pools 在对CPU/Memory的控制基础上增加了对卷(per volume Disk Partition逻辑分区)的IOPs控制，可以针对卷设置最大和最小的IOPS，从而实现更复杂的资源控制。
  • 可以对单个磁盘分区设置 maximum outstanding IO (在实例级别). 使用这个特性可以更好的调整磁盘子系统的负载。
  • 在DMV sys.dm_resource_governor_resource_pools和sys.dm_resource_governor_configuration中新增了栏位可以查询IO的使用和配置。另外新增加了DMV
    sys.dm_resource_governor_resource_pool_volumes,可以捕获IO跨不同分区的使用情况。

具体请参考：

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

  • 新增加了两个新的XEvents (file_write_enqueued, file_read_enqueued)，可以对于IO资源管理队列的IO请求。
  • 最后我们增加了性能监视指标SQLServer:Resource     Pool Stats包括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等。

当一个用户成功登录到SQL Server，如果IO资源调控被设置的话，数据库引擎将会调用分类函数去判断当前登录用户属于哪一个工作负荷组。当此用户执行一条SQL语句产生对IO的读写时，数据库引擎不会将此IO读写请求直接发送给OS，取而代之的是将此IO请求放到一个队列中，SQL IO资源管理会根据当前用户的设置和已有资源的使用情况从队列中取出相应的IO请求发送给OS进行执行，以实现对IO资源的管理与控制。

技术分享

我们以下面为例说明如何在一个SQL Server实例中使用IO资源调控器。

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

为了简化我们的演示，假设有两个用户需要访问此数据库，其中一个用户为IT管理人员，需要定期的备份数据库以保重数据安全，还有一个用户为财务人员，需要读取数据以生成报表。我们为这两个用户创建两个资源池和一个可以将用户会话映射到对应资源池的分类器函数。会话为用户backup被映射到资源池GroupBackup，会话为用户report映射到资源池GroupReport。为了使演示清晰明了，我们将资源池GroupBackup最小和最大IOPS设定为1，将资源池GroupReport最小和最大IOPS设定为2。

  1. <span style="color: #008080;"> 1</span> <span style="color: #0000ff;">use</span><span style="color: #000000;"> master;
  2. </span><span style="color: #008080;"> 2</span> 
  3. <span style="color: #008080;"> 3</span> <span style="color: #0000ff;">go</span>
  4. <span style="color: #008080;"> 4</span> 
  5. <span style="color: #008080;"> 5</span> <span style="color: #008080;">--</span><span style="color: #008080;"> Create 3 workload groups for different category of users or application</span>
  6. <span style="color: #008080;"> 6</span> 
  7. <span style="color: #008080;"> 7</span> <span style="color: #0000ff;">CREATE</span> WORKLOAD <span style="color: #0000ff;">GROUP</span>
  8. <span style="color: #008080;"> 8</span> <span style="color: #000000;">GroupAdmin;
  9. </span><span style="color: #008080;"> 9</span> 
  10. <span style="color: #008080;">10</span> <span style="color: #0000ff;">CREATE</span> WORKLOAD <span style="color: #0000ff;">GROUP</span>
  11. <span style="color: #008080;">11</span> <span style="color: #000000;">GroupBackup;
  12. </span><span style="color: #008080;">12</span> 
  13. <span style="color: #008080;">13</span> <span style="color: #0000ff;">CREATE</span> WORKLOAD <span style="color: #0000ff;">GROUP</span>
  14. <span style="color: #008080;">14</span> <span style="color: #000000;">GroupReports;
  15. </span><span style="color: #008080;">15</span> 
  16. <span style="color: #008080;">16</span> <span style="color: #008080;">--</span><span style="color: #008080;"> Create classifier function</span>
  17. <span style="color: #008080;">17</span> 
  18. <span style="color: #008080;">18</span> <span style="color: #0000ff;">CREATE</span> <span style="color: #0000ff;">FUNCTION</span> dbo.rgclassifier() <span style="color: #0000ff;">RETURNS</span><span style="color: #000000;"> sysname
  19. </span><span style="color: #008080;">19</span> <span style="color: #0000ff;">WITH</span><span style="color: #000000;"> SCHEMABINDING
  20. </span><span style="color: #008080;">20</span> <span style="color: #0000ff;">AS</span>
  21. <span style="color: #008080;">21</span> <span style="color: #0000ff;">BEGIN</span>
  22. <span style="color: #008080;">22</span> 
  23. <span style="color: #008080;">23</span>     <span style="color: #0000ff;">DECLARE</span> <span style="color: #008000;">@grp_name</span><span style="color: #000000;"> sysname
  24. </span><span style="color: #008080;">24</span> 
  25. <span style="color: #008080;">25</span>       <span style="color: #0000ff;">IF</span> (<span style="color: #ff00ff;">SUSER_NAME</span>() <span style="color: #808080;">=</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">admin</span><span style="color: #ff0000;">‘</span><span style="color: #000000;">)
  26. </span><span style="color: #008080;">26</span> 
  27. <span style="color: #008080;">27</span>           <span style="color: #0000ff;">SET</span> <span style="color: #008000;">@grp_name</span> <span style="color: #808080;">=</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">GroupAdmin</span><span style="color: #ff0000;">‘</span>
  28. <span style="color: #008080;">28</span> 
  29. <span style="color: #008080;">29</span>       <span style="color: #0000ff;">IF</span> (<span style="color: #ff00ff;">SUSER_NAME</span>() <span style="color: #808080;">=</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">backup</span><span style="color: #ff0000;">‘</span><span style="color: #000000;">)
  30. </span><span style="color: #008080;">30</span> 
  31. <span style="color: #008080;">31</span>          <span style="color: #0000ff;">SET</span> <span style="color: #008000;">@grp_name</span> <span style="color: #808080;">=</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">GroupBackup</span><span style="color: #ff0000;">‘</span>
  32. <span style="color: #008080;">32</span> 
  33. <span style="color: #008080;">33</span>       <span style="color: #0000ff;">IF</span> (<span style="color: #ff00ff;">SUSER_NAME</span>() <span style="color: #808080;">=</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">report</span><span style="color: #ff0000;">‘</span> )<span style="color: #008080;">--</span><span style="color: #008080;">or APP_NAME() LIKE ‘%REPORT</span>
  34. <span style="color: #008080;">34</span> SERVER<span style="color: #808080;">%</span><span style="color: #ff0000;">‘</span><span style="color: #ff0000;">)
  35. </span><span style="color: #008080;">35</span> 
  36. <span style="color: #008080;">36</span> <span style="color: #ff0000;">          SET @grp_name = </span><span style="color: #ff0000;">‘</span>GroupReports<span style="color: #ff0000;">‘</span>
  37. <span style="color: #008080;">37</span> 
  38. <span style="color: #008080;">38</span> <span style="color: #ff0000;">    RETURN @grp_name
  39. </span><span style="color: #008080;">39</span> 
  40. <span style="color: #008080;">40</span> <span style="color: #ff0000;">END;
  41. </span><span style="color: #008080;">41</span> 
  42. <span style="color: #008080;">42</span> <span style="color: #ff0000;">GO
  43. </span><span style="color: #008080;">43</span> 
  44. <span style="color: #008080;">44</span> <span style="color: #ff0000;">-- Register the classifier function with Resource Governor
  45. </span><span style="color: #008080;">45</span> 
  46. <span style="color: #008080;">46</span> <span style="color: #ff0000;">ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION
  47. </span><span style="color: #008080;">47</span> <span style="color: #ff0000;">= dbo.rgclassifier);
  48. </span><span style="color: #008080;">48</span> <span style="color: #ff0000;">GO
  49. </span><span style="color: #008080;">49</span> 
  50. <span style="color: #008080;">50</span> <span style="color: #ff0000;"> -- Start Resource Governor
  51. </span><span style="color: #008080;">51</span> 
  52. <span style="color: #008080;">52</span> <span style="color: #ff0000;">ALTER RESOURCE GOVERNOR RECONFIGURE;
  53. </span><span style="color: #008080;">53</span> <span style="color: #ff0000;">GO
  54. </span><span style="color: #008080;">54</span> 
  55. <span style="color: #008080;">55</span> <span style="color: #ff0000;">-- Create resource pools and map it to workload groups
  56. </span><span style="color: #008080;">56</span> 
  57. <span style="color: #008080;">57</span> <span style="color: #ff0000;">CREATE RESOURCE POOL
  58. </span><span style="color: #008080;">58</span> <span style="color: #ff0000;">PoolAdmin
  59. </span><span style="color: #008080;">59</span> <span style="color: #ff0000;">WITH (   
  60. </span><span style="color: #008080;">60</span> <span style="color: #ff0000;">MIN_IOPS_PER_VOLUME = 100,
  61. </span><span style="color: #008080;">61</span> <span style="color: #ff0000;">MAX_IOPS_PER_VOLUME = 100
  62. </span><span style="color: #008080;">62</span> <span style="color: #ff0000;"> );
  63. </span><span style="color: #008080;">63</span> 
  64. <span style="color: #008080;">64</span> <span style="color: #ff0000;">CREATE RESOURCE POOL PoolBackup
  65. </span><span style="color: #008080;">65</span> <span style="color: #ff0000;">WITH (
  66. </span><span style="color: #008080;">66</span> <span style="color: #ff0000;">MIN_IOPS_PER_VOLUME = 1,
  67. </span><span style="color: #008080;">67</span> <span style="color: #ff0000;">MAX_IOPS_PER_VOLUME = 1
  68. </span><span style="color: #008080;">68</span> <span style="color: #ff0000;"> );
  69. </span><span style="color: #008080;">69</span> 
  70. <span style="color: #008080;">70</span> <span style="color: #ff0000;">CREATE RESOURCE POOL PoolReports
  71. </span><span style="color: #008080;">71</span> <span style="color: #ff0000;">WITH (
  72. </span><span style="color: #008080;">72</span> <span style="color: #ff0000;">MIN_IOPS_PER_VOLUME = 2,  
  73. </span><span style="color: #008080;">73</span> <span style="color: #ff0000;">MAX_IOPS_PER_VOLUME = 2
  74. </span><span style="color: #008080;">74</span> <span style="color: #ff0000;"> );
  75. </span><span style="color: #008080;">75</span> 
  76. <span style="color: #008080;">76</span> <span style="color: #ff0000;">ALTER WORKLOAD GROUP
  77. </span><span style="color: #008080;">77</span> <span style="color: #ff0000;">GroupAdmin
  78. </span><span style="color: #008080;">78</span> 
  79. <span style="color: #008080;">79</span> <span style="color: #ff0000;">USING PoolAdmin;
  80. </span><span style="color: #008080;">80</span> 
  81. <span style="color: #008080;">81</span> <span style="color: #ff0000;">ALTER WORKLOAD GROUP
  82. </span><span style="color: #008080;">82</span> <span style="color: #ff0000;">GroupBackup
  83. </span><span style="color: #008080;">83</span> 
  84. <span style="color: #008080;">84</span> <span style="color: #ff0000;">USING PoolBackup;
  85. </span><span style="color: #008080;">85</span> 
  86. <span style="color: #008080;">86</span> <span style="color: #ff0000;">ALTER WORKLOAD GROUP
  87. </span><span style="color: #008080;">87</span> <span style="color: #ff0000;">GroupReports
  88. </span><span style="color: #008080;">88</span> 
  89. <span style="color: #008080;">89</span> <span style="color: #ff0000;">USING PoolReports;
  90. </span><span style="color: #008080;">90</span> 
  91. <span style="color: #008080;">91</span> <span style="color: #ff0000;">ALTER RESOURCE GOVERNOR RECONFIGURE;
  92. </span><span style="color: #008080;">92</span> 
  93. <span style="color: #008080;">93</span> <span style="color: #ff0000;">GO</span>

通过这个配置，SQL Server将会限制IO资源池的负载，从而满足2个IOPS给用户Report,1个IOPS给用户Backup。通过设置IO资源调控器对客户设置最大的工作负载限制，一方面可以达到预测的性能，同时还会保护其他的用户性能不受影响。

技术分享

限制：

IO资源调控器主要是针对物理IO（磁盘读写）的限制，对逻辑IO（内存）是不起作用的。此外，IO资源调控器也只会限制用户完成任务产生的读写IO。对于一些SQL
Server 发起的IO，是源于SQL本身的后台进程，例如：checkpoint, lazy writer。这部分的IO都在默认的INTERNAL工作负荷组中，是无法被限制的。所以IO资源调控器对IO的控制主要的两个应用场景为限制重建索引和备份带来的大量磁盘开销。

原文链接：http://blogs.msdn.com/b/apgcdsd/archive/2014/12/13/sql-2014-5-io.aspx

SQL Server 2014里的IO资源调控器

标签：

人气教程排行

本站所有资源全部来源于网络，若本站发布的内容侵害到您的隐私或者利益，请联系我们删除！