原文: 配置SQL Server 2008的資源調(diào)控器實(shí)現(xiàn)負(fù)載均衡

轉(zhuǎn)自：http://www.ithov.com/server/93267.shtml

1、為什么引入“資源調(diào)控器”?

在SQL Server 2008之前，數(shù)據(jù)庫(kù)引擎曾嘗試為所有并發(fā)用戶提供均衡的性能，這使我們幾乎無法控制分配給用戶或工作負(fù)載的優(yōu)先級(jí)，而且隨著并發(fā)登錄的增加會(huì)雪上加霜。“資源調(diào)控器”有助于防止性能問題，并識(shí)別正在使用的應(yīng)用程序。“資源調(diào)控器”可以控制分配給會(huì)話的CPU和內(nèi)存，并進(jìn)一步設(shè)置應(yīng)用程序能夠使用的最大和最小的CPU百分比和內(nèi)存，以及允許的最大并行度。盡管資源調(diào)控器目前還有一些不足之處，但它對(duì)于處理許多常見的資源問題已經(jīng)足夠了。

2、創(chuàng)建“資源池”

　　配置“資源調(diào)控器”是從創(chuàng)建“資源池”開始的。在SQL Server 2008中使用CREATE RESOURCE pool命令來創(chuàng)建資源池，從而通過它來分配會(huì)話，并設(shè)置其占用系統(tǒng)資源的最大和最小百分比。

　　比如使用下面命令以根據(jù)CPU百分比限制來創(chuàng)建3個(gè)資源池：

    CREATE RESOURCE pool LowPriorityAppspool WITH (MAX_CPU_PERCENT = 20); 

　　CREATE RESOURCE pool MediumPriorityAppspool WITH (MAX_CPU_PERCENT = 60); 

　　CREATE RESOURCE pool HighPriorityAppspool WITH (MAX_CPU_PERCENT = 100);
  

這些命令中使用的CPU百分比不是硬性限制。例如，如果操作系統(tǒng)上只有一個(gè)低優(yōu)先級(jí)應(yīng)用程序在運(yùn)行，那么它將能夠使用整個(gè)CPU。基于CPU百分比的資源調(diào)控只有在并行需求重疊時(shí)才會(huì)發(fā)揮作用。

　　另外，在“資源調(diào)控器”中，最多可以建立2O個(gè)資源池，其中18個(gè)可以進(jìn)行配置。在另外兩個(gè)資源池中，一個(gè)分配給內(nèi)部進(jìn)程，如清理類操作;另一個(gè)供沒有分配給其它池的會(huì)話使用的默認(rèn)池。因此，不應(yīng)過早創(chuàng)建太多資源池，應(yīng)該謹(jǐn)慎考慮哪些池是必要的。

3、創(chuàng)建工作負(fù)載組

　　對(duì)現(xiàn)有資源進(jìn)行分組以后，我們需要?jiǎng)?chuàng)建創(chuàng)建工作負(fù)載組以實(shí)現(xiàn)對(duì)分配給這些資源池的應(yīng)用程序分組。在SQL Server 2008中使用CREATE WORKLOAD GROUP命令創(chuàng)建工作負(fù)載組。

　　下面列舉幾個(gè)CREATE WORKLOAD GROUP命令應(yīng)用實(shí)例：

    CREATE WORKLOAD GROUP 

　　UnidentnifiedApplications 

　　USING LowPriorityAppspool;



　　CREATE WORKLOAD GROUP 

　　WellBehavedAccessApplications 

　　USING MediumPriorityAppspool;



　　CREATE WORKLOAD GROUP 

　　PoorlyBehavedAccessApplications 

　　USING LowPriorityAppspool;



　　CREATE WORKLOAD GROUP 

　　WellBehavedExcelApplications 

　　USING MediumPriorityAppspool;



　　CREATE WORKLOAD GROUP 

　　CriticalCorporateApplications 

　　USING HighPriorityAppspool;
  

