SQL Server 2014 released a new cardinality estimator (CE) and more can be learnt from the MSDN documentation on the specifics. In this post I just wanted to call out how you can quickly identify if we are using the old estimator or the new CE. This information stays inside the execution plans or the properties pane.
Recently one of the customers did see me talking about this and complained that his queries run on Adventureworks were never using the new CE and he had to always use a trace flag. This got me interested and I will tell you what I found as part of this learning.
- Create some random query in samples DB or a DB of your choice. I used the AdventureworksDB for my experiment.
- Enable Actual Execution Plan.
- Execute the query, and wait for the Execution Plan Tab to appear.
- On the Execution Plan tab, right click select “Show Execution Plan XML …”
This now opens up the XML Plan for the query under question. And right at the top we will find the element which says “CardinalityEstimatorModelVersion”, if this is 70 then we are using the old CE and if this reads 120 then we are using the new CE.
We can also find this information in the Properties Pane (F4 shortcut). This is shown in the diagram below.
It was baffling to me why this was always showing as 70 for my environment. So the first place for me to check was the compatibility of the Database. It was at 110 (SQL Server 2012), I changed the same to 120 (SQL Server 2014) and I reran the same query.
As you can see, SQL Server has picked up the new CE and has evaluated the same. There is so much to learn about the new CE that I will reserve it for future posts.
This entry was posted on Wednesday, June 4th, 2014 at 08:30 and is filed under Technology. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.