MSSQL 2008 Lock Timeouts/sec counter - Clarity required

Posted on 2013-08-27
Medium Priority
Last Modified: 2013-10-21
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

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.

Question by:samabdelhamid
LVL 70

Expert Comment

by:Scott Pletcher
ID: 39445854
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.
LVL 10

Accepted Solution

Ramesh Babu Vavilla earned 2000 total points
ID: 39463616

Featured Post

Build your data science skills into a career

Are you ready to take your data science career to the next step, or break into data science? With Springboard’s Data Science Career Track, you’ll master data science topics, have personalized career guidance, weekly calls with a data science expert, and a job guarantee.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
Microsoft provides a rich set of technologies for High Availability and Disaster Recovery solutions.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

607 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question