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 Cars
3 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

注：此文章為

WoodyTu

學習MS SQL技術，收集整理相關文檔撰寫，歡迎轉載，請在文章頁面明顯位置給出此文連結！

若您覺得這篇文章還不錯請點選下右下角的推薦，有了您的支援才能激發作者更大的寫作熱情，非常感謝！