作為一個(gè)DBA，排除SQL Server問題是我們的職責(zé)之一，每個(gè)月都有很多人給我們帶來(lái)各種不能解釋卻要解決的性能問題。

我就多次聽到，以前的SQL Server的性能問題都還好且在正常范圍內(nèi)，但現(xiàn)在一切已經(jīng)改變，SQL Server開始糟糕， 瘋狂的事情不能解釋。在這個(gè)情況下我介入，分析下整個(gè)SQL Server的安裝，最后用一些神奇的調(diào)查方法找出性能問題的根源。

但很多時(shí)候問題的根源是一樣的：所謂的計(jì)劃回歸（Plan Regression），即特定查詢的執(zhí)行計(jì)劃已經(jīng)改變。昨天SQL Server已經(jīng)緩存了在計(jì)劃緩存里緩存了一個(gè)好的執(zhí)行計(jì)劃，今天就生成、緩存最后重用了一個(gè)糟糕的執(zhí)行計(jì)劃——不斷重復(fù)。

進(jìn)入SQL Server 2016后，我就變得有點(diǎn)多余了，以為微軟引進(jìn)了查詢存儲(chǔ)（Query Store）。這是這個(gè)版本最熱門的功能！查詢存儲(chǔ)幫助你很容易找出你的性能問題是不是計(jì)劃回歸造成的。如果你找到了計(jì)劃回歸，這很容易強(qiáng)制一個(gè)特定計(jì)劃不使用計(jì)劃向?qū)АＢ犉饋?lái)很有意思？讓我們通過一個(gè)特定的場(chǎng)景，向你展示下在SQL Server 2016里，如何使用查詢存儲(chǔ)來(lái)找出并最終修正計(jì)劃回歸。

查詢存儲(chǔ)（Query Store）——我的對(duì)手

在SQL Server 2016里，在你使用查詢存儲(chǔ)功能前，你要對(duì)這個(gè)數(shù)據(jù)庫(kù)啟用它。這是通過ALTER DATABASE語(yǔ)句實(shí)現(xiàn)，如你所見的下列代碼：

CREATE DATABASE QueryStoreDemo
GO

USE QueryStoreDemo
GO

-- Enable the Query Store for our database
ALTER DATABASE QueryStoreDemo
SET QUERY_STORE = ON
GO

-- Configure the Query Store
ALTER DATABASE QueryStoreDemo SET QUERY_STORE
(
 OPERATION_MODE = READ_WRITE, 
 CLEANUP_POLICY = (STALE_QUERY_THRESHOLD_DAYS = 367), 
 DATA_FLUSH_INTERVAL_SECONDS = 900, 
 INTERVAL_LENGTH_MINUTES = 1, 
 MAX_STORAGE_SIZE_MB = 100, 
 QUERY_CAPTURE_MODE = ALL, 
 SIZE_BASED_CLEANUP_MODE = OFF
)
GO

在線幫助為你提供了各個(gè)選項(xiàng)的詳細(xì)信息。接下來(lái)我創(chuàng)建一個(gè)簡(jiǎn)單的表，創(chuàng)建一個(gè)非聚集索引，最后插入80000條記錄。

-- Create a new table
CREATE TABLE Customers
(
 CustomerID INT NOT NULL PRIMARY KEY CLUSTERED,
 CustomerName CHAR(10) NOT NULL,
 CustomerAddress CHAR(10) NOT NULL,
 Comments CHAR(5) NOT NULL,
 Value INT NOT NULL
)
GO

-- Create a supporting new Non-Clustered Index.
CREATE UNIQUE NONCLUSTERED INDEX idx_Test ON Customers(Value)
GO

-- Insert 80000 records
DECLARE @i INT = 1
WHILE (@i = 80000)
BEGIN
 INSERT INTO Customers VALUES
 (
  @i,
  CAST(@i AS CHAR(10)),
  CAST(@i AS CHAR(10)),
  CAST(@i AS CHAR(5)),
  @i
 )
 
 SET @i += 1
END
GO

為了訪問我們的表，我額創(chuàng)建了一個(gè)簡(jiǎn)單的存儲(chǔ)過程，傳入value值作為過濾謂語(yǔ)。

-- Create a simple stored procedure to retrieve the data
CREATE PROCEDURE RetrieveCustomers
(
 @Value INT
)
AS
BEGIN
 SELECT * FROM Customers
 WHERE Value  @Value
END
GO

現(xiàn)在我用80000的參數(shù)值來(lái)執(zhí)行存儲(chǔ)過程。

-- Execute the stored procedure.
 -- This generates an execution plan with a Key Lookup (Clustered).
 EXEC RetrieveCustomers 80000
 GO

現(xiàn)在當(dāng)你查看實(shí)際的執(zhí)行計(jì)劃時(shí)，你會(huì)看到查詢優(yōu)化器已經(jīng)選擇了有419個(gè)邏輯讀的聚集索引掃描運(yùn)算符。SQL Server并沒有使用非聚集索引，因?yàn)檫@樣沒有意義，由于臨界點(diǎn)。這個(gè)查詢結(jié)果并沒有選擇性。

現(xiàn)在假設(shè)SQL Server發(fā)生了些事情（例如重啟，故障轉(zhuǎn)移），SQL Server忽略已經(jīng)緩存的計(jì)劃，這里我通過執(zhí)行DBCC FREEPROCCACHE從計(jì)劃緩存里抹掉每個(gè)緩存的計(jì)劃來(lái)模擬SQL Server重啟（不要在生產(chǎn)環(huán)境里使用！）。

 -- Get rid of the cached execution plan...
 DBCC FREEPROCCACHE
 GO

