Will blocking cause timeout error

Question

When i checking in sys.sysprocesses in SQL 2008R2 more number of blocking sessions are there. Most of them are insert,update,delete statements and wait types are locks. Whether this will cause time out issues??? any one can confirm whether this will cause performance issue?

Answer 1

Hi @kumar-9444 ,

Most of them are insert,update,delete statements and wait types are locks.Whether this will cause time out issues???

Yes. It will. If there are many lock types and blocked ones here.

... any one can confirm whether this will cause performance issue?

Yes. It will cause performance issue. You need to identify the casue of it.

Some can resolve itselft, some not.You can reference this : inf-understanding-and-resolving-sql-server-blocking-problems
or troubleshooting as next:

I.Is there any blocking on the database? Which connections are blocked? Which one blcock it?

[Check]: Is there any connection whose blocked field is not 0? If it is not 0, and it is not -2, -3, -4, then it is blocked by the connection whose SPID is equal to the value of this field. Generally speaking, the blocked field of the blocking source will be Null. If it is not equal to 0, it means that it is also blocked by others, and you must continue to look for the connection that blocked it.

II. When did it start

[Check]: Just look at waittime here to know the time when this blocking occurred. If you run multiple queries, each blocked spid waittime is very short, it means that the duration of a single block is not very long, and the block may not be very serious (of course this will also affect the response speed).

III. On which database

[Check]: Generally check dbid. After getting the dbid, you can run the following query to get the name of the database.

SELECT name, dbid FROM master.sys.sysdatabases  

IV. Which form or forms are blocked? Which resources

[Check]: You can run sp_lock to find the lock resource whose status is wait in the result set. Or run the following query directly to get the same effect.

 SELECT CONVERT(smallint, req_spid) AS spid,  
     rsc_dbid AS dbid,  
     rsc_objid AS ObjId,  
     rsc_indid AS IndId,  
     SUBSTRING(v.name, 1, 4) AS Type,  
     SUBSTRING(rsc_text, 1, 32) AS Resource,  
     SUBSTRING(u.name, 1, 8) AS Mode,  
     SUBSTRING(x.name, 1, 5) AS Status  
     FROM master.dbo.syslockinfo,  
     master.dbo.spt_values v,  
     master.dbo.spt_values x,  
     master.dbo.spt_values u  
     WHERE master.dbo.syslockinfo.rsc_type = v.number  
     AND v.type = 'LR'  
     AND master.dbo.syslockinfo.req_status = x.number  
     AND x.type = 'LS'  
     AND master.dbo.syslockinfo.req_mode + 1 = u.number  
     AND u.type = 'L'  
     AND SUBSTRING(x.name, 1, 5) = 'WAIT'  
     ORDER BY spid   

V. From which applications are connections related to blocking come from

[Check]: The result of sysprocesses provides a lot of information to tell the user from which client a connection is connected.

VI. Why does blocking happen

You can also reference this to find the cause: how-to-identify-blocking-in-sql-server

More information: troubleshooting-blocking, tracing-a-sql-server-deadlock

BR,
Mia

---

If the answer is helpful, please click "Accept Answer" and upvote it.

Answer 2

Direct answer to your question a blocking or if blocking goes for long it can cause both timeout issue in application depending on how it is configured and also performance issue. To handle blocking and timeout issues application should be configured accordingly with proper error handling.

Answer 3

Hi @kumar-9444 ,

Is the reply helpful?

BR,
Mia

---

If the answer is helpful, please click "Accept Answer" and upvote it.