上面實(shí)例我們創(chuàng)建了5個(gè)工作負(fù)載組，它們被分配給3個(gè)資源池。Access和Excel應(yīng)用程序被分配給不同的資源池，這樣，我們可以在未來根據(jù)一個(gè)資源池對(duì)分配給另一個(gè)資源池的資源進(jìn)行更改。為性能較差的Access應(yīng)用程序和無法識(shí)別的其它應(yīng)用程序分配較少的資源。我們?yōu)橐呀?jīng)識(shí)別的對(duì)工作至關(guān)重要的應(yīng)用程分配具有最高的資源分配的優(yōu)先級(jí)。

4、查看“資源調(diào)控器”配置

　　創(chuàng)建了工作負(fù)載組并將其分配給資源池以后，它們不會(huì)立即開始工作。要確定還需要進(jìn)行什么配置才能使“資源調(diào)控器”正常運(yùn)行，可以查看當(dāng)前的“資源調(diào)控器”配置，這些信息在一個(gè)新的動(dòng)態(tài)管理視圖中顯示。

　　(1).查看“資源調(diào)控器”配置信息

　　執(zhí)行命令：

    SELECT * FROM sys.dm_resource_governor_configuration;
  

命令執(zhí)行完畢就會(huì)輸出“資源調(diào)控器”的配置信息。需要注意的是：此輸出信息是在SQL Server Management Studio(SSMS)的Results選項(xiàng)卡中顯示的。最左邊一列顯示的值1是輸出的行號(hào)。

　　此輸出信息告訴我們兩個(gè)信息：

　　第一、is_reconfiguration_pending標(biāo)記表示在你所做的更改生效之前，需要執(zhí)行命令：ALTER RESOURCE GOVERNOR RECONFIGURE。運(yùn)行這條命令之后，is_reconfiguration_pending的值就變成0。

　　第二、classifier_function_id的值為0，表示沒有分配分類器函數(shù)。分類器函數(shù)用于決定一個(gè)會(huì)話應(yīng)該放在哪個(gè)工作負(fù)載組中。

　　(2).查看資源池配置信息

　　執(zhí)行下面的命令來查看資源池配置：

    SELECT * FROM sys.dm_resource_governor_resource_pools;
  

命令執(zhí)行完畢后會(huì)看到資源池的相關(guān)信息。需要注意的是：，在執(zhí)行重新配置命令之前，這個(gè)輸出信息中只顯示內(nèi)部資源池和默認(rèn)資源池。

　　可以使用下面這個(gè)命令查看工作負(fù)載池配置：

    SELECT * FROM sys.dm_resource_governor_workload_groups;
  

5、應(yīng)用程序?qū)崿F(xiàn)分類

　　配置“資源調(diào)控器”的最后一步是確定想要控制的會(huì)話。這可以利用分類器函數(shù)來實(shí)現(xiàn)，分類器將會(huì)話分配給特定的工作負(fù)載組。

　　(1).應(yīng)用APP_NAME()函數(shù)分類

　　分類器函數(shù)必須返回一個(gè)SYSNAME類型的值，這個(gè)值當(dāng)前是NVARCHAR(128)的別稱。對(duì)于本文來說，我們根據(jù)應(yīng)用程序名稱函數(shù)(APP_NAME())來決定會(huì)話應(yīng)該歸入哪一個(gè)工作負(fù)載組，這個(gè)函數(shù)返回應(yīng)用程序的連接字符串中指定的值。筆者特別喜歡選擇這個(gè)選項(xiàng)，因?yàn)樗С謱?duì)正在運(yùn)行的應(yīng)用程序進(jìn)行分類，在對(duì)會(huì)話進(jìn)行分類時(shí)還有其它幾個(gè)選項(xiàng)可供選擇我們后面再說。

