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'

traceFlag2312_1

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)

traceFlag2312_2

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

   

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.