Queries without Good Enough Plan Found

• 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.

If you go over to http://schemas.microsoft.com/sqlserver/2004/07/showplan/showplanxml.xsd (which is the link you'll see if you open an execution plan as xml), you'll see the three reasons listed, which are:

• TimeOut
• MemoryLimitExceeded
• GoodEnoughPlanFound

The articles you mention seem ok for finding these events, are you having a specific problem? The only thing to bear in mind is that these DMVs don't capture all SQL commands ever run on the server and get reset when the server restarts. You could trap showplan xml with Extended Events and query that, but it seems like overkill to me.

I wouldn't worry too much about GoodEnoughPlanFound either, seems like the optimiser is doing its job (of finding a good plan quickly) pretty well.

HTH