淺談數(shù)據(jù)庫內(nèi)存和CPU資源控制及資源管理器的應(yīng)用

數(shù)據(jù)庫系統(tǒng)的資源是指內(nèi)存和CPU（處理器）資源，擁有資源的多寡，決定了數(shù)據(jù)查詢的性能。當(dāng)一個(gè)SQL Server實(shí)例上，擁有多個(gè)獨(dú)立的工作負(fù)載（workload）時(shí)，使用資源管理器（Resource Governor），能夠?qū)崿F(xiàn)系統(tǒng)資源在邏輯上的隔離，解決在一臺(tái)SQL Server實(shí)例上，管理多用戶工作負(fù)載的需求。資源管理器允許數(shù)據(jù)庫管理員（DBA）通過編程設(shè)置資源池，配置資源池?fù)碛匈Y源的上限，資源池是每一個(gè)請(qǐng)求能夠使用的資源，這樣設(shè)置之后，強(qiáng)制系統(tǒng)在處理用戶發(fā)送的請(qǐng)求（Requsts）時(shí)所耗費(fèi)的CPU 和 Memory資源的數(shù)量不能超過限制，在一定程度上，限制用戶能夠使用的資源數(shù)量，隔離了失控（runaway）的查詢對(duì)系統(tǒng)的影響。對(duì)于SQL Server 2012來說，用戶能夠基于工作負(fù)載，實(shí)現(xiàn)CPU資源的完全隔離，并能設(shè)置CPU資源使用量的硬上限（CAP Usage，Hard Limit）。在一個(gè)多用戶、高并發(fā)的SQL Server實(shí)例上，管理員使用Resource Governor，控制不同工作負(fù)載對(duì)內(nèi)存和CPU資源的使用量，使不同的應(yīng)用程序在資源的使用上相互隔離，使系統(tǒng)性能得到可預(yù)測(cè)性的控制和保證。

一，資源管理器的基本構(gòu)成

Resource Governor的可編程部分由三部分組成：Resource Pool，Workload Group 和 Classifier Function，每個(gè)部分實(shí)現(xiàn)不同的功能。

1，資源池（Resource Pool）

在SQL Server實(shí)例中，資源隔離的基本單位是Resource Pool，正如其名，該對(duì)象是資源池，在創(chuàng)建Resource Pool時(shí)，指定該P(yáng)ool擁有的CPU和Memory資源的數(shù)量范圍。在SQL Server 2012版本中，最多可以創(chuàng)建62個(gè)用戶自定義的Resource Pool。SQL Server內(nèi)置了兩個(gè)ResourcePools：internal用于系統(tǒng)Task，用戶不能配置；default是默認(rèn)的Resource Pool，用于任何沒有指定Resource Pool的Request；
2，負(fù)載分組（Workload Group）

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

SQL Server內(nèi)置兩個(gè)負(fù)載分組：internal和default，關(guān)聯(lián)到相應(yīng)的internal和default資源池，internal負(fù)載分組用于系統(tǒng)Task，SQL Server將沒有被分類函數(shù)顯式指定負(fù)載分組的Request劃分到default 分組中。

3，分類函數(shù)（Classifier Function）

分類函數(shù)根據(jù)指定的規(guī)則（Rule），例如，根據(jù)Login，應(yīng)用程序名稱，數(shù)據(jù)庫名字等屬性，將接收的Request分配（路由）到不同的負(fù)載分組中，可以指定用戶定義的負(fù)載分組或default負(fù)載分組。

4，處理流程

Resource Governor各個(gè)部分相互配合，控制內(nèi)存和CPU資源的使用：Classification將SQL Server實(shí)例接收到的Requests進(jìn)行分類，劃分到不同的負(fù)載組中，負(fù)載組與之關(guān)聯(lián)的Resource Pool中包含的CPU和內(nèi)存資源來處理Request，Resource Governor的處理流程如下圖：

Resource Pool 是SQL Server實(shí)例中物理資源的子集，由于位于同一個(gè)實(shí)例上的所有數(shù)據(jù)庫共享該實(shí)例的所有資源，因此，最好將Resource Pool的三個(gè)組成對(duì)象創(chuàng)建在master 數(shù)據(jù)庫中。

