由於臨時表，統計資訊更新後執行計劃錯誤

儲存過程查詢有時會在其中一個表的統計資訊更新後得到一個糟糕的計劃，但之後可以立即重新編譯為好的計劃。相同的編譯參數。

問題似乎來自在 SP 中創建然後加入的小型臨時表。糟糕的計劃在臨時表上警告連接列沒有統計資訊。是什麼賦予了？

SQL Server 2016 SP1 CU4，具有 2014 兼容級別

糟糕的計劃：

好計劃：

儲存過程

USE AppDB
GO
SET QUOTED_IDENTIFIER ON
SET ANSI_NULLS ON
GO
CREATE PROCEDURE [MySchema].[MySP]
   @MyId VARCHAR(50),
   @Months INT
AS
BEGIN

   SET NOCOUNT ON

   SELECT * 
   INTO #MyTemp
   FROM AppDB.MySchema.View_Feeder vf WITH (NOLOCK)
   WHERE vf.MyId = @MyId AND vf.Status IS NOT NULL

   SELECT wd.Col1
        , vp.Col2
        , vp.Col3 
   FROM AppDB.MySchema.View_VP vp WITH (FORCESEEK)
   INNER JOIN #MyTemp wd ON wd.Col1 = vp.Col1
   WHERE vp.Col3 > DATEADD(MONTH, @Months * -1, GETDATE())

END

內部視圖

USE AppDB
GO
SET QUOTED_IDENTIFIER ON
SET ANSI_NULLS ON
GO
CREATE VIEW [MySchema].[View_VP]
AS

   SELECT pp.Col1,
          pd.Col2 AS Col2, 
          MAX(pp.Col4) AS Col3
   FROM P_DB..LargeTable pp WITH (NOLOCK)
   INNER JOIN P_DB..SmallTable pd WITH (NOLOCK) ON pp.P_Id = pd.P_Id
   WHERE pp.[Status] IN (3, 4)
   GROUP BY pp.Col1, pd.Col2

計劃

編輯好的計劃和壞的計劃。

附加資訊

當時FORCESEEK添加了提示以嘗試處理同樣的問題並穩定計劃。無論如何，不管有沒有它，我真的很想了解這裡發生了什麼。

我無法隨意重現該問題，因此很難說用SELECT INTO顯式表替換 是否會有所作為。但是，我相信它的行為方式應該相同。

SELECT
   database_id, 
   is_auto_create_stats_on, 
   is_auto_update_stats_on, 
   is_auto_update_stats_async_on
FROM sys.databases
WHERE
   database_id IN (2, <relevant user databases>)

返回：

 database_id   is_auto_create_stats_on   is_auto_update_stats_on   is_auto_update_stats_async_on  
------------- ------------------------- ------------------------- ------------------------------- 
 2             1                         1                         0                              
 7             1                         1                         1                              
 37            1                         1                         1                              

很明顯，這種搜尋很糟糕，但問題是為什麼它一開始就沒有做好搜尋。

查詢沒有返回 1m 行，估計是錯誤的。輸出可能會有細微的變化，但行數總是很低（最多可能數百）。

即使是返回相對多行的那些也會生成由 theId而不是由 the搜尋的計劃status（如您所見，這不是選擇性的）。無論編譯什麼值，我似乎都無法重現狀態尋求計劃。我什至嘗試waitfor delay在創建臨時表和第二個查詢之間添加一個，並在第二個會話中更新統計資訊/重新編譯，也沒有任何效果。

糟糕的計劃在臨時表上警告連接列沒有統計資訊。是什麼賦予了？

這可能有一個更深奧的原因，但更可能是一個簡單的統計創建失敗。例如，當任務無法獲得所需的記憶體資源時，或者統計創建受到限制（並發編譯過多）時，可能會發生這種情況。請參閱Microsoft SQL Server 2008 中查詢優化器使用的 Microsoft 白皮書統計資訊。您可以通過查看自動統計分析器或擴展事件以及大約同時的其他事件來進一步調試。

也就是說，需要更多的資訊和調查才能將計劃選擇的責任歸咎於失去的臨時表統計資訊。即使沒有詳細的統計資訊，優化器仍然可以看到臨時表的總基數，這似乎是這裡的一個重要因素。

…但可以立即重新編譯為好的計劃。相同的編譯參數。

@Months參數可能相同，但臨時表中的行數（來自未知視圖）View_Feeder不同，並且提供的計劃不顯示 的值@MyId。

從可用資訊來看：“好”計劃（僅估計，不提供性能數據）基於包含4 行的臨時表。“壞計劃”基於一個有114 行的臨時表。當然，缺少密度和直方圖資訊可能無濟於事，但很容易看出優化器如何為 4 行和 114 行選擇不同的計劃，儘管這些計劃的密度和分佈未知。

如果對不依賴於臨時表的計劃運算符的估計大大偏離，這是一個強烈的信號，表明目前的主表統計資訊不能代表基礎數據。問題中缺乏資訊使得這無法評估。

然而，可以看到優化器被要求在次優選項之間進行選擇。所提出的兩個計劃都不是一個“明顯不錯”的選擇，因為兩者都涉及查找（缺少“覆蓋”索引）和後期過濾（見下文）。特別是查找具有與之相關的高成本，這敏感地取決於基數估計。

使用視圖會限制優化器和提示選項：

