SQL Server 2014 Another reason to use New Cardinality Estimator

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.

Create database CETEST
go
USE CETEST
go
Create table xttest
(
id int identity(1,1),
name varchar(50)
)
go
Insert into xttest values('SQLServerGeeks')
go 1000
Select id,name from xttest where id<800
go

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

SELECT name FROM sys.stats WHERE object_id =OBJECT_ID('xttest')
go

NewCE_1

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

DBCC SHOW_STATISTICS('xttest','_WA_Sys_00000001_21B6055D') WITH STAT_HEADER 
Go

NewCE_2

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

insert into xttest values('SQLServerGeeks')
go 500
Select id,name from xttest where id>800
go

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.

   
DBCC SHOW_STATISTICS('xttest','_WA_Sys_00000001_21B6055D') WITH STAT_HEADER 
go

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.

Select id,name from xttest where id>1100 OPTION (QUERYTRACEON 9481)
Go

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.

Select id,name from xttest where id>1000
go

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

   

About Prince Rastogi

Prince Rastogi is working as Database Administrator at Elephant Insurance, Richmond. He is having more than 8 years of experience and worked in ERP Domain, Wealth Management Domain. Currently he is working in Insurance domain. In the starting of his career he was working on SQL Server, Internet Information Server and Visual Source Safe. He is post graduate in Computer Science. Prince is ITIL certified professional. Prince likes to explore technical things for Database World and Writing Blogs. He is Technical Editor and Blogger at SQLServerGeeks.com. He is a regular speaker at DataPlatformDay events in Delhi NCR. He has also presented some in depth sessions about SQL Server in SQL Server Conferences in Bangalore.

View all posts by Prince Rastogi →

Leave a Reply

Your email address will not be published.