Recently, using PERFMON, I've been rather dismayed to find that our
application is averaging 3 - 4 lock timeouts per second, and
frequently has extended periods of several minutes where this figure
reaches the hundreds.
Average LockWaits/sec are less than 0.05, and TableLockEscalations/sec
are less than 0.5
These last two seem very good to me, and as a result I would
intuitively expect a LockTimeout figure of near-zero.
Can anyone suggest why the measured LockTimeout value might be so high?
Is the measured value actually considered "high" at all? Doubtless
this depends on a number of things (transaction rate, number of users
etc), but a rule-of-thumb opinion would be welcomed. 4 8799
Philip Yale (ph********@btopenworld.com) writes: Recently, using PERFMON, I've been rather dismayed to find that our application is averaging 3 - 4 lock timeouts per second, and frequently has extended periods of several minutes where this figure reaches the hundreds.
Average LockWaits/sec are less than 0.05, and TableLockEscalations/sec are less than 0.5
These last two seem very good to me, and as a result I would intuitively expect a LockTimeout figure of near-zero.
Can anyone suggest why the measured LockTimeout value might be so high? Is the measured value actually considered "high" at all? Doubtless this depends on a number of things (transaction rate, number of users etc), but a rule-of-thumb opinion would be welcomed.
Whether this value of lock timeout is high or low depends the lock-
timeout settings. By default, SQL Server does not time out on locks,
but this can be changed with SET LOCK_TIMEOUT. If there some process
which sets the lock timeout to 0, it seems quite normal that you would
get a high lock-timeout rate as 0 means "give up as soon as there is
a lock".
I would use the Profiler and trace the LockTimeout event, to see
where the action is taking place, and then analyse that code to see
if there is a real issue or not.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se
Books Online for SQL Server SP3 at http://www.microsoft.com/sql/techinf...2000/books.asp
There's only one SP in the entire application suite which specifies a
lock_timeout of 1000 (1 sec), and this is only called at most once
every 2 seconds, so wouldn't itself account for the figures I'm seeing.
Everything else uses the default.
Of course, there's nothing to say that this timeout isn't being set in
the ADO application layer somewhere (I've got the developers looking
into this now).
I set a SQLAgent Alert threshold on SQLServer:Locks:LockTimeouts/sec
to send an email whenever they exceeded a value of 10. Last night, for
a period of 2 hours, I received an alert every minute, with reported
values starting at 12 in the first alert, and rising progressively to
625966 in the last alert !!!!!!!!
The dataserver was rebooted immediately before these alerts started
appearing, and I don't know if that could have caused them (not sure
how, at least not for 2 hours). Over that 2 hour period we also got
raised levvels of lock waits and table lock escalations.
The other alternative is a batch process as the cause, but this didn't
happen the previous night (when all the same batches run), and no
batch that was running over that period failed, as I would have
expected if we were getting so many lock timeouts.
Phil (ph********@btopenworld.com) writes: I set a SQLAgent Alert threshold on SQLServer:Locks:LockTimeouts/sec to send an email whenever they exceeded a value of 10. Last night, for a period of 2 hours, I received an alert every minute, with reported values starting at 12 in the first alert, and rising progressively to 625966 in the last alert !!!!!!!!
That's a lot!
Did you run Profiler to see what processes that runs into the lock timeouts?
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se
Books Online for SQL Server SP3 at http://www.microsoft.com/sql/techinf...2000/books.asp
Yes, I did.
I think the problem with the Alert setting is that it counts CUMULATIVE
timeouts, and not those occurring at a given sample time. Not terribly
useful.
Regarding the profiler, this is giving me a few pointers to the root
cause, but also raising some new questions. For example, the dbid
column frequently refers to database IDs of 0 and 132. Using SELECT
DB_NAME(), dbid 132 simply doesn't exist, whereas 0 seems to be the
master database, which I always thought was dbid 1 (which it is as
well). Anyone know why master can have 2 database IDs? Anyone know
why profiler reports IDs of 0 and 132, when these databases don't exist? This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: Phil |
last post by:
Recently, using PERFMON, I've been rather dismayed to find that our
application is averaging 3 - 4 lock timeouts per second, and
frequently has extended periods of several minutes where this...
|
by: MLH |
last post by:
If I choose to dim MyDate as Date or MyDate as Variant,
what is the most significant difference between the two
choices?
|
by: Jason Huang |
last post by:
Hi,
Would someone explain the following coding more detail for me? What's the
( ) for?
CurrentText = (TextBox)e.Item.Cells.Controls;
Thanks.
Jason
|
by: Z D |
last post by:
Hello,
I have a Windows Server 2003 machine running IIS6.0. I recently xcopied over
an ASP.NET application that was originally running on a Windows 2000 Server
machine.
The application works...
|
by: Nic |
last post by:
On a w2k3 running .NET framework 1.1.
The ASP.NET counters don't show in the perfmon list. All .NET framework
counters
are there.
Any ideas what I need to do to enable ASP.NET counters?
...
|
by: James Hunter Ross |
last post by:
Friends,
We have been experiencing unexpected Application Timeout issues in our ASP
1.1 application. We eventually read-up on aspnet_wp (or w3wp) "recycling"
and various related concepts...
|
by: Marc Melancon |
last post by:
I have a situation where accessing perfmon counters remotely does not
display the same list of Performance Objects remotely as it does when doing
it from the host of SQL Server.
From the host of...
|
by: Frank Rizzo |
last post by:
I am trying to do some monitoring of some PerfMon counters and have a
question.
Does PerfMon figure out the Minimum, Maximum, Average values for each
counter? Or are those values part of the...
|
by: Ryan Liu |
last post by:
Hi,
I use C# wrote an Client/Server application. In production environment, will
be 130 clients (Windows XP) connect to a Server (Windows 2000/2003 Server)
thought TCP/IP socket in a local 100M...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing,...
| |