Improve tempdb spill diagnostics by using Extended Events 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 and 2014 fix release.
Summary
This update adds a new extended event hash_spill_details. This new extended event contains the following columns (event fields).

ColumnTypeDescription
query_operation_node_idUInt(32)Identifies the node ID of the operation that causes the hash spill
thread_idUInt(32)Identifies worker thread ID which matches to showplan thread ID
dopUInt(32)Degree of parallelism
granted_memory_kbUInt(64)Granted memory in KB
used_memory_kbUInt(64)Used memory in KB
workfile_physical_readsUInt(64)Number of pages read from workfile
workfile_physical_writesUInt(64)Number of pages written to workfile
worktable_physical_readsUInt(64)Number of pages read from worktable
worktable_physical_writesUInt(64)Number of pages written to worktable
actual_row_countUInt(64)Actual number of processed rows


This update also adds the following columns (event fields) to the following existing extended events.

sort_warning

ColumnTypeDescription
thread_idUInt32Identifies worker thread ID which matches to showplan thread ID
dopUInt32Degree of parallelism.
granted_memory_kbUInt64Granted memory in KB
used_memory_kbUInt64Used memory in KB
worktable_physical_readsUInt64Number of pages read from worktable
worktable_physical_writesUInt64Number of pages written to worktable
actual_row_countUInt64Actual number of sorted rows

hash_warning

ColumnTypeDescription
thread_idUInt32Identifies worker thread ID that matches the showplan thread ID
dopUInt32Degree of parallelism
granted_memory_kbUInt64Granted memory in KB
used_memory_kbUInt64Used memory in KB
workfile_physical_writesUInt64Number of pages written to workfile
worktable_physical_writesUInt64Number of pages written to worktable
actual_row_countUInt64Actual number of processed rows

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
References
Learn about the terminology that Microsoft uses to describe software updates.
Properties

Article ID: 3107172 - Last Review: 07/11/2016 17:04: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 Standard

  • kbqfe kbsurveynew kbfix kbexpertiseinter KB3107172
Feedback