Sql-server – Building an execution plan takes too long on SQL Server

execution-planoptimizationsql serversql server 2014

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 🙂

Best Answer

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