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

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.

Select productid, price from xtTraceTest 
Where price<50 and productid<11000
OPTION (QUERYTRACEON 9481)

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

Avatar

About Prince Rastogi

Prince Rastogi is working as Database Administrator at Elephant Insurance, Richmond. He is having more than 8 years of experience and worked in ERP Domain, Wealth Management Domain. Currently he is working in Insurance domain. In the starting of his career he was working on SQL Server, Internet Information Server and Visual Source Safe. He is post graduate in Computer Science. Prince is ITIL certified professional. Prince likes to explore technical things for Database World and Writing Blogs. He is Technical Editor and Blogger at SQLServerGeeks.com. He is a regular speaker at DataPlatformDay events in Delhi NCR. He has also presented some in depth sessions about SQL Server in SQL Server Conferences in Bangalore.

View all posts by Prince Rastogi →

3 Comments on “SQL Server 2014 Trace Flags 9481”

  1. Hi Prince,
    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.

    Regards,
    Phani

  2. 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.

Leave a Reply to Prince Rastogi Cancel reply

Your email address will not be published. Required fields are marked *