如何使用執行計劃優化 T-SQL 查詢

我有一個 SQL 查詢，過去兩天我一直在嘗試使用試錯法和執行計劃進行優化，但無濟於事。請原諒我這樣做，但我會在這裡發布整個執行計劃。為了簡潔和保護我公司的智慧財產權，我努力使查詢和執行計劃中的表名和列名通用。可以使用SQL Sentry Plan Explorer打開執行計劃。

我已經完成了大量的 T-SQL，但是使用執行計劃來優化我的查詢對我來說是一個新領域，我真的試圖了解如何去做。因此，如果有人可以幫助我並解釋如何破譯此執行計劃以在查詢中找到優化它的方法，我將永遠感激不盡。我還有更多查詢要優化——我只需要一個跳板來幫助我完成第一個。

這是查詢：

DECLARE @Param0 DATETIME     = '2013-07-29';
DECLARE @Param1 INT          = CONVERT(INT, CONVERT(VARCHAR, @Param0, 112))
DECLARE @Param2 VARCHAR(50)  = 'ABC';
DECLARE @Param3 VARCHAR(100) = 'DEF';
DECLARE @Param4 VARCHAR(50)  = 'XYZ';
DECLARE @Param5 VARCHAR(100) = NULL;
DECLARE @Param6 VARCHAR(50)  = 'Text3';

SET NOCOUNT ON

DECLARE @MyTableVar TABLE
(
   B_Var1_PK int,
   Job_Var1 varchar(512),
   Job_Var2 varchar(50)
)

INSERT INTO @MyTableVar (B_Var1_PK, Job_Var1, Job_Var2) 
SELECT B_Var1_PK, Job_Var1, Job_Var2 FROM [fn_GetJobs] (@Param1, @Param2, @Param3, @Param4, @Param6);

CREATE TABLE #TempTable
(
   TTVar1_PK INT PRIMARY KEY,
   TTVar2_LK VARCHAR(100),
   TTVar3_LK VARCHAR(50),
   TTVar4_LK INT,
   TTVar5 VARCHAR(20)
);

INSERT INTO #TempTable
SELECT DISTINCT
   T.T1_PK,
   T.T1_Var1_LK,
   T.T1_Var2_LK,
   MAX(T.T1_Var3_LK),
   T.T1_Var4_LK
FROM
   MyTable1 T
   INNER JOIN feeds.MyTable2 A ON A.T2_Var1 = T.T1_Var4_LK
   INNER JOIN @MyTableVar B ON B.Job_Var2 = A.T2_Var2 AND B.Job_Var1 = A.T2_Var3
GROUP BY T.T1_PK, T.T1_Var1_LK, T.T1_Var2_LK, T.T1_Var4_LK

-- This is the slow statement...
SELECT 
   CASE E.E_Var1_LK
       WHEN 'Text1' THEN T.TTVar2_LK + '_' + F.F_Var1
       WHEN 'Text2' THEN T.TTVar2_LK + '_' + F.F_Var2
       WHEN 'Text3' THEN T.TTVar2_LK
   END,
   T.TTVar4_LK,
   T.TTVar3_LK,
   CASE E.E_Var1_LK
       WHEN 'Text1' THEN F.F_Var1
       WHEN 'Text2' THEN F.F_Var2
       WHEN 'Text3' THEN T.TTVar5
   END,
   A.A_Var3_FK_LK,
   C.C_Var1_PK,
   SUM(CONVERT(DECIMAL(18,4), A.A_Var1) + CONVERT(DECIMAL(18,4), A.A_Var2))
FROM #TempTable T
   INNER JOIN TableA (NOLOCK) A ON A.A_Var4_FK_LK  = T.TTVar1_PK
   INNER JOIN @MyTableVar     B ON B.B_Var1_PK     = A.Job
   INNER JOIN TableC (NOLOCK) C ON C.C_Var2_PK     = A.A_Var5_FK_LK
   INNER JOIN TableD (NOLOCK) D ON D.D_Var1_PK     = A.A_Var6_FK_LK
   INNER JOIN TableE (NOLOCK) E ON E.E_Var1_PK     = A.A_Var7_FK_LK  
   LEFT OUTER JOIN feeds.TableF (NOLOCK) F ON F.F_Var1 = T.TTVar5
