LOTS of New Goodies in SQL Server Denali Query Plans

After the last post on new warnings in SQL Server Denali Showplan, just for the fun of it, I had a look at the XML version of the showplan. And saw some fairly amazing new things I hadn't noticed before.

Everyone's been looking into the new "Batch Execution Mode" with the new ColumnStore index feature. The the new "WindowSpool" operator for use the new Windowing enhancements (listed under "Enhancements to the OVER clause" in BOL's What's New). But somehow I'd missed these (raw XML included even though these do show up in the Query Plan Properties Windows when using the appropriate iterator)

<QueryPlan DegreeOfParallelism="0" NonParallelPlanReason="MaxDOPSetToOne" MemoryGrant="1344" CachedPlanSize="24" CompileTime="2" CompileCPU="2" CompileMemory="264">
<MemoryGrantInfo SerialRequiredMemory="512" SerialDesiredMemory="1344" RequiredMemory="512" DesiredMemory="1344" RequestedMemory="1344" GrantWaitTime="0" GrantedMemory="1344" MaxUsedMemory="1344" />
<OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="39308" EstimatedPagesCached="3416" EstimatedAvailableDegreeOfParallelism="1" />

Optimizer Hardware Dependent Properties? Really? I'm sure I'd never seen that before. Nor thought that SQL Server even considered the "EstimatedPagesCached" when creating a plan. NonParallelPlanReason would also be a big hit, perhaps. In fact, I do want to see why some of my spatial query plans don't use parallelism when spatial indexes are involved. Just what I needed (and no, it wasn't that I set MaxDOP to 1).

Intruiged, I took a look at the XSD schema for Showplan in Denali (did you know there is an XSD schema for Showplan? It's under the SQL Server directory under "…110\Tools\Binn\Schemas"). And did a quick diff with the XSD schema for Showplan in SQL Server 2008 R2. All the new elements and attributes I've mentioned here (and more) are there in the new schema (which retains the same XML targetNamespace but does add an attribute "version=1.2". Nice. There's too many interesting items to list here, and some of them don't seem to be implemented, at least in the plans I'm looking at. Some of these look like they're for special hardware configurations.

I'm impressed by just those two new elements in the XML above. And the new "NonParallelPlanReason" attribute. Maybe I'm just easy to please…

@bobbeauch

Other articles

Over 1000 XEvents in SQL Server 2016 CTP2. Here are the new ones.

Extended events has firmly established itself as the premier diagnostic feature in SQL Server and SQL Server 2016 brings along more events to correspond to

Taking the Azure SQL Database row-level security preview for a spin

The security announcements around Azure SQL Database keep coming. Auditing was implemented a few months ago, and today it was followed by a preview of

Azure SQL Database V12 Preview – Spatial Fully Functional

Yesterday’s blog post about Azure SQL Database V12 mentioned that one of the features I was particularly interested in seeing/testing were the spatial features. Interestingly,

Not a “me-too announcement” blog on Azure SQL Database V12 preview

In general, I usually hate “me too” announcement blog posts. Over the years, I’ve considered it less than useful to simply repeat “Product XXX released

AzureML: What components are used by the sample experiments?

A few months ago, I embarked on a project to learn more about data mining, machine learning and, as a prerequisite, statistics. I was tired

Book Review: Chris Webb’s “Power Query for Power BI and Excel”

I’ve been experimenting with Power Query and the rest of the Power BI suite in Excel (desktop edition) since it was originally released. But, like

Imagine feeling confident enough to handle whatever your database throws at you.

With training and consulting from SQLskills, you’ll be able to solve big problems, elevate your team’s capacity, and take control of your data career.