從 SQL Server 2000 升級到 2012 時的索引問題
大約 6 個月前，我們將一個大型 .NET 項目從 SQL Server 2000 升級到 2012。（我知道它已經落後了 2 個版本，不要讓我開始！）
其中一個儲存過程在隔夜批處理過程中執行查詢，自升級以來耗時 55 分鐘，但上周躍升至 3 小時。然而，在升級之前，查詢需要 1 分鐘。我根據執行計劃在表上添加了一個索引，它將執行時間縮短到 10 分鐘。我不是 DBA，所以我想知道是否有人可以解釋為什麼會這樣，因為這對我來說沒有意義。
這是導致問題的查詢：
DELETE @ExcludedPersons FROM @ExcludedPersons WHERE personID NOT IN (SELECT personID FROM PersonStudy ps WHERE (ps.StudyTypeID = 1) AND (ps.StudyClosedDate IS NULL))PersonStudy 表上有 2 個索引：
- 作為主鍵的 StudyID 上的聚集索引。
- PersonID 上的非聚集索引也是唯一的。
這些索引只是從 SQL 2000 繼承而來。表中有大約 60,000 行。@ExcludedPerson 表變數中有大約 500,000 行（但只有 1 列）。
在過去的幾個月裡，where 子句中的子查詢返回了 1 行，查詢大約需要 55 分鐘。上週，最後一個人在 StudyClosedDate 中添加了一個日期，因此子查詢返回了 0 行，此時時間躍升至 3 小時。
執行計劃建議的索引是添加一個帶有 StudyTypeID 和 StudyClosedDate 鍵的索引，並包含 PersonID，這使得處理時間達到了 10 分鐘。
所以，我的問題是：
- 為什麼返回 0 行的子查詢有這麼多處理時間，為什麼索引會產生如此大的差異？
- 為什麼返回 1 行和 0 行的子查詢之間的處理時間會發生跳躍？是否與 sql 2000 和 2012 中處理索引的不同方式有關？
問題中未說明的假設是先執行子查詢，然後
DELETE處理外部查詢。這不是事情的運作方式。人們編寫表達邏輯要求的查詢，然後 SQL Server 查詢優化器試圖找到一個有效的物理實現。優化器的決策是由它探索的各種可能的物理選項的成本估算驅動的。
垃圾進垃圾出
通過使用表變數，目前排列剝奪了優化器的兩個重要資訊：表中的行數（基數）；以及這些值的分佈（統計數據）。
在大多數情況下，優化器無法查看表變數的基數，只能猜測一行。它基於表變數中有一行而選擇的物理執行策略很可能對於 50 萬行是次優的。
假設有一行，優化器很可能會認為掃描
PersonStudy表以查找匹配項是一個足夠好的策略：
在實踐中，該計劃導致
PersonStudy表在執行時被掃描 500,000 次（表變數中的每行一次）。這可能是 60,000 * 500,000 = 300 億行。難怪需要一段時間。如果有關數據的資訊不正確或不完整，優化器很可能會提供糟糕的執行計劃。
建議
- 使用臨時表（例如
#ExcludedPersons）而不是表變數。這將提供準確的基數資訊，並允許 SQL Server 自動創建統計資訊。personID將列約束為**NOT NULL**。這為優化器提供了有用的資訊，並允許它避免與NOT IN.- 使
personID臨時表（或表變數）中的列成為**PRIMARY KEY**. 同樣，這為優化器提供了有用的資訊（唯一性、排序、非空）。- 在表上提供有用的索引。
PersonStudy建議的索引是一個合理的選擇，但可能有更好的選擇。一個好的索引為優化器提供了更有效的數據訪問路徑。特別是如果您無法切換到使用臨時表，請測試以下內容（但仍要添加上面提到的約束和索引/鍵）：
- 向查詢添加**
OPTION (RECOMPILE)**提示。這將允許優化器在執行時查看表變數的基數（但不是統計分佈）。- **或者：使用
OPTION (HASH JOIN)**提示。雜湊連接比帶有表掃描的嵌套循環更好地擴展。雜湊聯接可能會在執行時溢出和反轉角色，但這仍然應該比您現在擁有的要好得多。- **或者：**如果您的工作負載經常使用具有大量行的表變數，請測試啟用跟踪標誌 2453的影響。這將像上面一樣暴露基數，而沒有計劃重新編譯的（通常很小的）成本。
