Understanding and Resolving SQL Server Blocking with SentryOne

Find out how Performance Advisor can help you capture, analyze and resolve SQL Server blocking, and make your job easier.

Start resolving SQL Server blocking with SentryOne Performance Advisor today with a 15-day no obligation trial.

Let's get started

SQL Server Blocking

“This utility has transcended our core monitoring and alerting functionality at the shop. The customization and report ability is second to none, and allows us an in-depth look enterprise wide at our systems.”

Chris Yates, Assistant Vice President | Database Administration Manager

What is SQL Server blocking?

Blocking is inevitable in any relational database platform that uses lock-based concurrency. In SQL Server, blocking can happen when one session, or SPID, holds locks on a particular resource, and a second SPID tries to obtain incompatible locks on the same resource. Usually, the first session's locks on the resource are held for a very short amount of time; however, when they are held for longer, the second SPID must wait. When these locks are finally released, the second SPID can then obtain its own particular locks on the resource in order to continue processing. Blocking is fairly typical when working with relational databases; it may happen many times throughout the day with little to no impact on performance, but when there are more blocks due to concurrency, or the blocks last longer, they can have a tremendous impact and cause performance issues.

Detecting SQL Blocking with SentryOne

SQL Server blocking doesn’t have to be difficult to track down and resolve. SentryOne offers block analysis that captures all SQL blocking details based on the configured blocking duration thresholds. You can view blocking chains either in real-time or historically. Information such as the executed statement, login, host, and database are all provided for every process involved. You can also see wait types as well as wait resources. View blocks right when they occur and kill blocking processes directly from the SentryOne Client with the click of a button.

Performance Advisor retains Top SQL, Blocking SQL, and deadlock data for 15 days by default, making investigations of historical SQL blocking simple by painting a clear picture of what has occurred. You can even configure actions to respond to various blocking SQL conditions. So whenever a particular SQL blocking condition occurs, you are alerted to the situation and a specified action is taken based on what your preferences are.

With the blocking SQL grid view, Performance Advisor displays blocks in a hierarchical view that shows the relationships of all blocking and blocked SPIDs in a SQL Server blocking chain. Color coded nodes displayed with the blocking statement let you know if a statement is still running, has completed, or if it is blocked. You can also view blocks in the Event Manager calendar side by side with other events. Performance Advisor offers incredible functionality to help you resolve SQL Server blocking issues and can help you get to the root of problems quickly and efficiently.

LOOKS LIKE YOUR BROWSER IS OUT OF DATE

For your convenience here are links to supported browsers:

Your browser could be incompatible with this website.
If you close this window and continue you may experience less than optimal behavior.

Don't show this again