Will Blocking cause timeout issue When I check in sys.sysprocesses in SQL 2008R2, a number of blocking sessions are there. Most of them are insert, update, and delete statements, with lock-related wait types.
Will this cause time out issues? Can anyone confirm whether this will cause time out / performance issues?
There are two type of timeouts.
One is when the client application (API) aborts the batch after a certain amount of time. Called &quot;attention&quot; in a SQL server trace. Default in many APIs is 30 seconds.
The other is when the client requests that SQL server abort the query with error 1222 after a certain time. SET LOCK_TIMEOUT. Default is indefinitely.
Yes, if a blocking lasts longer than any of above timeouts values, then you will get a timeout.
Do the user consider waiting for a long time (blocking) and perhaps even getting a timeout as bad performance. Yes, absolutely.