2016/9/21 PASS online event
DBA Fundamentals: SQL Server Performance Tuning
Presented by Pinal Dave
The New and Improved Cardinality Estimator in SQL Server 2014
Cardinality Estimation (SQL Server)
CE(Cardinality Estimation)會預測您的查詢可能傳回的資料列數目。 查詢最佳化工具使用基數預測，來產生最佳查詢計劃。 CE 愈精確，查詢計劃通常愈理想。
In 1998, a major update of the CE was part of Microsoft SQL Server 7.0, for which the compatibility level was 70. Subsequent updates came with SQL Server 2014 and SQL Server 2016, meaning compatibility levels 120 and 130. The CE updates for levels 120 and 130 incorporate assumptions and algorithms that work well on modern data warehousing workloads and on OLTP (online transaction processing).
新版(層級 120 和 130 )的基數估計更新了合併假設與演算法，增強並適用於新式資料倉儲工作負載和 OLTP (線上交易處理)。
Your application system could possibly have an important query whose plan is changed to a slower plan due to the new CE. Such a query might be like one of the following:
- An OLTP query that runs so frequently that multiple instance of it often run concurrently.
- A SELECT with substantial aggregation that runs during your OLTP business hours.
Troubleshooting Poor Query Performance: Cardinality Estimation
3.Missing index (script from Pinal Dave blog)
4.Wait Stats and Queues (script from Pinal Dave blog)