詳解SQL Server 2008新特性Resource Governor

2009-04-08 09:29:34

運(yùn)維數(shù)據(jù)庫(kù)運(yùn)維 SQL Server

Sql Server2008 推出了已經(jīng)有一段時(shí)間了，這里給大家介紹一下 Sql Server2008 的一個(gè)很不錯(cuò)的新特性，Resource Governor。

相信大家都遇到過(guò)，一個(gè)服務(wù)器上面運(yùn)行多個(gè)數(shù)據(jù)庫(kù)的情況，如果1個(gè)數(shù)據(jù)庫(kù)占用資源過(guò)多，很可能直接導(dǎo)致另外一個(gè)數(shù)據(jù)庫(kù)無(wú)法處理，直到超時(shí)的情況。過(guò)去這種情況基本無(wú)法處理（當(dāng)然不排除使用三方程序處理的方法）。嘿嘿，在新的Sql Server 2008 中，就可以***的解決這個(gè)問(wèn)題了。答案就是Resource Governor。

Resource Governor可以通過(guò)創(chuàng)建資源池（Resource Pool）的方式，對(duì)不同資源池分別分配服務(wù)器資源（CPU，內(nèi)存），這里設(shè)置的是，資源池最繁忙時(shí)期的分配值。簡(jiǎn)單的說(shuō)，現(xiàn)在我有兩個(gè)數(shù)據(jù)庫(kù)，DataBaseA和DataBaseB，那么我們可以創(chuàng)建兩個(gè)資源池PoolA和PoolB，給PoolA分配10%的CPU和內(nèi)存，PoolB分配90%的CPU和內(nèi)存。那么當(dāng)DataBaseA和DataBaseB都繁忙的時(shí)候，系統(tǒng)會(huì)分別分配相應(yīng)的資源給他們，讓他們都能夠完成自己的工作（當(dāng)然，性能可能有所下降，畢竟只使用10%的資源），從而避免了高并發(fā)性時(shí)，資源獨(dú)占的情況。很好吧，下面我們就通過(guò)一個(gè)實(shí)例來(lái)給大家演示一下。

1. 首先可以查看一下當(dāng)前資源池的配置情況

USE master;
GO 
SELECT * FROM sys.dm_resource_governor_configuration;
GO

如果如上圖所示，那么說(shuō)明目前沒(méi)有創(chuàng)建任何資源池。（創(chuàng)建資源池的時(shí)候需要綁定一個(gè)classifier的函數(shù)，如果綁定了，會(huì)顯示函數(shù)的ObjectID）

2. 創(chuàng)建2個(gè)資源池MarketingPool和DevelopmentPool,

CREATE RESOURCE POOL MarketingPool
WITH (MAX_CPU_PERCENT = 10);
GO 
CREATE RESOURCE POOL DevelopmentPool
WITH (MAX_CPU_PERCENT = 90);
GO

MarketingPool分配了10%CPU資源，DevelopmentPool分配了90%CPU資源。（當(dāng)然你也可以加上內(nèi)存的分配，不過(guò)個(gè)人感覺(jué)一般CPU比較重要，內(nèi)存這東西不好說(shuō)）

3. 可以先看一下當(dāng)前Resource Governor的配置情況

SELECT * FROM sys.dm_resource_governor_resource_pools;
GO

你會(huì)發(fā)現(xiàn)，如上所示結(jié)果，只有兩個(gè)資源池，并且不是我們創(chuàng)建的。

internal和default資源池是系統(tǒng)默認(rèn)的資源池，如果沒(méi)有手動(dòng)設(shè)置Resource Governor的話，創(chuàng)建的數(shù)據(jù)庫(kù)會(huì)被默認(rèn)放到default資源池當(dāng)中。（后面你還會(huì)看到對(duì)應(yīng)的 WORKLOAD GROUP）

為什么我們創(chuàng)建的資源池沒(méi)有看到呢？其實(shí)是我們的配置還沒(méi)有設(shè)定上去，Sql Server08的設(shè)置修改后，需要手動(dòng) RECONFIGURE 來(lái)重置配置信息。

