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
create table xtTraceTest ( productid int identity(10001,1) Primary Key, price int ) go declare @temp int declare @i int SET @i=0 while @i<=10000 begin SET @temp = RAND()*550 insert into xtTraceTest values(@temp) SET @i=@i+1 end go create nonclustered index IX_xtTraceTest on xtTraceTest(price)
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.
Select productid, price from xtTraceTest Where price<50 and productid<11000
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.
Select productid, price from xtTraceTest Where price<50 and productid<11000 OPTION (QUERYTRACEON 9481)
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.
Prince Kumar Rastogi