程式師世界 >> 數據庫知識 >> 其他數據庫知識 >> MSSQL >> 若何調優SQL Server查詢

若何調優SQL Server查詢

編輯：MSSQL

若何調優SQL Server查詢。本站提示廣大學習愛好者：（若何調優SQL Server查詢）文章只能為提供參考，不一定能成為您想要的結果。以下是若何調優SQL Server查詢正文

在明天的文章裡，我想給你展現下，當你想對特定查詢創立索引設計時，若何把你的任務和思慮進程轉達給查詢優化器。上面就一路來商量一下吧！

有成績的查詢
我們來看以下查詢：

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

如你所見，這裡用了一個當地變量與一個不等於謂語來從Sales.SalesOrderDetail表來獲得一些記載。當你履行誰人查詢，看它的履行籌劃時，你會發明它有一些嚴重的成績：

SQL Server須要掃描Sales.SalesOrderDetail表的全部非集合索引，由於沒有支撐的非集合索引。對這個掃描，查詢須要1382個邏輯讀，運轉時光近800毫秒。
查詢優化器在查詢籌劃裡引入了挑選器（Filter）運算符，它停止逐行比擬用來檢討相符的行（ProductID < @i）
由於ORDER BY CarrierTrackingNumber，在履行籌劃裡一個排序（Sort）運算符被引入。
排序運算符舒展到了TempDb，由於不准確的基數盤算（Cardinality Estimation）。用了帶了當地變量與不等於謂語的組合，SQL Server從表的基數硬碼估量30%的行。在我們的情形裡估量行數是36395（121317 * 30%）。現實上查詢前往120621行，這意味這排序（Sort）運算符必需舒展到TempDb，由於要求的內存授與太小了。

如今我問你——你能改良這個查詢麼？你的建議是甚麼？歇息下，想個幾分鐘。不修正查詢自己，你若何改良這個查詢？

我們來調試查詢！
固然，我們要做索引相干的調劑來改良。沒有支撐的非集合索引，那只能是查詢優化器獨一可使用籌劃來運轉我們的查詢。但對這個指定查詢，甚麼是好的非集合索引呢？普通來講，我經由過程看搜刮謂語來斟酌能夠的非集合速印。在我們的例子裡，搜刮謂語以下：

WHERE ProductID < @i

我們要求在ProductID列過濾的行。是以我們想在誰人列創立支撐的非集合索引。我們樹立索引：

CREATE NONCLUSTERED INDEX idx_Test ON Sales.SalesOrderDetail(ProductID)

 GO

在非集合索引創立後，我們須要驗證下轉變，是以我們再次履行適才的查詢代碼。成果若何捏？查詢優化器並沒有應用我們剛創立的非集合索引！我們在搜刮謂語上創立了支撐的非集合索引，查詢優化器沒有援用它？平日人們對此就無轍了。其實我們可以提醒查詢優化器來應用非集合索引，來更好的懂得“為何”查詢優化器沒有主動選擇索引：

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

當你如今看履行籌劃時，你會看到以下的野性——一個並行籌劃：

查詢消費了370109個邏輯讀！運轉時光根本和適才的一樣。這裡究竟產生了甚麼？當你細心看履行籌劃，你會發明查詢優化器引入了書簽查找，由於適才創立的非集合索引，關於查詢來講，不是一個籠罩非集合索引。查詢超出了所謂的臨界點（Tipping Point），由於我們用以後的搜刮謂語來取得簡直一切行。是以用非集合索引和書簽查找來組合沒成心義。

不去想為何查詢優化器不選擇適才創立的非集合索引，我們曾經把本身的思緒表達給了查詢優化器自己，經由過程查詢提醒停止了訊問了查詢優化器，為何非集合索引沒被主動選擇。如我剛開端說的：我不想斟酌太多。

應用非集合索引處理這個成績，在非集合索引的葉子層，我們必需對從SELECT列表的要求的額定列停止包括。你可以再次看下書簽查找來看下在葉子層哪些列以後喪失：

CarrierTrackingNumber
OrderQty
UnitPrice
UnitDiscountPrice

我們重建誰人非集合索引：

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

我們曾經做出了另1個轉變，是以我們可以從新運轉了查詢來驗證下。然則此次我們不加查詢提醒，由於如今查詢優化器會主動選擇非集合索引。成果若何捏？當你看履行籌劃時，索引如今已被選擇。

SQL Server如今在非集合索引長進行了查找操作，但在履行籌劃裡我們還有排序（Sort）運算符。由於基數盤算30%的硬編碼，排序（Sort）照樣要舒展到TempDb。偶滴神！我們的邏輯讀曾經降到了757，但運轉時光照樣近800毫秒。你如今應當怎樣做？

如今我們可以測驗考試在非集合索引的導航構造直接包括CarrierTrackingNumber列。這是SQL Server停止排序運算符的列。當我們在非集合索引直接加了這列（作為主鍵），我們就物理排序了那列，是以排序（Sort）運算符應當會消逝。作為積極的反作用，也不會舒展到TempDb。在履行籌劃裡，如今也沒有運算符關懷毛病的基數盤算。是以我們測驗考試誰人假定，再次重建非集合索引：

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

從索引界說可以看到，如今我們曾經對CarrierTrackingNumber和ProductID列的數據物理預排序。當你再次從新履行查詢，在你檢查履行籌劃時，你會看到排序（Sort）運算符曾經消逝，SQL Server掃描了非集合索引的全部葉子層（應用殘剩謂語（residual predicate）作為搜刮謂語）。

這個履行籌劃其實不壞！我們只須要763個邏輯讀，如今的運轉時光曾經降至600毫秒。和適才的比擬曾經有25%的改良！然則：查詢優化器建議我們一個更好的非集合索引，經由過程缺乏索引建議（Missing Index Recommendations）！暫且信任下，我們創立建議的非集合索引：

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

當你如今從新履行最後的查詢，你會發明使人驚奇的工作：查詢優化器應用“我們”適才創立的非集合索引，缺乏索引建議曾經消逝！

你方才創立了SQL Server從不應用的索引——除INSERT，UPDATE和DELETE語句，SQL Server都要去保護你的非集合索引。關於你的數據庫，你剛創立了“純真”糟蹋空間的索引。當另外一方面，你曾經經由過程清除喪失索引建議，知足了查詢優化器。但這不是目標：目標是創立會被再次應用的索引。

結論：永不信任查詢優化器！

小結

明天的文章有點爭議性，但我想你向你展現下，但你在創立索引時，查詢優化器若何贊助你，還有查詢優化器若何捉弄你。是以做出小的調劑，就立刻運轉你的查詢，驗證轉變異常主要。

以上就是本文的全體內容，願望對年夜家的進修有所贊助。