SQL Server 2014裡的針對基數估計的新設計(New Design for Cardinality Estimation)

標籤：

對於SQL Server資料庫來說，效能一直是一個繞不開的話題。而當我們去分析和研究效能問題時，執行計畫又是一個我們一直關注的重點之一。

我們知道，在進行編譯時間，SQL Server會根據當前的資料庫裡的統計資訊，在一定的時間內，結合本機資源，挑選一個當前最佳的執行計畫去執行該語句。

那麼資料庫分析引擎如何使用這些統計資訊的呢？資料庫引擎會根據資料庫裡的統計資訊，去計算每次操作大約返回多少行。這個動作稱之為基數計算（cardinality estimation）。資料庫分析引擎會基於這些資訊判斷選擇邏輯或物理的操作符，操作成本等等，產生一系列執行計畫並最終挑選一個合適的執行計畫。

在SQL Server 2014中，基數計算與之前的版本相比出現了較大的變化，並且這些變化對執行計畫的產生有客觀的促進作用。新的基數計算相對於之前的版本而言並不是增加了一個新的補丁，修複了一些bug，可以說是一次重寫，甚至基於的數學計算模型也發生了變化。

新的基數計算主要適用於DW(資料倉儲)的情境，會給DW系統帶來較大的效能提升。

就效果而言，由於採用的數學模型的一些變化，新的基數計算在對返回行數預估上，較以往往往會更加準確。

以下兩個例子是對新舊基數計算的對比。

1. 獨立性假設

測試語句如下：

1 Select *2 From Cars3 Where Make=‘Honda’ AND Model =‘Civic’

在測試資料庫中運行上述語句，其中表的行數是1000行，Make=’Honda’ 有200行，Model=’Civic’ 有50行。

在之前般的CE中，會認為這兩個篩選條件之前沒關係，所以預測返回行數是0.05 * 0.2 * 1000 = 10, 而在新的版本CE中，會認為這兩者之間應該是有關係的，因此會採用指數退避演算法，預測傳回值是0.05 * sqrt(0.2) * 1000 = 22.36。

實際返回行數50行。

因此新的CE會更加的保守，在這種情況下會更加準確。

2. 串連(join)的變化

當出現等值串連時，會採用下面的計算方法：

• 選取兩個輸入中distinct值較少的一個
• 上面步驟取得的值乘以兩邊的平均頻率、

例如

新的基數計算涉及的修改較多，例如還有針對ascending key情境所做的修改，使用統計資訊方法的修改等等。但是對傳統的一些內容仍然保持原樣，例如表變數預估為一行，預存程序中的本地變數會認為是未知值，parameter sniffing 問題仍然可能發生等等。

但是總整體而言，新的基數計算給DW情境的工作負載會帶來客觀的效能提升，包括編譯時間和執行時間兩方面。
前述中我們提到了統計資訊，在SQL Server 2014中，會有一個新的統計資訊概念，增量統計資訊（Incremental Statistics）。

一般說來，統計資訊記錄的是列或者索引中的資料分布，資料密度等等。當使用者開啟自動統計資訊更新後，假如資料發生了大約20%的變化，那麼會觸發統計資訊自動更新。

在舊的版本資料庫中，關於統計資訊會遇有以下兩個不足之處：1. 對於非常大的表，20%的自動統計資訊閾值太大。2. 重建統計資訊需要重新掃描或者重新取樣掃描整個表，假如能做到只掃描新的資料，那麼更佳。

以此為目標，SQL Server 2014 出現了一個新的功能增量統計資訊（Incremental Statistics）。

Incremental Statistics有以下特點：

1. 它適用於分區表，並且主要的資料更新發生在新的分區
2. 每個分區都有自己的統計資訊對象，全域會將這些統計更新合并
3. 由於多數資料改變發生的新的分區，因此更新統計資料時，我們只需要更新新區的統計更新，系統會將其在與其他的分區的統計資訊更新。這樣會避免去重建其他分區的統計資訊。
4. 分析引擎使用全域統計資訊而不是每個分區的統計資訊。
5. 當自動統計資訊開啟後，對每個分區而言，觸發的閾值為該分區20%的資料更新。對全域而言是平均分區大小的20%。

原文連結：http://blogs.msdn.com/b/apgcdsd/archive/2014/12/25/sql-2014-7-new-design-for-cardinality-estimation.aspx

SQL Server 2014裡的針對基數估計的新設計(New Design for Cardinality Estimation)