二，創(chuàng)建和使用資源管理器

資源管理器默認(rèn)是關(guān)閉的，在使用之前，必須啟用。用戶可以通過SSMS啟用資源管理器，展開Management，選擇Resource Governor，右擊彈出快捷菜單，點(diǎn)擊“Enable”，啟用資源管理器：

或者，使用TSQL命令，重新配置，啟用資源管理器：

ALTER RESOURCE GOVERNOR RECONFIGURE;
GO

1， 創(chuàng)建自定義的資源池（Resource Pool）

SQL Server 內(nèi)置兩個(gè)資源池：internal和default，internal是系統(tǒng)內(nèi)部使用的，default是默認(rèn)的資源池，當(dāng)一個(gè)查詢請(qǐng)求沒有指定資源池時(shí)，使用默認(rèn)的資源池。為了管理用戶不同的工作負(fù)載，DBA需要根據(jù)業(yè)務(wù)需求創(chuàng)建自定義的資源池。在創(chuàng)建資源池時(shí)，需要注意，資源池的選項(xiàng)都是比例關(guān)系，所有資源的下限（MIN_MEMORY_PERCENT或MIN_CPU_PERCENT）的加和不能超過系統(tǒng)的物理資源總量，即不能超過100。

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

CAP_CPU_PERCENT選項(xiàng)：設(shè)置資源池?fù)碛蠧PU資源的硬上限，任何Workload Group使用的CPU數(shù)量不可能超過該上限，而資源池使用的CPU資源有可能超過MAX_CPU_PERCENT 選項(xiàng)指定的比例。

2，創(chuàng)建工作負(fù)載組（Workload Group）

創(chuàng)建工作負(fù)載分組，通過using子句關(guān)聯(lián)該分組能夠使用的資源池，一個(gè)工作負(fù)載分組只能關(guān)聯(lián)一個(gè)資源池，一個(gè)資源池可以服務(wù)一個(gè)或多個(gè)工作負(fù)載分組。

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選項(xiàng)：有三個(gè)可選值：Low，Medium和High，默認(rèn)值是Medium，該選項(xiàng)用于指定該Workload Group在關(guān)聯(lián)的資源池（Resource Pool）中相對(duì)的重要性，由于同一個(gè)可以Resource Pool關(guān)聯(lián)多個(gè)Workload Group，當(dāng)一個(gè)工作負(fù)載組（Workload Group）的Importance數(shù)值高于其他工作負(fù)載組（Workload Group）時(shí)，在競(jìng)爭(zhēng)Resource Pool中的資源時(shí)，更容易獲勝。當(dāng)前工作負(fù)載組的IMPORTANCE的選項(xiàng)不會(huì)使用其他資源池，也不會(huì)影響其他資源組中的工作負(fù)載。

REQUEST_MAX_MEMORY_GRANT_PERCENT：默認(rèn)值是25，用于指定工作負(fù)載組能夠從資源組中申請(qǐng)到的最大授予內(nèi)存（Grant Memory）資源，由于授予內(nèi)存跟執(zhí)行的SORT和HASH JOIN操作有關(guān)，如果設(shè)置REQUEST_MAX_MEMORY_GRANT_PERCENT為0，這意味著，SQL Server引擎將不允許執(zhí)行排序和哈希鏈接操作。

MAX_DOP：用于指定并發(fā)查詢的最大并發(fā)度（Degree of Parallelism），默認(rèn)值是0，表示使用全局配置（Global Setting）。

3，創(chuàng)建分類函數(shù)（Classifier Function）

分類函數(shù)根據(jù)特定的規(guī)則（Rule）把接收的request路由到不同的資源池中，用戶應(yīng)該把分類函數(shù)創(chuàng)建在master數(shù)據(jù)庫中，這樣，函數(shù)的作用域就是整個(gè)SQL Server實(shí)例，分類函數(shù)的執(zhí)行上下文是master 數(shù)據(jù)庫；如果分類函數(shù)返回NULL，default或不存在的工作負(fù)載組的名稱，那么查詢被路由導(dǎo)default資源池：

If the user-defined function returns NULL, default, or the name of non-existent group the session is given the default workload group context. The session is also given the default context if the function fails for any reason.

The function should be defined with server scope (master database).

