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:

[sourcecode language="sql"] SELECT * FROM Person.Address AS a;[/sourcecode]

This generates a simple execution plan, in fact a trivial 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:

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:

[sourcecode language="sql"]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[/sourcecode]

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:

[sourcecode language="sql"]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[/sourcecode]

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