I need your general opinion on "Lock: Timeout"

Hi
In the process of investigating a poor performing application, I've ran
number of traces on our SQL server. In these traces, I can see that
there're a high number of EventClass 27 - Lock: Timout.
I'd assume that in theory I shouldn't have any lock timeouts, but is
that also the case in the real world?
The trace has run for an hour, and if I look in the data afterwards, I
have 11583 records of eventclass 27 out of a total of approx 1,6 million
records in the trace file.
I know that these figures may not tell a lot since it depends on many
factors, but is it "normal" to see such a high number of timouts?
What are your experience from other situations?
Regards
Steen
"Steen Persson (DK)" <spe@.REMOVEdatea.dk> wrote in message
news:ex8CB4x0FHA.1564@.tk2msftngp13.phx.gbl...
> Hi
> In the process of investigating a poor performing application, I've ran
> number of traces on our SQL server. In these traces, I can see that
> there're a high number of EventClass 27 - Lock: Timout.
> I'd assume that in theory I shouldn't have any lock timeouts, but is that
> also the case in the real world?
> The trace has run for an hour, and if I look in the data afterwards, I
> have 11583 records of eventclass 27 out of a total of approx 1,6 million
> records in the trace file.
> I know that these figures may not tell a lot since it depends on many
> factors, but is it "normal" to see such a high number of timouts?
> What are your experience from other situations?
>
Unless the application has set a finite lock timeout, locks won't time out
at all. That event should tell you what object the lock is on. But more
importantly look at the Lock: Acquired event for events with a long duration
(it's in milliseconds). This is the surest sign of locking problems.
David
|||David Browne wrote:
> "Steen Persson (DK)" <spe@.REMOVEdatea.dk> wrote in message
> news:ex8CB4x0FHA.1564@.tk2msftngp13.phx.gbl...
> Unless the application has set a finite lock timeout, locks won't time out
> at all. That event should tell you what object the lock is on. But more
> importantly look at the Lock: Acquired event for events with a long duration
> (it's in milliseconds). This is the surest sign of locking problems.
> David
>
I can see that I haven't included the "Lock Acquired" event in my trace,
so I'll have to run a new one with this included.
I've looked further in my current trace data, but there're no Object on
the "Lock: Timeout" event, so that doesn't tell me much.
I'll check it further when I have the new trace data and see if I can
get something out of the lock acquired event.
Regards
Steen
|||Steen Persson (DK) wrote:
> I can see that I haven't included the "Lock Acquired" event in my
> trace, so I'll have to run a new one with this included.
> I've looked further in my current trace data, but there're no Object
> on the "Lock: Timeout" event, so that doesn't tell me much.
> I'll check it further when I have the new trace data and see if I can
> get something out of the lock acquired event.
> Regards
> Steen
Lock:Timeout will track internal type timeouts that are not really a
problem. If memory serves, you can use Lock:Timeout, but only worry
about those events that are raised with a Duration > 0. Also look for
Attention events, SQL:BatchCompleted/RPC:Completed events for a high
level analysis of SQL performance. Include SQL:StmtCompleted and
SP:StmtCompleted for more detail.
David Gugick
Quest Software
www.imceda.com
www.quest.com