SQLServer资源调控器

很早之前就有朋友问过我，能否按业务的优先等级分配SQLServer的资源，使得不同的应用能得到不同的响应，SQLServer2008之前对这个需求貌似没有什么 解决方法，不过从SQLServer2008开始，这个需求就变得很简单了，SQLServer直接就为我们提供了按用户的要求分配

     很早之前就有朋友问过我，能否按业务的优先等级分配SQLServer的资源，使得不同的应用能得到不同的响应，SQLServer2008之前对这个需求貌似没有什么

解决方法，不过从SQLServer2008开始，这个需求就变得很简单了，SQLServer直接就为我们提供了按用户的要求分配资源的能力，下面我们就来介绍这个功能。

 

    SQLServer资源调控器分成三个部分：资源池、负载组和分类器函数；资源池为我们提供了将资源（CPU、Memory等）划分到不同的载体中，负载组承载负载并

将这些负载映射到资源池，分类器函数将不同的会话映射到不同的负载组中。

 

资源池：

 08提供了两种预先定义好的资源池

 内部池：内部池只用于SQLServer数据库引擎，系统管理员不能改变和设置；

 默认池：默认池用于没有分配资源池的各种负载，因此，如果你不指定资源调控器，全部负载将使用默认池。默认池也不能改变或删除，但是可以修改它的资源上

            下限。

 资源池上下限要求：

    各个资源池的下限之和不能超过100%，因为SQLServer会尽力满足每个下限；

    上限可以设置为下限和100%之间的任意值。

以下是关于资源池的基本操作：

<span>--</span><span>创建资源池<br> </span><span>Create</span> Resource Pool UserQueries <span>with</span>(max_cpu_percent<span>=</span><span>100</span><span>)

</span><span>--</span><span>删除资源池<br></span><span>drop</span> Resource Pool UserQueries

 

负载组：

     负载组可以让管理员轻松地监控资源使用情况，在不同的资源池之间移动某类负载。

     负载组被映射到资源池上，一个资源池可以有零个或更多负载组，一个负载组为一组用户会话提供一个桶。

<span>--</span><span>创建负载组</span>
<span>Create</span> WorkLoad <span>Group</span><span> DailyExecReports USING UserQueries;

</span><span>--</span><span>删除负载组</span>
<span>drop</span> WorkLoad <span>Group</span> DailyExecReports

 

分类器函数：

     分类器函数将接入的会话分类，并为会话的请求和查询分配一个负载组。你可以根据连接串中的任意属性（IP地址/应用程序名、用户名等）分别分配组。

按以下条件分配组：

•   一个用户接入并使用SAP_Login登录名，则为他分配SAPUsers负载组；
•   一个用户接入程序名是SSMS，则让它成为AdhocAdmin负载组的一员；
•   一个用户是ReportUsers组一员，则让它成为DaliyExecReports负载组一员；
•   一个用户以共享内存连接，并在NightlyAdmin用户组中，则分配NightlyMaintanceTask组。

<span>--</span><span>创建资源池</span>
<span>Create</span> Resource Pool AdminQueries <span>with</span>(max_cpu_percent<span>=</span><span>100</span><span>)
</span><span>Create</span> Resource Pool UserQueries <span>with</span>(max_cpu_percent<span>=</span><span>100</span><span>)

</span><span>--</span><span>创建负载组</span>
<span>Create</span> WorkLoad <span>Group</span><span> NightlyMaintenanceTasks USING AdminQueries;
</span><span>Create</span> WorkLoad <span>Group</span><span> AdhocAdmin USING AdminQueries;
</span><span>Create</span> WorkLoad <span>Group</span><span> SAPUsers USING UserQueries;
</span><span>Create</span> WorkLoad <span>Group</span><span> DailyExecReports USING UserQueries;

