How long is too long for a SELECT compile?

Dan Holmes 2015-07-27 20:06:27

The goal is sql is a good plan fast but is 15 min reasonable or should i consider that a bug?

QueryPlan CachedPlanSize="16704" CompileTime="956878" CompileCPU="956407" CompileMemory="2674960"

Those 956 seconds is 15 min. I pulled that node from the Estimated plan.

SQLkiwi 2015-07-28 00:39:34
Fifteen minutes seems excessive, assuming the query is "reasonable" i.e. not purposely designed to be problematic.

If this compilation occurred under the new CE model, be sure you are running the very latest CU with any relevant QO fix trace flags enabled. There have been a few new CE (and columnstore) related compile time bugs fixed, so you should check the relevant KBs.

That said, although the optimizer goes to some lengths to prevent excessive compilation times, it's probably inevitable that edge cases will always exist.

Once you have tested on the latest bits with TFs enabled, and if the behaviour persists, you should report it (with a repro if possible) via your normal support channel.