一.本文所涉及的內容（Contents）

1. 本文所涉及的內容（Contents）
2. 背景（Contexts）
3. 撰寫經歷（Experience）
4. 特約稿正文（Content-body）
   1. 第一部分：生活中資源調控器；
   2. 第二部分：SQL Server中資源調控器；
   3. 第三部分：SQL Server資源調控器運用場景—CPU；
   4. 第四部分：SQL Server資源調控器運用場景—IO；
   5. 第五部分：總結；
   6. 第六部分：作者簡介；
5. SQL Server 2014 新特性文章鏈接（IT168）

二.背景（Contexts）

　　寫這篇文章的目的是想記錄這次的撰寫文章的經歷，其實一篇技術文章來得并不容易，之前自己寫博客的隨意性很強，排版可以根據自己喜好，但是一篇文章要被大家所接受，讓大家愿意讀完其實是一件很難的事情，所以 排版 還是挺重要的，這里我想提示下IT168的編輯，我的SQL代碼其實是有高亮的，但是編輯發出來后發現高亮不見，閱讀起來的確很不爽，估計大家也有同樣的感覺。再者就是講講這次的技術內容，其實在SQL Server 2014之前就有資源調控器這個功能了，所以網上也不少這方面的資料，所以要寫這個主題就需要找到一些新的 亮點（新功能+通俗類比） ，這也是讀者感興趣的地方。我對寫這篇文章還有一個感受就是，你需要 不斷修改、完善 你文章的內容，包括整體文章的架構是否合理？語言描述是否通順？邏輯是否清晰？內容是否豐富？測試案例是否全面等等。所以這篇文章給我最大的感受是： 一篇憑良心出品的文章的確來之不易 。

　　2014年5月7日，從宋沄劍宋大俠得知有一個為IT168和微軟撰寫SQL Server 2014新功能文章的機會，撰寫的內容為：SQL Server 2014的資源調控器（Resource Governor），這里再次感謝宋大俠的推薦。

三.撰寫經歷（Experience）

1. 接到任務的時候在忙公司的事情，簡單看了看這個功能在網上的介紹，大概了解下之后就繼續忙其它工作了；
2. 過了幾天，感覺是時候動手撰寫這篇文章了，繼續查資料，簡單做了下構思，就動手搭了文章的基本框架；
3. 框架搭建完，寫了部分的描述，這需要一個環境進行測試，馬上想到了在Azure創建一個帶有SQL Server 2014的虛擬機，簡單的測試了Resource Governor的CPU控制功能；
4. 繼續寫內容，突然有個靈感，我想把生活中的BRT引用到文中來，因為他們之間有著許多的共同點，而且可以作為開篇，讓讀者一下子就能在生活中找到共鳴；
5. 努力找BRT的圖片當中；
6. 看到MSDN上關于Resource Governor的架構圖，感覺有些不妥，于是就自己畫了一個新的，而且我也可以畫一個關于BRT的架構圖，把公路資源這種畫到圖里面去，更加能通過BRT架構圖引出Resource Governor的架構圖；
7. 對Resource Governor的IO進行了測試和場景的設計與分析；
8. 繼續對文章的架構進行調整，組織語言，初稿出來了；
9. 把文章發給宋沄劍，希望能得到一些建議，首先得到了他的肯定，同時他建議在開頭和結尾加入云服務的概念，馬上動手，確定后，再發一次給他，他轉發給了IT168的編輯，在期待中煎熬度過；
10. 過了幾天，想了想，為了避免編輯的修改建議，繼續對文章進行再一次的修改，無論從語言、結構、描述等等方面繼續完善，看得都感覺要吐了，再次轉發給宋沄劍了；
11. 2014年5月27日，從宋沄劍那里得知，文章一次性通過了IT168和微軟的審核了，爽了；
12. 2014年5月28日，文章在IT168的首頁發布了；

四.特約稿正文（Content-body）

特此說明：由于版權的原因，這里的正文為引用IT168的稿件地址： http://tech.it168.com/a2014/0526/1627/000001627131_all.shtml

?

　　【IT168 專稿】在數據庫 服務器 上，有三種硬件資源一直是影響數據庫性能好壞的關鍵，甚至會影響到整個生產系統的使用，這三種資源分別是 內存 、 CPU 和物理IO。當數據庫 服務器 上掛載了多個數據庫的時候，極有可能發生資源的爭奪，如何能保證重要數據庫在擁有足夠資源的前提下再把多余的資源提供給其它數據庫呢?資源調控器應運而生。

