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
set lock_timeout -1
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.