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

Summary

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

• The exchange_spillxEvent 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:

  • waiterType–Consumer, Producer, Coordinator.

  • tid–unique identifier for each thread inside a parallel plan.

  • ownerActivity–What resource owner(s) did previously. Can be “receivedData”, “sentData”, “notYetOpened”, or “opened.”

  • waiterActivity–What resource requestor is trying to do. Can be “tryToSendData”, “needMoreData”, “waitForAllConsumersToBeReady”, “waitForAllOwnersToOpen”, or “waitForAllownersToClose.”

  • merging–whether waiter is participating in merging data or not.

  • spilling–whether waiter is spilling data or not.

  • waitingToClose–whether 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:

  • logicalOperatorType–logical operator type that matches the one at showplan xml.

  • physicalOperatorType–physical operator type that matches the one at showplan xml.

  • WaitType and synchronizingAt–Two 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.

  • ownerActivity–always “NotYetArrived.”

  • waiterActivity–always “Arrived.”

More information

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

About SQL Server 2017 builds

Each new build for SQL Server 2017 contains all the hotfixes and security fixes that were in the previous build. We recommend that you install the latest build for SQL Server 2017.

References

Learn about the terminology Microsoft uses to describe software updates.