• 該視圖包含一個GROUP BY防止謂詞vp.Col3 > DATEADD(MONTH, @Months * -1, GETDATE())被下推的a，即使轉換在這種非常特殊的情況下是有效的。

  • 將視圖內聯到查詢將提供一種更早過濾日期/時間列的自然方法（儘管問題沒有說明重構查詢是否是一種選擇）。

• 不可能在視圖上提示索引，而FORCESEEK只是要求優化器找到任何索引搜尋計劃（不一定使用您喜歡的索引）。刪除視圖同樣會刪除此限制。

允許謂詞下推也應該在大表上打開索引機會。例如：

CREATE INDEX give_me_a_good_name
ON dbo.LargeTable (Col1, [Status], Col4) 
INCLUDE (P_Id);

…為重寫的查詢提供了良好的訪問路徑：

DECLARE @Date datetime = DATEADD(MONTH, @Months * -1, GETDATE());

SELECT
   MT.Col1,
   ST.Col2,
   MAX(LT.Col4)
FROM #MyTemp AS MT
JOIN dbo.LargeTable AS LT
   ON LT.Col1 = MT.Col1
JOIN dbo.SmallTable AS ST
   ON ST.P_id = LT.P_Id
WHERE
   LT.[Status] IN (3, 4)
   AND LT.Col4 > @Date
GROUP BY
   MT.Col1,
   ST.Col2
OPTION (RECOMPILE);

另一個考慮因素是臨時表和統計資訊記憶體的影響，如我的文章儲存過程中的臨時表和解釋的臨時表記憶體中所述。如果一個好的計劃取決於臨時對象的目前UPDATE STATISTICS #MyTemp;內容，那麼在主查詢之前顯式地添加OPTION (RECOMPILE)到主查詢中可能是一個很好的解決方案。

或者，如果一個特定的計劃形狀對於此查詢始終是最佳的，那麼您有許多可用的選項，包括各種提示、計劃指南和查詢儲存計劃強制。您可能會發現使用表變數而不是臨時表是更好的選擇，因為它有利於低基數情況，並且不提供（或依賴）統計資訊。

總而言之，在擔心臨時表上偶爾失去統計資訊的原因之前，應該進行一些一般性的改進：

• 確保統計數據對優化器具有代表性和有用
• 檢查一系列參數值的實際值與估計值
• 通過改進現有索引為查詢提供良好的數據訪問路徑
• 如果可能，移除視圖；或考慮使用日期/時間參數的顯式謂詞的“參數化視圖”（內聯表值函式）。
• 確保自動統計創建不會受到不必要的限制
• 為任務使用正確類型的臨時對象（表與變數）
• 考慮RECOMPILE計劃選擇是否對參數值非常敏感
• 添加UPDATE STATISTICS，RECOMPILE如果記憶體的統計數據有問題
• 考慮一個帶有主鍵的臨時表，而不是SELECT INTO它是否為優化器提供有用的資訊
• 檢查架構以確保優化器擁有盡可能多的資訊（例如外鍵、其他約束）
• 根據您對數據的了解，考慮過濾索引/統計資訊的適用性
• 不要NOLOCK為了提高性能而添加提示

---

複製品

以下是根據提供的編輯執行計劃中可用的有限資訊建構的：

DROP VIEW IF EXISTS dbo.View_VP;
DROP TABLE IF EXISTS dbo.SmallTable, dbo.LargeTable, #MyTemp;
GO
CREATE TABLE LargeTable (P_Id integer NOT NULL, Status integer NOT NULL, Col1 integer NOT NULL, Col4 datetime NOT NULL);
CREATE TABLE SmallTable (P_id integer NOT NULL, Col2 integer NOT NULL)
CREATE TABLE #MyTemp (Col1 integer NOT NULL);
GO
CREATE VIEW dbo.View_VP 
AS
   SELECT
       pp.Col1,
       pd.Col2 AS Col2,
       MAX(pp.Col4) AS Col3
   FROM LargeTable pp
   JOIN SmallTable pd
       ON pd.P_id = pp.P_Id
   WHERE 
       pp.[Status] IN (3, 4)
   GROUP BY 
       pp.Col1, pd.Col2;
GO
CREATE UNIQUE CLUSTERED INDEX PK_SmallTable ON dbo.SmallTable (P_id)
CREATE CLUSTERED INDEX ix_P_id ON dbo.LargeTable (P_Id)
CREATE INDEX ix_Col1 ON dbo.LargeTable (Col1)
CREATE INDEX ix_Status ON dbo.LargeTable ([Status])
GO
UPDATE STATISTICS dbo.LargeTable WITH ROWCOUNT = 32268200, PAGECOUNT = 322682;
UPDATE STATISTICS dbo.SmallTable WITH ROWCOUNT = 6349, PAGECOUNT = 63;
UPDATE STATISTICS #MyTemp WITH ROWCOUNT = 4;

查詢是：

DECLARE @Months integer = 6;

SELECT wd.Col1
        , vp.Col2
        , vp.Col3 
   FROM dbo.View_VP vp WITH (FORCESEEK)
   INNER JOIN #MyTemp wd ON wd.Col1 = vp.Col1
   WHERE vp.Col3 > DATEADD(MONTH, @Months * -1, GETDATE())

如果沒有關於基表的真實統計資訊，這有利於接近“壞計劃”範例的計劃（使用ix_Status）：

這表明關於 的選擇性的資訊Col1是優化器選擇的一個重要因素。

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