1

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?

  • The key here is how long the blocks typically last, the thresholds of the timeout values Tibor mentioned, and what's acceptable in your use cases. While blocking can be a common cause for contention, it's not unusual to occur and it's also contextual. If you see a lot of blocks but the runtime of the blocking queries are fast and your applications aren't showing any signs of contention, everything might be fine as is. If your applications are showing signs of contention, then that's a different story. – J.D. Oct 31 at 14:18
7

There are two type of timeouts.

One is when the client application (API) aborts the batch after a certain amount of time. Called "attention" 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.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.