一.本文所涉及的內(nèi)容（Contents）

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

二.背景（Contexts）

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

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

三.撰寫經(jīng)歷（Experience）

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

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

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

?

　　【IT168 專稿】在數(shù)據(jù)庫(kù) 服務(wù)器 上，有三種硬件資源一直是影響數(shù)據(jù)庫(kù)性能好壞的關(guān)鍵，甚至?xí)绊懙秸麄€(gè)生產(chǎn)系統(tǒng)的使用，這三種資源分別是 內(nèi)存 、 CPU 和物理IO。當(dāng)數(shù)據(jù)庫(kù) 服務(wù)器 上掛載了多個(gè)數(shù)據(jù)庫(kù)的時(shí)候，極有可能發(fā)生資源的爭(zhēng)奪，如何能保證重要數(shù)據(jù)庫(kù)在擁有足夠資源的前提下再把多余的資源提供給其它數(shù)據(jù)庫(kù)呢?資源調(diào)控器應(yīng)運(yùn)而生。

在SQL Server 2014中已經(jīng)增加了對(duì)物理IO資源的控制，這個(gè)功能在私有云的數(shù)據(jù)庫(kù)服務(wù)器上的作用體現(xiàn)得尤為重要，它能夠?yàn)樗接性朴脩籼峁┯行У目刂啤⒎峙洌⒏綦x物理IO資源。

?

一、生活中資源調(diào)控器

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

▲圖1：生活中的資源調(diào)控器——城市交通

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

▲圖2：生活中的資源調(diào)控器——BRT專用車道

　　交通部門規(guī)定：在出行高峰期，BRT專用道只供公交車使用，其它機(jī)動(dòng)車只能使用BRT專用道之外的車道資源。人有專門的人行道，自行車有自行車道。根據(jù)這個(gè)邏輯，可以得出如圖3所示的人與車在使用道路資源的邏輯結(jié)構(gòu)圖：

▲圖3：道路資源邏輯結(jié)構(gòu)圖

?

二、SQL Server中資源調(diào)控器

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

　　SQL Server 2014的資源調(diào)控器默認(rèn)包含了兩個(gè)工作負(fù)荷組(internal、default)和兩個(gè)資源池(internal、default)，在沒有手動(dòng)設(shè)置資源調(diào)控器的情況下，創(chuàng)建的數(shù)據(jù)庫(kù)會(huì)默認(rèn)放到default資源池當(dāng)中。

　　SQL Server資源調(diào)控器接收到會(huì)話請(qǐng)求后，通過用戶定義的分類器函數(shù)把會(huì)話進(jìn)行劃分并路由到相應(yīng)的工作負(fù)荷組，再通過工作負(fù)荷組找到對(duì)應(yīng)的資源池，由資源池中設(shè)置的 內(nèi)存 、 CPU 和物理IO資源的閥值來決定會(huì)話請(qǐng)求的資源分配。根據(jù)描述，可以得出如圖4所示的SQL Server資源調(diào)控器邏輯結(jié)構(gòu)圖：

▲圖4：SQL Server資源調(diào)控器邏輯結(jié)構(gòu)圖

?

三、SQL Server資源調(diào)控器運(yùn)用場(chǎng)景—CPU

　　理解了SQL Server資源調(diào)控器的原理之后，接著講述資源調(diào)控器在CPU方面的運(yùn)用場(chǎng)景：假設(shè)數(shù)據(jù)庫(kù)服務(wù)器上有一個(gè)重要的數(shù)據(jù)庫(kù)ImportantDB和一個(gè)普通的數(shù)據(jù)庫(kù)GeneralDB，從業(yè)務(wù)出發(fā)，希望服務(wù)器上的資源在滿足了ImportantDB之后才考慮分配多余的資源給GeneralDB，保證重要業(yè)務(wù)系統(tǒng)的正常運(yùn)行。

　　需要說明的是：筆者的機(jī)器是Azure上的虛擬機(jī)，機(jī)器的配置如圖5所示，下面的測(cè)試都是基于這個(gè)環(huán)境的，所以讀者在自行測(cè)試的時(shí)候應(yīng)該根據(jù)自己的機(jī)器環(huán)境進(jìn)行調(diào)整。

▲圖5：硬件環(huán)境

假設(shè)GeneralDB 數(shù)據(jù)庫(kù)占用的CPU最大值為10%，ImportantDB數(shù)據(jù)庫(kù)占用的CPU最大值為90%，實(shí)現(xiàn)這個(gè)需求的步驟如下：

1. 創(chuàng)建測(cè)試數(shù)據(jù)庫(kù)

          --創(chuàng)建重要業(yè)務(wù)數(shù)據(jù)庫(kù)

CREATE DATABASE ImportantDB

GO

--創(chuàng)建普通業(yè)務(wù)數(shù)據(jù)庫(kù)

CREATE DATABASE GeneralDB

GO
        

