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

migrated from stackoverflow.com Oct 3 '14 at 9:50

This question came from our site for professional and enthusiast programmers.

  • 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 '14 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 '14 at 15:40
10

As far as I know, trace flags 8722, 8755, and 8602 were never officially documented. The last time I remember them being effective was in SQL Server 2000, so it is not terribly surprising that you find they are ignored in SQL Server 2012.

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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