Improve tempdb spill diagnostics in DMV and Extended Events in SQL Server 2017 and SQL Server 2016 SP2

Applies to: SQL Server 2017 DeveloperSQL Server 2017 EnterpriseSQL Server 2017 Enterprise Core More

Details


This update improves the tempdb spill diagnostics by making the following changes in SQL Server 2017 and Microsoft SQL Server 2016 Service Pack 2 (SP2):

  • Adds the following columns to the sys.dm_exec_query_stats, sys.dm_exec_procedure_stats, and sys.dm_exec_trigger_stats DMVs.
Column Type Description
total_spills bigint Total number of pages spilled by execution of this plan since it was compiled.
last_spills bigint Number of pages spilled the last time the plan was executed
min_spills bigint Minimum number of pages that this plan has ever spilled during a single execution
max_spills bigint Maximum number of pages that this plan has ever spilled during a single execution
  • Adds the following counters to the sql_statement_completed, sp_statement_completed, and sql_batch_completed Extended Events.
XEvent Column Type Description
sql_statement_completed  spills bigint The number of pages that were written while spilling data by the statement
sp_statement_completed  spills bigint The number of pages that were written while spilling data by the stored procedure
sql_batch_completed spills bigint The number of pages written while spilling data by the batch.

Resolution


Cumulative Update Information for SQL Server:

This update is included in the following cumulative update for SQL Server:

       Cumulative Update 3 for SQL Server 2017

Service pack information for SQL Server 2016

This update is introduced in the following service pack for SQL Server:

       Service Pack 2 for SQL Server 2016

References


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