如果有5個業務都很繁忙的數據庫需要部署在一臺服務器上，為了避免相互的資源搶占，我們通常會在服務器上安裝5個SQL Server實例來分別承載這5個數據庫，然后分別設置每個實例的最大和最小內存、CPU掩碼等，用以s控制這5個業務數據庫的資源分配。

筆者的公司曾經就有這樣的案例。這種方式的缺點顯而易見：首先是授權，在上述例子中，5個SQL Server實例的費用應該是比1個要高；其次是管理成本提高，DBA不得不安裝和維護5個實例。那么，有沒有更好的方法呢？

其實從SQL Server2008（企業版）開始，微軟就已經考慮過這種情況，并提出解決方案——“SQL Server的資源調節器”。SQL Server的資源調節器引入了一種多租戶的理念，它將一個SQL Server實例的資源“出租”給不同的客戶端負載，每個客戶端負載所分配到的資源彼此隔離，互不影響。

下面我們看看SQL Server的資源調節器的原理和一個實例的來了解下SQL Server的資源調節器：

在SQL Server的資源調節器有三個重要的概念：

在上文多租戶的理念中，不同的客戶端負載之所以能夠分配彼此隔離的資源，其原因就是他們擁有各自獨立的資源池。

在資源池里面，DBA可以設置最大、最小內存和CPU資源，也就是說，資源池實際上就是內存和CPU資源的集合，DBA根據業務的需要，可以為重要業務設置較大的資源池，而SLA較低的業務設置小的資源池。

工作組負載是一些具有共同特征的客戶端請求的集合。SQL Server將客戶端請求按照一定的規則分類后，形成多個工作組負載。

工作組負載是資源池利用的主體。將不同的資源池綁定到不同的工作組負載，從而實現了各個工作組負載的資源隔離。

分類器提供一套劃分工作組負載的規則，在此規則基礎上，SQL Server將客戶端的請求進行分類形成多個工作組。

舉個例子：把SQL Server的用戶名作為一個分類器，來自A用戶的所有請求劃分到工作組負載1，來自B用戶的所有請求劃分到工作組負載2。然后工作組負載1和2分別對應不同的資源池。

原理圖如下：

假設有市場和銷售兩個部門，各自使用不同的數據庫，但兩個數據庫在同一個SQL Server實例上，為了確保兩個部門的數據庫彼此不受對方資源使用的影響，我們使用SQL Server的資源調控器來控制兩個數據庫的資源使用。

create database Sales

create database Marketing

為了更好的查看CPU資源的爭用情況，我們先設置SQL Server的CPU affinity mask，使SQL Server只能使用一個邏輯核心，所有的工作組負載共享同一個CPU核心資源。

sp_configure 'show advanced', 1

GO

RECONFIGURE

GO

sp_configure 'affinity mask', 1

GO

RECONFIGURE

GO

1) 創建資源池

CREATE RESOURCE POOL SalesPool

CREATE RESOURCE POOL MarketingPool

2) 創建工作組負載

--創建SalesGroup的工作組，并綁定到SalesPool

CREATE WORKLOAD GROUP SalesGroup

USING SalesPool

--創建MarketingGroup的工作組，并綁定到MarketingPool

CREATE WORKLOAD GROUP MarketingGroup

USING MarketingPool

GO

3) 創建分類器（分類函數）

--創建自定義的二分類函數，按照客戶端連接字符串中的數據庫分類并返回數據庫的名字

CREATE FUNCTION CLASSIFIER_DbName()

RETURNS SYSNAME WITH SCHEMABINDING

BEGIN

DECLARE @val varchar(32)

SET @val = 'default';

IF 'Sales' = ORIGINAL_DB_NAME()

SET @val = 'SalesGroup';

ELSE IF 'Marketing' = ORIGINAL_DB_NAME()

SET @val = 'MarketingGroup';

RETURN @val;

END

GO

--將分類函數綁定到資源調節器中

ALTER RESOURCE GOVERNOR

WITH (CLASSIFIER_FUNCTION = dbo.CLASSIFIER_DbName)

GO

4) 啟用資源調控器

ALTER RESOURCE GOVERNOR RECONFIGURE

GO

Ø 創建如下測試SQL語句，并保存到workload.sql文件中

SET NOCOUNT ON

DECLARE @i INT

DECLARE @s VARCHAR(100)

SET @i = 100000000

