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

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

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 durati
on
> (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

I need help performing a date query where the output is all related records to one specific date

Hi Everybody,

I need help with a query where the output is all records related to one specific date. Like take for ex. all records which have been entered on 5/26/07. I would really appreciate the help. Thanks in advance. . .

I've pasted the code which I've been using but just gives me a reserved error 3646.

SELECT Escal_Tracker.Escal_Type, Escal_Tracker.Cross_func, Escal_Tracker.cas_no, Escal_Tracker.Inc_no, Escal_Tracker.esc_com, Escal_Tracker.nt_login, Escal_Tracker.Date
FROM Escal_Tracker
WHERE date = '5/26/07';

Regards,

Inspired_One

Hi inspired_one,

Move the thread from Visual Basic Forum in order to get better answers, since this issue is related to Transact-SQL.
Thanks for your understanding!

|||

Your code is 'mostly' ok. I recommend using the ISO date format which for SQL Server, is unabiguous. Also, if any of the Date field values include a time component, they will not match. The best way to accomplish a filter criteria for a complete day is to bracket all values between midnight the desired date, and just before midnight the next date. Also when you use reserved words as your table or column names, you MUST then always enclose them in square brackets to let the server know that you made a mistake and now must type extra keystrokes to compensate. ( [Date] is a reserved word.) Refer to Books Online, Topic: 'Reserved Words'

Perhaps this will work for you...

Code Snippet

SELECT
e.Escal_Type,
e.Cross_func,
e.cas_no,
e.Inc_no,
e.esc_com,
e.nt_login,
e.[Date]
FROM Escal_Tracker e
WHERE ( e.[Date] >= '2007/05/26'
AND e.[Date] < '2007/05/27'

|||

Arnie,

Thanks a lot for the help bro. that actually led me to the answer to this problem which was .

SELECT
e.Escal_Type,
e.Cross_func,
e.cas_no,
e.Inc_no,
e.esc_com,
e.nt_login,
e.[Date]
FROM Escal_Tracker e
WHERE ( e.[Date] >= # " & varfirstdate & " # AND e.[Date] < # " & varseconddate & " #

The difference is I'm using vba variables for this code. . .

Regards,

Inspired one

I need help performing a date query where the output is all related records to one specific date

Hi Everybody,

I need help with a query where the output is all records related to one specific date. Like take for ex. all records which have been entered on 5/26/07. I would really appreciate the help. Thanks in advance. . .

I've pasted the code which I've been using but just gives me a reserved error 3646.

SELECT Escal_Tracker.Escal_Type, Escal_Tracker.Cross_func, Escal_Tracker.cas_no, Escal_Tracker.Inc_no, Escal_Tracker.esc_com, Escal_Tracker.nt_login, Escal_Tracker.Date
FROM Escal_Tracker
WHERE date = '5/26/07';

Regards,

Inspired_One

Hi inspired_one,

Move the thread from Visual Basic Forum in order to get better answers, since this issue is related to Transact-SQL.
Thanks for your understanding!

|||

Your code is 'mostly' ok. I recommend using the ISO date format which for SQL Server, is unabiguous. Also, if any of the Date field values include a time component, they will not match. The best way to accomplish a filter criteria for a complete day is to bracket all values between midnight the desired date, and just before midnight the next date. Also when you use reserved words as your table or column names, you MUST then always enclose them in square brackets to let the server know that you made a mistake and now must type extra keystrokes to compensate. ( [Date] is a reserved word.) Refer to Books Online, Topic: 'Reserved Words'

Perhaps this will work for you...

Code Snippet

SELECT
e.Escal_Type,
e.Cross_func,
e.cas_no,
e.Inc_no,
e.esc_com,
e.nt_login,
e.[Date]
FROM Escal_Tracker e
WHERE ( e.[Date] >= '2007/05/26'
AND e.[Date] < '2007/05/27'

|||

Arnie,

Thanks a lot for the help bro. that actually led me to the answer to this problem which was .

SELECT
e.Escal_Type,
e.Cross_func,
e.cas_no,
e.Inc_no,
e.esc_com,
e.nt_login,
e.[Date]
FROM Escal_Tracker e
WHERE ( e.[Date] >= # " & varfirstdate & " # AND e.[Date] < # " & varseconddate & " #

The difference is I'm using vba variables for this code. . .

Regards,

Inspired one