控制 Memory 和 CPU 資源的使用

Resource Governor的出現，解決了在一臺SQL Server實例上，管理多用戶工作負載和資源隔離的需求，它允許管理員限制系統處理Requsts時所耗費的CPU 和 Memory資源的數量，在一定程度上，限制和隔離了runaway查詢。對於SQL Server 2012，用戶能夠基於工作負載，實現CPU資源的完全隔離，並能設置CPU資源使用量的硬上限（CAP Usage，Hard Limit）。在一個多用戶，高併發的SQL Server實例上，管理員使用Resource Governor，控制不同工作負載對記憶體和CPU資源的使用量，使不同的應用程式在使用的資源上相互隔離，使系統性能得到可預測性的控制和保證。

一，基本實現（fundamental implementation）

Resource Governor的可編程部分由三部分組成：Resource Pool，Workload Group 和 Classifier Function，每個部分實現不同的功能。

1，資源池（Resource Pool）

在SQL Server實例中，資源隔離的基本單位是Resource Pool，正如其名，該對象是資源池，在創建Resource Pool時，指定該Pool擁有的CPU和Memory資源的數量範圍。在SQL Server 2012版本中，最多可以創建62個用戶自定義的Resource Pool。SQL Server內置了兩個Resource Pools：internal用於系統Task，用戶不能配置；default是預設的Resource Pool，用於任何沒有指定Resource Pool的Request；
2，負載分組（Workload Group）

Workload Group是邏輯上的實體，用於表示一個或多個工作負載。實際上，一個工作負載是SQL Server實例接收到的一個查詢請求（Request），通過Classifier Function將多個具有共同屬性的Requests劃分到相同的Workload Group中。每一個Resource Pool服務於一個或多個工作負載分組，這就是說，這些工作負載分組能夠共用同一個Resource Pool中擁有的資源。

SQL Server內置兩個負載分組：internal和default，關聯到相應的internal和default資源池，internal負載分組用於系統Task，SQL Server將沒有被分類函數顯式指定負載分組的Request劃分到default 分組中。

3，分類函數（Classifier Function）

分類函數根據Login，應用程式名稱，資料庫名字等屬性，將Request劃分到不同的負載分組中，可以指定用戶定義的負載分組或default負載分組。

4，處理流程

Resource Governor各個部分相互配合，控制記憶體和CPU資源的使用：Classification將SQL Server實例接收到的Requests進行分類，劃分到不同的負載組中，負載組與之關聯的Resource Pool中包含的CPU和記憶體資源來處理Request，Resource Governor的處理流程如下圖： 

Resource Pool 是SQL Server實例中物理資源的子集，由於位於同一個實例上的所有資料庫共用該實例的所有資源，因此，最好將Resource Pool的三個組成對象創建在master 資料庫中。

二，使用示例

1， 創建Resource Pool

CREATE RESOURCE POOL rp_20Percent
WITH 
(
     MIN_CPU_PERCENT = 0,
     MAX_CPU_PERCENT = 20,
     CAP_CPU_PERCENT = 20,
     AFFINITY SCHEDULER = auto,
     MIN_MEMORY_PERCENT = 0,
     MAX_MEMORY_PERCENT = 20
);

CAP_CPU_PERCENT選項設置資源池擁有CPU資源的硬上限，任何Workload Group使用的CPU數量不可能超過該上限，而資源池使用的CPU資源有可能超過 MAX_CPU_PERCENT 選項指定的比例。

2，創建Workload Group

創建工作負載分組，通過using子句關聯該分組能夠使用的資源池，一個工作負載分組只能關聯一個資源池，一個資源池服務一個或多個工作負載分組。

CREATE WORKLOAD GROUP wg_20Percent
WITH
(
    IMPORTANCE = MEDIUM,
    REQUEST_MAX_MEMORY_GRANT_PERCENT=20,
    REQUEST_MAX_CPU_TIME_SEC=0,
    REQUEST_MEMORY_GRANT_TIMEOUT_SEC=0,
    MAX_DOP=0,
    GROUP_MAX_REQUESTS=0
)
USING rp_20Percent;

IMPORTANCE選項： 該選項指定該Workload Group在Resource Pool中相對的重要性，由於同一個Resource Pool關聯多個Workload Group，Importance數值高的Workload Group，在競爭Resource Pool中的資源時，更容易獲勝。該選項有三個可選值：Low，Medium和High，預設值是Medium。

3，創建 Classifier Function

該分類函數根據用戶名稱，將Request劃分到wg_20Percent負載分組，預設情況下，任何沒有指定Workload Group的Request，都使用Default資源池。在實際產品環境中，也可以使用APP_NAME()獲取應用程式名字，根據應用程式劃分負載分組。

CREATE FUNCTION dbo.rgClassifierFunction_20Percent() 
RETURNS sysname
WITH SCHEMABINDING
AS
BEGIN
    DECLARE @Workload_Group_Name AS sysname
      IF (SUSER_NAME() = 'USER_READONLY')
          SET @workload_group_name = 'wg_20Percent'
    RETURN @workload_group_name
END;

4，啟動Resource Governor

首先，配置Resource Governor使用的分類函數；然後，執行重新配置命令，啟動Resource Governor，SQL Server使用分類函數對SQL Server實例接收的Requests，劃分到不同的負載分組。

ALTER RESOURCE GOVERNOR 
WITH (CLASSIFIER_FUNCTION=dbo.rgClassifierFunction_20Percent);
GO
ALTER RESOURCE GOVERNOR
RECONFIGURE;
GO

MSDN示例腳本：

CREATE FUNCTION dbo.rgclassifier_v1() 
RETURNS sysname 
WITH SCHEMABINDING
AS
BEGIN
-- Declare the variable to hold the value returned in sysname.
    DECLARE @grp_name AS sysname
-- If the user login is 'sa', map the connection to the groupAdmin  workload group. 
    IF (SUSER_NAME() = 'sa')
        SET @grp_name = 'groupAdmin'
-- Use application information to map the connection to the groupAdhoc workload group.
    ELSE IF (APP_NAME() LIKE '%MANAGEMENT STUDIO%')
        OR (APP_NAME() LIKE '%QUERY ANALYZER%')
            SET @grp_name = 'groupAdhoc'
-- If the application is for reporting, map the connection to the groupReports workload group.
    ELSE IF (APP_NAME() LIKE '%REPORT SERVER%')
        SET @grp_name = 'groupReports'
-- If the connection does not map to any of the previous groups, put the connection into the default workload group.
    ELSE
        SET @grp_name = 'default'
    RETURN @grp_name
END;
GO

-- Register the classifier user-defined function and update the 
-- the in-memory configuration.
ALTER RESOURCE GOVERNOR 
WITH (CLASSIFIER_FUNCTION=dbo.rgclassifier_v1);
GO
ALTER RESOURCE GOVERNOR RECONFIGURE;
GO

參考文檔：

Resource Governor

Resource Governor in SQL Server 2012

使用sql server 2008 資源調控器限制指定用戶查詢所使用CPU資源

CREATE RESOURCE POOL (Transact-SQL)

CREATE WORKLOAD GROUP (Transact-SQL)

ALTER RESOURCE GOVERNOR (Transact-SQL)