2. 創(chuàng)建并配置新的資源池和工作負(fù)荷組

          --創(chuàng)建重要業(yè)務(wù)數(shù)據(jù)庫(kù)的資源池

CREATE RESOURCE POOL rpImportantDB

WITH

(

     MAX_CPU_PERCENT = 90,

     MIN_CPU_PERCENT = 10

)

GO

--創(chuàng)建重要業(yè)務(wù)數(shù)據(jù)庫(kù)的工作負(fù)荷組

CREATE WORKLOAD GROUP wgImportantDB

WITH

(

     IMPORTANCE = MEDIUM

)

USING rpImportantDB

GO

 

--創(chuàng)建普通業(yè)務(wù)數(shù)據(jù)庫(kù)的資源池

CREATE RESOURCE POOL rpGeneralDB

WITH

(

     MAX_CPU_PERCENT = 10,

     MIN_CPU_PERCENT = 0

)

GO

--創(chuàng)建重要業(yè)務(wù)數(shù)據(jù)庫(kù)的工作負(fù)荷組

CREATE WORKLOAD GROUP wgGeneralDB

WITH

(

     IMPORTANCE = LOW

)

USING rpGeneralDB

GO
        

3. 更新內(nèi)存中資源調(diào)控器的配置

          --更新內(nèi)存中的配置

ALTER RESOURCE GOVERNOR RECONFIGURE

GO
        

4. 查詢資源調(diào)控器中資源池和工作負(fù)荷組的配置信息，返回結(jié)果如圖6所示：

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

USE master

SELECT * FROM sys.resource_governor_resource_pools

SELECT * FROM sys.resource_governor_workload_groups

GO
        

▲圖6

5. 創(chuàng)建分類器函數(shù)，這是一個(gè)用戶自定義函數(shù) (UDF)，它供資源調(diào)控器用來對(duì)會(huì)話進(jìn)行分類，以便將它們路由到對(duì)應(yīng)的工作負(fù)荷組中，函數(shù)返回工作負(fù)荷組的名稱；

          --創(chuàng)建分類器函數(shù)

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. 將分類器函數(shù)fn_Classifier注冊(cè)到資源調(diào)控器并更新內(nèi)存中的配置

          --注冊(cè)分類器函數(shù)到資源調(diào)控器并更新內(nèi)存中的配置

ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION = dbo.fn_Classifier)

ALTER RESOURCE GOVERNOR RECONFIGURE

GO
        

7. 接下來就是對(duì)CPU進(jìn)行測(cè)試，創(chuàng)建能占用CPU的測(cè)試腳本，把下面的腳本分別保存為GeneralDB.sql和ImportantDB.sql：

          --測(cè)試CPU

DECLARE @Counts INT

WHILE 1=1

BEGIN

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

END
        

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

▲圖7：添加計(jì)數(shù)器

9. 因?yàn)榉诸惼骱瘮?shù)fn_Classifier 通過函數(shù)ORIGINAL_DB_NAME()返回用戶在數(shù)據(jù)庫(kù)連接字符串中指定的數(shù)據(jù)庫(kù)名稱，所以可以通過使用SQLCMD來模擬用戶輸入，SQLCMD調(diào)用GeneralDB.sql和ImportantDB.sql腳本的命令如下：

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

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

10. 性能計(jì)數(shù)器CPU usage%記錄了在測(cè)試過程中CPU的使用情況，結(jié)果如圖8所示：?

▲圖8：性能計(jì)數(shù)器

　　執(zhí)行GeneralDB.sql后，藍(lán)色實(shí)例rpGeneralDB占用了25%左右的CPU;接著在另外的窗口執(zhí)行ImportantDB.sql后，紫色實(shí)例rpImportantDB同樣占用了25%左右的CPU，以同樣的方式繼續(xù)增加紫色實(shí)例rpImportantDB的CPU占用量，當(dāng)占用量超過了設(shè)置的90%，可以發(fā)現(xiàn)藍(lán)色實(shí)例rpGeneralDB占用CPU百分比馬上下降了。從這個(gè)測(cè)試的結(jié)果來看，SQL Server資源調(diào)控器已經(jīng)達(dá)到我們預(yù)期對(duì)CPU資源的規(guī)劃效果。

　　值得一提的是，大家可能已經(jīng)發(fā)現(xiàn)rpImportantDB資源池的CPU usage%計(jì)數(shù)器的最新值高達(dá)97.470%，資源池不是設(shè)置了MAX_CPU_PERCENT = 90嗎?怎么會(huì)超過這個(gè)限制呢?其實(shí)這是因?yàn)閞pGeneralDB資源池的MIN_CPU_PERCENT = 0，所以在沒有其它最小CPU的限制的話，rpImportantDB資源池占用的CPU是有可能達(dá)到100%的。

?

四、SQL Server資源調(diào)控器運(yùn)用場(chǎng)景—IO

