SQL Server Trace flag – 8780

Sometimes when you have a query that runs slow, you look at a the execution plan in XML format and find the following attribute StatementOptmEarlyAbortReason=”TimeOut”. When a query is compiled, the optimizer tries to find the best execution plan, but it can take a very long time to find the best execution plan for complex queries. To prevent this there is a limit for how long time the optimizer can try to find the best plan. When timeout has passed, the optimizer chooses the best one, but there might be another execution plan that’s not evaluated. If the optimizer could spend more time it might find another plan that is more useful.

With the traceflag 8780, you can give the optimizer more time for evaluating alternative exeution plans. This traceflag should not be applied at global scope as it will cause the optimizer to spend more time for every query. If you want to see if the query could find a beter plan you could add this trace flag as a query hint like this:

SELECT col1,col2,colx
FROM t1
….some complex join clauses, etc.
WHERE col1=1
OPTION(QUERYTRACEON 8780)

If the optimizer finds a faster plan, you can use it investigate how the query can be tuned.

CUATION: most traceflags are undocummented (and unsupported) and should be handled carefully

By Håkan Winther
Twitter: @h_winther