原文: SQL Server 執行計劃利用統計資訊對資料行的預估原理以及SQL Server 2014中預估政策的改變
前提
本文僅讨論SQL Server查詢時，
對于非複合統計資訊，也即每個字段的統計資訊隻包含目前列的資料分布的情況下，
在用多個字段進行組合查詢的時候，如何根據統計資訊去預估行數的。
利用不同字段的統計資訊做資料行數預估的算法原理，以及SQL Server 2012和SQL Server 2014該算法的差異情況，
這裡暫時不涉及複合統計資訊，暫不涉及統計資訊的更新政策及優化相關話題，以及其他SQL Server版本計算方式。
統計資訊是什麼
簡單說就是對某些字段的資料分布的一種描述，讓SQL Server在根據條件做查詢的時候，大概知道預期的資料大小，
進而指導生成合理執行計劃的一種資料庫對象
統計資訊的分類
索引上會自動建立統計資訊，SQL Server也會根據具體的查詢，在某些非索引自動建立索引，
當然也可以通過手動方式建立統計資訊。先來直覺地了解一下統計資訊長什麼樣，參考截圖，就是這麼個樣子，
_WA_Sys_****開頭的是系統根據需要建立的統計資訊，
與索引同名的是索引上建立的統計資訊，
手動建立統計資訊也可以在滿足SQL Server命名要求的情況下自行命名。
下面一個是索引的統計資訊。
統計資訊的作用
查詢引擎根據統計資訊提供的資料做出合理的執行計劃。
那麼，查詢引擎究竟是怎麼利用統計資訊做預估的呢，
以及下面将要提到的SQL Server 2014中較之前的版本有哪些變化？
本文将對此兩點做一個簡單的分析來說明SQL Server是怎麼根據統計資訊做估算的，下面開始正文。
測試環境搭建
習慣性地做一個示範的環境，建立一個表，寫入100W的資料後面測試用。
create table TestStatistics
(
Id int identity(1,1),
Status1 int,
Status2 int,
Status3 int
)
insert into TestStatistics values (RAND()*1000,RAND()*250,RAND()*50)
go 1000000 表中有四個字段，第一個是自增列，主要看Status1，Status2，Status3這三個字段，
三個字段的取值都是用随機數乘以一個常量系數的出來的，
是以這三個字段的資料分布範圍分别是
Status1：0-999（1000種資料分布）
Status2：0-249（250種資料分布）
Status3：0-49（50種資料分布）
這個後面有用。
首先在SQL Server 2012中做測試
先做這麼一個查詢：select * from TestStatistics where Status1=885 and Status2=88 and Status3=8
這個查詢完成之後，表上自動建立一個三個統計資訊，
這三個統計資訊分别是Status1，Status2，Status3這個三個字段的資料分布描述
首先來看一下其中這個_WA_Sys_00000002_0EA330E9，也即Status1這個列的統計資訊的詳細資訊，
注意All density字段值，選擇性是反應一個表中該字段的重複資料有多少或者說唯一性有多少，
計算方法是：1/表中該字段非重複個數。
上面說了，這個Status1這個列的取值範圍是0-999，一共有1000中取值可能行，
那麼這個選擇行就是1/1000=0.001，是以也是吻合這裡的All density=0.001的
照這麼計算，其餘兩個字段的選擇度分别是1/250=0.004 和1/50=0.02，分别如下截圖的 All density。
執行計劃對資料行的預估
說完統計資訊的基礎問題之後，我們就可以來觀察執行計劃對目标資料的預估規律了。
我們來看這麼一個查詢，如下，注意這個是查詢的條件是參數變量，而不是直接的值，後面我會解釋為什麼這麼做。
來觀察執行計劃對資料行的預估：可以看出來，預估為4行。
那麼這個4行是怎麼計算出來的呢？
這就要利用到我們上面的選擇性了，
Status1字段的選擇性是0.001，Status2的選擇性是0.04，
在SQL Server 2012中，對資料行的預估計算方式是各個字段的選擇性的乘積，
假如Pn代表不同字段的選擇性，那麼預估行數的計算方法就是: 預估行數=p0*p1*p2*p3……*RowCount
是以，執行計劃顯示的：預估行數=0.001*0.004*總行數（也即1000000）= 4
說到這裡解釋兩個可能存在的幾個疑問：
第一，上述示例是用兩個字段查詢的，為什麼不拿三個字段做示範說明？
首選，不管是多少個字段查詢，預估行數符合上述計算方式是沒有問題的，
但是如果通過上述公式計算出來的結果非常小，在少于1的情況下，SQL Server顯示預估為1行。
按照上述計算方法，用三個字段做查詢，
預估行數=0.001*0.004*0.02*總行數（也即1000000）= 0.08<1，是以預估為1行。
第二，為什麼不直接用值查詢，而是用變量做查詢？
熟悉SQL Server的同學應該都知道，直接用變量查詢的時候，SQL Server編譯的時候不知道具體的參數值，
在不知道具體參數值的情況下，它是使用字段的選擇性的時候是用到一般性（或者說是平均）的值，
也就是統計資訊中整體計算出來字段的選擇性，也即All density=0.001
這裡暫定認為資料分布是均勻的，也即每個值分布差别不大。
但事實上每個值的分布的差别還有存在的，
尤其是分布不均勻的時候，當然這個是另外一個非常大的話題了，這裡暫不讨論。
如果直接用明确的值做查詢。
比如 select * from TestStatistic where Status1=885 and Status2=88
SQL Server會根據統計資訊中每個字段 ：Status1=885 的行數和 Status2=88行數的具體的值，
利用上述公式做預估
那麼就繼續用具體的值做示範說明，
可以直接用where Status1=885 and Status2=88這個條件查詢來觀察預估結果。
首先我們看統計資訊中Status1=885 的分布行數，1079行
然後再看統計資訊中Status2=88 的分布行數，3996行
利用上述公式，預估行數為4.31168行
那麼直接利用值做查詢是不是這個預估的行數呢？直接上圖，完美地吻合了上述的計算方法得到的結果。
第三，沒有索引的情況下是符合預估的計算方法，如果建立了索引呢？
查詢條件中的各個列的統計資訊是非相關的，
如果分别在各個列上建立單個列的索引資訊，在查詢的時候也屬于非相關統計資訊。
如截圖，也就是說，雖然建立了索引，執行計劃發生了變化，
從一開始的表掃描變成了通過兩個索引查找後做hash join，然後Loop join查詢資料，咱不管它就是變成什麼執行計劃了
但是統對資料的預估還是跟上面全表掃描一樣的，都是預估為4.31168，沒有因為建立了索引以及執行計劃發生了變化而改變（預估行數）。
因為即便是建立了單列上的索引，執行計劃變了，但是統計資訊還是非相關的，也就是一個統計資訊隻描述一列字段的分布情況。
然後在SQL Server 2014中做測試
上述同樣的資料，我這裡通過link server 将上述SQL Server 2012執行個體下的測試表的結果導入到SQL Server 2014的執行個體下的表中。
現在表結構和資料完全一緻。
首選，做一個同樣的測試，利用兩個變量查詢的查詢條件做查詢，看看SQL Server 2014預估的算法有什麼變化。
還記得上面在 SQL Server 2012中同樣的寫法，同樣的資料的預估的情況吧，剛才預估的是4行，現在怎麼變成63.2456行了？
預估行數的計算公式變了嗎，當然變了，這正是本文要說的重點。
那麼SQL Server 2014中是怎麼預估的呢？公式是這麼來的：預估行數 = P0*P11/2 * P21/4 * P31/8……* RowCount
那麼來根據此計算方式來計算預估行數的問題：預估行數=0.001*0.0041/2*1000000 = ？
這裡我就不做開方運算了，拿來主義，直接用SQL Server來算拉倒了，SQL Server給我們提供了一個開方函數（SQRT），真JB好用。
計算一下結果吧，
沒錯，是63.24555，保留四位有效數字的話就是63.2456了，預估行數跟上面計算出來的結果也是完全吻合的。
補充測試1：
同樣地，用三個條件做查詢，預估算法也同樣複合上述公式的結果。
按照公式來計算預估行數，選擇性按照整體計算出來的選擇性來，同樣也是吻合的。
補充測試2：
如果把查詢條件換做具體的值，跟在SQL Server 2012中一樣，SQL Server2014 也同樣會根據具體的值得資料做計算
進行這麼個查詢：select * from TestStatistics2014 where Status1=858 and Status2=88
解釋一下為什麼這次Status1換成858了：
因為即便表結構，資料完全一緻吧，受限于統計資訊的步長（Steps）隻有200，兩個庫的統計資訊也不完全一緻，統計資訊不能精确到任何一個值，
我們這裡為了示範這個算法，找一個具體的RANGE_HI_KEY值，比較容易說明問題。
首先看Status1=858的資料分布情況
再看Status2=88的資料分布情況
利用上述計算方法計算出來的預估：63.27713
執行計劃的預估：63.27713，也是完全吻合的。
補充測試3，在查詢列上建立建立單獨的索引
跟SQL Server 2012中一樣，執行計劃發生了變化 ，但是對于資料行的預估，同樣并沒有因為執行計劃的變化而（預估行數）變化。
雖然執行計劃變了，但是對資料的預估并沒有變化，預估的算法還是符合：預估行數 = P0*P11/2 * P21/4 * P31/8……* RowCount
在此可以看出，執行計劃對于（未超過統計資訊範圍的情況下）資料行的預估，是有一定規律的，
這個規律就是：
SQL Server 2012 中，預估行數=p0*p1*p2*p3……*RowCount（Pn為查詢字段的選擇性），
SQL Server 2014 中，預估行數= P0*P11/2 * P21/4 * P31/8……* RowCount（Pn為查詢字段的選擇性）。
當然如果說統計資訊過期或者取樣密度不夠，那就另當别論了，這個就關系到統計資訊的更新政策問題了，也是一個非常大而且非常現實的問題，暫不深入展開讨論。
是以一開始我說暫不考慮統計資訊自身是否理想，這裡是在統計資訊非常完整的情況下做測試的。
微軟為什麼在SQL Server 2014中，對非相關且未超出統計資訊範圍的預估行數算法做這麼一個變化，
因為PN的值是小于1的
預估行數的計算方法從p0*p1*p2*p3……*RowCount變化為P0*P11/2 * P21/4 * P31/8……* RowCount，顯然是增加了預估行數的大小，
同時本文未提及的另外一種情況：對于超出統計資訊範圍的情況下，新的預估方法也增加預估行數的大小，
從整體上看，算法是傾向于"估多不估少”的，有這麼一個改變
至于為什麼要做出這個改變？
如果經常做SQL優化的就會發現，不少問題都是少估了預期的資料行數（因為種種原因吧，這裡暫時不讨論為什麼少估），
造成執行SQL時配置設定的資源不夠，進而拖慢了SQL的執行效率
一個非常典型的問題就是，預估的資料比實際的資料行數小，造成比如記憶體授予的不夠大，以及實際運算過程中采用不合理的執行計劃
個人認為，（控制在一定範圍之内的）估多的情況下可以通過擷取更多的系統資源來提升SQL的執行效率，
正常情況下也不會說是跟實際值差的太離譜造成資源的浪費。
當然也有特殊情況，那就另當别論
要注意的是我這裡有個前提，非相關的統計資訊，不管是沒有任何索引，還是是建立和單列上的索引，對應的統計資訊，都屬于非相關統計資訊，
如果建立複合索引（有人習慣叫組合索引），那麼執行計劃對于資料行的預估并不符合上述算法，具體算法我也不清楚。
此種情況下，在SQL Server 2012和SQL Server 2014中預估算法也不一樣，這個有機會再研究吧。
對于測試結果的補充說明：
測試過程中一定要保證統計資訊的完整性，以及取樣的百分比問題，理性情況下都是按照100%取樣的，
中間我略去了一些細節問題，比如沒此測試之前都會 update statistics TestStatistic with fullscan，保證100%取樣。
既然要精确到小數點後幾位，當然要求條件是理想情況下的，目的就是一定要排除其他條件對測試結果的影響。
總結：
本文通過一個簡單的示例，來了解了SQL Server通過統計資訊對資料預估的計算方式和原理，以及SQL Server 2012和SQL Server2014之間的差異。
統計資訊對于SQL執行計劃的選擇起着中樞神經般的作用，不光是在SQL Server資料庫中，包括其他關系資料庫，統計資訊都是一個非常重要的資料庫對象。
可以說，SQL優化，統計資訊以及與之息息相關的執行計劃是一個非常重要的因素，了解統計資訊方面的知識對性能調優有着非常重要的作用。
在涉及到組合索引上的統計資訊情況下，執行計劃對資料行的預估，SQL Server2012和SQL Server 2014中也不一樣，問題将會更加有趣，待有時間再寫吧。
參考：
Fanr_Zh大神的
http://www.cnblogs.com/Amaranthus/p/3678647.html以及
http://msdn.microsoft.com/en-us/library/dn673537.aspx