在SQL Server 2014中已經增加了對物理IO資源的控制，這個功能在私有云的數據庫服務器上的作用體現得尤為重要，它能夠為私有云用戶提供有效的控制、分配，并隔離物理IO資源。

?

一、生活中資源調控器

　　在講述SQL Server 2014的資源調控器(Resource Governor)之前，讓我們來找一找生活中資源調控器的影子。大家都知道一線城市的交通可以用擁堵一詞來形容，你時常可以見到如圖1所示的路面狀況。

▲圖1：生活中的資源調控器——城市交通

　　為了緩解擁堵的交通，北京、上海、廣州等城市都修建了一個稱之為BRT的交通設施，BRT的全稱為Bus Rapid Transit，意為快速公交，就是在擁堵的公路上分配一條專門提供給公交車使用的車道，盡量保證大部分人出行的通暢。BRT專用車道的大致情況如圖2所示。

▲圖2：生活中的資源調控器——BRT專用車道

　　交通部門規定：在出行高峰期，BRT專用道只供公交車使用，其它機動車只能使用BRT專用道之外的車道資源。人有專門的人行道，自行車有自行車道。根據這個邏輯，可以得出如圖3所示的人與車在使用道路資源的邏輯結構圖：

▲圖3：道路資源邏輯結構圖

?

二、SQL Server中資源調控器

　　了解了生活中的資源調控器之后，我們馬上進入今天的主題：SQL Server資源調控器，它與生活中的資源調控器有著驚人的相似之處。

　　SQL Server 2014的資源調控器默認包含了兩個工作負荷組(internal、default)和兩個資源池(internal、default)，在沒有手動設置資源調控器的情況下，創建的數據庫會默認放到default資源池當中。

　　SQL Server資源調控器接收到會話請求后，通過用戶定義的分類器函數把會話進行劃分并路由到相應的工作負荷組，再通過工作負荷組找到對應的資源池，由資源池中設置的 內存 、 CPU 和物理IO資源的閥值來決定會話請求的資源分配。根據描述，可以得出如圖4所示的SQL Server資源調控器邏輯結構圖：

▲圖4：SQL Server資源調控器邏輯結構圖

?

三、SQL Server資源調控器運用場景—CPU

　　理解了SQL Server資源調控器的原理之后，接著講述資源調控器在CPU方面的運用場景：假設數據庫服務器上有一個重要的數據庫ImportantDB和一個普通的數據庫GeneralDB，從業務出發，希望服務器上的資源在滿足了ImportantDB之后才考慮分配多余的資源給GeneralDB，保證重要業務系統的正常運行。

　　需要說明的是：筆者的機器是Azure上的虛擬機，機器的配置如圖5所示，下面的測試都是基于這個環境的，所以讀者在自行測試的時候應該根據自己的機器環境進行調整。

▲圖5：硬件環境

假設GeneralDB 數據庫占用的CPU最大值為10%，ImportantDB數據庫占用的CPU最大值為90%，實現這個需求的步驟如下：

1. 創建測試數據庫

          --創建重要業務數據庫

CREATE DATABASE ImportantDB

GO

--創建普通業務數據庫

CREATE DATABASE GeneralDB

GO
        

2. 創建并配置新的資源池和工作負荷組

          --創建重要業務數據庫的資源池

CREATE RESOURCE POOL rpImportantDB

WITH

(

     MAX_CPU_PERCENT = 90,

     MIN_CPU_PERCENT = 10

)

GO

--創建重要業務數據庫的工作負荷組

CREATE WORKLOAD GROUP wgImportantDB

WITH

(

     IMPORTANCE = MEDIUM

)

USING rpImportantDB

GO

 

--創建普通業務數據庫的資源池

CREATE RESOURCE POOL rpGeneralDB

WITH

(

     MAX_CPU_PERCENT = 10,

     MIN_CPU_PERCENT = 0

)

GO

--創建重要業務數據庫的工作負荷組

CREATE WORKLOAD GROUP wgGeneralDB

WITH

(

     IMPORTANCE = LOW

)

USING rpGeneralDB

GO
        

3. 更新內存中資源調控器的配置

          --更新內存中的配置

ALTER RESOURCE GOVERNOR RECONFIGURE

GO
        

4. 查詢資源調控器中資源池和工作負荷組的配置信息，返回結果如圖6所示：

          --查詢獲取資源池和工作負荷組配置

USE master

SELECT * FROM sys.resource_governor_resource_pools

SELECT * FROM sys.resource_governor_workload_groups

GO
        

▲圖6

5. 創建分類器函數，這是一個用戶自定義函數 (UDF)，它供資源調控器用來對會話進行分類，以便將它們路由到對應的工作負荷組中，函數返回工作負荷組的名稱；

          --創建分類器函數