　　構(gòu)建應(yīng)用程序時(shí)通常不會(huì)在連接字符串中指定應(yīng)用程序名稱，但把應(yīng)用程序名稱添加到連接字符串或在應(yīng)用程序配置文件中指定該值是很重要的。這種方法在“資源調(diào)控器”之外也很有用，因?yàn)閼?yīng)用程序名稱會(huì)出現(xiàn)在SQL Server Profiler創(chuàng)建的跟蹤記錄中，能夠用來篩選跟蹤記錄中的事件。通過連接字符串訪問應(yīng)用程序名稱也有助于在隔離與應(yīng)用程序相關(guān)的問題時(shí)跟蹤對(duì)SQL Server的訪問。不過，有些設(shè)計(jì)不太好的應(yīng)用程序具有硬編碼的連接字符串，這時(shí)它們不適合使用這種方法。硬編碼連接字符串是一種應(yīng)該避免的做法。

　　在使用中筆者還發(fā)現(xiàn)，按名稱對(duì)應(yīng)用程序進(jìn)行分類是識(shí)別連接服務(wù)器的未知應(yīng)用程序的一個(gè)好的起點(diǎn)。在這種情況下，“資源調(diào)控器”的好處在于，未知應(yīng)用程序?qū)⒈环旁诘蛢?yōu)先級(jí)的資源池里，隨后我們能夠快速識(shí)別正在使用的應(yīng)用程序。這項(xiàng)功能可幫助你確定誰(shuí)正在使用你的系統(tǒng)。

　　(2).其他分類函數(shù)和屬性

　　除了使用APP_NAME()函數(shù)可進(jìn)行會(huì)話分類外，也可以使用許多其它函數(shù)來實(shí)現(xiàn)，比如HOST_NAME()、SUSER_NAME()、SUSER_SNAME()、IS_SRVROLEMEMBER()和IS_MEMBER()。

　　如果決定使用HOST_NAME()或APP_NAME()作為分級(jí)器函數(shù)，一定要注意用戶可能更改這些函數(shù)。不過在實(shí)際中，筆者發(fā)現(xiàn)APP_NAME()很好用。

　　此外，在決定如何分類時(shí)，還可以使用一些屬性。L0GINPROPERTY()函數(shù)現(xiàn)在包含兩個(gè)可在分類函數(shù)中使用的屬性(DefaultDatabase和DefauItLanguage)。而且L0GINPROPERTY()函數(shù)提供了對(duì)連接所用網(wǎng)絡(luò)協(xié)議的訪問，還支持訪問驗(yàn)證方案、本地IP地址、TCP端口和客戶端IP地址。ORIGINAL_DB_NAME()函數(shù)也很有用，因?yàn)樗梢苑祷貢?huì)話在首次連接時(shí)提供的數(shù)據(jù)庫(kù)名稱，而不是默認(rèn)數(shù)據(jù)庫(kù)。這些屬性支持基于任何這類值進(jìn)行分類。例如，可以基于用戶通常使用的數(shù)據(jù)庫(kù)把連接分配給工作負(fù)載組。

　　(3).分類前必要的測(cè)試

　　在使用分類器函數(shù)之前對(duì)其進(jìn)行測(cè)試是很重要的，否則系統(tǒng)可能無響應(yīng)。例如，可以在SSMS中執(zhí)行以下命令來測(cè)試APP_NAME()分類器函數(shù)：

SELECT?APP_NAME();

　　因?yàn)榉诸惼骱瘮?shù)無法識(shí)別SSMS，因此執(zhí)行命令：

    SELECT dbo.UserClassifier();
  

如果不修改分類器函數(shù)而直接啟用它，SSMS將在低優(yōu)先級(jí)應(yīng)用程序資源池中運(yùn)行。也可以使用分類器函數(shù)在數(shù)據(jù)庫(kù)表中查找工作負(fù)載組名稱，而不是將所有應(yīng)用程序名稱硬編碼到該函數(shù)中。通常，在訪問數(shù)據(jù)庫(kù)表來查找工作負(fù)載組時(shí)，性能不會(huì)受到太大影響，因?yàn)閿?shù)據(jù)庫(kù)表不會(huì)很大，能夠被快速緩存，并且只有在建立連接之后才能使用該函數(shù)制定分類決策。

