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

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

但很多時(shí)候問(wèn)題的根源是一樣的：所謂的計(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ǔ)幫助你很容易找出你的性能問(wèn)題是不是計(jì)劃回歸造成的。如果你找到了計(jì)劃回歸，這很容易強(qiáng)制一個(gè)特定計(jì)劃不使用計(jì)劃向?qū)?。?tīng)起來(lái)很有意思？讓我們通過(guò)一個(gè)特定的場(chǎng)景，向你展示下在SQL Server 2016里，如何使用查詢存儲(chǔ)來(lái)找出并最終修正計(jì)劃回歸。

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

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

CREATE DATABASE QueryStoreDemoGOUSE QueryStoreDemoGO-- Enable the Query Store for our databaseALTER DATABASE QueryStoreDemoSET QUERY_STORE = ONGO-- Configure the Query StoreALTER 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 tableCREATE 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 recordsDECLARE @i INT = 1WHILE (@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 += 1ENDGO

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

-- Create a simple stored procedure to retrieve the dataCREATE PROCEDURE RetrieveCustomers( @Value INT)ASBEGIN SELECT * FROM Customers WHERE Value < @ValueENDGO

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

-- 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并沒(méi)有使用非聚集索引，因?yàn)檫@樣沒(méi)有意義，由于臨界點(diǎn)。這個(gè)查詢結(jié)果并沒(méi)有選擇性。

現(xiàn)在假設(shè)SQL Server發(fā)生了些事情（例如重啟，故障轉(zhuǎn)移），SQL Server忽略已經(jīng)緩存的計(jì)劃，這里我通過(guò)執(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ǔ)過(guò)程，這次輸入?yún)?shù)值是1。這次執(zhí)行計(jì)劃不一樣，因?yàn)楝F(xiàn)在在執(zhí)行計(jì)劃里你會(huì)有書(shū)簽查找。SQL Server估計(jì)行數(shù)是1，在非聚集索引里沒(méi)有找到任何行。因此與非聚集索引查找結(jié)合的書(shū)簽查找才有意義，因?yàn)檫@個(gè)查詢是有選擇性的。

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

-- Execute the stored procedureEXEC RetrieveCustomers 1GO-- 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 80000GO

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

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

讓我們通過(guò)查詢存儲(chǔ)來(lái)詳細(xì)了解這個(gè)問(wèn)題。在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ǔ)過(guò)程里的SELECT語(yǔ)句。在查詢存儲(chǔ)里每個(gè)捕獲的查詢都有一個(gè)標(biāo)示號(hào)——這里是7。最后當(dāng)你看報(bào)告的右邊，你會(huì)看這個(gè)查詢的不同執(zhí)行計(jì)劃。

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

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

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

微軟告訴我們只給修正SQL Server性能相關(guān)的“新方式”。你只是強(qiáng)制了特定的計(jì)劃，一切都還好。這個(gè)方法有個(gè)大的問(wèn)題，因?yàn)樾阅軉?wèn)題的根源并沒(méi)有解決！這個(gè)問(wèn)題的關(guān)鍵是因?yàn)闀?shū)簽查找計(jì)劃沒(méi)有穩(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í)解決問(wèn)題——你還是要修正你問(wèn)題的根源。

小結(jié)

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