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.

Sort Hash Match

References

Learn about the terminology that Microsoft uses to describe software updates.
Savybės

Straipsnio ID: 3107400 – Paskutinė peržiūra: 2016-07-11 – Peržiūra: 1

Atsiliepimai