例如，根據(jù)用戶名稱，將Request劃分到wg_20Percent負(fù)載分組。默認(rèn)情況下，任何沒有指定Workload Group的Request，都分發(fā)到Default資源池。在實(shí)際產(chǎn)品環(huán)境中，也可以使用APP_NAME()獲取應(yīng)用程序名字，或者SUSER_NAME()獲取用戶的登陸（Login）名稱，根據(jù)應(yīng)用程序或登陸名稱劃分負(fù)載分組。

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;

常用于分類函數(shù)（Classifier Function）中的函數(shù)如下，這些函數(shù)的執(zhí)行上下文是整個(gè)Server：

APP_NAME()：返回當(dāng)前Session所在的應(yīng)用程序的名稱

HOST_NAME()：返回主機(jī)名稱；

SUSER_NAME()：返回登陸名；

IS_SVROLEMEMBER()：當(dāng)用戶是特定的服務(wù)器角色時(shí)，返回True；

注意：列表中函數(shù)執(zhí)行的上下文和分類函數(shù)的上下文相同，如果分類函數(shù)被創(chuàng)建在master數(shù)據(jù)庫，那么分類函數(shù)的查詢的范圍是在master數(shù)據(jù)庫。

執(zhí)行上下文是單個(gè)數(shù)據(jù)庫的函數(shù)是：

IS_MEMBER('role | group')

IS_ROLEMEMBER( 'role' [ , 'database_principal' ] )

函數(shù)：IS_MEMBER('role | group')，用于檢測(cè)當(dāng)前的用戶（User）是否屬于當(dāng)前數(shù)據(jù)庫的role或windows group。通常情況下，我們把分類函數(shù)創(chuàng)建在master系統(tǒng)數(shù)據(jù)庫中，這會(huì)導(dǎo)致函數(shù)的執(zhí)行上下文是master數(shù)據(jù)庫，而不是用戶數(shù)據(jù)庫，而master數(shù)據(jù)庫中可能不存在windows group（數(shù)據(jù)庫用戶User），這會(huì)導(dǎo)致函數(shù)無法起到分類的作用：

IS_MEMBERdoesn’t work here as it’ll be firing in the context of the master db, not the user database you’re interested in.

為了解決這個(gè)問題，必須在master數(shù)據(jù)庫中創(chuàng)建相應(yīng)的windows group（數(shù)據(jù)庫用戶User）。

4，啟動(dòng)資源管理器（Resource Governor）

為了啟動(dòng)資源管理器(Resource Governor)，首先配置資源管理器使用的分類函數(shù)，把資源管理器和分類函數(shù)關(guān)聯(lián)起來；然后，重新配置資源管理器，啟動(dòng)Resource Governor。啟動(dòng)之后，SQL Server引擎使用分類函數(shù)對(duì)把SQL Server實(shí)例接收的查詢請(qǐng)求（Requests），分配到不同的負(fù)載分組中。

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

5，修改分類函數(shù)

修改分類函數(shù)，需要先禁用資源管理器，然后解除分類函數(shù)和資源管理器的關(guān)聯(lián)關(guān)系，然后修改分類函數(shù)，把資源管理器和分類函數(shù)重新綁定：

ALTER RESOURCE GOVERNOR
DISABLE;
GO
ALTER RESOURCE GOVERNOR 
WITH (CLASSIFIER_FUNCTION=NULL);
GO

ALTER FUNCTION dbo.rgClassifierFunction_20Percent() 
RETURNS sysname
WITH SCHEMABINDING
AS
BEGIN
    ....
END;
GO

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

三，查看資源管理器

通過以下三個(gè)視圖查看資源管理器的元數(shù)據(jù)：

sys.resource_governor_configuration
sys.resource_governor_workload_groups
sys.resource_governor_resource_pools

通過以下三個(gè)DMV查看資源管理器的統(tǒng)計(jì)數(shù)據(jù)：

sys.dm_resource_governor_configuration
sys.dm_resource_governor_workload_groups
sys.dm_resource_governor_resource_pools

DMVsys.dm_exec_requests 和 sys.dm_exec_sessions 中包含group_id字段，用于表示當(dāng)前session或request處于的工作負(fù)載分組。

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

編輯：hfy