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

Hope you like it.

Posted by: MR. JOYDEEP DAS