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

select @@LOCK_TIMEOUT
set lock_timeout -1

Open in new window


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
samabdelhamidAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Scott PletcherSenior DBACommented:
You might be getting an application timeout or some other timeout.

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.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.