4. RECONFIGURE RESOURCE GOVERNOR 來(lái)讓我們新建的資源池生效。

ALTER RESOURCE GOVERNOR RECONFIGURE;
GO 
加下去執(zhí)行
SELECT * FROM sys.dm_resource_governor_resource_pools;
GO

來(lái)看一下現(xiàn)在的配置信息。

你會(huì)發(fā)現(xiàn)新創(chuàng)建的MarketingPool和DevelopmentPool已經(jīng)被添加了進(jìn)去，并且max_cpu_percent為相應(yīng)的10和90。

只有Resource Pool 是沒(méi)用效果的，因?yàn)镻ool下面還有下級(jí)單位，叫做 WORKLOAD GROUP , 所以接下去我們需要?jiǎng)?chuàng)建Group。

5. 創(chuàng)建 WORKLOAD GROUP

CREATE WORKLOAD GROUP MarketingGroup
USING MarketingPool;
GO

CREATE WORKLOAD GROUP DevelopmentGroup
USING DevelopmentPool;
GO

分別給兩個(gè)Pool創(chuàng)建對(duì)應(yīng)的 WorkLoad Group 。并且使用 Using 關(guān)鍵字綁定Group到對(duì)應(yīng)的Pool。

6. 查看WorkLoad Group 的配置信息

SELECT * FROM sys.dm_resource_governor_workload_groups;
GO

會(huì)發(fā)現(xiàn)，和Pool中的情況相同，只有兩個(gè)Group。（就是系統(tǒng)默認(rèn)的Group，分別與默認(rèn)的Pool對(duì)應(yīng)）

同樣是由于沒(méi)有ReConfigure造成的。

7. 更新配置信息

ALTER RESOURCE GOVERNOR RECONFIGURE;
GO

接著在查看Group信息。

新的Group已經(jīng)被添加進(jìn)去了。

8. 創(chuàng)建兩個(gè)數(shù)據(jù)庫(kù)，用來(lái)做測(cè)試

IF DB_ID ('MarketingDB') IS NULL
    CREATE DATABASE MarketingDB;
GO
IF DB_ID ('DevelopmentDB') IS NULL
    CREATE DATABASE DevelopmentDB;
GO

9. 創(chuàng)建classifier function

IF OBJECT_ID ('dbo.MyClassifier') IS NOT NULL
    DROP FUNCTION dbo.MyClassifier;
GO 
CREATE FUNCTION dbo.MyClassifier ()
RETURNS SYSNAME WITH SCHEMABINDING
AS
BEGIN
    DECLARE @GroupName SYSNAME;
    IF ORIGINAL_DB_NAME () = 'MarketingDB'
        SET @GroupName = 'MarketingGroup';
    ELSE IF  ORIGINAL_DB_NAME () = 'DevelopmentDB'
        SET @GroupName = 'DevelopmentGroup';
    ELSE SET @GroupName = 'Default';
    RETURN @GroupName;
END;
GO

這里，Sqlserver2008對(duì)于 Resource Governor 的設(shè)計(jì)方式，很類(lèi)似代理。Resource Governor 通過(guò)一個(gè)方法來(lái)綁定數(shù)據(jù)庫(kù)對(duì)象到WorkLoad Group ，這個(gè)方法需要外部給出定義，并且手動(dòng)綁定到Resource Governor 上面，定義方式可以參照上面代碼。分別將兩個(gè)測(cè)試用數(shù)據(jù)庫(kù)綁定到各自的Group中，其他的數(shù)據(jù)庫(kù)綁定到Default上面。

10. 綁定Classifier Function 到Resource Governor上面。

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

11. 更新Resource Governor 配置信息

ALTER RESOURCE GOVERNOR RECONFIGURE;
GO

12. 查看Resource Governor配置信息，確認(rèn)設(shè)置

SELECT * FROM sys.dm_resource_governor_configuration;
GO

如果如下圖所示，說(shuō)明配置成功。

到這里，資源池的創(chuàng)建完成，然后使用Server2003的Performance來(lái)查看CPU的使用情況

