SQL Server Blocking and Deadlocks Views in the SQL Sentry Portal

Hi, I'm Tyler Benfield, a full stack engineer here at SQL Sentry. For the past two years, I've been part of a team working to make SQL Sentry’s powerful database performance monitoring capabilities available from a web browser with the SQL Sentry Portal. SQL Sentry Portal makes it easy to check to make sure your monitored instances are running smoothly no matter where you are.

Today, I have some exciting new SQL Sentry Portal capabilities to tell you about that I think will give you the upper hand on SQL Server performance, whether you are an application developer, DBA, or, like me, somewhere in between. For years, the SQL Sentry SQL Sentry rich client has included features to troubleshoot and resolve blocking and deadlocks; now, those features have been fully redesigned for the SQL Sentry Portal. Let's dive into the details!

Blocking

SQL Server blocks can occur when queries require the same resource but can't access it at the same time because it is locked. Locking and blocking is a perfectly normal part of a system that allows for concurrent processing. The problem is that, sometimes, the query holding the lock ends up holding it for too long and other queries must wait. Blocks can be complex with many queries waiting and more queries waiting on those queries. They can also be simple, involving only the blocking query and a single waiting query. Either way, database performance can suffer. Blocked queries must continue waiting on that locked resource before they can continue to run. Wouldn't it be great to have an at-a-glance view of these blocking situations with the level of detail you need to tune the problem out of existence?

The Blocking View in the SQL Sentry Portal

SQL Sentry Portal provides you with a time-driven list of blocks that have occurred in your database. You can even expand each block to view the block’s children, and its children’s children—the blocking “chain” if you will—to get a full picture of the impact the block had on performance. The Blocking view in SQL Sentry Portal shows you the query text for any of the queries involved in the block, with syntax highlighting to help you quickly identify and address the root cause.

Another unique feature available in the SQL Sentry Portal is the ability to switch between multiple versions of a blocking chain. If blocked SPIDs were added or removed, the version details will give you full insight into the dynamic blocking chain.

Deadlocks

SQL Server deadlocks occur when waiting on a block to resolve is not an option. The simplest form is when a query needs a resource that is locked by another query, but the second query needs a resource that is locked by the first query. SQL Server solves this problem by choosing a query to kill. This leads to a best-case user experience of the application retrying the query, which still delays the results for the user, or a worst-case experience of downtime or even a system outage. No one wants that, so SQL Sentry gives you the tools to track them down and handle the root cause.

The Deadlocks View in the SQL Sentry Portal

The SQL Sentry Portal not only shows you the deadlocks that have occurred in your database but also gives you a unique, easy to consume, graphical representation of them. Eliminating duplication and unrelated noise is key to helping you efficiently solve the problem. SQL Sentry does this for you in the deadlock display. You can hover over any process or resource, or dependency between them, to see only the items directly associated with that node. All the detailed information about a process or resource is easily accessible by clicking the node. Panning and zooming allow you to navigate the diagram for larger deadlocks with ease. We have even more improvements to the diagram coming soon that I think you will enjoy using as much as I enjoyed working on them, so stay tuned!

Wrapping Up

I sincerely hope you enjoy the new blocking and deadlocks functionality available in the SQL Sentry Portal. It makes my day to know that our work improves the lives of data professionals who have the same challenges, needs, and expectations as me. I would love to hear your feedback on SQL Sentry, so please leave any comments or questions you might have. Until next time!