TSQL Tuesday #2

I’ve wracked my brain for some bit of puzzle that I could present as part of TSQL Tuesday #2 and I finally came up with a bit of something.

When you’re looking at an execution plan for a query, you know that this represents SQL Servers best attempt at a good execution plan. But, it may not represent the best possible plan. Or, it could be the only possible plan. The puzzle is, how do you know what you’re looking at? Is this a trivial plan, meaning it’s the only possible execution method for the query? Is this plan fully optimized, or did the optimizer go through it’s prescribed cycles and simply take the best plan it had generated up to that point?

These questions can be answered directly from information available to you in the execution plan. Let’s take two queries, one incredibly simple and one mildly complex:

SELECT a.StateProvinceID
FROM Person.Address AS a
WHERE a.StateProvinceID = 42
SELECT soh.AccountNumber
,sod.LineTotal
FROM Sales.SalesOrderHeader AS soh
JOIN Sales.SalesOrderDetail AS sod
ON soh.SalesOrderID = sod.SalesOrderID
WHERE sod.SalesOrderID = 43933

When you run the first query the execution plan looks like this:

It’s clearly a simple plan for a simple query, but what did the optimizer do?

Let’s take a quick look at the next query and it’s execution plan:

A slightly more complicated plan, but again, the question is, what did the optimizer do? Was it able to do a complete and full optimization or did it timeout? How can you tell?

Tune in next week…

Kidding. It’s pretty easy. Take a look at the SELECT operator in both queries. Right click on that operator and open up the Properties window. Here you’ll get a lot of interesting information about the execution plan itself, how long it took, the plan hash & query hash, how much memory it used and, most interesting for this little mystery, the Optimization Level and, depending on that level, the Reason for Early Termination. Take a look at the properties for the first query:

As you can see, the Optimization Level for the first query is TRIVIAL. This means it’s a Trivial plan, no other possible plan could be created for it. Notice also that there is a Parameter List, consisting of one parameter, @1, despite the fact that no parameters were used. This is evidence of simple parameterization. Yet more information that can be found in the Properties.

If we then look at the second query’s  properties:

The Optimization Level in this case is FULL, meaning that it went through a complete cycle within the query optimizer. Looking down, below the Query Hash & Query Plan Hash you can see the Reason For Early Termination Of Statement Optimization, slightly truncated. This shows that a “Good Enough Plan Found.” Which means that the optimizer feels that it’s done everything for this that it thinks it can. Generally, this is a good sign. The other thing you’ll usually see here is Timeout, meaning that the optimizer gave up and used the best plan it had at the moment. That’s generally a bad sign and an indication that you may have tuning opportunities in the query. However, neither of these are hard and fast rules. You can get a full optimization with a good enough plan and still have a pretty stinky query plan. This is just another bread crumb on the trail, another piece of information that you can use to figure out if you’ve got a good execution plan or not.