There are two ways to know the cardinality of executed query, and I show both below. 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.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
1 Comment. Leave new
Dear Pinal Sir
My problem is that I have a database named rdh14 whivh have approx 50 tables, store procedure(encrypted), indexes (clustered and non clustered both) and triggers etc. And this database is filled. Now I have to make a new BLANK or Empty database rdh15. How can I do? If I generate script then it is generate error against encrypted store procedure. Along with it’s not generate all non clustered index except primary key or unique index.
Kindly suggest me . Thanks in advance
Regards Gaurav Srivastava