Whats new in SQL Server 2012 Execution Plans

Execution plans show you what’s going on behind the scenes in SQL Server. They can provide you with a wealth of information on how your queries are being executed by
SQL Server, including:

• Which indexes are being used, and where no indexes are being used at all.
• How the data is being retrieved, and joined, from the tables defi ned in your query.
• How aggregations in GROUP BY queries are put together.
• The anticipated load, and the estimated cost, that all these operations place upon
the system.

All this information makes the execution plan a vitally important part for database administrators, developers, report writers, and pretty much anyone who writes TSQL to access data in a SQL Server database.

In SQL Server 2012, there are new operators such as Window Spools (for the new windowing functions), Offsets/Sequences and ColumnStore Index Scans (including whether execution mode was row or batch). There is also information about hint changes first introduced in SQL Server 2008 R2 SP1 (FORCESCAN, and FORCESEEK with columns).

In SQL Server 2012, there are also new warnings at the operator level, such as spills to tempdb (from sort operations or aggregates), and more details about implicit conversion warnings (for example, whether a conversion affects cardinality or seek choice). At the statement level, we can see information about things like whether the plan was retrieved from cache. And at the plan level, we can see many new properties, including the reason a plan was not parallel (for example, MAXDOP), detailed memory grant information (including, in some cases, how much memory was requested and how long the grant took), and deeper information about parallel queries (for example, thread usage across NUMA nodes).

To get an idea of all the information that is coming to a ShowPlan near you in SQL Server 2012, and even to get some insight into future features that have yet to be announced, perform a file comparison between the latest XSD and the 2008/2008 R2 version. You can find the SQL Server 2012 XSD at the following location:

C:\Program Files (x86)\
Microsoft SQL Server\110\Tools\Binn\schemas\
sqlserver\2004\07\showplan\showplanxml.xsd

(For SQL Server 2008/R2, change 110 to 100.)