SQL Server: Deadlock Analysis and Prevention
Learn how to analyze and prevent troublesome deadlocks that can occur in SQL Server, applicable for developers, DBAs, and anyone who uses SQL Server from SQL Server 2005 onwards
What you'll learn
Deadlocks can be very problematic when they occur, especially if you do not know how to figure out what's making the deadlocks occur so that you can take steps to prevent them. This course explains how the locking mechanism in SQL Server works and the circumstances that can lead to deadlocks occurring. The course then describes and demonstrates the various methods for detecting deadlocks in SQL Server and collecting information about the deadlocks so they can be analyzed, plus how to perform deadlock graph analysis using XML, Profiler, Management Studio and third-party tools. Common deadlock scenarios are explained and demonstrated, including lock escalation deadlocks, multi-victim deadlocks, reverse object order deadlocks, and more. The course concludes with how to handle deadlocks in Transact-SQL and ADO.NET code, including the implementation of custom retry logic. This course is perfect for those with no experience of deadlock analysis and those with some experience but who want to solidify their understanding of deadlocks and how to analyze and prevent them. The information in the course applies to all versions from SQL Server 2005 onwards.
Table of contents
- Introduction 2m
- Reverse Object Order Deadlocks 2m
- Demo: Reverse Object Order Deadlocks 4m
- Bookmark Lookup Deadlocks 3m
- Demo: Bookmark Lookup Deadlocks 3m
- Serializable Deadlocks 3m
- Demo: Serializable Deadlocks 3m
- Cascading Constraint Deadlocks 2m
- Lock Escalation Deadlocks 2m
- Demo: Lock Escalation Deadlocks 4m
- Memory Grant Deadlocks 3m
- Intra-Query Parallelism Deadlocks 2m
- Multi-Victim Deadlocks 3m
- Demo: Multi-Victim Deadlocks 5m
- Resolving Deadlocks 3m
- Demo: Resolving Deadlocks 7m
- Summary 2m
About the author
Jonathan Kehayias is a Principal Consultant with SQLskills. He is the youngest person ever to achieve the Microsoft Certified Master - SQL Server 2008 certification and has been a SQL Server MVP for many years. Jonathan is heavily involved in the SQL community; including being the top answerer on the MSDN Database Engine forum, writing deep technical content on his blog (plus his old blog, and answering questions as @SQLPoolBoy on Twitter. Jonathan is a performance tuning expert, both SQL Se... more