Building an execution plan takes too long on SQL Server

I have an SQL Server 2014 (12.0.2000.8)

RTM version? I recall running into excessive compilation duration (minutes) for some queries. The issue was fixed post RTM. I suggest you patch your server to a supported patch level (SP3+).


I expect that most of the compilation time is spent by the optimizer considering different approaches to re-ordering the many joins.

Two options for fixing this are:

Break the query up

One approach that might work, depending on your query, is separating the relational from the informational part of your query. This involves breaking up the query into two parts, so each part has less joins (and thus less complexity for the optimizer to dig through).

The approach is essentially to take the things that filter rows or otherwise provide the logic of your query (columns involved in where clauses, inner joins, etc) and run just that query, inserting it into a temp table.

Then add in the rest of the "informational" or display-related things in a separate query that's just joining to the temp table.

I first heard about this from Erik Darling, see here for a great example: Informational vs. Relational

Note that that's largely about avoiding wide results and indexes, but it can be effective for compilation if you're able to eliminate some of the joins in the first query entirely.

Use a FORCE ORDER hint

Adding OPTION (FORCE ORDER) to the end of the query should limit the compile time, although you may have to experiment with the written order of the joins in order to get a reasonable execution plan (and this might change over time as your data or schema changes).

At this risk of coming across as an Erik Darling Data fan boy, this post is relevant to your situation and worth reading: Are Long Compile Times Bringing You Down?

That post discusses the FORCE ORDER approach to solving this (in particular using a plan guide, since the problem query was generated by EF and thus the hint couldn't easily be added at the source).