Hi Friends,

This blog is the continuation of my previous blogs on SQL Server 2014 New Cardinality Estimator. You can go to previous blog by clicking here.

Using the legacy Cardinality Estimator, I have created one table and insert 1000 rows and perform a select query with where clause. Here SQL Server generates an automatic column statistics for the where clause column. Now the concern is, if we are running any query to get the subset from 1000 rows then optimizer will calculate the estimate number of rows from that column statistics.

Now if we will insert 500 more rows to the table then statistics will not be updated automatically as per threshold limitation. If I will query to get the newly inserted rows then Legacy Cardinality Estimator will calculate the estimated number of rows to 1 because it doesn’t have statistics for newly inserted rows.

If we will query to get the newly inserted rows then New Cardinality Estimator will calculate the estimated number of rows very near to actual number of rows as comparison to Legacy CE. This is a great improvement.

Now run below query to get the name of statistics created on id column. this name may be different on your system.

NewCE_1

Replace the statistics name [which you got from above query] in below query and run it

NewCE_2

Output clearly shows that there are only 1000 rows. Now run the below query to insert 500 more rows to the table.

If you will check the statistics header again then it will still show you the total number of rows 1000 for statistics because of statistics threshold value for update.

NewCE_21

Now we will query to get newly inserted rows with include Actual Execution Plan under legacy CE. Here interesting thing is, statistics don’t know about these newly inserted rows.

NewCE_3

As per the query plan estimated number of rows is 1 while actual number of rows is 500. This is not good for performance. Now let’s run the same query under New CE.

NewCE_4

Now from above execution plan it is clear that the estimated number of rows are much better as comparison to Legacy CE. This is a great improvement.

HAPPY LEARNING!

Regards:
Prince Kumar Rastogi

Like us on FaceBook | Join the fastest growing SQL Server group on FaceBook

Follow Prince Rastogi on Twitter | Follow Prince Rastogi on FaceBook