詳解SQL Server 2008新特性Resource Governor

2009-04-08 09:29:34

Sql Server2008 推出了已經有一段時間了，這里給大家介紹一下 Sql Server2008 的一個很不錯的新特性，Resource Governor。

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

Resource Governor可以通過創建資源池（Resource Pool）的方式，對不同資源池分別分配服務器資源（CPU，內存），這里設置的是，資源池最繁忙時期的分配值。簡單的說，現在我有兩個數據庫，DataBaseA和DataBaseB，那么我們可以創建兩個資源池PoolA和PoolB，給PoolA分配10%的CPU和內存，PoolB分配90%的CPU和內存。那么當DataBaseA和DataBaseB都繁忙的時候，系統會分別分配相應的資源給他們，讓他們都能夠完成自己的工作（當然，性能可能有所下降，畢竟只使用10%的資源），從而避免了高并發性時，資源獨占的情況。很好吧，下面我們就通過一個實例來給大家演示一下。

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

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

如果如上圖所示，那么說明目前沒有創建任何資源池。（創建資源池的時候需要綁定一個classifier的函數，如果綁定了，會顯示函數的ObjectID）

2. 創建2個資源池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資源。（當然你也可以加上內存的分配，不過個人感覺一般CPU比較重要，內存這東西不好說）

3. 可以先看一下當前Resource Governor的配置情況

SELECT * FROM sys.dm_resource_governor_resource_pools;
GO

你會發現，如上所示結果，只有兩個資源池，并且不是我們創建的。

internal和default資源池是系統默認的資源池，如果沒有手動設置Resource Governor的話，創建的數據庫會被默認放到default資源池當中。（后面你還會看到對應的 WORKLOAD GROUP）

為什么我們創建的資源池沒有看到呢？其實是我們的配置還沒有設定上去，Sql Server08的設置修改后，需要手動 RECONFIGURE 來重置配置信息。

4. RECONFIGURE RESOURCE GOVERNOR 來讓我們新建的資源池生效。

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

來看一下現在的配置信息。

你會發現新創建的MarketingPool和DevelopmentPool已經被添加了進去，并且max_cpu_percent為相應的10和90。

只有Resource Pool 是沒用效果的，因為Pool下面還有下級單位，叫做 WORKLOAD GROUP , 所以接下去我們需要創建Group。

5. 創建 WORKLOAD GROUP

CREATE WORKLOAD GROUP MarketingGroup
USING MarketingPool;
GO

CREATE WORKLOAD GROUP DevelopmentGroup
USING DevelopmentPool;
GO

分別給兩個Pool創建對應的 WorkLoad Group 。并且使用 Using 關鍵字綁定Group到對應的Pool。

6. 查看WorkLoad Group 的配置信息

SELECT * FROM sys.dm_resource_governor_workload_groups;
GO

會發現，和Pool中的情況相同，只有兩個Group。（就是系統默認的Group，分別與默認的Pool對應）

同樣是由于沒有ReConfigure造成的。

7. 更新配置信息

ALTER RESOURCE GOVERNOR RECONFIGURE;
GO

接著在查看Group信息。

新的Group已經被添加進去了。

8. 創建兩個數據庫，用來做測試

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

9. 創建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對于 Resource Governor 的設計方式，很類似代理。Resource Governor 通過一個方法來綁定數據庫對象到WorkLoad Group ，這個方法需要外部給出定義，并且手動綁定到Resource Governor 上面，定義方式可以參照上面代碼。分別將兩個測試用數據庫綁定到各自的Group中，其他的數據庫綁定到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配置信息，確認設置

SELECT * FROM sys.dm_resource_governor_configuration;
GO

如果如下圖所示，說明配置成功。

到這里，資源池的創建完成，然后使用Server2003的Performance來查看CPU的使用情況

1. 在管理工具中啟動Performance

2. 點擊Action菜單中的New WindowForm Here

3. 點擊加號添加監視

4. 選中對應SQL實例的Resource Governor ，并選定兩個資源池

5. 點擊Add添加2個資源池連接

目前由于沒有操作，所以占用資源為0

接下去將使用兩個循環腳本，不斷更新數據庫，來測試效果

1. 創建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. 創建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; 
與***個腳本一樣，因為測試發現，一個腳本消耗的資源不足以體現出9：1效果，所以同時運行2個腳本。
3. 使用sqlcmd來運行腳本（因為是死循環）
a) cd 腳本所在目錄
b) sqlcmd -S .\SQLDEV01 -d MarketingDB -iRunQueries.sql
通過sqlcmd運行腳本 –S 指定使用的SQL實例 -d 指定使用數據庫 -i 指定運行腳本
分別使用4個sqlcmd運行4分腳本，直接給出sqlcmd命令（注意替換SQL實例名稱）
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 成功應用了

備注：
Resource Governor 能夠自動判斷資源分配比例，如果沒有競爭資源池的話，那么會分配100%資源應用，如果競爭激烈，會按照比例分配。從而使得各個資源池中的對象都能夠使用一定的服務器資源完成自己的工作，從而可以使得我們預測系統的最差情況，增強對數據庫的管理性。