Reason for Early Termination of Statement

Wouldn’t you like to know why the optimizer stopped tuning the execution plan you’re looking at? It’s actually possible and simple to get this information. I talked about this a little more than a year ago, but I left out some information that might be useful.

Let’s take a very simple query:

SELECT * FROM Person.Address AS a;

This generates a simple execution plan, in fact a trivial plan:

Trivial Execution Plan

I know that this is a trivial plan one of two ways. The hard way is to look at the XML (BTW, this is the type of thing you can’t see it in STATISTICS PROFILE, which I understand a lot of people are using). Right at the top is the SELECT element. You can see the value for StatementOptmLevel property is equal to “TRIVIAL.” But reading XML is a pain. Right clicking on the SELECT operator above and selecting Properties will result in this property sheet:

Trivial Plan Properties Sheet

If you look at the properties here, just six up from the bottom is “Optimization Level,” the human readable equivalent to StatementOptmLevel. You can see that it’s set to TRIVIAL.

I know exactly what you’re thinking, “Hey, what the heck happened to early termination & stuff?”

Hang on. I’ll get you there.  The fact is, not every plan has a reason for early termination, like the one above. When a plan is identified as TRIVIAL, it doesn’t go through the optimizer at all. This is why it doesn’t display a reason for early termination. It didn’t terminate, it just stopped the process. The trick is, to get a more complicated query so that we get a more complicated plan:

SELECT p.LastName + ', ' + p.FirstName AS FullName,
a.AddressLine1,
a.City,
a.PostalCode
FROM Person.Address AS a
JOIN Person.BusinessEntityAddress AS bea
ON a.AddressID = bea.AddressID
JOIN Person.Person AS p
ON bea.BusinessEntityID = p.BusinessEntityID
WHERE a.AddressID = 252

This query will not return a trivial plan because the plan, despite the simplicity of the query, is too complex, due to the joins, etc. Regardless of the details about the trivial plan, the interesting point here is to be found, again, in the properties of the SELECT operator:

With this set of properties, look first at the “Optimization Level.” Instead of TRIVIAL, we have a FULL optimization, something to shoot for. Looking down near the bottom of the properties you can see “Reason For Early Termination.” The value is “Good Enough Plan Found.” Which means that the optimizer feels, based on the statistics, that it has found a pretty good plan, not necessarily the best possible, plan, but a plan that is good enough. Which is exactly what the optimizer is supposed to do. You can also see the termination reason in the XML for the plan in the property StatementOptmEarlyAbortReason.

The trouble comes in when the queries are truly complex, nested views where views join to views are a good example. With a view, the optimizer can look at the query being run against it and make determinations as to whether all the tables are needed to satisfy the query. It’s possible that some tables are not needed. But when you start nesting views and joining views to views, well, things change. This is just an example (yes, I’m using *, I wouldn’t do this in production, I just need to get an ugly plan, and this is ugly) that uses three views from AdventureWorks2008R2:

SELECT *
FROM HumanResources.vEmployee AS ve
JOIN Sales.vSalesPerson AS vsp
ON ve.BusinessEntityID = vsp.BusinessEntityID
JOIN Sales.vSalesPersonSalesByFiscalYears AS vspsbfy
ON vspsbfy.SalesPersonID = vsp.BusinessEntityID

This, seemingly simple, query produces a pretty heinous execution plan. I’ve zoomed and shrunk it to see it all at once:

And while this seems quite complex, the fact is, these views, and this execution plan, are relatively well written. My query against them is fairly out of line, but imagine what the plan might look like if someone who would write such an out of line query also wrote the views. Yes, these execution plans can get quite ugly. If we go on to examine the properties on the SELECT statement we’ll see something different:

This time you see that the “Optimization Level” is FULL, but the “Reason For Early Termination” is “Time Out.” This means that the optimizer, which doesn’t really measure time, but the number of attempts it makes at finding an optimal plan, has run through all it’s attempts and arrived at something less than “good enough.” This time it simply stopped trying. In this case, it takes the plan that is currently the best, which might be a good plan… or it might not be, and applies that plan to the query. While it’s guaranteed to return the data correctly, it may or may not return the data efficiently, and looking at the execution plan in this, that’s unlikely.

