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

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.
</ol>

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.
</ol>

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

</div>

來自： http://www.cnblogs.com/kerrycode/p/5118101.html

</span></span></span></span></span></span></span></span>