SQL Server 2014 Trace Flags 9481

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

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.

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