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.

 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 (http://blog.sqlauthority.com)

About these ads

One thought on “SQL SERVER – How to Know Cardinality of Executed Query?

  1. 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

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s