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.
Prince Kumar Rastogi
Like us on FaceBook | Join the fastest growing SQL Server group on FaceBook
Follow me on Twitter | Follow me on FaceBook
3 Comments on “SQL Server 2014 Trace Flags 9481”
My name is Phaneendra & It’ss really pleasure to see your blog on “SQL Server 2014 Trace Flags 9481”.
I have come across similar kind of situation in my work.
we have recently migrated from SQL 2012 to 2014.
After migrating the data, some of the Stored Procedures are taking more time to execute the data.
SP’s are taking more time to execute in PROD compare to NON PROD databases.
Traceflag 9481 has been used to overcome this issue temporarily.
But, we ended up in another permissions issues.
Is it possible for you to help me out to resolve this issue.
can you please share details about permission issue that you are facing?
If anyone still have the permission problem and you are facing the that issue while using the OPTION (QUERYTRACEON XXXX), please be aware that you need to be a sysadmin fixed server role member to execute this query hint. There are two work arounds,
1. Ask your DBA to give you sysadmin privilege.
2. Or, write the stored procedure with this query hint and give it to your DBA, ask them to compile the SP form the security context of an sysadmin member. Then a non sysadmin member can execute that as sql server support permission chaining.