Building an execution plan takes too long on SQL Server

Question

I have an SQL Server 2014 instance (12.0.2000.8) and a quite complex SELECT statement with about 20 joins. This query works fine with the same data set on PostgreSQL, Oracle and other databases, and entire execution takes about 1 minute.

But on SQL Server it takes about 40 minutes. I tried to look at the execution plan and I started to wait... I tried to get the execution plan by executing a query from an application session, but there were no execution plan.

Then I got the query and asked in SQL Server Management Studio for "Display estimated execution plan", and I also started to wait. So, it looks like it takes too much time just to build the execution plan. All statistics is gathered with "exec sp_updatestats", and I checked it in sys.stats - everything looks fine. All indexes are in place.

I commented all joins and started to uncomment them one by one, and

  SET STATISTICS TIME ON

shows that with every uncommented join it takes a longer time to parse, e.g. timing for 13 joins:

SQL Server parse and compile time:
   CPU time = 32250 ms, elapsed time = 32729 ms.

so, it's definitely a parsing issue.

select count(*) from sys.index_columns
where object_id in (OBJECT_ID('tables_names'),...')

says there are 128 columns, when

select * from sys.indexes
where object_id in (OBJECT_ID('tables_names'),...')

returns 43 rows with HEAP, CLUSTERED, NONCLUSTERED indexes.

Could you recommend what to look at? Why does it parse so much?

Update: Thank you for "Break the query up" and "Use a FORCE ORDER hint", but this SQL is generated by our application, so it could be a lot of effort to make it possible with the application logic, but in general cases they should be a great solution.

Second update: Applying SP3 did the whole thing - the whole execution query takes less than one second. The performance became better by two thousand times :)

Answer 1

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+).

Answer 2

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).