samabdelhamid
asked on
MSSQL 2008 Lock Timeouts/sec counter - Clarity required
Hi All,
I am a performance and tuning tester, and have been for many years.
I require some clarity on an application that I am testing as we are unsure of the behavior of MSSQL.
Firstly. i wll add, that we have set this
So we should have No Lock timeouts...(which is not happening)
This counter Lock Timeouts/Sec, is returning anywhere between 40-60 lock timeouts per second. Which we believe is reasonably high.
The issue here is, we do not understand what happens when this occurs, or if its a problem we should be worried about.
It is clear, from the title, that this counter measures a thread that is locked, awaiting for access to a table/row etc, and it timed out waiting for such occurrence, however, what happens in this instance.
Does the application that is controlling the query fail, and then need to re-send the query (totally nothing to do with the DB, I know)
Does the query get re-tried ?
Any information that Microsoft has produced about this counter is not helping us to understand the real cuase/effect and if it is in fact serious.
If any MSSQL Dba's can provide some light for us that would be great.
Cheers
I am a performance and tuning tester, and have been for many years.
I require some clarity on an application that I am testing as we are unsure of the behavior of MSSQL.
Firstly. i wll add, that we have set this
select @@LOCK_TIMEOUT
set lock_timeout -1So we should have No Lock timeouts...(which is not happening)
This counter Lock Timeouts/Sec, is returning anywhere between 40-60 lock timeouts per second. Which we believe is reasonably high.
The issue here is, we do not understand what happens when this occurs, or if its a problem we should be worried about.
It is clear, from the title, that this counter measures a thread that is locked, awaiting for access to a table/row etc, and it timed out waiting for such occurrence, however, what happens in this instance.
Does the application that is controlling the query fail, and then need to re-send the query (totally nothing to do with the DB, I know)
Does the query get re-tried ?
Any information that Microsoft has produced about this counter is not helping us to understand the real cuase/effect and if it is in fact serious.
If any MSSQL Dba's can provide some light for us that would be great.
Cheers
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
As you've noted, you will not get a lock timeout from SQL Server once you've explicitly set it to -1.
You could also run into a deadlock, where 2+ tasks need the same resources and are deadlocked trying to get to them: in that case, SQL Server will kill/cancel the task(s) it needs to in order to free the deadlock.