CREATE FUNCTION fn_Classifier()

RETURNS SYSNAME

WITH SCHEMABINDING

AS

BEGIN

    DECLARE @strGroupName SYSNAME

    IF ORIGINAL_DB_NAME()='ImportantDB'

        SET @strGroupName='wgImportantDB'

    ELSE IF ORIGINAL_DB_NAME()='GeneralDB'

        SET @strGroupName='wgGeneralDB'

    ELSE 

        SET @strGroupName='default'

    RETURN @strGroupName

END

GO
        

6. 將分類器函數fn_Classifier注冊到資源調控器并更新內存中的配置

          --注冊分類器函數到資源調控器并更新內存中的配置

ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION = dbo.fn_Classifier)

ALTER RESOURCE GOVERNOR RECONFIGURE

GO
        

7. 接下來就是對CPU進行測試，創建能占用CPU的測試腳本，把下面的腳本分別保存為GeneralDB.sql和ImportantDB.sql：

          --測試CPU

DECLARE @Counts INT

WHILE 1=1

BEGIN

    SELECT @Counts=COUNT(*) FROM SYS.COLUMNS A,SYS.COLUMNS B

END
        

8. Windows操作系統為SQL Server資源調控器提供了大量的性能計數器幫助了解資源的使用情況，查看SQLServer:Resource Pool Stats對象下的CPU usage%計數器，里面包括四個對象實例：default、internal和剛剛創建的rpGeneralDB、rpImportantDB，如圖7所示：

▲圖7：添加計數器

9. 因為分類器函數fn_Classifier 通過函數ORIGINAL_DB_NAME()返回用戶在數據庫連接字符串中指定的數據庫名稱，所以可以通過使用SQLCMD來模擬用戶輸入，SQLCMD調用GeneralDB.sql和ImportantDB.sql腳本的命令如下：

SQLCMD -S . -d GeneralDB -i GeneralDB.sql

SQLCMD -S . -d ImportantDB -i ImportantDB.sql

10. 性能計數器CPU usage%記錄了在測試過程中CPU的使用情況，結果如圖8所示：?

▲圖8：性能計數器

　　執行GeneralDB.sql后，藍色實例rpGeneralDB占用了25%左右的CPU;接著在另外的窗口執行ImportantDB.sql后，紫色實例rpImportantDB同樣占用了25%左右的CPU，以同樣的方式繼續增加紫色實例rpImportantDB的CPU占用量，當占用量超過了設置的90%，可以發現藍色實例rpGeneralDB占用CPU百分比馬上下降了。從這個測試的結果來看，SQL Server資源調控器已經達到我們預期對CPU資源的規劃效果。

　　值得一提的是，大家可能已經發現rpImportantDB資源池的CPU usage%計數器的最新值高達97.470%，資源池不是設置了MAX_CPU_PERCENT = 90嗎?怎么會超過這個限制呢?其實這是因為rpGeneralDB資源池的MIN_CPU_PERCENT = 0，所以在沒有其它最小CPU的限制的話，rpImportantDB資源池占用的CPU是有可能達到100%的。

?

四、SQL Server資源調控器運用場景—IO

　　前面講述關于CPU資源的控制更多的是為下面講述SQL Server 2014在IO資源上的調控做鋪墊，上面的例子是以數據庫為單位規劃各個庫占用CPU的百分比，下面的例子我們就將以登陸用戶來劃分磁盤的IOPS;

　　假設generalUser用戶占用的IOPS最大值為10，importantUser用戶占用IOPS的最大值為20，實現這個需求的步驟如下：

1. 首先，解除分類器函數與資源調控器注冊關系；

          USE [master]

GO

--解除分類器函數注冊

ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION = NULL)

GO
        

2. 接著，修改rpImportantDB、rpGeneralDB資源池，添加MAX_IOPS_PER_VOLUME和MIN_IOPS_PER_VOLUME兩個屬性值；

          --修改重要業務數據庫的資源池

ALTER RESOURCE POOL rpImportantDB

WITH

(

     MAX_CPU_PERCENT = 90,

     MIN_CPU_PERCENT = 10,

     MAX_IOPS_PER_VOLUME = 20,

     MIN_IOPS_PER_VOLUME = 0

)

 

--修改普通業務數據庫的資源池

ALTER RESOURCE POOL rpGeneralDB

WITH

(

     MAX_CPU_PERCENT = 10,

     MIN_CPU_PERCENT = 0,

     MAX_IOPS_PER_VOLUME = 10,

     MIN_IOPS_PER_VOLUME = 0

)

GO
        

