Dec 12 2011

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:

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):

2012

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.

11 Comments

  • By Dave Ballantyne, December 12, 2011 @ 9:39 am

    Hi Grant,

    there are quite a few more new warnings, ive been working on documenting them here

    http://sqlblogcasts.com/blogs/sqlandthelike/archive/tags/Plan+Warnings/default.aspx

    I really hope to see more of these in the future

  • By Grant Fritchey, December 12, 2011 @ 10:52 am

    There are a bunch of new operators too, but I haven’t even tried documenting anything yet. Nicely done on those posts. Thanks for sharing.

  • By Gail, December 12, 2011 @ 11:57 am

    Here’s one way to get ‘retrieved from cache’ false…

    OPTION (RECOMPILE)

    Needs much further investigation, but I wonder if that’s a way, in the plan to see some recompiles. It’s not all recompiles, a query off a temp table (with a stats-based recompile) showed true.

  • By Chris McGowan, December 12, 2011 @ 11:59 am

    Exciting stuff, I first got really into execution plans and the information they provide when I read “Inside the SQL Server Query Optimizer” by Benjamin Nevarez. I always have it close at hand for reference, I can’t wait for 2012!

  • By Grant Fritchey, December 12, 2011 @ 12:02 pm

    Excellent catch Gail. Thanks. Clearly some more experimentation. I found nothing in the documentation, so right now it’s up in the air.

    Chris, I think Benjamin’s books is great. That despite the fact that I was the technical editor for it.

  • By Gail, December 12, 2011 @ 12:03 pm

    Or, maybe to show the cases where the plan is never cached at all.

    Even with an empty plan cache, the execution engine technically gets the plan from cache since it gets inserted before execution.

  • By Richard, December 14, 2011 @ 11:35 am

    Is there a reason the cached plan size has gone from 64 bytes to 64KB for the same query?

  • By Grant Fritchey, December 14, 2011 @ 11:41 am

    I’d be willing to bet that’s just a glitch.

  • By Gail, December 14, 2011 @ 11:58 am

    Looks like that was a bug in SQL 2008 SSMS.

    I checked a query out on SQL 2008, in the properties of the select the cached plan size showed 64 bytes,

    I queried sys.dm_exec_cached_plans and for the same plan the value in the size_in_bytes column was 40960 for the compiled plan and 24576 for the parse tree – 64kb in total.

  • By Thomas LeBlanc, January 25, 2012 @ 10:35 am

    Excellent info, needed this for a new presentation.

    Thanks,
    Thomas

Other Links to this Post

  1. Changes to SQL Server 2012 Execution Plans | Home Of The Scary DBA | SQL Server Development | Scoop.it — December 13, 2011 @ 4:02 am

RSS feed for comments on this post. TrackBack URI

Leave a comment