Hi Friends,

A few days back I penned down a few quick notes about Cardinality Estimation in SQL Server. You can read Part 1 here.

SQL Server Cardinality Estimation is a complicated topic until you understand the internals of SQL Server Statistics. Here are some quick notes that will answer some common questions that you generally have:

How does SQL Server Query Optimizer estimate a default cardinality? Be sure to check out Part 1

Let’s see another example (turn ON Actual Execution Plan or press Ctrl + M)

cardinality_estimation_in_sql server_1

You can observe that the Estimated Number of Rows is 9439.5

Let’s change the parameter value.

cardinality_estimation_in_sql server_2

You again see that the Estimated Number of Rows is 9439.5

No matter what constant you specify, SQL Optimizer will always computer 9439.5

Why?

The optimizer cannot use the Statistics simply because of the preceding expression (TaxAmt  + TotalDue). Even if there are statistics for TaxAmt and TotalDue columns, they are of no use because for SQL Server, it’s the left side expression value that is now considered, not the columns. So where does 9439.5 come from?

That value is 30% of the total number of rows. This is the default estimation done in case expressions are used.

Total number of rows are 31465.

30% of 31465 = 9439.5

cardinality_estimation_in_sql server_3

That’s how the default estimation was computed in this case. Hope the quick note was helpful.

 

Amit Bansal
SQL MCM, SQL MVP
Director – eDominer Systems, Peopleware India, SQLMaestros

Follow me on Twitter | Follow me on FaceBook | Connect on LinkedIn | Google+
FB page | Know more about me

SQL Server Cardinality Estimation - Quick Notes, Part 1
SQL Server Cardinality - Quick Notes, Part 3