Hi Friends,

In my previous blogs we have seen how we can use new cardinality estimator. Link for those blogs are mention here : Link1 and Link2.

Now I am using SQL Server 2014 with new cardinality estimator. Let’s consider that while using new cardinality estimator 98% query workload is running fine but for rest of the 2% performance is not good (consuming much resources) as compared to previous version of SQL Server. Here I want to use Legacy Cardinality estimator for these 2% queries. But the question is, Can we use legacy cardinality estimator for specific queries? Answer is yes. Microsoft provided a trace flag for such type of scenarios i.e. SQL Server 2014 Trace Flags 9481. 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 New Cardinality Estimator and this query is also using the same.

CardinalityEstimator2014_1

When you will put 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 new 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 9481 for using Legacy Cardinality Estimator.

CardinalityEstimator2014_2

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

Reference: click here.

Regards :
Prince Kumar Rastogi

Like us on FaceBook | Join the fastest growing SQL Server group on FaceBook

Follow me on Twitter | Follow me on FaceBook