How to Find Table Cardinality from the Execution Plan? – Interview Question of the Week #213

Question: How to Find Table Cardinality from the Execution Plan?

Answer: During SQL Server Performance Health Check, I often get such questions, which are often very interesting. Let me answer this question in two different ways. However, before we see two different methods, let us run the following query on the sample database AdventuerWorks. You can pretty much run this query for any table in your system.

SELECT TOP (1000) *
FROM [AdventureWorks2014].[Person].[Address]

Before you run the above query, make sure that you enable execution plan. Now let us see two different methods to identify Table Cardinality. You can enable the execution plan by also using the SSMS shortcut CTRL + M.

Method 1: Execution Plan Property

In the execution plan, click on the rightmost table operator and click on the property. Once you click on the property, it will bring up the properties on the right side where scroll down all the way to the bottom. Here you can see a property TableCardinality which will show how many rows are there in the table.

How to Find Table Cardinality from the Execution Plan? - Interview Question of the Week #213 tablecardinality1

Method 2: Execution Plan XML

Right click on the execution plan and select the Show Execution Plan XML…It will bring up the XML for the execution plan. Over here search for the word TableCardinality and you will see the value of the rows in the table.

How to Find Table Cardinality from the Execution Plan? - Interview Question of the Week #213 tablecardinality3

Well, here are two different methods, I am familiar with to know the TableCardinality from the execution plan. However, if you know any third method do let us know by leaving a comment so I can post a blog post on the subject with due credit to you.

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

, , , , , ,
Previous Post
What is Consolidation of Index? – Interview Question of the Week #212
Next Post
How to Extract Alphanumeric Only From A String? – Interview Question of the Week #214

Related Posts

Leave a Reply

Menu