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