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

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.


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.


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.


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