現(xiàn)在有人再次調(diào)用你的存儲(chǔ)過程，這次輸入?yún)?shù)值是1。這次執(zhí)行計(jì)劃不一樣，因?yàn)楝F(xiàn)在在執(zhí)行計(jì)劃里你會(huì)有書簽查找。SQL Server估計(jì)行數(shù)是1，在非聚集索引里沒有找到任何行。因此與非聚集索引查找結(jié)合的書簽查找才有意義，因?yàn)檫@個(gè)查詢是有選擇性的。

現(xiàn)在我再執(zhí)行用80000參數(shù)值的查詢。

-- Execute the stored procedure
EXEC RetrieveCustomers 1
GO

-- Execute the stored procedure again
-- This introduces now a plan regression, because now we get a Clustered Index Scan
-- instead of the Key Lookup (Clustered).
EXEC RetrieveCustomers 80000
GO

當(dāng)你再次看STATISTICS IO的輸出，你會(huì)看到這個(gè)查詢現(xiàn)在產(chǎn)生了160139個(gè)邏輯讀——?jiǎng)偛诺牟樵冎挥?19個(gè)邏輯讀。這個(gè)時(shí)候DBA的手機(jī)就會(huì)響起，性能問題。但今天我們要不同的方式解決——使用剛才啟用的查詢存儲(chǔ)。

當(dāng)你再次看實(shí)際的執(zhí)行計(jì)劃，在你面前你會(huì)看到有一個(gè)計(jì)劃回歸，因?yàn)镾QL Server剛重用了書簽查找的的計(jì)劃緩存。剛才你有聚集索引掃描運(yùn)算符的執(zhí)行計(jì)劃。這是SQL Server里參數(shù)嗅探的副作用。

讓我們通過查詢存儲(chǔ)來(lái)詳細(xì)了解這個(gè)問題。在SSMS里的對(duì)象資源管理器里，SQL Server 2016提供了一個(gè)新的結(jié)點(diǎn)叫查詢存儲(chǔ)，這里你會(huì)看到一些報(bào)表。

【前幾個(gè)資源使用查詢】向你展示了最昂貴的查詢，基于你選擇的維度。這里切換到【邏輯讀取次數(shù)】。

這里在你面前有一些查詢。最昂貴的查詢生成了近500000個(gè)邏輯讀。這是我們的初始語(yǔ)句。這已經(jīng)是第一個(gè)WOW效果的的查詢存儲(chǔ)：SQL Server重啟后，查詢存儲(chǔ)的數(shù)據(jù)還是存在的！第2個(gè)是你存儲(chǔ)過程里的SELECT語(yǔ)句。在查詢存儲(chǔ)里每個(gè)捕獲的查詢都有一個(gè)標(biāo)示號(hào)——這里是7。最后當(dāng)你看報(bào)告的右邊，你會(huì)看這個(gè)查詢的不同執(zhí)行計(jì)劃。

如你所見，查詢存儲(chǔ)捕獲了2個(gè)不同的執(zhí)行計(jì)劃，一個(gè)ID是7，一個(gè)ID是8。當(dāng)你點(diǎn)擊計(jì)劃ID時(shí)，SQL Server會(huì)在報(bào)表的最下面為你顯示估計(jì)的執(zhí)行計(jì)劃。

計(jì)劃8是聚集索引掃描，計(jì)劃7是書簽查找。如你所見，使用查詢存儲(chǔ)分析計(jì)劃回歸非常簡(jiǎn)單。但你現(xiàn)在還沒結(jié)束。你現(xiàn)在可以對(duì)指定的查詢強(qiáng)制執(zhí)行計(jì)劃。 現(xiàn)在你知道包含聚集索引掃描的執(zhí)行計(jì)劃有更好的性能。因此現(xiàn)在你可以通過點(diǎn)擊【強(qiáng)制執(zhí)行計(jì)劃】強(qiáng)制查詢7使用執(zhí)行計(jì)劃。

搞定，我們已經(jīng)解決問題了！

現(xiàn)在當(dāng)你執(zhí)行存儲(chǔ)過程（用80000的輸入?yún)?shù)值），在執(zhí)行計(jì)劃里你可以看到聚集索引掃描，執(zhí)行計(jì)劃只生成419個(gè)邏輯讀——很簡(jiǎn)單，是不是？絕對(duì)不是！！！！

微軟告訴我們只給修正SQL Server性能相關(guān)的“新方式”。你只是強(qiáng)制了特定的計(jì)劃，一切都還好。這個(gè)方法有個(gè)大的問題，因?yàn)樾阅軉栴}的根源并沒有解決！這個(gè)問題的關(guān)鍵是因?yàn)闀灢檎矣?jì)劃沒有穩(wěn)定性。取決于首次執(zhí)行計(jì)劃默認(rèn)的輸入值，執(zhí)行計(jì)劃因此就被不斷重用。

通常我會(huì)建議調(diào)整下你的索引設(shè)計(jì)，創(chuàng)建一個(gè)覆蓋索引來(lái)保證計(jì)劃的穩(wěn)定性。但強(qiáng)制特定執(zhí)行計(jì)劃只是臨時(shí)解決問題——你還是要修正你問題的根源。

小結(jié)

不要誤解我：SQL Server 2016里的查詢存儲(chǔ)功能很棒，可以幫你更容易理解計(jì)劃回歸。它也會(huì)幫你“臨時(shí)”強(qiáng)制特定的執(zhí)行計(jì)劃。但性能調(diào)優(yōu)的目標(biāo)還是一樣：你要找到問題根源，嘗試解決問題——不要在外面晃蕩！