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.

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

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.

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 Microsoft uses to describe software updates.

Need more help?

Expand your skills
Explore Training
Get new features first
Join Microsoft Insiders

Was this information helpful?

Thank you for your feedback!

Thank you for your feedback! It sounds like it might be helpful to connect you to one of our Office support agents.

×