Improved tempdb spill diagnostics in Showplan XML schema in SQL Server 2012 and 2014


Microsoft distributes Microsoft SQL Server 2012 and 2014 fixes as one downloadable file. Because the fixes are cumulative, each new release contains all the hotfixes and all the security fixes that were included with the previous SQL Server 2012 fix release.
Summary
This update makes the following changes to the Showplan XML schema:
  • Adds the following new attribute to the SpillToTempDbType complex type. This is referenced by the SpillToTempDb element of the WarningsType complex type.

    AttributeTypeDescription
    SpilledThreadCountXsd:unisignedLongTotal amount of spilled threads.

  • Adds the following new complex types to the XML schema.

    Complex Type
    SortSpillDetailsType
    HashSpillDetailsType

  • Adds the following new elements to the WarningsType complex type:

    ElementTypeDescription
    SortSpillDetailsShp:SortSpillDetailsTypeReferences to the SortSpillDetailsType complex type.
    HashSpillDetailsShp:SortSpillDetailsTypeReferences to the HashSpillDetailsType complex type.

    SortSpillDetailsType This is referenced by the SortSpillDetails element contains the following attributes.

    Complex TypeTypeDescription
    GrantedMemoryKbXsd:unsignedLongTotal amount of memory actually granted in kilobytes.
    UsedMemoryKbXsd:unsignedLongPhysical memory used at this moment in kilobytes.
    WritesToTempDbXsd:unisignedLongInclude both worktable and workfile IO as one counter.
    ReadsFromTempDbXsd:unisignedLongInclude both worktable and workfile IO as one counter.

    • SortSpillDetailsType: This is referenced by the SortSpillDetails element, contains the following attributes.
    • HashSpillDetailsType: This is referenced by the HashSpillDetails element and contains the following attributes.

      The newly added HashSpillDetailsType complex type that's referenced by the HashSpillDetails element contains the following attributes:

      Complex TypeTypeDescription
      GrantedMemoryKbXsd:unsignedLongTotal amount of memory actually granted in kilobytes.
      UsedMemoryKbXsd:unsignedLongPhysical memory used at this moment in kilobytes.
      WritesToTempDbXsd:unisignedLongInclude both worktable and workfile IO as one counter.
      ReadsFromTempDbXsd:unisignedLongInclude both worktable and workfile IO as one counter.
Note After you apply this update, the Showplan XML schema is available with the SQL Server installation files at the following location:

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

Resolution
This functionality was fist introduced in the following Service Packs for SQL Server. 
For more information about SQL Server 2012 Service Pack 3 (SP3), see bugs that are fixed in SQL Server 2012 Service Pack 3.

About Service packs for SQL Server

Service packs are cumulative. Each new service pack contains all the fixes that are in previous service packs, together with any new fixes. Our recommendation is to apply the latest service pack and the latest cumulative update for that service pack. You do not have to install a previous service pack before you install the latest service pack. Use Table 1 in the following article for finding more information about the latest service pack and latest cumulative update.

How to determine the version, edition and update level of SQL Server and its components
More information
The memory grant information and the storage I/O information that was mentioned earlier is also available in the Warning section of the tooltip for the Sort operation or the Hash operation in the graphical execution plan output in SQL Server Management Studio. Additionally, the same information is also available in the Warning section of the Properties window of the Sort operation or the Hash operation.

Note This information is available only when the SET STATISTICS XML option is set ON.

SortHash Match
References
Learn about the terminology that Microsoft uses to describe software updates.
Properties

Article ID: 3107400 - Last Review: 07/11/2016 17:11:00 - Revision: 2.0

Microsoft SQL Server 2012 Analysis Services, Microsoft SQL Server 2012 Developer, Microsoft SQL Server 2012 Enterprise, Microsoft SQL Server 2012 Standard, Microsoft SQL Server 2012 Web, Microsoft SQL Server 2014 Developer, Microsoft SQL Server 2014 Enterprise, Microsoft SQL Server 2014 Enterprise Core, Microsoft SQL Server 2014 Express, Microsoft SQL Server 2014 Standard

  • kbqfe kbsurveynew kbfix kbexpertiseinter KB3107400
Feedback