23

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:

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

  2. 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].

enter image description here

Improve this question
1

2 Answers 2

Reset to default
25

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

Improve this answer
10

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

Improve this answer
0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.