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:
- 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.
- 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.”