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:

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

Menu
{"cart_token":"","hash":"","cart_data":""}