Why is this? Well, the easiest place to start is pretty simple, look at the number of tables in that execution plan. If you take into account the data that could meet the criteria of the query, in this case, most of it, and the number of tables, you can see why the optimizer might be taxed. After that, things get complicated. You have to begin to look at how you’re putting your query together, the types of functions and methods you’re using and, in general, the overall complexity of the process. My respect for the developers who have created the optimizer is just about boundless. These are some amazing people to have put all this together. But, the problem they’re solving is incredibly difficult, and they are not working miracles (even though it sometimes feels that way). The best thing you can do is to understand how the optimizer works and try to help it along in places where it might need it.

There is one more reason for early termination of the optimizer, and you may see it. I can’t reproduce it at will in a sample database, but it’s “Memory Limit Exceeded.” That one is largely self-explanatory and, unlike the other two values, it represents a serious problem. In this case, you need to do something about the available memory on your server. You can increase memory or decrease memory pressure by taking load off the server. Those are really your only options. You’ll still get a functional execution plan on this, but, like the timeout, it’s very likely this is not an optimal plan and certainly not “good enough.”

18 thoughts on “Reason for Early Termination of Statement

  • Grant, this post is excellent. I sadly don’t get enough time with Query Optimization these days but its posts like these that get my excited about the prospect of deep diving again. Keep up the good work!

  • Klaus Aschenbrenner

    Hello Grant,

    very good article. Are you able to produce “memory limit exceeded” when setting the max-memory setting?

    Thanks

    -klaus

  • Tommy Petersson

    Thanks for the article, now it would be nice to get some tips on the safest and best ways of “helping it along”…
    With complex queries I try to split them, change unnecessary joins to “AND EXISTS”, rearrange the joins, add query hints…

  • Amici

    Thanks for the article (although I’m a bit late). What I’m wondering is – is the number of timeouts mentioned here the same as the one we find in:

    SELECT * FROM sys.dm_exec_query_optimizer_info WHERE counter = ‘timeout’
    ?

  • Very nice article, thanks Grant. Someone have found a query to get all plans from cache that suffer from this kind of timeout?

    The dm_exec_query_optimizer_info gives only the counter value and not the query itself.

  • Clayton Hoyt

    Hi Grant

    I occasionally see SELECT statements that have their Optimization Level set to FULL; however, there is no Reason For Early Termination field present to view. Does that mean that the optimizer didn’t finish early but did find a good enough plan?

  • “My respect for the developers who have created the optimizer is just about boundless.”
    Well put, Grant. Enjoyed this article not just for my own benefit, but for certification 70-764.

    Thanks for sharing!

  • J.D.

    Great article Grant! One question I’ve been wondering and unable to find anything more than anecdotal evidence on is whether nesting multiple views themselves can lead to early termination timeouts, regardless of how cleanly written the queries inside each view is. I’ve lately started encountering some weird execution plans myself, for code that I know is very well written individually, but unfortunately due to the complex nature of the system, has about 10 layers deep of nested views, and multiple instances of those deep views joined together in one final view. The execution plan is very complex, and the number of unique tables involved is probably around 25 or so. I’ve heard anecdotal evidence that unwinding those layers and inlining most of the code into the final view theoretically can resolve it. But just wondering your take, is it hearsay or holds some truth?

    • Yeah, nesting views, even well written views, could absolutely make it more likely for the optimizer to timeout. Because it has to go through the simplification process, it has to unpack all those views and figure out which tables it really needs and which it doesn’t, which are repetitive, if all the joins are necessary, all that stuff. The more work like that you put on the optimizer, the less time it has for optimization.

      The problem is simple. Views look like a way to sort of turn SQL Server into a decent programming language. Write the query one time, then just reference it like an object. Then, join object to object, etc.. Problem is, that’s just not how SQL Server works. It doesn’t treat everything as a discrete object. Instead, it treats it as a gigantic query. So while the temptation is reasonable and logical, I do not recommend joining and nesting views, ever. Instead, purpose build the queries, even if it means chunks of them are repetitive.

Please let me know what you think about this article or any questions:

This site uses Akismet to reduce spam. Learn how your comment data is processed.