這期內(nèi)容當中小編將會給大家?guī)碛嘘P(guān)如何調(diào)優(yōu)SQL Server查詢，文章內(nèi)容豐富且以專業(yè)的角度為大家分析和敘述，閱讀完這篇文章希望大家可以有所收獲。

創(chuàng)新互聯(lián)公司網(wǎng)站建設(shè)提供從項目策劃、軟件開發(fā)，軟件安全維護、網(wǎng)站優(yōu)化(SEO)、網(wǎng)站分析、效果評估等整套的建站服務(wù)，主營業(yè)務(wù)為成都做網(wǎng)站、網(wǎng)站建設(shè)、外貿(mào)營銷網(wǎng)站建設(shè)，成都App制作以傳統(tǒng)方式定制建設(shè)網(wǎng)站，并提供域名空間備案等一條龍服務(wù)，秉承以專業(yè)、用心的態(tài)度為用戶提供真誠的服務(wù)。創(chuàng)新互聯(lián)公司深信只要達到每一位用戶的要求，就會得到認可，從而選擇與我們長期合作。這樣，我們也可以走得更遠！

在今天的文章里，我想給你展示下，當你想對特定查詢創(chuàng)建索引設(shè)計時，如何把你的工作和思考過程傳達給查詢優(yōu)化器。下面就一起來探討一下吧！

有問題的查詢我們來看下列查詢：

 DECLARE @i INT = 999 SELECT   SalesOrderID,    SalesOrderDetailID,   CarrierTrackingNumber,    OrderQty,    LineTotal FROM Sales.SalesOrderDetail WHERE ProductID < @i ORDER BY CarrierTrackingNumber GO

如你所見，這里用了一個本地變量與一個不等于謂語來從Sales.SalesOrderDetail表來獲取一些記錄。當你執(zhí)行那個查詢，看它的執(zhí)行計劃時，你會發(fā)現(xiàn)它有一些嚴重的問題：

SQL Server需要掃描Sales.SalesOrderDetail表的整個非聚集索引，因為沒有支持的非聚集索引。對這個掃描，查詢需要1382個邏輯讀，運行時間近800毫秒。  查詢優(yōu)化器在查詢計劃里引入了篩選器（Filter）運算符，它進行逐行比較用來檢查符合的行（ProductID < @i）  因為ORDER BY CarrierTrackingNumber，在執(zhí)行計劃里一個排序（Sort）運算符被引入。  排序運算符蔓延到了TempDb，因為不正確的基數(shù)計算（Cardinality Estimation）。用了帶了本地變量與不等于謂語的組合，SQL Server從表的基數(shù)硬碼估計30%的行。在我們的情況里估計行數(shù)是36395（121317 * 30%）。實際上查詢返回120621行，這意味這排序（Sort）運算符必須蔓延到TempDb，因為請求的內(nèi)存授予太小了。

現(xiàn)在我問你——你能改善這個查詢么？你的建議是什么？休息下，想個幾分鐘。不修改查詢本身，你如何改善這個查詢？

我們來調(diào)試查詢！當然，我們要做索引相關(guān)的調(diào)整來改善。沒有支持的非聚集索引，那只能是查詢優(yōu)化器可以使用計劃來運行我們的查詢。但對這個指定查詢，什么是好的非聚集索引呢？一般來說，我通過看搜索謂語來考慮可能的非聚集速印。在我們的例子里，搜索謂語如下：

WHERE ProductID < @i

我們請求在ProductID列過濾的行。因此我們想在那個列創(chuàng)建支持的非聚集索引。我們建立索引：

CREATE NONCLUSTERED INDEX idx_Test ON Sales.SalesOrderDetail(ProductID) GO

在非聚集索引創(chuàng)建后，我們需要驗證下改變，因此我們再次執(zhí)行剛才的查詢代碼。結(jié)果如何捏？查詢優(yōu)化器并沒有使用我們剛創(chuàng)建的非聚集索引！我們在搜索謂語上創(chuàng)建了支持的非聚集索引，查詢優(yōu)化器沒有引用它？通常人們對此就無轍了。其實我們可以提示查詢優(yōu)化器來使用非聚集索引，來更好的理解“為什么”查詢優(yōu)化器沒有自動選擇索引：

 DECLARE @i INT = 999  SELECT  SalesOrderID,   SalesOrderDetailID,  CarrierTrackingNumber,   OrderQty,   LineTotalFROM Sales.SalesOrderDetail WITH (INDEX(idx_Test))WHERE ProductID < @i ORDER BY CarrierTrackingNumber GO

當你現(xiàn)在看執(zhí)行計劃時，你會看到下列的野性——一個并行計劃：

查詢花費了370109個邏輯讀！運行時間基本和剛才的一樣。這里到底發(fā)生了什么？當你仔細看執(zhí)行計劃，你會發(fā)現(xiàn)查詢優(yōu)化器引入了書簽查找，因為剛才創(chuàng)建的非聚集索引，對于查詢來說，不是一個覆蓋非聚集索引。查詢越過了所謂的臨界點（Tipping Point），因為我們用當前的搜索謂語來獲得幾乎所有行。因此用非聚集索引和書簽查找來組合沒有意義。