WHERE A.A_Var8_FK_LK = @Param1
GROUP BY
   CASE E.E_Var1_LK
       WHEN 'Text1' THEN T.TTVar2_LK + '_' + F.F_Var1
       WHEN 'Text2' THEN T.TTVar2_LK + '_' + F.F_Var2
       WHEN 'Text3' THEN T.TTVar2_LK
   END,
   T.TTVar4_LK,
   T.TTVar3_LK,
   CASE E.E_Var1_LK 
       WHEN 'Text1' THEN F.F_Var1
       WHEN 'Text2' THEN F.F_Var2
       WHEN 'Text3' THEN T.TTVar5
   END,
   A.A_Var3_FK_LK, 
   C.C_Var1_PK


IF OBJECT_ID(N'tempdb..#TempTable') IS NOT NULL
BEGIN
   DROP TABLE #TempTable
END
IF OBJECT_ID(N'tempdb..#TempTable') IS NOT NULL
BEGIN
   DROP TABLE #TempTable
END

我發現第三個語句（評論為慢）是花費最多時間的部分。之前的兩個語句幾乎立即返回。

執行計劃在此連結中以 XML 形式提供。

最好右鍵點擊並保存，然後在 SQL Sentry Plan Explorer 或其他一些查看軟體中打開，而不是在瀏覽器中打開。

如果您需要我提供有關表格或數據的更多資訊，請隨時詢問。

在獲得主要答案之前，您需要更新兩個軟體。

所需的軟體更新

第一個是 SQL Server。您正在執行 SQL Server 2008 Service Pack 1（內部版本 2531）。您應該至少修補到目前的 Service Pack（SQL Server 2008 Service Pack 3 - build 5500）。在撰寫本文時，SQL Server 2008 的最新版本是 Service Pack 3，累積更新 12（版本 5844）。

第二個軟體是SQL Sentry Plan Explorer。最新版本具有重要的新功能和修復，包括直接上傳查詢計劃以供專家分析的能力（無需在任何地方粘貼 XML！）

查詢計劃分析

由於語句級重新編譯，表變數的基數估計是完全正確的：

不幸的是，表變數不維護分佈統計資訊，所以優化器只知道有六行；它對這六行中可能存在的值一無所知。鑑於下一個操作是連接到另一個表，此資訊至關重要。該連接的基數估計是基於優化器的瘋狂猜測：

從那時起，優化器選擇的計劃是基於不正確的資訊，所以性能如此糟糕也就不足為奇了。特別是，為雜湊連接的排序和雜湊表預留的記憶體太小了。在執行時，溢出的排序和散列操作將溢出到物理tempdb 磁碟。

SQL Server 2008 並沒有在執行計劃中強調這一點；您可以使用 Extended Events 或 Profiler Sort Warnings和Hash Warnings監控溢出。在執行開始之前，記憶體是為基於基數估計的排序和散列而保留的，並且無論您的 SQL Server 可能有多少空閒記憶體，都不能在執行期間增加記憶體。因此，準確的行計數估計對於任何涉及工作區記憶體消耗操作的執行計劃都是至關重要的。

您的查詢也已參數化。OPTION (RECOMPILE)如果不同的參數值影響查詢計劃，您應該考慮添加到查詢中。@Param1無論如何，您可能應該考慮使用它，以便優化器可以在編譯時看到 的值。如果沒有別的，這可能有助於優化器對上面顯示的索引查找產生更合理的估計，因為表非常大並且是分區的。它還可以啟用靜態分區消除。

使用臨時表而不是表變數和 再次嘗試查詢OPTION (RECOMPILE)。您還應該嘗試將第一個連接的結果具體化到另一個臨時表中，然後針對該表執行其餘的查詢。行數並不是那麼大（3,285,620），所以這應該相當快。然後，優化器將對連接結果進行精確的基數估計和分佈統計。運氣好的話，計劃的其餘部分會很好地落實到位。

根據計劃中顯示的屬性，具體化查詢將是：

SELECT
   A.A_Var7_FK_LK,
   A.A_Var4_FK_LK,
   A.A_Var6_FK_LK, 
   A.A_Var5_FK_LK,
   A.A_Var1,
   A.A_Var2,
   A.A_Var3_FK_LK
INTO #AnotherTempTable
FROM @MyTableVar AS B
JOIN TableA AS A
   ON A.Job = B.B_Var1_PK
WHERE
   A_Var8_FK_LK = @Param1;

您還可以INSERT進入預定義的臨時表（計劃中未顯示正確的數據類型，因此我無法執行該部分）。新的臨時表可能會也可能不會受益於聚集索引和非聚集索引。

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