TSQL 執行計劃 - 估計行數 = 1 - 執行不佳的查詢

TSQL MSSQL 2008r2

我正在嘗試調整性能不佳的查詢。

對於正在查詢（索引搜尋）的 4 個最大表（數百萬行）中的 3 個，計劃已決定估計的行數 = 1。我認為這就是問題所在。邏輯讀取的數量非常高，我認為這是結果。

我不知道是什麼讓查詢引擎假設是這種情況。統計資訊已更新，我沒有使用任何使用者定義的函式。查詢引擎不會嘗試“convert_implicit”任何列。那麼我需要看什麼，改變以使這個查詢更有效率。

計劃在這裡，https://www.brentozar.com/pastetheplan/?id=S18GOLFig

粘貼計劃中缺少變數。他們來了：

DECLARE  @1stDay_StartExtract   DATE
       ,@LastDay_EndExtract    DATETIME
       ,@WhenProcessLastTime   DATETIME;

SET @1stDay_StartExtract= DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE())-3, 0); 
SET @LastDay_EndExtract = DATEADD(SECOND,-1,DATEADD(MONTH, DATEDIFF(MONTH,0,GETDATE())-1,0)); 

DECLARE  @dtStartDate_LT DATETIME= DATEADD( DAY, -1,@1stDay_StartExtract )
       ,@dtEndDate_LT   DATETIME= DATEADD( DAY, 1, @LastDay_EndExtract );

如果您需要更多資訊，請與我們聯繫。

先感謝您。

我知道解決由基數錯誤估計引起的查詢性能問題的三種主要方法：

1. 給優化器更多資訊

如果查詢優化器有更高質量的資訊來通知模型，它通常會更好地工作。此處的步驟可以包括更新統計資訊、創建新統計資訊、使用RECOMPILE提示傳遞文字值或變數，或具體化關鍵中間結果集以提供更好的基數估計或索引。

2. 重寫查詢以使優化器更清楚

這可以包括簡化程式碼以刪除冗餘過濾器或重構它以使優化器更清楚。查詢看起來很複雜，我們沒有視圖程式碼，所以很難說更多。查詢中有一些過濾器看起來非常複雜。優化器無法很好地猜測這些過濾器將如何影響結果，這一點我一點都不感到驚訝。

3. 利用 SQL Server 增強功能

有時，您可以使用某些功能來使 SQL Server 更好地處理您的工作負載。如果您不使用跟踪標誌 4199，則可以使用它測試此查詢。跟踪標誌 4199 是 Microsoft 多年來所做的查詢優化修復的集合。它是我在 SQL Server 2016 中的預設設置。跟踪標誌 2301 不太簡單。它圍繞連接基數估計對優化器進行了一些更改，粗略地說，您可以說優化器更加努力地尋找更好的計劃。它的風險更大，而且不像跟踪標誌 4199 那樣常見。可能不實用，但值得一提的是，每個新版本的 SQL Server 都會進行更改以提高查詢性能。在 SQL Server 2014 中，有一個新的基數估計器模型，它更適合某些工作負載。

對於您的特定查詢，我還想指出，很容易誤讀您看到的單行估計。您在嵌套循環內側看到的估計行數是循環每次迭代返回的行數。看到從嵌套循環查找估計的一行是常見的，通常不是問題的跡象。

但是，查詢外部部分的基數估計值有點偏離（實際為 36269 行，而實際為 6976 行）。使用嵌套循環看到大量邏輯讀取並懷疑部分查詢很慢並且需要改進是很自然的。我發現嘗試考慮查詢優化器應該做什麼來獲取它需要的數據很有用。雜湊連接會更好嗎？合併加入？具有不同索引的嵌套循環？

我沒有完整的圖片，但是您呼叫的嵌套循環連接對我來說看起來並沒有那麼糟糕。我沒有看到任何關鍵查找，並且其中一個索引正在覆蓋。向前推進的一種方法是將查詢的所有結果具體化直到該點。收集臨時表的統計資訊。然後查看調整後的查詢的查詢計劃，看看執行需要多長時間。如果查詢計劃發生了更好的變化，那麼您就有了一個有用的線索，知道如何讓它執行得更快。如果它沒有改變，那麼您至少可以更精確地測量您認為緩慢的部分是什麼。祝你好運！