　　現(xiàn)在可以使用以下命令實(shí)現(xiàn)分類器函數(shù)：

    　ALTER RESOURCE GOVERNOR 

　　WITH (CLASSIFIER_FUNCTION = dbo.UserCIassifier);
  

然后必須重新配置“資源調(diào)控器”，使掛起的配置生效。這可以使用以下命令來完成：

    ALTER RESOURCE GOVERNOR RECONFIGURE;
  

至此，“資源調(diào)控器”配置就完成了。然后需要確保“資源調(diào)控器”能夠按預(yù)期工作。如果未對(duì)分類器函數(shù)進(jìn)行良好的測(cè)試，系統(tǒng)可能不會(huì)響應(yīng)新的登錄操作，可能需要使用專用管理員連接(DAC)來獲取對(duì)SQL Server的訪問。如果還沒有配置DAC，可以以單用戶模式重啟服務(wù)器。在以單用戶模式運(yùn)行時(shí)，連接不會(huì)受到“資源調(diào)控器”約束，因此可以禁用資源調(diào)控器，直到問題修復(fù)。

(4).改變分類器函數(shù)的注意事項(xiàng)

　　如果需要改變分類器函數(shù)，必須首先禁用“資源調(diào)控器”，因?yàn)闊o法在“資源調(diào)控器”正在運(yùn)行時(shí)對(duì)其進(jìn)行更改。可以運(yùn)行以下命令來禁用當(dāng)前的分類器函數(shù)：

    ALTER RESOURCE GOVERNOR 

　　WITH (CLASSIFIER_FUNCTION = NULL);
  

需要注意的是：“資源調(diào)控器”更改應(yīng)該由組織中最資深的DBA(數(shù)據(jù)庫(kù)管理員)來執(zhí)行，以免由于錯(cuò)誤配置“資源調(diào)控器”而引起問題。

6、監(jiān)控和配置資源的其它技術(shù)

　　除了“資源調(diào)控器”，我們也可以通過系統(tǒng)監(jiān)控(perfmon exe)工具來監(jiān)控SQL Server分配的CPU資源。當(dāng)添加想要監(jiān)控的計(jì)數(shù)器時(shí)，SQL Server:Resource pool Stats對(duì)象會(huì)顯示已配置的每個(gè)資源池的實(shí)例。

　　一些類似的與實(shí)例相關(guān)的選擇已被添加到SQL Server:Workload Group Stats計(jì)數(shù)器，相關(guān)的值也可通過查詢sys.dm_os_performance_counters視圖獲得。

　　為了進(jìn)行更仔細(xì)的監(jiān)控，SQL Server中還添加了其它一些事件，包括CPU Threshold Exceeded、PreConnectStarting和PreConnect:Completed事件。如果想要使用.NET代碼以編程方式控制“資源調(diào)控器”，可以使用已經(jīng)添加到SQL Server Management Objects(SMO)中的ResourceGovernor類。

　　通常情況下，我們比較關(guān)注配置資源池時(shí)最大的CPU百分比，因?yàn)楸仨毞浅Ｖ?jǐn)慎地配置最小百分比。如果將最小值配置得太高，很容易過度限制系統(tǒng)的資源利用率，尤其是內(nèi)存的最小百分比不宜過高。

總結(jié)：

　　需要說明的是，我們并不能利用“資源調(diào)控器”及時(shí)降低剛發(fā)現(xiàn)的嚴(yán)重影響系統(tǒng)性能的查詢的優(yōu)先級(jí)，因?yàn)樵谶B接會(huì)話時(shí)，工作負(fù)載組和資源池就已經(jīng)分配完成。所以，在配置“資源調(diào)控器”前做好統(tǒng)籌安排是非常必要的。雖然“資源調(diào)控器”并不是盡善盡美，但對(duì)于DBA任不失為一個(gè)實(shí)現(xiàn)資源的負(fù)載均衡的利器。

配置SQL Server 2008的資源調(diào)控器實(shí)現(xiàn)負(fù)載均衡