Earlier I wrote three blog posts on SQL Server 2014 Cardinality. Read them over here to get the context of this blog post.
- SQL SERVER – How to Force New Cardinality Estimation or Old Cardinality Estimation
- SQL SERVER – Simple Demo of New Cardinality Estimation Features of SQL Server 2014
- SQL SERVER – Cardinality Estimation and Performance – SQL in Sixty Seconds #072
I explained in the last blog post that you can execute query in different cardinality even though your database has different compatibility. One of the questions which I have received right after the blog post was how does the user know what is the compatibility of the query which is just executed. It is indeed a very good question. There are multiple ways to know the cardinality of the query just executed. Here are two ways to do the same.
1) Execution Plan Properties
I enabled execution plan (CTRL+M) when executing queries and right after that I selected one of the operators of the execution plan. Right after that I right clicked and selected properties of the execution plan. You can also enable the properties with shortcut key F4 as well. It will display the properties window on the right side in SSMS.
Over here see the property CardinalityEstimationModelVersion. If the value of this property is 70 it is of previous version. If the value is 120 it means cardinality estimator algorithm is of latest SQL Server version.
2) XML Execution Plan
This method also requires you to enable execution plan with (CTRL+M). Once the execution plan is enabled right click over the execution plan and click on the option Show XML execution plan. Once this option is clicked it will open a new window with XML plan.
In the XML execution plan, look for the property CardinalityEstimationModelVersion. If the value of this property is 70 it is of previous version. If the value is 120 it means cardinality estimator algorithm is of latest SQL Server version.
Well, it is very simple to know what is the cardinality of query execution.
Reference: Pinal Dave (https://blog.sqlauthority.com)
