Compilation Timeout

Complex queries can take a long time to compile. The SQL Server query optimizer will only spend so much time attempting to optimize a query plan before it gives up and returns the best guess that it has. You will typically see this when the “Reason for Early Termination” plan property is set to “Time Out”.

How to Fix the Problem:

There’s no easy way to fix this problem. The only thing to do is simplify your query.

If you are using a lot of functions and views, try removing the functions or views. It may be possible to inline the function or view logic and produce a much smaller search space for the query optimizer. If that doesn’t work, break apart large amounts of joins into smaller logical chunks and use temporary tables as a way to mitigate the complexity.

You can find queries generating a timeout warning using the following query:

WITH XMLNAMESPACES('http://schemas.microsoft.com/sqlserver/2004/07/showplan' AS p)
SELECT  CASE
              WHEN qs.statement_end_offset > 0
                     THEN substring(st.text, qs.statement_start_offset/2 + 1,
                                                       (qs.statement_end_offset-qs.statement_start_offset)/2)
              ELSE 'SQL Statement'
       END as timeout_statement,
       st.text AS batch,
       qp.query_plan
FROM    (
       SELECT  TOP 50 *
       FROM    sys.dm_exec_query_stats
       ORDER BY total_worker_time DESC
) AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) AS qp
WHERE qp.query_plan.exist('//p:StmtSimple/@StatementOptmEarlyAbortReason[.="TimeOut"]') = 1

WITH XMLNAMESPACES('http://schemas.microsoft.com/sqlserver/2004/07/showplan' AS p)

SELECT CASE

WHEN qs.statement_end_offset > 0

THEN substring(st.text, qs.statement_start_offset/2 + 1,

(qs.statement_end_offset-qs.statement_start_offset)/2)

ELSE 'SQL Statement'

END as timeout_statement,

st.text AS batch,

qp.query_plan

FROM (

SELECT TOP 50 *

FROM sys.dm_exec_query_stats

ORDER BY total_worker_time DESC

) AS qs

CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st

CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) AS qp

WHERE qp.query_plan.exist('//p:StmtSimple/@StatementOptmEarlyAbortReason[.="TimeOut"]') = 1

Thanks to Stuart Wilson for adding the current statement to the query, and Simon Holzman for improving it to work with dynamic SQL.