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.
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.
SentryOne Performance Analysis retains Top SQL, Blocking SQL, and deadlock data for 15 days by default, making investigations of historical SQL blocking easier 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, SentryOne 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 SentryOne Event Calendar side-by-side with other events. SentryOne offers incredible functionality to help you resolve SQL Server blocking issues and can help you get to the root of problems quickly and efficiently.
Start resolving SQL Server blocking with a 15-day no obligation trial of SentryOne today.
“This utility has transcended our core monitoring and alerting functionality at the shop. The customization and reportability
- Chris Yates, The SQL Professor