Blocking in SQL Server
In this article I am trying to explain the Blocking.
What is it
Blocking in SQL Server is a scenario where one connection to SQL Server locks one or more records, and a second connection to SQL Server requires a conflicting lock type on the record or records locked by the first connection. This causes the second connection to wait until the first connection releases its locks. By default, a connection will wait an unlimited amount of time for the blocking lock to go away.
A certain amount of blocking is normal and unavoidable. But too much blocking can cause connections (representing applications and users) to wait extensive periods of time, hurting overall SQL Server performance.
Type of Blocking
1. Short-time Blocking - average duration less than three seconds. They do not create problems.
2. Mid-time Blocking - average duration ranges from 3 seconds to a maximum of 10 seconds. Usually do not create problems
3. Long-time Blocking - average duration more than 10 seconds. Require attention.
Monitoring Blocking
Sysprocesses is the system table that can reveal the most clues about why a process is blocking others. The sysprocesses table holds information about processes running on Microsoft SQL Server. These processes can be client processes or system processes. Sysprocesses is stored in the master database.
SELECT 'Server : ' + rtrim(@@servername) + ', SPID=' +
rtrim(convert(char(6),p.spid))+', STATUS='+
rtrim(convert(char(15),IsNull(p.status, ' ')))+' , LOGIN NAME='+
rtrim(convert(char(25),IsNull(p.loginame, ' ')))+' ,HOST NAME='+
rtrim(convert(char(15),IsNull(p.hostname, ' ')))+' ,PROGRAM NAME='+
rtrim(convert(char(30),IsNull(p.program_name, ' ')))+' ,COMMAND='+
rtrim(convert(char(25),IsNull(p.cmd, ' ')))+', LOGIN TIME='+
rtrim(convert(char(19),IsNull(p.login_time,'1900-01-01'),121))+' ,LAST BATCH='+
rtrim(convert(char(19),IsNull(p.last_batch,'1900-01-01'),121))
FROM master..sysprocesses p
GO
To avoided unnatural blocking
1. Keep the transactions as short as possible. We must take care the transaction time by optimize Transact-SQL code, optimize indexes, break long transactions into multiple, smaller transactions, avoiding cursors, etc.
2. In any cases if the running query is cancelled it is important that the code also roll back the transaction. If this does not happen, locks held by the query will not be released, which means blocking can occur.
3. Avoid INSER, UPDATE or DELETE large numbers of records in a single transaction.
4. Add query time-out for each of the query.
5. Add lock time-out for each of the query.
Using SET LOCK_TIMEOUT
Specifies the number of milliseconds a statement waits for a lock to be released. This is the number of milliseconds that will pass before Microsoft SQL Server returns a locking error. A value of -1 (default) indicates no time-out period (that is, wait forever). When a wait for a lock exceeds the time-out value, an error is returned. The value of "0" means not to wait and return a message as soon as a lock is encountered.
SET LOCK_TIMEOUT 1800
GO
BLOCKING IN SQL SERVER
Let say one session has placed a lock which will not allow other sessions to access the same resource (table or row or page). The other session query waits for unlimited time till the previous session releases the lock. This scenario is called blocking.
When a transaction is trying to place any type of lock (shared, exclusive, update) on a resource on which already exclusive lock is placed, then it results into blocking.
Certain amount of blocking is normal and unavoidable but regular blocking or blocking which causes the applications or users to wait for unlimited times should be consider. We have to minimize blocking in order to reduce the performance bottlenecks. Blocking causes timeout problems for applications.
Example
In the above figure check that session1 has placed the exclusive lock on the row with pid=1 and second session is trying to access the same row. The second session is currently executing and waiting for the first session to release the lock. Hence session2 is blocked by session1.
SELECT command causes any blocking?
When select command is executed it places Shared Locks and restricts other queries to perform manipulations till the lock is released. But this lock does not cause too much blocking. But locks placed by insert or update or delete as we have seen in the previous example causes excessive blocking.
Scenarios where sessions experience with excessive blocking
1. Long running queries which places Shared or Exclusive locks.
2. Queries which are cancelled and not rollback.
3. In some cases, lock escalation also one reason for blocking because SQL has escalated locks, and so locked the entire table.
4. When the process of index rebuilding is running.
5. Snapshot agent is generating fresh snapshot in case of Snapshot of transactional replication.
FAQ: - While tracing server activities using profiler, any blocking occurs?
While using tuning template in profiler causes blocking.
Monitoring blocking
SQL Server supports stored procedures and DMVs (Dynamic Management Views) to monitor and troubleshoot blocking issues. Using DMVs as well as event notifications, and the blocked_
process_report and lock_escalation events, we can automate notification when a
blocking event occurs.
To find blocking we can use sp_who or sp_who2.
In the above picture, we can check that session 53 is blocking session 55.
Alternatively we can use sysprocesses of master database.
Steps to reduce blocking
1. Keep the workload small as possible
a. Avoid INSERTing, UPDATEing, or DELETEing large numbers of records in a single transaction.
2. Ensure that your tables have appropriate indexes
a. For the above indexes specially clustered we can set Online Index Processing to true.
3. Use NOLOCK hint or read uncommitted isolation level
a. We can make use of NOLOCK option to read the rows and provide more concurrency.
Using SNAPSHOT ISOLATION LEVEL