不去想為什么查詢優(yōu)化器不選擇剛才創(chuàng)建的非聚集索引，我們已經(jīng)把自己的思路表達給了查詢優(yōu)化器本身，通過查詢提示進行了詢問了查詢優(yōu)化器，為什么非聚集索引沒被自動選擇。如我剛開始說的：我不想考慮太多。

使用非聚集索引解決這個問題，在非聚集索引的葉子層，我們必須對從SELECT列表的請求的額外列進行包含。你可以再次看下書簽查找來看下在葉子層哪些列當前丟失：

CarrierTrackingNumber  OrderQty  UnitPrice  UnitDiscountPrice

我們重建那個非聚集索引：

CREATE NONCLUSTERED INDEX idx_Test ON Sales.SalesOrderDetail(ProductID)INCLUDE (CarrierTrackingNumber, OrderQty, UnitPrice, UnitPriceDiscount) WITH( DROP_EXISTING = ON )GO

我們已經(jīng)做出了另1個改變，因此我們可以重新運行了查詢來驗證下。但是這次我們不加查詢提示，因為現(xiàn)在查詢優(yōu)化器會自動選擇非聚集索引。結(jié)果如何捏？當你看執(zhí)行計劃時，索引現(xiàn)在已被選擇。

SQL Server現(xiàn)在在非聚集索引上進行了查找操作，但在執(zhí)行計劃里我們還有排序（Sort）運算符。因為基數(shù)計算30%的硬編碼，排序（Sort）還是要蔓延到TempDb。偶滴神！我們的邏輯讀已經(jīng)降到了757，但運行時間還是近800毫秒。你現(xiàn)在應(yīng)該怎么做？

現(xiàn)在我們可以嘗試在非聚集索引的導(dǎo)航結(jié)構(gòu)直接包含CarrierTrackingNumber列。這是SQL Server進行排序運算符的列。當我們在非聚集索引直接加了這列（作為主鍵），我們就物理排序了那列，因此排序（Sort）運算符應(yīng)該會消失。作為積極的副作用，也不會蔓延到TempDb。在執(zhí)行計劃里，現(xiàn)在也沒有運算符關(guān)心錯誤的基數(shù)計算。因此我們嘗試那個假設(shè)，再次重建非聚集索引：

 CREATE NONCLUSTERED INDEX idx_Test ON Sales.SalesOrderDetail(CarrierTrackingNumber, ProductID)INCLUDE (OrderQty, UnitPrice, UnitPriceDiscount) WITH(   DROP_EXISTING = ON )GO

從索引定義可以看到，現(xiàn)在我們已經(jīng)對CarrierTrackingNumber和ProductID列的數(shù)據(jù)物理預(yù)排序。當你再次重新執(zhí)行查詢，在你查看執(zhí)行計劃時，你會看到排序（Sort）運算符已經(jīng)消失，SQL Server掃描了非聚集索引的整個葉子層（使用剩余謂語（residual predicate）作為搜索謂語）。

這個執(zhí)行計劃并不壞！我們只需要763個邏輯讀，現(xiàn)在的運行時間已經(jīng)降至600毫秒。和剛才的相比已經(jīng)有25%的改善！但是：查詢優(yōu)化器建議我們一個更好的非聚集索引，通過缺少索引建議（Missing Index Recommendations）！暫且相信下，我們創(chuàng)建建議的非聚集索引：

CREATE NONCLUSTERED INDEX [SQL Server doesn't care about names, why I should care about names?]ON [Sales].[SalesOrderDetail] ([ProductID])INCLUDE ([SalesOrderID],[SalesOrderDetailID],[CarrierTrackingNumber],[OrderQty],[LineTotal])GO

當你現(xiàn)在重新執(zhí)行最初的查詢，你會發(fā)現(xiàn)令人驚訝的事情：查詢優(yōu)化器使用“我們”剛才創(chuàng)建的非聚集索引，缺少索引建議已經(jīng)消失！

你剛剛創(chuàng)建了SQL Server從不使用的索引——除了INSERT，UPDATE和DELETE語句，SQL Server都要去維護你的非聚集索引。對于你的數(shù)據(jù)庫，你剛創(chuàng)建了“單純”浪費空間的索引。當另一方面，你已經(jīng)通過消除丟失索引建議，滿足了查詢優(yōu)化器。但這不是目的：目的是創(chuàng)建會被再次使用的索引。

結(jié)論：永不相信查詢優(yōu)化器！

上述就是小編為大家分享的如何調(diào)優(yōu)SQL Server查詢了，如果剛好有類似的疑惑，不妨參照上述分析進行理解。如果想知道更多相關(guān)知識，歡迎關(guān)注創(chuàng)新互聯(lián)行業(yè)資訊頻道。

網(wǎng)站欄目：如何調(diào)優(yōu)SQLServer查詢-創(chuàng)新互聯(lián)
分享路徑：http://weahome.cn/article/dpsisc.html