HiveBrain v1.2.0
Get Started
← Back to all entries
patternsqlModerate

T-SQL - OPTION (FAST x) and trace flag 8722

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
fastsqlflagoptionandtrace8722

Problem

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

Solution

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.

Context

StackExchange Database Administrators Q#78306, answer score: 11

Revisions (0)

No revisions yet.