　　前面講述關(guān)于CPU資源的控制更多的是為下面講述SQL Server 2014在IO資源上的調(diào)控做鋪墊，上面的例子是以數(shù)據(jù)庫(kù)為單位規(guī)劃各個(gè)庫(kù)占用CPU的百分比，下面的例子我們就將以登陸用戶來劃分磁盤的IOPS;

　　假設(shè)generalUser用戶占用的IOPS最大值為10，importantUser用戶占用IOPS的最大值為20，實(shí)現(xiàn)這個(gè)需求的步驟如下：

1. 首先，解除分類器函數(shù)與資源調(diào)控器注冊(cè)關(guān)系；

          USE [master]

GO

--解除分類器函數(shù)注冊(cè)

ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION = NULL)

GO
        

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

          --修改重要業(yè)務(wù)數(shù)據(jù)庫(kù)的資源池

ALTER RESOURCE POOL rpImportantDB

WITH

(

     MAX_CPU_PERCENT = 90,

     MIN_CPU_PERCENT = 10,

     MAX_IOPS_PER_VOLUME = 20,

     MIN_IOPS_PER_VOLUME = 0

)

 

--修改普通業(yè)務(wù)數(shù)據(jù)庫(kù)的資源池

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. 接著修改分類器函數(shù)fn_Classifier()，把它修改成按照登陸用戶：importantUser和generalUser（這兩個(gè)登陸用戶請(qǐng)自行創(chuàng)建，為了方便測(cè)試，這兩個(gè)用戶都是管理員身份）返回對(duì)應(yīng)的工作負(fù)荷組名稱；

          --修改分類器函數(shù)

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. 重新把分類器函數(shù)fn_Classifier()注冊(cè)到資源調(diào)控器并更新內(nèi)存中的配置；

          --注冊(cè)分類器函數(shù)到資源調(diào)控器并更新內(nèi)存中的配置

ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION = dbo.fnClassifier)

ALTER RESOURCE GOVERNOR RECONFIGURE

GO
        

5. 在GeneralDB數(shù)據(jù)庫(kù)中創(chuàng)建一個(gè)名為TestIOPS的表，并向表中插入10000行記錄；

          USE [GeneralDB]

GO

--創(chuàng)建表

CREATE TABLE [dbo].[TestIOPS](

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

    [MyStr] [nchar](450) NULL

) ON [PRIMARY]

GO

 

--插入測(cè)試數(shù)據(jù)

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字段創(chuàng)建一個(gè)非聚集索引來測(cè)試IOPS，在執(zhí)行創(chuàng)建索引的腳本之前，打開性能計(jì)數(shù)器幫助監(jiān)控創(chuàng)建索引時(shí)的IOPS，找到SQLServer:Resource Pool Stats對(duì)象下的Disk Write IO/sec計(jì)數(shù)器，計(jì)數(shù)器監(jiān)控的結(jié)果如圖9所示：

          USE [GeneralDB]

GO

--創(chuàng)建索引

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

GO
        

▲圖9：計(jì)數(shù)器監(jiān)控結(jié)果

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

7. 接著，使用importantUser用戶登陸SSMS，創(chuàng)建上一步驟中一樣的索引結(jié)構(gòu)，只需要修改索引名稱即可，同樣需要監(jiān)控SQLServer:Resource Pool Stats對(duì)象下的Disk Write IO/sec計(jì)數(shù)器，計(jì)數(shù)器監(jiān)控的結(jié)果如圖10所示：

          USE [GeneralDB]

GO

--創(chuàng)建索引

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

GO
        

▲圖10：Write IO/sec計(jì)數(shù)器監(jiān)視結(jié)果

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

　　通過上面資源調(diào)控器的IOPS的測(cè)試，可以發(fā)現(xiàn)即使兩個(gè)用戶執(zhí)行了相同操作，但I(xiàn)OPS卻可以根據(jù)不同用戶而有所區(qū)別，這完全歸功于資源調(diào)控器對(duì)IO資源的控制，這將為數(shù)據(jù)庫(kù)主機(jī)或者私有云上運(yùn)行IO密集型工作負(fù)載提供解決方案。

?

五、總結(jié)

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

?

作者簡(jiǎn)介

　　陳暢亮，微軟SQL Server最有價(jià)值專家，目前就職于廣州某互聯(lián)網(wǎng)公司任數(shù)據(jù)庫(kù)團(tuán)隊(duì)Leader，專注于數(shù)據(jù)庫(kù)解決方案與性能調(diào)優(yōu)。

?

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

• SQL Server 2014內(nèi)存數(shù)據(jù)庫(kù)深度解析
• SQL Server 2014新特性：SSD緩沖區(qū)擴(kuò)展
• SQL Server 2014 可更新聚集列存儲(chǔ)索引
• SQL Server 2014新特性：IO資源調(diào)控
• SQL Server 2014新特性：原生備份加密
• SQL Server 2014云特性：無縫集成公有云