Obscure changes in SQL Server 2022: ShowPlan

As a former technical product manager for Plan Explorer, I can't help but snoop around in what has changed in the XSD for showplan. Even though I am not the best person to actually analyze what those changes mean, and even though changes in XSD don't necessarily reflect changes the engine can produce right now – these usually lay the groundwork for engine changes that will happen later.

Play along at home

To get a quick look at how much showplan has changed, just run older and newer versions of the file showplanxml.xsd through your favorite diff tool.

The new version of the showplan XSD is placed on your machine when you install SQL Server Management Studio, which you can download here:

Download SQL Server Management Studio (SSMS)

Once installed, pull out showplanxml.xsd from the following folder (I copy it to c:\temp\ and rename it showplanxml_2022.xsd):

C:\Program Files (x86)\Microsoft SQL Server\160\Tools\
Binn\schemas\sqlserver\2004\07\showplan\

You can get older versions (say, SQL Server 2019) at:

schemas.microsoft.com/…/showplan/sql2019/showplanxml.xsd
schemas.microsoft.com/…/showplan/sql2017/showplanxml.xsd
schemas.microsoft.com/…/showplan/sql2016/showplanxml.xsd
…and so on… and if you get "Request is blocked" errors, try an incognito window.

You can also get an older one locally if you have an existing version of SSMS installed, e.g. check for:

SQL Server 2019
C:\Program Files (x86)\Microsoft SQL Server\150\Tools\
Binn\schemas\sqlserver\2004\07\showplan\

SQL Server 2017
C:\Program Files (x86)\Microsoft SQL Server\140\Tools\
Binn\schemas\sqlserver\2004\07\showplan\

SQL Server 2016
C:\Program Files (x86)\Microsoft SQL Server\130\Tools\
Binn\schemas\sqlserver\2004\07\showplan\

…and so on…

Just note that the further back you go, the more diffs you'll have to compare. I move that to c:\temp\ as well, and name it showplanxml_2019.xsd, for example. I back these up, too, so I can always go back and compare specific versions later, even if I've removed the version of SSMS they came with.

Let's run a diff!

The new showplan is 2,489 lines long; that's a heck of a lot of XML. Thankfully the changes are not too bad (the 2019 version was 2,415 lines). The diff map is to the right (click to view). I ignored a couple of places where NodeId changed from Optional to Required; other than that, here are the rest of the changes:

From lines 59-69 we have this new section that has to do with data virtualization and PolyBase:

<xsd:complexType name="ExternalDistributedComputationType">
 <xsd:annotation>
  <xsd:documentation>This is only found in the serialized 
     xml for Gen3 external distributed statements.
  </xsd:documentation>
 </xsd:annotation>
 <xsd:sequence>
  <xsd:choice minOccurs="0" maxOccurs="unbounded">
   <xsd:element name="StmtSimple" 
    type="shp:StmtSimpleType" />
  </xsd:choice>
 </xsd:sequence>
 <xsd:attribute name="EdcShowplanXml" 
  type="xsd:string" use="required" />
</xsd:complexType>

And then on line 76:

<xsd:element name="ExternalDistributedComputation" 
type="shp:ExternalDistributedComputationType" />

Line 95 shows a new attribute for BaseStmtInfoType:

<xsd:attribute name="QueryCompilationReplay" 
type="xsd:int" use="optional" />

On line 126 we see these new attributes for Query Store hints and ledger tables:

<xsd:attribute name="QueryStoreStatementHintId" 
type="xsd:unsignedLong" use="optional" />
<xsd:attribute name="QueryStoreStatementHintText" 
type="xsd:string" use="optional" />
<xsd:attribute name="QueryStoreStatementHintSource" 
type="xsd:string" use="optional" />
<xsd:attribute name="ContainsLedgerTables" 
type="xsd:boolean" use="optional" />

On line 138, an additional element for StmtSimpleType:

<xsd:element name="Dispatcher" 
type="shp:DispatcherType" minOccurs="0" maxOccurs="1" />

On line 260, there is a new bit of info for MemoryGrantFeedbackInfoType:

<xsd:enumeration value="Yes: Percentile Adjusting" />

Line 275 shows another Dispatcher under CursorPlanType:

<xsd:element name="Dispatcher" 
type="shp:DispatcherType" minOccurs="0" maxOccurs="1" />

