Changes to SQL Server 2012 Execution Plans

I’ve been working with execution plans quite a lot in SQL Server 2012. There are a number of changes, most of them associated with new or different functionality. I had not noticed anything really fundamental until recently. I’ve become a huge proponent of always checking the properties of the SELECT statement. There’s so much useful information in there about what’s happened with the plan generation in the optimizer (not low level stuff, but the big picture items) that you should always be checking it first as a fundamental part of your plan examinations. Here’s an example from SQL Server 2008R2:

You can see cool stuff like the size of the plan, the time it took to compile, the optimization level, the reason for early termination. Many of the properties on this page are useful for determining information about this plan and how the optimizer dealt with it.

Here’s the property sheet from SQL Server 2012 from an identical query against an almost identical database (AdventureWorks2008R2):

Oh yeah, we’ve got some meat on this bone to chew on. All the good stuff from the original is still in place. But we have more added. Working from the top, we see MemoryGrantInfo. I’ll have to do some more research and testing to validate this, but I’m pretty sure that’s a measure of temporary data storage assigned for expenseive join and sort operations, and now it’s built right into the execution plan. That’s going to be a very useful tool. Note that this plan, while marginally complex, doesn’t need any of the memory grant.

Next is the OptimizerHardwareDependentProperties which very much explains itself don’t you think.But how cool to know that for this plan the optimizer thought it could get 2 processors to work. Also that the optimizer takes memory and pages into account as part of its internal processes, probably as part of determining parallelism as well.

Next is RetrievedFromCache. At first I about did a backflip to know that this information, whether or not a query was pulled out of the cache, was on the execution plan, but I saw it set to true for the first execution of the query. Out comes DBCC FREEPROCCACHE()… nope, still retrieving it from cache, whatever that means. OK, how about a CHECKPOINT, DBCC DROPCLEANBUFFERS(), DBCC FREEPROCCACHE()… no change. More work is needed to understand this one (and yes, before you ask, I looked it up in Books Online. Nothing).

And at the bottom is some of what I was told is coming, new warnings about things that can affect plans. In this case, a conversion that’s occurring within a calculated column that will prevent good cardinality estimates, possibly leading to scans. We’re just retrieving the data here, so it doesn’t affect us, but it could. This is also excellent information that is going to be extremely useful. The only shortcoming with this one is, that there’s no indication where the problem is occurring. I had to do a quick search in the XML to identify which operation had the questionable column.

I’m pretty excited about these little additions to the SELECT operator.