Basics of Blocking Problems on SQL Server Instance
Blocking is one of the common and major issues noticed in most of the RDBMS products. This is mostly result of poor application design and improper SQL, OS and Hardware configurations. SQL Server uses locks as mechanism to avoid concurrency problems and achieve consistency when multiple transactions are trying to use the resource at same time. For example, if one transaction is performing an read operation on a row in a table, then SQL Server will hold a row level lock on the row to be read in that table so that no other transactions can modify or delete that row until the first transaction completes its operation, this way the data will be consistent through out the transaction.
There are different types of Locks in SQL Server like Shared locks (S), Update locks (U), Exclusive locks (X), Intent locks (I), Schema locks (Sch), Bulk Update locks (BU), Conversion locks, Key – Range locks, etc. Discussing about each of these locks is out of scope of this article. Also there are various Isolation Levels available in SQL Server which controls the behavior of locks like how long a lock is placed on a object and which locks are compatible with which other locks, etc.
It is common to see all transactions take certain locks on objects and mostly these locks are held for very short duration based on the queries being run in the transactions. However, due to various problems, sometimes the locks are held for longer duration in which case other transactions which would want to take non-compatible locks on the same object which is already locked will have to wait and this is called blocking.
So a blocking scenario occurs when there are two or more transactions, where one transaction holds a lock on a particular object and other transactions are trying to take non-compatible lock on the same object and thus gets blocked by the first transaction which already holds the locks. Example, by default a transaction performing an select operation on a table may block another transaction which is trying to perform an write operation on the same table until the first transaction completes.
When there is a blocking problem with the SQL Server instance, as part of the troubleshooting process the first thing to check is to identify which session is head blocker and which session is being blocked. There are various methods by which you can identify the existence of blocking in SQL Server instance. You can run a simple query like below which will list all sessions which are being blocked in the SQL Server instance.
Select * from sys.sysprocesses where blocked<>0
The result of the above query will list all sessions which are currently being blocked by other sessions, but head blocked will not be listed by this query. So, need to look into the column blocked which has the ID of the session that is blocking the request. If this column is NULL, the request is not blocked, or the session information of the blocking session is not available (or cannot be identified). Once we know which is the session which is causing the blocking, then check what that particular session is doing and what query it is running and try to tune the query so that it completes quickly thus leaving the locks quickly so that other sessions does not have to wait thus there wont be any blocking.
This is applicable on below versions of SQL Server
SQL Server 2005
SQL Server 2008 R2
SQL Server 2012
SQL Server 2014
Hope this was helpful.
In-Depth Blogs on SQL Server, Information about SQL Server Conferences and Events, SQL Server Frequently asked questions, SQL Server Trainings.