Lines 400-401 show a couple of new ObjectType attributes:

<xsd:attribute name="OnlineInbuildIndex" 
type="xsd:int" use="optional" />
<xsd:attribute name="OnlineIndexBuildMappingIndex" 
type="xsd:int" use="optional" />

Line 568 shows the most interesting change to me: a new warning for columns with "stale" statistics. I tried producing this warning but either I don't know the magic ingredients or the engine won't produce it yet. Or maybe both.

<xsd:element name="ColumnsWithStaleStatistics"
type="shp:ColumnReferenceListType" minOccurs="0" maxOccurs="1" />

On line 767 there is a new section on "page servers," whatever those are, that will appear as additional data in RunTimeCountersPerThread:

<xsd:attribute name="ActualPageServerPushedPageIDs" 
type="xsd:unsignedLong" use="optional" />
<xsd:attribute name="ActualPageServerRowsReturned" 
type="xsd:unsignedLong" use="optional" />
<xsd:attribute name="ActualPageServerRowsRead" 
type="xsd:unsignedLong" use="optional" />
<xsd:attribute name="ActualPageServerPushedReads" 
type="xsd:unsignedLong" use="optional" />

A new complex type on line 843:

<xsd:complexType name="OptimizationReplayType">
  <xsd:attribute name="Script" type="xsd:string" use="required" />
</xsd:complexType>

Some additional documentation for runtime information on line 927:

ExclusiveProfileTimeActive:  true if the actual elapsed time
(ActualElapsedms attribute) and the actual CPU time 
(ActualCPUms attribute) represent the time interval spent
exclusively within the relational iterator.

Another element related to OptimizationReplay on line 943:

<xsd:element name="OptimizationReplay" 
type="shp:OptimizationReplayType" minOccurs="0" maxOccurs="1" />

Line 969 shows a few new attributes relating to parameter sensitive plan optimization:

<xsd:attribute name="QueryVariantID" 
type="xsd:int" use="optional" />
<xsd:attribute name="DispatcherPlanHandle" 
type="xsd:string" use="optional" />
<xsd:attribute name="ExclusiveProfileTimeActive" 
type="xsd:boolean" use="optional" />

Line 1132 shows a couple of new HyperScale attributes for RelOpType:

<xsd:attribute name="HyperScaleOptimizedQueryProcessing" 
type="xsd:string" use="optional" />
<xsd:attribute name="HyperScaleOptimizedQueryProcessingUnusedReason" 
type="xsd:string" use="optional" />

And on line 2066, there's a new section on parameter sensitive plan optimization:

<xsd:complexType name="DispatcherType">
  <xsd:annotation>
    <xsd:documentation>This is the dispatcher expression in XML
     format for the parameter sensitive plan.</xsd:documentation>
  </xsd:annotation>
  <xsd:sequence>
    <xsd:element name="ParameterSensitivePredicate" 
      type="shp:ParameterSensitivePredicateType" 
      minOccurs="1" maxOccurs="3" />
  </xsd:sequence>
</xsd:complexType>
<xsd:complexType name="ParameterSensitivePredicateType">
  <xsd:annotation>
    <xsd:documentation>This contains information related to the
        parameter sensitive predicate:
        Boundaries used to determine different ranges;
        Statistics information used to compute the boundaries;
        Predicate details.
    </xsd:documentation>
  </xsd:annotation>
  <xsd:sequence>
    <xsd:element name="StatisticsInfo" 
     type="shp:StatsInfoType" minOccurs="1" maxOccurs="unbounded" />
    <xsd:element name="Predicate" 
     type="shp:ScalarExpressionType" minOccurs="1" maxOccurs="1" />
  </xsd:sequence>
  <xsd:attribute name="LowBoundary" 
   type="xsd:double" use="required" />
  <xsd:attribute name="HighBoundary" 
   type="xsd:double" use="required" />
</xsd:complexType>

By: Aaron Bertrand

I am a passionate technologist with industry experience dating back to Classic ASP and SQL Server 6.5. I am a long-time Microsoft MVP, write at Simple Talk, SQLPerformance, and MSSQLTips, and have had the honor of speaking at more conferences than I can remember. In non-tech life, I am a husband, a father of two, a huge hockey and football fan, and my pronouns are he/him.