Queries without Good Enough Plan Found I have a SQL Server 2012 database. I noticed value of Reason for early termination of statement optimization for some queries and all gave Good Enough Plan Found. Now my questions are:


What are all the possible types of  “Reason for early termination of
statement optimization”. I did searching for this in msdn but didn’t
get a complete list of values.
Is there a DMV or extended event to list all queries for which optimization was terminated due to reasons other than Good Enough Plan Found? I referred following two articles which does not list the complete list of possibilities. [Also, they give me different result in my database].


Finding: Query Compilation
Timeout 
Identifying Query Plans that are not good
enough
Memory Limit Exceeded

The optimizer was forced to stop looking for better plan alternatives due to memory pressure. The reason for that should be investigated and corrected, then query compilation attempted again. The plan returned may very well not be the one the optimizer would have selected had the low memory condition not existed.
Time Out

This reason is much misunderstood.

The query optimizer aims to find a reasonable plan quickly. It does not perform an exhaustive search to find the best possible plan. By design, it avoids spending more time on optimization than necessary. One of these features that works to ensure this is the 'time out' (not a measure of time).

The optimizer sets itself an 'exploration budget' based on the complexity of the logical query, cardinality estimates, and the estimated cost of the cheapest plan found so far (if any). More complex queries with higher cardinality are given a higher budget.

If this budget is exceeded during one of its search phases, the phase ends. This is part of the design and normal operation of the optimizer. Queries that need more optimizer effort get it; ones that don't, don't.

Think of 'Time Out' as 'Good Enough Plan Found'.
Good Enough Plan Found

This means exactly the same as a blank reason. It is simply a historical quirk that plans with a cost below 0.909090... (1/1.1) are labelled this way. Nothing stops early or is otherwise specially handled or different within the optimizer code when this reason appears.


Aside from Memory Limit Exceeded, none of the 'early termination reasons' mean much (if anything at all) for query tuning or performance analysis. I generally ignore them.

Advice

Target query tuning efforts based on real performance metrics (elapsed time, CPU/memory usage, ... whatever is important in context). If a query is too slow for its intended purpose, spend time making it faster. Measure actual performance, compare it with the baseline and history, and target tuning effort at the important variances.

Store guaranteed-clean data in a proper relational schema, with useful statistics and indexes, and well-written, optimizer-friendly queries.