在處理更大的數據集時，將所有這些 OR 和 AND 結合起來可能會降低效率。如果您將它們分解為聯合在一起的單獨查詢，則可能有助於改進執行計劃。

嘗試改變這個：

  SELECT TOP 250 --Just for testing
    V.vNodeId
   ,V.VehicleKey
   ,V.BusGrpId
   ,V.BusinessGroupKey
   ,V.CreateDate 'StartDate'
   ,V.DeletedTime
  FROM
   dbo.vw_xED_Test_Month V
  WHERE
   (
    ( CreateDate <= @1stDay_StartExtract AND DeletedTime IS NULL ) 
    OR ( CreateDate BETWEEN @1stDay_StartExtract AND @LastDay_EndExtract AND DeletedTime IS NULL ) 
    OR ( CreateDate <= @1stDay_StartExtract AND COALESCE(DeletedTime,'2100-12-31') BETWEEN @1stDay_StartExtract AND @LastDay_EndExtract )
    OR ( CreateDate BETWEEN @1stDay_StartExtract AND @LastDay_EndExtract ) 
    OR ( @1stDay_StartExtract BETWEEN CreateDate AND COALESCE(DeletedTime,'2100-12-31') ) --MI

對此：

SELECT V.vNodeId ,V.VehicleKey ,V.BusGrpId ,V.BusinessGroupKey ,V.CreateDate 'StartDate' ,V.DeletedTime
  FROM
   dbo.vw_xED_Test_Month V
WHERE ( CreateDate <= @1stDay_StartExtract AND DeletedTime IS NULL ) 
UNION
SELECT V.vNodeId ,V.VehicleKey ,V.BusGrpId ,V.BusinessGroupKey ,V.CreateDate 'StartDate' ,V.DeletedTime
  FROM
   dbo.vw_xED_Test_Month V
WHERE ( CreateDate BETWEEN @1stDay_StartExtract AND @LastDay_EndExtract AND DeletedTime IS NULL ) 
UNION
SELECT V.vNodeId ,V.VehicleKey ,V.BusGrpId ,V.BusinessGroupKey ,V.CreateDate 'StartDate' ,V.DeletedTime
  FROM
   dbo.vw_xED_Test_Month V
WHERE ( CreateDate <= @1stDay_StartExtract AND COALESCE(DeletedTime,'2100-12-31') BETWEEN @1stDay_StartExtract AND @LastDay_EndExtract )
UNION
SELECT V.vNodeId ,V.VehicleKey ,V.BusGrpId ,V.BusinessGroupKey ,V.CreateDate 'StartDate' ,V.DeletedTime
  FROM
   dbo.vw_xED_Test_Month V
WHERE ( CreateDate BETWEEN @1stDay_StartExtract AND @LastDay_EndExtract ) 
UNION
SELECT V.vNodeId ,V.VehicleKey ,V.BusGrpId ,V.BusinessGroupKey ,V.CreateDate 'StartDate' ,V.DeletedTime
  FROM
   dbo.vw_xED_Test_Month V
WHERE ( @1stDay_StartExtract BETWEEN CreateDate AND COALESCE(DeletedTime,'2100-12-31') ) 

注意： UNION 刪除重複項，而不是 UNION ALL 包括重複項。

這是一些與分解複雜查詢相關的好資訊。

也有可能用 ISNULL 替換大部分或所有這些 COALESCE 可以提高性能。

這是一些與 COALESCE 和 ISNULL 相關的好資訊。

引用那篇文章：

ISNULL 函式和 COALESCE 表達式具有相似的目的，但可以表現不同。因為 ISNULL 是一個函式，所以它只計算一次。如上所述，可以多次計算 COALESCE 表達式的輸入值。

似乎您還可以簡化一些事情，例如結合這兩個條件：

( CreateDate <= @1stDay_StartExtract AND DeletedTime IS NULL ) 
    OR ( CreateDate BETWEEN @1stDay_StartExtract AND @LastDay_EndExtract AND DeletedTime IS NULL ) 

進入這一條件：

CreateDate <= @LastDay_EndExtract AND DeletedTime IS NULL

引用自：https://dba.stackexchange.com/questions/167452