This video discusses the concept of blocking in SQL Server, what it is, how it occurs, and how to troubleshoot it. Blocking is an unavoidable characteristic of any relational database management system (RDBMS) with log-based concurrency control. It occurs when one process acquires a lock on a resource and another process wants to access the same resource, causing the second process to wait until the first one releases the lock. This situation is referred to as blocking. Blocking can be good or bad, as it's a normal part of SQL Server, but it can also create problems if it causes performance degradation due to excessive blocking or blocking chains.
The video provides four methods to find and troubleshoot blocking in SQL Server:
1. Standard Reports (Activity or Blocking Transaction under Reports): This shows all blocking transaction information.
2. Activity Monitor: This provides additional information like login name, command, and wait type.
3. System Stored Procedures (SP_WHO, SP_WHO2): These provide information about current users, sessions, and processes, including which sessions are blocking others.
4. Dynamic Management Views (DMVs) and sys.process: These provide additional information about blocking sessions.
5. Extended Events: This creates an event session that reports the blocking process.
The video also discusses the steps to troubleshoot blocking:
1. Identify the main blocking session.
2. Find the query and transaction causing the blocking.
3. Understand and analyze why the blocking occurs for a prolonged period.
4. Resolve the blocking issue by redesigning the query and transaction.
The video concludes by emphasizing that killing a blocking session should be the last resort and should only be done after proper troubleshooting and confirmation from the application team.
1. The video discusses blocking in SQL Server.
2. Blocking is a state that starts when one process acquires a lock on a resource, and another process wants to lock the same resource.
3. Blocking is an unavoidable characteristic of any RDBMS with log-base concurrency.
4. Blocking can occur when a transaction is not yet committed or rolled back, meaning it's an open transaction.
5. Blocking can cause performance degradation if the initial process locks the resource for an extended period of time or does not release the resource at all.
6. Blocking chains can be created where one user is blocked by another, which can lead to a performance degradation.
7. Blocking in SQL Server is quite normal, but it can also create problems.
8. Blocking can be found and troubleshooted using standard reports, activity monitor, extended events, some Dynamic management views, and stored procedures.
9. Blocking can also be found using system stored procedures, DMVs, and Extended Event Sessions.
10. To troubleshoot blocking, the first step is to identify the main blocking session.
11. The second step in troubleshooting blocking is to find the query and transaction causing the blocking.
12. The third step is to understand and analyze why the blocking occurs for a prolonged period.
13. The final step is to resolve the blocking issue by redesigning the query and transaction to optimize it.
14. The store procedure SP_WHO2 can be used to get more useful information for resolving blocking.
15. The SP_WHO2 stored procedure provides information such as duration, query running in the session, login name, blockchain, session ID, and status.
16. If necessary, blocking sessions can be killed, but this should be done after proper troubleshooting and confirmation from the application team.
17. Killing a blocking session is the last option to release the blocked sessions.