SQL SERVER – How to Know Cardinality of Executed Query?

Earlier I wrote three blog posts on SQL Server 2014 Cardinality. Read them over here to get the context of this blog post.

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.

SQL SERVER - How to Know Cardinality of Executed Query? cardinalityesti

 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.

SQL SERVER - How to Know Cardinality of Executed Query? cardinalityesti1

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.

SQL SERVER - How to Know Cardinality of Executed Query? cardinalityesti2

Well, it is very simple to know what is the cardinality of query execution.

Reference: Pinal Dave (https://blog.sqlauthority.com)

Quest

Previous Post
SQL Authority News – 3000th Blog Posts and Thank You
Next Post
SQL SERVER – Live Plans for Long Running Queries

Related Posts

No results found.

1 Comment. Leave new

  • Gaurav Srivastava
    September 1, 2014 7:31 am

    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

    Reply

Leave a Reply