Diagnosing Blocking and Deadlocks with Spotlight on SQL Server

Quickly finding locking troubles

In a few minutes, you’ll know how to use Spotlight on SQL Server to find sessions and t-sql involved in blocking and deadlocking.  Both of these conditions can trigger email and Spotlight mobile client notifications, and both are revealed through the Blocked Processes indicator on the SQL Server dashboard.  Let’s look at blocking first:

Here we see an alarm indicating 3 blocked sessions.  By the way, the blue-green bar across the top of the dashboard is telling us that we’re seeing the appearance of the dashboard as of some time in the past, a functionality in Spotlight called Playback (more on that at https://www.quest.com/community/b/en/posts/exploring-your-sql-server-s-performance-history-with-spotlight-on-sql-server), but whether the dashboard is showing us the past or present state of our SQL Server, clicking on an alarmed indicator and then Diagnose will lead us to the underlying details:

The Diagnose button has led us to the Blocking detail page.  Here we can see a couple of blocking chains, and know the user, client machine, application and sql statement details. The rewind, skip and play button let us move the time indicated on the blue-green bar forward and backward, so we can see how the blocking event played out, including whether additional blocking occurred downstream of the blocked sessions seen here.

A great way to investigate the history of blocking is the Alarms by Time view, as seen below. This view is helpful because the blocking event is drawn out over a timeline, enabling you to see its duration at-a-glance.  You can right-click on a bar and click Diagnose to be taken to the above detail page.

So that’s it for blocking, so on to deadlocks. Deadlock alarms appear in the same Blocked Processes indicator on the SQL Server dashboard, as in the very first screenshot of this post.  From there, and here on Alarms by Time, you can click to reach the Diagnose button, which will guide you to the Deadlock drilldown:

This page will show you a list of Deadlock events (just one in this case), and clicking on the date/time will show the deadlock XML that Spotlight has captured from SQL Server.  XML is human-readable but rather than bothering with that, you can click the View graph button in the navigation ribbon to invoke SQL Server Management Studio to render a standard, interactive deadlock graph.

Thanks for taking the time to check out this post – all of my Quest Database Performance blog posts can be found here.