如何在SQL Server 2014中用資源調(diào)控器壓制你的存儲(chǔ)？

在今天的文章里，我想談下SQL Server 2014里非?？岬奶嵘含F(xiàn)在你終于可以根據(jù)需要的IOPS來(lái)壓制查詢！資源調(diào)控器（Resource Governor）自SQL Server 2008起引入，但提供的功能還是有所限制：你只能限制CPU時(shí)間（這個(gè)已經(jīng)很棒了），還有你能限制查詢（從每個(gè)獨(dú)立的查詢）內(nèi)存量。

但作為DBA的你，你經(jīng)常會(huì)進(jìn)行一些數(shù)據(jù)庫(kù)維護(hù)操作，例如索引重建，DBCC CHECKDB操作等。我們都知道，這些操作會(huì)在你的存儲(chǔ)里帶來(lái)大量的IOPS直至峰值。如果在7 * 24在線的數(shù)據(jù)庫(kù)來(lái)說(shuō)，這個(gè)會(huì)影響你的生產(chǎn)力，給業(yè)務(wù)和銷售額帶來(lái)很大影響。

自SQL Server 2014開始，這個(gè)情況就變了，因?yàn)槟憧梢酝ㄟ^(guò)資源調(diào)控器來(lái)部署指定的資源池來(lái)限制IOPS使用率。當(dāng)你隔離你的DBA操作到指定的資源池時(shí)，你能指定資源池可以使用的最大IOPS（包括最小IOPS）。因此你可以壓制下DBA操作需要的IOPS。你的生產(chǎn)工作量就可以更好的使用你的存儲(chǔ)。更多信息可以查看微軟在線幫助。

我想用一個(gè)非常簡(jiǎn)單的例子來(lái)展示下這個(gè)行為。假設(shè)你是DBA，正要進(jìn)行常規(guī)索引重建操作，這個(gè)需要通過(guò)資源調(diào)控器對(duì)它們的最大IOPS使用率進(jìn)行控制。第1步我們?yōu)镈BA操作創(chuàng)建專用的資源池和工作負(fù)荷組。

-- Create a new Resource Pool for the DBAs.
-- We use a very high value for MAX_IOPS_PER_VOLUME so that we are
-- currently running unlimited.
CREATE RESOURCE POOL DbaPool WITH
(
 MAX_IOPS_PER_VOLUME = 100000
 )
GO

-- Create a new Workload Group for the DBAs
CREATE WORKLOAD GROUP DbaGroup
USING DbaPool
GO

從剛才的代碼可以看到，CREATE RESOURCE POOL語(yǔ)句現(xiàn)在為你提供MAX_IOPS_PER_VOLUME屬性（包括MIN_IOPS_PER_VOLUME）。這里我設(shè)置了一個(gè)很高的值，因此在第一次執(zhí)行時(shí)IOPS不會(huì)受限，這里我們根據(jù)需要的IOPS建立了初始基線。下一步我會(huì)創(chuàng)建資源調(diào)控器需要的分類函數(shù)。

-- Create a new Classifier Function for Resource Governor
CREATE FUNCTION dbo.MyClassifierFunction()
RETURNS SYSNAME WITH SCHEMABINDING
AS
BEGIN
DECLARE @GroupName SYSNAME
 
IF SUSER_NAME() = 'DbaUser'
BEGIN
SET @GroupName = 'DbaGroup'
END
ELSE
BEGIN
SET @GroupName = 'Default'
END
 
RETURN @GroupName;
END
GO

在分類函數(shù)里我們根據(jù)登錄進(jìn)行評(píng)估。如果登錄是DbaUser，進(jìn)入的會(huì)話會(huì)在DbaGroup工作負(fù)荷組里。否則就進(jìn)入默認(rèn)的工作負(fù)荷組。最后我們?cè)谫Y源調(diào)控器注冊(cè)并配置它，這樣我們的設(shè)置就生效了。

-- Register the Classifier Function within Resource Governor
ALTER RESOURCE GOVERNOR WITH
(
 CLASSIFIER_FUNCTION = dbo.MyClassifierFunction
 )
GO

-- Reconfigure Resource Governor
ALTER RESOURCE GOVERNOR RECONFIGURE
GO

現(xiàn)在當(dāng)你創(chuàng)建名為DbaUser的登錄時(shí)，你可以用它連接到你的SQL Server。你可以在DMV sys.dm_exec_sessions 看下 group_id列驗(yàn)證下到來(lái)的會(huì)話是否在正確的工作負(fù)荷組里。下一步我在ContoRetailDW數(shù)據(jù)庫(kù)的FactOnlineSales表里的DataKey里創(chuàng)建一個(gè)非聚集索引。

-- Create a simple Non-Clustered Index
CREATE NONCLUSTERED INDEX idx_DateKey ON FactOnlineSales(DateKey)
GO

我們從開始就創(chuàng)建了資源池，現(xiàn)在在我們?cè)谖覀兊馁Y源池里并沒(méi)有限制。因此當(dāng)我們現(xiàn)在進(jìn)行剛才創(chuàng)建的非聚集索引的索引重建時(shí)，SQL Server會(huì)占用大量的IOPS。我們可以通過(guò)性能監(jiān)控里的“SQL Server:Resource Pool Stats:Disk Write IO/Sec”性能計(jì)數(shù)器來(lái)驗(yàn)證剛才創(chuàng)建的資源池。

ALTER INDEX idx_DateKey ON FactOnlineSales REBUILD
GO

可以看到索引重建花費(fèi)近100的IOPS。接下來(lái)我要做的是限制DbaPool資源池為僅50的IOPS：

-- Let's change the Resource Pool by lowering the maximum IOPS.
ALTER RESOURCE POOL DbaPool WITH
(
 MAX_IOPS_PER_VOLUME = 50
 )
GO

現(xiàn)在當(dāng)你執(zhí)行索引重建時(shí)，在性能監(jiān)視器里可以清楚看到，在特定的資源池里只有平均50 IOPS。

另外Disk Write IO Throttled/sec性能計(jì)數(shù)器也會(huì)告訴為你資源調(diào)控器的IOPS的限制數(shù)。

使用以前的資源調(diào)控器，查詢本身毫無(wú)辦法，它是否被壓制了。這對(duì)性能調(diào)優(yōu)也是個(gè)非常重要的因素。當(dāng)啟用資源調(diào)控器時(shí)，沒(méi)有特定的等待類型出現(xiàn)在SQL Server里。我的測(cè)試顯示一旦資源調(diào)控器啟用時(shí)，有更多的PAGEIOLATCH_SH/PAGEIOLATCH_EX等待類型，這就對(duì)了。下面2個(gè)圖片顯示了對(duì)于發(fā)生索引重建的會(huì)話里具體的等待類型信息——第1個(gè)沒(méi)有資源調(diào)控器，第2個(gè)有資源調(diào)控器壓制了IOPS。

從2個(gè)圖中可以看到，2個(gè)運(yùn)行的測(cè)試有巨大的區(qū)別，尤其是在PAGEIOLATCH_EX 和 SOS_SCHEDULER_YIELD等待類型。

從我站在IOPS壓制來(lái)看，對(duì)于已有的功能來(lái)說(shuō)，資源調(diào)控器是個(gè)很好的附加，這讓資源調(diào)控器更加成熟。

大家可以嘗試用這個(gè)新功能解決IOPS方面的問(wèn)題。

以上所述就是本文的全部?jī)?nèi)容，希望對(duì)大家的學(xué)習(xí)有所幫助。