在今天的文章裡，我想談下SQL Server 2014裡非常酷的提升：現在你終于可以根據需要的IOPS來壓制查詢！資源調控器（Resource Governor）自SQL Server 2008起引入，但提供的功能還是有所限制：你隻能限制CPU時間（這個已經很棒了），還有你能限制查詢（從每個獨立的查詢）記憶體量。
但作為DBA的你，你經常會進行一些資料庫維護操作，例如索引重建，DBCC CHECKDB操作等。我們都知道，這些操作會在你的存儲裡帶來大量的IOPS直至峰值。如果在7 * 24線上的資料庫來說，這個會影響你的生産力，給業務和銷售額帶來很大影響。
自SQL Server 2014開始，這個情況就變了，因為你可以通過資源調控器來部署指定的資源池來限制IOPS使用率。當你隔離你的DBA操作到指定的資源池時，你能指定資源池可以使用的最大IOPS（包括最小IOPS）。是以你可以壓制下DBA操作需要的IOPS。你的生産工作量就可以更好的使用你的存儲。更多資訊可以檢視微軟線上幫助。
我想用一個非常簡單的例子來展示下這個行為。假設你是DBA，正要進行正常索引重建操作，這個需要通過資源調控器對它們的最大IOPS使用率進行控制。第1步我們為DBA操作建立專用的資源池和工作負荷組。
1 -- Create a new Resource Pool for the DBAs.
2 -- We use a very high value for MAX_IOPS_PER_VOLUME so that we are
3 -- currently running unlimited.
4 CREATE RESOURCE POOL DbaPool WITH
5 (
6 MAX_IOPS_PER_VOLUME = 100000
7 )
8 GO
9
10 -- Create a new Workload Group for the DBAs
11 CREATE WORKLOAD GROUP DbaGroup
12 USING DbaPool
13 GO 從剛才的代碼可以看到，CREATE RESOURCE POOL語句現在為你提供MAX_IOPS_PER_VOLUME屬性（包括MIN_IOPS_PER_VOLUME）。這裡我設定了一個很高的值，是以在第一次執行時IOPS不會受限，這裡我們根據需要的IOPS建立了初始基線。下一步我會建立資源調控器需要的分類函數。
1 -- Create a new Classifier Function for Resource Governor
2 CREATE FUNCTION dbo.MyClassifierFunction()
3 RETURNS SYSNAME WITH SCHEMABINDING
4 AS
5 BEGIN
6 DECLARE @GroupName SYSNAME
7
8 IF SUSER_NAME() = 'DbaUser'
9 BEGIN
10 SET @GroupName = 'DbaGroup'
11 END
12 ELSE
13 BEGIN
14 SET @GroupName = 'Default'
15 END
16
17 RETURN @GroupName;
18 END
19 GO 在分類函數裡我們根據登入進行評估。如果登入是DbaUser，進入的會話會在DbaGroup工作負荷組裡。否則就進入預設的工作負荷組。最後我們在資源調控器注冊并配置它，這樣我們的設定就生效了。
1 -- Register the Classifier Function within Resource Governor
2 ALTER RESOURCE GOVERNOR WITH
3 (
4 CLASSIFIER_FUNCTION = dbo.MyClassifierFunction
5 )
6 GO
7
8 -- Reconfigure Resource Governor
9 ALTER RESOURCE GOVERNOR RECONFIGURE
10 GO 現在當你建立名為DbaUser的登入時，你可以用它連接配接到你的SQL Server。你可以在DMV sys.dm_exec_sessions 看下 group_id列驗證下到來的會話是否在正确的工作負荷組裡。下一步我在ContoRetailDW資料庫的FactOnlineSales表裡的DataKey裡建立一個非聚集索引。
1 -- Create a simple Non-Clustered Index
2 CREATE NONCLUSTERED INDEX idx_DateKey ON FactOnlineSales(DateKey)
3 GO 我們從開始就建立了資源池，現在在我們在我們的資源池裡并沒有限制。是以當我們現在進行剛才建立的非聚集索引的索引重建時，SQL Server會占用大量的IOPS。我們可以通過性能監控裡的“SQL Server:Resource Pool Stats:Disk Write IO/Sec”性能計數器來驗證剛才建立的資源池。
1 ALTER INDEX idx_DateKey ON FactOnlineSales REBUILD
2 GO 
可以看到索引重建花費近100的IOPS。接下來我要做的是限制DbaPool資源池為僅50的IOPS：
1 -- Let's change the Resource Pool by lowering the maximum IOPS.
2 ALTER RESOURCE POOL DbaPool WITH
3 (
4 MAX_IOPS_PER_VOLUME = 50
5 )
6 GO 現在當你執行索引重建時，在性能螢幕裡可以清楚看到，在特定的資源池裡隻有平均50 IOPS。
另外Disk Write IO Throttled/sec性能計數器也會告訴為你資源調控器的IOPS的限制數。
使用以前的資源調控器，查詢本身毫無辦法，它是否被壓制了。這對性能調優也是個非常重要的因素。當啟用資源調控器時，沒有特定的等待類型出現在SQL Server裡。我的測試顯示一旦資源調控器啟用時，有更多的PAGEIOLATCH_SH/PAGEIOLATCH_EX等待類型，這就對了。下面2個圖檔顯示了對于發生索引重建的會話裡具體的等待類型資訊——第1個沒有資源調控器，第2個有資源調控器壓制了IOPS。
從2個圖中可以看到，2個運作的測試有巨大的差別，尤其是在PAGEIOLATCH_EX 和 SOS_SCHEDULER_YIELD等待類型。
從我站在IOPS壓制來看，對于已有的功能來說，資源調控器是個很好的附加，這讓資源調控器更加成熟。大家可以嘗試用這個新功能解決IOPS方面的問題。
感謝關注！
參考文章：
https://www.sqlpassion.at/archive/2014/01/14/how-to-throttle-your-storage-with-resource-governor-in-sql-server-2014/
注：此文章為
WoodyTu學習MS SQL技術，收集整理相關文檔撰寫，歡迎轉載，請在文章頁面明顯位置給出此文連結！
若您覺得這篇文章還不錯請點選下右下角的推薦，有了您的支援才能激發作者更大的寫作熱情，非常感謝！