</span><span>--</span><span>创建分类器函数</span>
    <span>USE</span><span> master
    </span><span>GO</span>
    <span>create</span> <span>FUNCTION</span><span> class_func_1()
    </span><span>Returns</span> sysname <span>with</span><span> schemabinding
    </span><span>begin</span>
      <span>Declare</span> <span>@val</span><span> sysname
      </span><span>--</span><span>Handle workload groups defined by login names</span>
      <span>IF</span> <span>SUSER_SNAME</span>()<span>=</span><span>'</span><span>SAP_Login</span><span>'</span>
        <span>begin</span>
          <span>SET</span> <span>@val</span><span>=</span><span>'</span><span>SAPUsers</span><span>'</span><span>;
          </span><span>Return</span> <span>@val</span><span>;
        </span><span>end</span>
        
      <span>IF</span> <span>APP_NAME</span>() <span>like</span> <span>'</span><span>Microsoft SQL Server Management Studio%</span><span>'</span>
        <span>begin</span>
          <span>Set</span> <span>@val</span><span>=</span><span>'</span><span>AdhocAdmin</span><span>'</span><span>;
          </span><span>Return</span> <span>@val</span><span>;
        </span><span>end</span>
      
      <span>IF</span> <span>IS_MEMBER</span>(<span>'</span><span>ReportUsers</span><span>'</span>)<span>=</span><span>1</span>
        <span>begin</span>
          <span>Set</span> <span>@val</span><span>=</span><span>'</span><span>DailyExecReports</span><span>'</span><span>;
          </span><span>Return</span> <span>@val</span><span>;
        </span><span>end</span>
        
      <span>IF</span> CONNECTIONPROPERTY(<span>'</span><span>net_transport</span><span>'</span>)<span>=</span><span>'</span><span>Shared memory</span><span>'</span> <span>and</span> <span>IS_MEMBER</span>(<span>'</span><span>NightlyAdmin</span><span>'</span>)<span>=</span><span>1</span>
        <span>begin</span>
          <span>Set</span> <span>@val</span><span>=</span><span>'</span><span>NightlyMaintenanceTasks</span><span>'</span><span>;
          </span><span>Return</span> <span>@val</span><span>;
        </span><span>end</span>
        
      <span>Return</span> <span>@val</span><span>;
    </span><span>end</span>

绑定分类器函数：

<span>--</span><span>将分类器函数绑定到资源调控器上</span>
  <span>Alter</span> Resource Governor <span>With</span>(Classifier_Function<span>=</span>dbo.class_func_1);

启用和禁用分类器函数：

<span>--</span><span>启用</span>
 <span>ALter</span> Resource Governor <span>Reconfigure</span><span>;
</span><span>--</span><span>禁用</span>
 <span>ALTER</span> RESOURCE GOVERNOR DISABLE;

 

测试：

现在我们分别使用SAP_Login和sysadmin用户调用此脚本

<span>--</span><span>测试脚本(分别使用SAP_Login和sysadmin用户调用此脚本）</span>
  <span>set</span> nocount <span>on</span> 
  <span>Declare</span> <span>@i</span> <span>int</span><span>=</span><span>100000000</span><span>;
  </span><span>Declare</span> <span>@s</span> <span>varchar</span>(<span>100</span>),<span>@count</span> <span>int</span><span>;
   </span><span>While</span> <span>@i</span><span>></span><span>0</span>
   <span>begin</span>
    <span>Select</span> <span>@s</span><span>=</span><span>@@VERSION</span><span>;
    </span><span>select</span> <span>@count</span><span>=</span><span>COUNT</span>(<span>0</span>) <span>from</span><span> sys.sysobjects 
    </span><span>set</span> <span>@i</span><span>=</span><span>@i</span><span>-</span><span>1</span><span>;
   </span><span>end</span>

 

通过性能计数器查看资源分配：

 我们可以选择性能计数器的资源统计：SQL Server:Resource Pools Stats;

 我们先将资源池按一比一的比例分配：

<span>Create</span> Resource Pool AdminQueries <span>with</span>(max_cpu_percent<span>=</span><span>100</span><span>)
</span><span>Create</span> Resource Pool UserQueries <span>with</span>(max_cpu_percent<span>=</span><span>100</span>)

 运行测试脚本，显示的CPU利用率图如下

 现在将资源分配做如下调整：

<span>Create</span> Resource Pool AdminQueries <span>with</span>(max_cpu_percent<span>=</span><span>10</span><span>)
</span><span>Create</span> Resource Pool UserQueries <span>with</span>(max_cpu_percent<span>=</span><span>90</span>)

 再次运行测试脚本，显示的CPU利用率图如下

可以看到，当我们调整资源后，两个Session中运行同样的脚本，它们所使用的资源差别很大，这样就达到了根据不同的应用分配不同的资源的目的。

 

DMV查看资源池：

<span>--</span><span>查看Session所在的资源池</span>
    <span>select</span><span> s.session_id,s.login_name ,s.program_name,s.group_id,g.name 
    </span><span>from</span><span> 
    sys.dm_exec_sessions s </span><span>join</span><span> sys.dm_resource_governor_workload_groups g
    </span><span>on</span> s.group_id<span>=</span><span>g.group_id
    </span><span>where</span> session_id<span>></span><span>50</span>

<span>--</span><span>查看资源池情况</span>
    <span>select</span> <span>*</span> <span>from</span> sys.dm_resource_governor_resource_pools

可以看到，我们创建的两个资源池（还有两个是系统资源池和默认资源池），而且不同的Session对应到了不同的资源池中。