First and foremost, what is a Deadlock? A Deadlock occurs when two or more transactions block each other’s progress because one transaction has a lock on a database object the others need access to. This creates a hard stop on any sort of progress because each process can wait an indefinite amount of time for the locked object. SQL Server steps in to break the stalemate by killing one or more of the blocked process. The unlucky processes that were killed have to start over from scratch.
Most DBAs struggle trying to locate a deadlock (or try to find out if a deadlock is even what happened in the first place) because it's so time consuming. Often, by the time a deadlock is located, a few more might have occurred, compounding the problem. Even when you figure out a deadlock occurred, it’s nearly impossible to fix it without knowing what’s been blocked, the nature of the lock type requested by each process, and what sessions were involved.
Features like deadlock analysis are typically only seen in expensive“enterprise-level” monitoring tools. But our developers at Spotlight Cloud understand sometimes DBAs aren’t monitoring hundreds of SQL Servers – sometimes, it’s only 1 or 2. That’s why we are so excited to announce that deadlock analysis is now included as a standard feature in our hosted monitoring tool, Spotlight Cloud Professional. Here’s how it works.
With Spotlight Cloud SQL Server Deadlock Analysis, you’ll be able to access all the performance data required to identify and resolve deadlocks and improve your database performance.
The Deadlock Analysis dashboard in Spotlight Cloud shows a complete view of deadlocks in your SQL Server environment. Here, you can view all the processes and resources impacted by the deadlock in a single screen, see a summary of how many deadlocks occurred within a specific time frame, the time each process lost as a result of the deadlock, the associated SQL, and which processes became victims of the SQL Server deadlock resolution.
In the Alarms overview, you can see all deadlock related alarms. If you click on the alarm, you see a summary of how many deadlocks occurred, the number of sessions sacrificed as victims, and the overall time lost due to deadlock resolution. In addition to all of the information listed above, you will see the sessions, databases and objects most impacted.
Spotlight Cloud Deadlock Analysis allows you to view sessions and programs that were running during the incident of a deadlock. In the dashboard, you’ll see the exact SQL text that was executed by that particular program. When you drill into that SQL, you can identify why it was deadlocked and what else was trying to grab it at that time. You can also filter by lock type – row, key, page, SPID, etc. With the type of lock, the object that was involved, the statement text that was involved, and the sessions, you’ll be able to piece together the deadlock and begin resolving.
With Spotlight Cloud’s Deadlock Analysis, you can…
- View/edit your deadlock timeline and throughput request per second in an easy-to-read table.
- Filter based on databases, programs, users, and objects so that you can identify the root cause of the killed processes. For example, if a particular object, perhaps a table in the database, was inaccessible due to the deadlock.
- Identify all sessions, the associated program and all victims associated with a particular deadlock.
- See a list of objects requested by each session and the lock type requested
- See the exact SQL text that was executed by a particular program.
So, instead of spending hours attempting to locate a deadlock, try a Spotlight Cloud Professional 30-day Trial to maximize your time and resources.