Yesterday I wrote blog post based on my latest Pluralsight course on learning SQL Server 2014. I discussed newly introduced cardinality estimation in SQL Server 2014 and how it improves the performance of the query. The cardinality estimation logic is responsible for quality of query plans and majorly responsible for improving performance for any query. This logic was not updated for quite a while, but in the latest version of SQL Server 2104 this logic is re-designed. The new logic now incorporates various assumptions and algorithms of OLTP and warehousing workload. This video covers cardinality estimation and performance in simple words.
I hope my earlier blog post clearly explained how new cardinality estimation logic improves performance. If not, I suggest you watch following quick video where I explain this concept in extremely simple words.
You can download the code used in this course from Simple Demo of New Cardinality Estimation Features of SQL Server 2014.
Action Item: Read More on Cardinality Estimation and Performance
Here are the blog posts I have previously written. You can read it over here:
You can subscribe to my YouTube Channel for frequent updates.
Switching Back to the Old Estimator
One practical note. The new cardinality estimator is used when the database compatibility level is 120 or higher. If a query becomes slower after an upgrade, compare its plan under both estimators before you change anything else. On SQL Server 2016 and later you can switch one database back to the old model without changing the compatibility level:
ALTER DATABASE SCOPED CONFIGURATION
SET LEGACY_CARDINALITY_ESTIMATION = ON;Treat this as a temporary fix. The better long term answer is to find the few queries that suffer and fix them with better statistics, better indexes or a rewrite. That way the rest of your workload keeps the benefit of the new estimator.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
2 Comments. Leave new
Hi I tried this feature on SQL Server 2017 but for me there is no change in logical reads. Every time its same. I tried the code as in your example.
Table ‘Customers’. Scan count 1, logical reads 104, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table ‘Customers’. Scan count 1, logical reads 104, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Not every query would benefit from new CE.