Better intra-query parallelism deadlocks troubleshooting in SQL Server 2017 and 2016

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

Summary


This SQL Server update improves troubleshooting parallelism-related (exchange) deadlocks by adding the following enhancements:

  • The exchange_spill xEvent reports how much data parallelism operators spill per each thread, in the event field name worktable_physical_writes. Each thread may report an exchange_spill xEvent multiple times.
  • The DMVs sys.dm_exec_query_stats, sys.dm_exec_procedure_stats and sys.dm_exec_trigger_stats now include the data that is spilled by parallelism operators, in the columns total_spills, last_spills, max_spills, and min_spills.
  • A showplan warning is reported at run time if there are parallelism spills. This warning is rendered in a showplan xml attribute (<ExchangeSpillDetails WritesToTempDb=”spill_amount” />).
  • The XML deadlock graph is improved for parallelism deadlock scenarios. More attributes are added to the exchangeEvent resource in:
    • waiterTypeConsumer, Producer, Coordinator.
    • tidunique identifier for each thread inside a parallel plan.
    • ownerActivityWhat resource owner(s) did previously. Can be “receivedData”, “sentData”, “notYetOpened”, or “opened.”
    • waiterActivityWhat resource requestor is trying to do. Can be “tryToSendData”, “needMoreData”, “waitForAllConsumersToBeReady”, “waitForAllOwnersToOpen”, or “waitForAllownersToClose.”
    • mergingwhether waiter is participating in merging data or not.
    • spillingwhether waiter is spilling data or not.
    • waitingToClosewhether waiter is waiting for consumer to close pipe.
  • The XML deadlock graph is improved for deadlocks involving batch-mode operators. More attributes are added to the SyncPoint resource in:
    • logicalOperatorTypelogical operator type that matches the one at showplan xml.
    • physicalOperatorTypephysical operator type that matches the one at showplan xml.
    • WaitType and synchronizingAtTwo attributes can collectively identify point at which waiters and owners  synchronize.

      Notes
      • The WaitType and synchronizingAt attribute values are internal. But they can be used to help Microsoft Support troubleshoot an issue.
      • The synchronizingAt attribute is added for batch-mode sort, hash join, and hash aggregation operators.
    • ownerActivityalways “NotYetArrived.”
    • waiterActivityalways “Arrived.”

More information


This update is included in Cumulative Update 5 for SQL Server 2017.

Service pack information for SQL Server 2016

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

       Service Pack 2 for SQL Server 2016

References


Learn about the terminology Microsoft uses to describe software updates.