SQL Server is a powerful relational database management system used by businesses of all sizes. However, as your database grows, it is not uncommon to experience SQL blocking, which can significantly impact your database's performance. In this blog post, we will discuss what SQL blocking is, how to identify and analyse it, and how to resolve it to keep your database running smoothly.
What is SQL Blocking?
SQL blocking occurs when one query blocks another query from accessing a shared resource, such as a table or page. When a query is blocked, it can't proceed until the resource it needs is available, leading to delays and slowdowns in your database.
Identifying SQL Blocking:
The first step in resolving SQL blocking is identifying it.
SQL Server Management Studio (SSMS) provides several tools for identifying
blocking, such as the Activity Monitor and the Blocked Process Report.
There are number of ways to find out the details of the spid's involved in blocking
- sp_who2
- sys.sysprocesses
- sys.dm_exec_requests
- sys.dm_tran_locks and sys.dm_os_waiting_tasks
- SQL Server Management Studio Activity Monitor
- SQL Server Management Studio Reports
- SQL Server Profiler
- SQL Server Extended Events
in this post we are focusing on TSQL methods
sp_who2 is a system stored procedure that returns
information about the current SQL Server processes and sessions, including
details such as session ID, login name, database being used, and the command
being executed. It is commonly used for troubleshooting and identifying
blocking issues in a SQL Server instance.
Below is sample code and a screen shot
USE master
GO
EXEC sp_who2
GO
We can check if there is any server blocking by querying
sys.sysprocesses. If the results return no rows, this indicates that there is
no active blocking on the server.
Below is sample code and a screen shot
select * from sys.sysprocesses where blocked<>0 |
| blocked statements |
Identifying and resolving head blocking in SQL Server is
important because it can significantly impact database performance and user
experience. Head blocking occurs when a transaction is blocking other
transactions, causing them to wait in a queue for resources that are being held
by the blocking transaction.
select loginame, cpu, memusage, physical_io, *
from master..sysprocesses a
where exists ( select b.*
from master..sysprocesses b
where b.blocked > 0 and
b.blocked = a.spid ) and not
exists ( select b.*
from master..sysprocesses b
where b.blocked > 0 and
b.spid = a.spid )
order by spid
Analysing SQL Blocking:
Once you've identified blocking, you need to analyze it to determine the root cause. This may involve identifying the queries causing the blocking, checking indexes and statistics, or checking for lock escalation.
Resolving SQL Blocking:
There are several ways to resolve SQL blocking, depending on the cause. In some cases, killing the blocking process may be necessary, but this can have unintended consequences, such as rolling back transactions. Other methods for resolving SQL blocking include optimizing queries, using indexes, adjusting transaction isolation levels, and redistributing data.
Preventing SQL Blocking:
The best way to deal with SQL blocking is to prevent it from occurring in the first place. Strategies for preventing SQL blocking include optimizing queries, creating appropriate indexes, and setting transaction isolation levels appropriately.
Conclusion:
SQL blocking can be a frustrating issue that impacts your database's performance. However, by understanding how to identify, analyse, and resolve SQL blocking, you can keep your database running smoothly and ensure your users have a positive experience. Remember to also focus on prevention, so you can avoid SQL blocking in the future.