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