1. 在管理工具中啟動(dòng)Performance

2. 點(diǎn)擊Action菜單中的New WindowForm Here

3. 點(diǎn)擊加號(hào)添加監(jiān)視

4. 選中對(duì)應(yīng)SQL實(shí)例的Resource Governor ，并選定兩個(gè)資源池

5. 點(diǎn)擊Add添加2個(gè)資源池連接

目前由于沒(méi)有操作，所以占用資源為0

接下去將使用兩個(gè)循環(huán)腳本，不斷更新數(shù)據(jù)庫(kù)，來(lái)測(cè)試效果

1. 創(chuàng)建RunQueries.sql腳本，代碼如下
IF OBJECT_ID ('t1') IS NOT NULL DROP TABLE t1;
GO 
CREATE TABLE t1 (
    c1 INT IDENTITY,
    c2 VARCHAR (8000) DEFAULT (REPLICATE ('a', 8000)));
CREATE CLUSTERED INDEX t1c1 ON t1 (c1);
GO 
SET NOCOUNT ON;
GO
DECLARE @count INT = 0;
WHILE (@count < 1000)
BEGIN
    INSERT INTO t1 DEFAULT VALUES;
    SET @count +=  1;
END; 
WHILE (1 = 1) ALTER INDEX t1c1 ON t1 REORGANIZE; 
不斷更新Index以消耗資源。
2. 創(chuàng)建RunQueries2.sql腳本，代碼如下
IF OBJECT_ID ('t2') IS NOT NULL DROP TABLE t2;
GO 
CREATE TABLE t2 (
    c1 INT IDENTITY,
    c2 VARCHAR (8000) DEFAULT (REPLICATE ('a', 8000)));
CREATE CLUSTERED INDEX t2c1 ON t2 (c1);
GO 
SET NOCOUNT ON;
GO
DECLARE @count INT = 0;
WHILE (@count < 1000)
BEGIN
    INSERT INTO t2 DEFAULT VALUES;
    SET @count +=  1;
END; 
WHILE (1 = 1) ALTER INDEX t2c1 ON t2 REORGANIZE; 
與***個(gè)腳本一樣，因?yàn)闇y(cè)試發(fā)現(xiàn)，一個(gè)腳本消耗的資源不足以體現(xiàn)出9：1效果，所以同時(shí)運(yùn)行2個(gè)腳本。
3. 使用sqlcmd來(lái)運(yùn)行腳本（因?yàn)槭撬姥h(huán)）
a) cd 腳本所在目錄
b) sqlcmd -S .\SQLDEV01 -d MarketingDB -iRunQueries.sql
通過(guò)sqlcmd運(yùn)行腳本 –S 指定使用的SQL實(shí)例 -d 指定使用數(shù)據(jù)庫(kù) -i 指定運(yùn)行腳本
分別使用4個(gè)sqlcmd運(yùn)行4分腳本，直接給出sqlcmd命令（注意替換SQL實(shí)例名稱(chēng)）
sqlcmd -S .\SQLDEV01 -d MarketingDB -iRunQueries.sql 
sqlcmd -S .\SQLDEV01 -d DevelopmentDB -i RunQueries.sql 
sqlcmd -S .\SQLDEV01 -d MarketingDB -i RunQueries2.sql 
sqlcmd -S .\SQLDEV01 -d DevelopmentDB -i RunQueries2.sql 
4. 觀察Performance中曲線的變化，如下所示則操作成功

可以看到，CPU使用趨于 1：9 Resource Governor 成功應(yīng)用了

備注：
Resource Governor 能夠自動(dòng)判斷資源分配比例，如果沒(méi)有競(jìng)爭(zhēng)資源池的話，那么會(huì)分配100%資源應(yīng)用，如果競(jìng)爭(zhēng)激烈，會(huì)按照比例分配。從而使得各個(gè)資源池中的對(duì)象都能夠使用一定的服務(wù)器資源完成自己的工作，從而可以使得我們預(yù)測(cè)系統(tǒng)的最差情況，增強(qiáng)對(duì)數(shù)據(jù)庫(kù)的管理性。