SQL Server 2016改進了查詢最佳化工具

SQL Server 2016改進了查詢最佳化工具

　　SQL Server 的前兩個版本主要是通過提供新特性提高效能，而 SQL Server 2016 主要是改進本身已有的功能。

　　基數估計器

　　基數估計器是所有查詢最佳化工具的核心。它會查看被查詢表的統計資訊以及執行的操作，估計查詢執行計畫每一步的行數。有經驗的 DBA 都知道，錯誤的基數估計會嚴重降低資料庫的效能。可能導致的問題包括：

• 選擇了錯誤的索引；
• 選擇了錯誤的串連運算子（例如嵌套迴圈、雜湊和合并）；
• 分配的記憶體過多，妨礙了其他查詢；
• 分配的記憶體過少，導致過多資料溢出到 tempdb。

　　考慮到基數估計器如此重要，你可能會驚訝，SQL Server 2012 使用的基數估計器基本上與 1998 年 SQL Server 7 引入的基數估計器相同。僅僅是兩年之前，我們才看到了“SQL Server 查詢最佳化工具基數估計過程的第一次大規模重新設計”。要想深入瞭解那個版本，可以閱讀白皮書《使用 SQL Server 2014 基數估計器最佳化查詢計劃》。

　　SQL Server 2016 就是以那項工作為基礎構建的，當相容性層級設定為 130 時，可以提供更準確的估計。不過，也可能出現退化，因此，在生產環境中更改相容性層級之前，要對現有的資料庫進行徹底地測試。

　　相容性層級

　　你可能不熟悉這個術語，相容性層級在 SQL Server 中有兩個關鍵的作用。首先，可以確定哪些資料庫特性可用。將資料庫設定到一個比較低的相容性層級上，就會無法使用一些較新的特性。通常，這不會帶來升級問題，因為資料庫的設計考慮了較老的特性集。

　　另外一件受相容性層級控制的事情是使用哪個查詢最佳化工具和基數估計器。在經過精心調優的資料庫中，為了降低效能退化的可能性，你可以選擇一個較低的相容性層級，強制 SQL Server 使用使用一個來自舊版本的查詢最佳化工具。

　　在某些情況下，你需要更細粒度的控制。例如，SQL Server 2016 允許你將相容性層級設為 130，以使用所有的新特性，但仍然使用舊版本的基數估計器。這可以通過下面的命令設定：

ALTER DATABASE SCOPED CONFIGURATIONSET LEGACY_CARDINALITY_ESTIMATION = ON;

　　除了 SQL Server 2008 R2 之外，每個版本的相容性層級都比上一個版本大 10。所以 SQL Server 2000 的相容性層級為 80，而 SQL Server 2016 為 130。每個 SQL Server 版本都至少支援前面的兩個版本。

　　多線程插入

　　在 SQL Server 2016 之前，Insert-Select 語句只在選擇階段是多線程的，而實際的插入操作是序列化的。現在，插入操作也可以是“多線程的或者可以有並行計劃”。

　　記憶體最佳化表

　　記憶體最佳化表也具備了多線程的能力。

　　統計

　　SQL Server 2016 在統計方面有兩個變化。第一個是在使用大表時更新頻率更高了。

以前，當發生變化的行數達到 20% 時才會觸發統計資訊的自動更新，這對於大表來說是不合適的。從 SQL Server 2016（相容性層級 130）開始，該閾值會與表的行數關聯起來——表的行數越多，觸發統計資訊更新的閾值就越低。注意，在以前的版本中，該行為由 Trace Flag 2371 控制。
例如，如果一個表有 10 億行，在以前的行為模式下，只有當發生變化的行數達到 2 億時才會啟動自動統計更新。在 SQL Server 2016 中，只需要 100 萬行就可以觸發自動統計更新。

　　繼續討論並行化。現在，當使用相容性層級 130 時，統計資訊可以“由一個多線程的進程抽樣收集”。

　　外鍵約束

　　關係型資料庫的一大賣點是能夠將一個表和其他的表關聯，並使用外鍵約束確保資料一致性。但那有一些開銷，因此，在 SQL Server 2014 及更早的版本中，表的外鍵約束上限為 253。

　　你可能會覺得這個數很大了，但在一個大型資料庫中，當你開始考慮包含審計列時，如“CreatedByKey”，就很容易達到那個限制了。為了緩解這個問題，微軟將傳入外鍵約束的上限增加到了 10000。就是說，你可以有數千個表同時引用某一個使用者表。但有一些注意事項。

　　首先，這不適用於傳出外鍵約束，也不適用於自參考資料表。這些情況的外鍵上限仍然是 200 多個。

　　其次，被引用的表不能使用 MERGE 操作修改；只允許進行 DELETE 和 UPDATE 操作。（理論上，SELECT 和 INSERT 操作也是允許的，但文檔中並沒有提到它們，因為它們不受傳入外鍵約束的影響。）

　　註：“關係型資料庫管理系統”中的“關係”一詞實際上並不是指表之間的關係。更確切地說，它是一個資料科學術語，是指行裡的每個值和同一行裡的其他所有值的關係。在樞紐分析表中，每個儲存格都是一個獨立的和或平均值，它是一個非關係型表的例子。

　　串連和外鍵約束

　　前面已經講過，外鍵約束是有成本的。如果你修改一個被外鍵約束潛在引用的行，那麼資料庫就需要進行檢查，以確保沒有違反約束。

　　在 SQL Server 2014 中，執行檢查時會串連每個引用上述表的表。不難想象，開銷很快就會變得非常大。為瞭解決這個問題，SQL Server 引入了一個新的“參照完整性運算器（Referential Integrity Operator）”。

新的查詢執行運算器會就地執行參照完整性檢查，比較修改的行和參考資料表裡的行，以驗證修改是否會破壞參照完整性。這會極大地減少此類計劃的編譯時間及相應的執行時間。

　　英文原文：Query Optimizer Improvements in SQL Server 2016

本文永久更新連結地址：