WHILE @i > 0

BEGIN

SELECT @s = @@version;

SET @i = @i - 1;

END

Ø 以管理員身份運行cmd

Ø 然后以sqlcmd的方式加載workload.sql語句

sqlcmd -S localhost/sql2014 -U sa -P 95938 -d Sales –I "d:/workload.sql"

這條語句的意思是在sales數據庫中執行workload.sql，

Ø 打開性能計數器

SQLServer:Resource Pool Stats->CPU usage

可以看到salespool的CPU資源占用情況如下圖：

下圖中，salespool CPU資源的使用率為25%，（相當于完全使用了使用了筆者電腦4核CPU中1核資源（前面設置了SQL Server的affinity mask為1））。因為我們在前面的分類器的中把sales數據庫綁定到了資源池salespool上了，sales數據庫的CPU資源占用情況就是25%。

Ø 新開一個cmd窗口，執行如下語句：

sqlcmd -S localhost/sql2014 -U sa -P 95938 -d Marketing -i "d:/workload.sql"

這條語句的意思是在Marketing數據庫中執行workload.sql，

此時出現如下結果：

salespool和marketingPool共享25%的CPU資源（且兩者占用的資源量相同（接近），這是因為他們都是采用默認的資源分配權重）。這里的marketingPool同上文中介紹的salespool一樣，它也是綁定到了一個數據庫，不同的是這里綁定了marketing數據庫。

因此，這兩個資源池的CPU資源爭用反映的就是sales數據庫和Marketing數據庫的資源爭用。

備注：紅色的曲線代表sales pool

綠色曲線代表marketingPool

Ø 現在，我們更改銷售系統的資源池的CPU資源的權重為70

ALTER RESOURCE POOL SalesPool

WITH (MIN_CPU_PERCENT = 70)

GO

ALTER RESOURCE GOVERNOR RECONFIGURE

GO

結果如下：

此時salesPool占用的資源將增加到70%，而marketingPool的CPU資源降低到30%，兩者按照7:3進行分配。

這樣就相當于給sales數據庫分配了更多的CPU資源，而削減了marketing的CPU資源。

--設置MarketingPool的最大CPU占用

ALTER RESOURCE POOL MarketingPool

WITH (MAX_CPU_PERCENT = 5)

GO

ALTER RESOURCE GOVERNOR RECONFIGURE

GO

此時，在marketingPool占用的資源如下圖：

如果此時在sales數據庫中執行的workload.sql已經執行完畢，則marketing數據庫會突破之前設置的MAX_CPU_PERCENT = 5的限制，使用到全部CPU資源，這樣的好處就是可以充分利用資源，避免資源浪費。如下圖的藍色框中的曲線。

當然，如果你一定要限制Marketing數據庫的CPU資源必須在5%以內，可以通過如下語句實現：

ALTER RESOURCE POOL MarketingPool

WITH (CAP_CPU_PERCENT=5)

GO

ALTER RESOURCE GOVERNOR RECONFIGURE

GO

SQL Server資源調節器通過資源池、工作組負載、分類器等機制可以為不同的數據庫、不同的請求、不同的用戶分配彼此獨立的CPU、內存、IO等資源，起到資源調節和隔離的作用。通過該技術，我們可以在確保每個數據庫分配到合理的資源的前提下，合并多個數據庫到同一個實例，也可以為某些SQL代碼預定義服務器資源及避免某些SQL語句占用過多資源，還可以按照業務的優先級分配資源等?？傊?，使用SQL Server資源調節器可以更為細膩的分配服務器資源，是DBA的必殺技之一。

2015.1.8補充：

我在樣例中使用的創建資源池的方法非常簡單，其實，詳細的語法如下：

CREATE RESOURCE POOL pool_name[ WITH([ MIN_CPU_PERCENT = value ][ [ , ] MAX_CPU_PERCENT = value ] [ [ , ] CAP_CPU_PERCENT = value ] [ [ , ] AFFINITY {SCHEDULER =AUTO | ( <scheduler_range_spec> ) | NUMANODE = ( <NUMA_node_range_spec> )} ] [ [ , ] MIN_MEMORY_PERCENT = value ][ [ , ] MAX_MEMORY_PERCENT = value ][ [ , ] MIN_IOPS_PER_VOLUME = value ][ [ , ] MAX_IOPS_PER_VOLUME = value ]) ][;]