3. 接著修改分類器函數fn_Classifier()，把它修改成按照登陸用戶：importantUser和generalUser（這兩個登陸用戶請自行創建，為了方便測試，這兩個用戶都是管理員身份）返回對應的工作負荷組名稱；

          --修改分類器函數

ALTER FUNCTION fn_Classifier()

RETURNS SYSNAME

WITH SCHEMABINDING

AS

BEGIN

    DECLARE @strGroupName SYSNAME

    IF SUSER_SNAME()='importantUser'

        SET @strGroupName='wgImportantDB'

    ELSE IF SUSER_SNAME()='generalUser'

        SET @strGroupName='wgGeneralDB'

    ELSE 

        SET @strGroupName='default'

    RETURN @strGroupName

END

GO
        

4. 重新把分類器函數fn_Classifier()注冊到資源調控器并更新內存中的配置；

          --注冊分類器函數到資源調控器并更新內存中的配置

ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION = dbo.fnClassifier)

ALTER RESOURCE GOVERNOR RECONFIGURE

GO
        

5. 在GeneralDB數據庫中創建一個名為TestIOPS的表，并向表中插入10000行記錄；

          USE [GeneralDB]

GO

--創建表

CREATE TABLE [dbo].[TestIOPS](

    [Id] [int] IDENTITY(1,1) NOT NULL,

    [MyStr] [nchar](450) NULL

) ON [PRIMARY]

GO

 

--插入測試數據

SET NOCOUNT ON;

GO

DECLARE @count INT = 0;

WHILE (@count < 10000)

BEGIN

    INSERT INTO [GeneralDB].[dbo].[TestIOPS](MyStr) 

        VALUES(REPLICATE('a',450));

    SET @count +=  1;

END

GO
        

6. 使用generalUser用戶登陸SSMS，通過在TestIOPS表的MyStr字段創建一個非聚集索引來測試IOPS，在執行創建索引的腳本之前，打開性能計數器幫助監控創建索引時的IOPS，找到SQLServer:Resource Pool Stats對象下的Disk Write IO/sec計數器，計數器監控的結果如圖9所示：

          USE [GeneralDB]

GO

--創建索引

CREATE NONCLUSTERED INDEX idx_MyStr_1 ON [GeneralDB].[dbo].[TestIOPS] ([MyStr]);

GO
        

▲圖9：計數器監控結果

　　從圖9可以看出資源池rpGeneralDB 中Disk Write IO/sec計數器的最大值為10，發生Disk Write IO/sec的時間從22:54:52持續到22:55:02，持續時間大概為20秒；

7. 接著，使用importantUser用戶登陸SSMS，創建上一步驟中一樣的索引結構，只需要修改索引名稱即可，同樣需要監控SQLServer:Resource Pool Stats對象下的Disk Write IO/sec計數器，計數器監控的結果如圖10所示：

          USE [GeneralDB]

GO

--創建索引

CREATE NONCLUSTERED INDEX idx_MyStr_2 ON [GeneralDB].[dbo].[TestIOPS] ([MyStr]);

GO
        

▲圖10：Write IO/sec計數器監視結果

　　從圖10可以看出資源池rpImportantDB 中Disk Write IO/sec計數器的最大值為20，發生Disk Write IO/sec的時間從22:59:52持續到23:00:11，持續時間大概為9秒;

　　通過上面資源調控器的IOPS的測試，可以發現即使兩個用戶執行了相同操作，但IOPS卻可以根據不同用戶而有所區別，這完全歸功于資源調控器對IO資源的控制，這將為數據庫主機或者私有云上運行IO密集型工作負載提供解決方案。

?

五、總結

　　資源調控器(Resource Governor)在沒有資源爭奪的時候，那么運用可以分配到100%資源，如果達到了分類器函數的臨界值，會按照預先分配的比例進行調配，從而保證重要業務在資源緊缺的情況下順利進行，增強對數據庫的管理性。SQL Server 2014的新功能中提供了一個非常重要的IO資源控制，這將為私有云用戶提供更人性化的管理和服務。

?

作者簡介

　　陳暢亮，微軟SQL Server最有價值專家，目前就職于廣州某互聯網公司任數據庫團隊Leader，專注于數據庫解決方案與性能調優。

?

五.SQL Server 2014 新特性文章鏈接（IT168）

• SQL Server 2014內存數據庫深度解析
• SQL Server 2014新特性：SSD緩沖區擴展
• SQL Server 2014 可更新聚集列存儲索引
• SQL Server 2014新特性：IO資源調控
• SQL Server 2014新特性：原生備份加密
• SQL Server 2014云特性：無縫集成公有云