SQL Server 2014 Trace Flags 2312

Hi Friends,

In my previous blog SQL Server 2014 Trace Flags 9481 we have seen how we can use legacy cardinality Estimator while using compatibility level 120. If you want that link then click here.

There may be the situation, when you are using SQL Server 2014 but with compatibility level 110 for the database. That means your database is using legacy cardinality estimator. There may be some queries for those you want to use new cardinality estimator. Microsoft provided a trace flag 2312 for such type of scenario. You can run your query with new cardinality estimator while using compatibility level 110 on SQL Server 2014. Let me show you the use of this trace flag. Create a new table, insert some data and create a non clustered index

Use master
go
Create database CETEST2014
go
alter database CETEST2014 set compatibility_level=110
go
USE CETEST2014
go
create table xtTraceTest
(
custcode int identity(10001,1) Primary Key,
 balance int,
 state_name varchar(50),
 country varchar(50)
)
go
insert into xtTraceTest(balance,state_name,country) values(RAND(),'UP','INDIA')
go 1000
insert into xtTraceTest(balance,state_name,country) values(RAND(),'HARYANA','INDIA')
go 1233
insert into xtTraceTest(balance,state_name,country) values(RAND(),'KERALA','INDIA')
go 677
insert into xtTraceTest(balance,state_name,country) values(RAND(),'CALABRIA','ITALY')
go 1872
insert into xtTraceTest(balance,state_name,country) values(RAND(),'GOA','INDIA')
go 4534
insert into xtTraceTest(balance,state_name,country) values(RAND(),'MP','INDIA')
go 2763
insert into xtTraceTest(balance,state_name,country) values(RAND(),'GANGSU','CHINA')
go 86
insert into xtTraceTest values(RAND(),'ASSAM','INDIA')
go 123
create nonclustered index IX_xtTraceTest on xtTraceTest(country,state_name) include (balance)

Now run the below query along with include actual execution plan. As of now my database is using old Cardinality Estimator and this query is also using the same.

Select custcode,balance from xtTraceTest 
where country='INDIA' and state_name='GOA'

When you will bring the cursor on index seek then it will show you a tool tip. Here Actual and Estimated number of rows are showing very much different values. From here it is clear that legacy cardinality estimator not able to correctly estimate the number of rows to be processed for this query. Now let me run above query with trace flag 2312 for using New Cardinality Estimator.

Select custcode,balance from xtTraceTest 
where country='INDIA' and state_name='GOA' 
OPTION (QUERYTRACEON 2312)

After using trace flag 2312, Actual and estimated number of rows are very much near and query performance will be good. Now we can say in this case New Cardinality Estimator is working fine as comparison to Legacy Cardinality Estimator.

Reference :  Click Here.

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