小心SQL SERVER 2014新特性——基數評估引起一些性能問題

    在前陣子寫的一篇博文“SQL SERVER 2014 下IF EXITS 居然引起執行計劃變更的案例分享”里介紹了資料庫從SQL SERVER 2005升級到 SQL SERVER 2014後，發現一個SQL出現性能問題，當時分析後發現執行計劃變了，導致SQL出現了性能問題。但是沒有徹底搞清楚為什麼出現這種情況。當時看到Actual Number of Rows 與Estimated Number of Rows之間的偏差較大（統計信息是最新的），以為是優化器的Bug造成的。其實罪魁禍首是SQL SERVER 2014新特性——基數評估（Cardinality Estimator）所引起的。IF EXISTS完全成了我這個標題黨的替罪羊（罪過罪過）。下麵我再就這個問題展開做一次分析。

    查看該SQL語句的實際執行計劃，在屬性裡面我們可以看到CardinalityEstimationModelVersion的值為120，120表示這是新的基數評估，70就是老的基數評估

其實當資料庫的相容級別為120的時候，預設使用新的基數評估。也就是說啟用了新的基數評估，那麼我們現在使用查詢跟蹤標記9481來關閉新的基數評估，使用老的基數評估。

DBCC TRACEON(9481, 1);

GO

啟用跟蹤標記9481後，這個SQL語句的執行計劃變了（可以對比圖4），可以看到CardinalityEstimationModelVersion的值也變為了70。SQL語句一秒就執行完了。這個是因為基數評估出現了偏差導致了不合適的JOIN演算法。

我們對比下麵”圖四：舊執行計劃“，發現其實還是使用Nested Loops，只是外部迴圈表與內部迴圈表變了。   

圖四：舊執行計劃

那麼關於新的基數評估（Cardinality Estimator）特性，你想多瞭解一些這方面的知識，可以參考官方文檔Optimizing Your Query Plans with the SQL Server 2014 Cardinality Estimator。 中文翻譯版本可以參考SQL Server 2014新特性——基數評估（白皮書閱讀筆記）。下麵是官方文檔關於基數評估出現偏差可能會造成的一些後果：

對於基數評估，每個執行計劃中的運算符都有評估值輸入，這個值決定了優化器使用什麼演算法的操作符，同時也決定了最終的執行計劃。所以如果評估出現偏差，會導致執行計劃選擇出現偏差，導致無法選出一個高效的執行計劃。

評估出現偏差會出現以下結果：

如果評估過小：

1.原本可以使用並行計劃更加有效的，現在使用串列計劃

2.不合適的join演算法

3.不合適的索引選擇，和索引訪問方法

如果評估過大：

1.原本使用串列計劃更加有效，現在使用並行計劃

2.不合適的join演算法

3.不合適的索引選擇，和索引訪問方法

4.過多的記憶體分配

5.記憶體浪費和沒必要的併發

上面這段對應的英文資料如下所示(英語原文作參考,這才是原汁原味的信息):

The individual operator cost models receive the estimates as input. The estimates are a major factor in deciding which physical operator algorithms and plan shapes (such as join orders) are chosen. They also determine the final query plan that executes. Given these critical plan choices, when the cardinality estimation process contains a significantly skewed assumption, this can lead to an inefficient plan choice. This can, in turn, result in degraded performance.

Under estimating rows can lead to memory spills to disk, for example, where not enough memory was requested for sort or hash operations. Under estimating rows can also result in:

1. The selection of serial plan when parallelism would have been more optimal.
2. Inappropriate join strategies.
3. Inefficient index selection and navigation strategies.

Inversely, over estimating rows can lead to:

1. Selection of a parallel plan when a serial plan might be more optimal.
2. Inappropriate join strategy selection.
3. Inefficient index navigation strategies (scan versus seek).
4. Inflated memory grants.
5. Wasted memory and unnecessarily throttled concurrency.

Improving the accuracy of row estimates can improve the quality of the query execution plan and, as a result, improve the performance of the query.

  其實關於SQL SERVER 2014這個新的基數評估（Cardinality Estimator）特性，確實造成了不少SQL出現性能問題。我們資料庫升級到SQL SERVER 2014後，被這個新特性坑慘了,由於沒有選擇最優的執行計劃，導致一些SQL出現嚴重的性能問題，也間接導致了SQL之間的阻塞（block）急劇上升。開發人員和我都在救火隊員的角色中疲於奔命。最後我不得不採取將資料庫的相容基本從120降為110。從而立馬解決了這個問題。另外從我搜索的一些資料看，SQL SERVER 2014這個新的基數評估（Cardinality Estimator）這個新特性確實還有很多不完善的地方。因為也有不少人都發現升級到SQL Server 2014後出現了性能問題。例如:

   MS SQL Server CPU load goes up dramatically when turning on 2014 features by setting compatibility level

      Query is slow in SQL Server 2014, fast in SQL Server 2012

參考資料：

http://dba.stackexchange.com/questions/95609/sql-server-performance-is-slow-when-migrated-from-sql-server-2012-to-sql-server

http://www.cnblogs.com/Amaranthus/p/3678647.html