9

I have searched a long time and still haven't found the answer to my problem.

Our Dynamics AX is generating queries with the query hint OPTION (FAST x), which sometimes forces a bad execution plan to be used. The devs say it is by default and is hard to change (has to be fixed on all forms potentially).

So I was after a way to override these hints using trace flags. I found the lovely trace flag 8722 which is claimed to make SQL Server ignore some query hints, especially the ones in the OPTION clause.

However, this doesn't work in my case. I tried to enable trace flag 8602 (disable index hints) as well, but my query is still running with the FAST x hint (it is much slower than when I actually remove the OPTION clause).

I tried to clear the plan cache as well, to no avail.

Any ideas? Am I missing anything?

P.S. I enabled the trace flags globally, this is SQL Server 2012 Developer edition

Improve this question
2
  • 1
    How did you turn on the trace flags? Did you turn it on via the server startup or using dbcc traceon? dbcc traceon is a per connection trace flag, so turning it on in one client connection doesn't affect other server connections. To turn it on server wide, you need to add -T <traceflagnumber> to the startup command line. I believe you may be able to use a dbcc traceon(flag1,flag2,...,-1) to apply it server wide, but I've never tried it.
    – Mike
    Oct 2, 2014 at 14:09
  • Hi Mike, I did enable the trace flags globally using DBCC TRACEON(8722, -1) and then checked the status with DBCC TRACESTATUS. Maybe the trace flag is no longer officially supported or I actually need to put it in the startup parameter and restart the service, or it is the subject of CU3 (I am running 2012 SP2 RTM).
    – Nelson 2.0
    Oct 2, 2014 at 15:40

1 Answer 1

Reset to default
11

As far as I know, trace flags 8722, 8755, and 8602 were never officially documented. The last time I remember 8722 and 8755 being effective was in SQL Server 2000, so it is not surprising you find they are ignored in SQL Server 2012. Trace flag 8602 is still functional in SQL Server 2019 as a global or start-up flag to ignore index hints specifically.

For specific query patterns, it is often possible to remove the FAST n hint using plan guides. Even so, the best fix is to get the AX developers to fix it on the application side of things.

Improve this answer
0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.