Does anyone know the potential causes of a high average latch wait time
(12-20 sec) for LATCH_EX? Total average disk queue depth << 1, CPU at 40%,
not many inserts, client side cursors.
If the client side cursors are not detached from the main data source, that
could do it. Also, just the network traffic load to get it into a
scrollable client side cursor could cause excess latches.
What does you estimated execution plan look like?
Sincerely,
Anthony Thomas
"Bob S" <noone@.nowhere.com> wrote in message
news:uhNTPu4yEHA.1652@.TK2MSFTNGP11.phx.gbl...
> Does anyone know the potential causes of a high average latch wait time
> (12-20 sec) for LATCH_EX? Total average disk queue depth << 1, CPU at
40%,
> not many inserts, client side cursors.
>
|||You may want to monitor memory (vs disks) for this. You can
find explanations in the Latch_x section of the following
article:
http://www.sqldev.net/articles/WaitTypes.htm
-Sue
On Mon, 15 Nov 2004 22:22:52 -0500, "Bob S"
<noone@.nowhere.com> wrote:
>Does anyone know the potential causes of a high average latch wait time
>(12-20 sec) for LATCH_EX? Total average disk queue depth << 1, CPU at 40%,
>not many inserts, client side cursors.
>
Showing posts with label latch. Show all posts
Showing posts with label latch. Show all posts
Monday, March 26, 2012
Latch wait time
Does anyone know the potential causes of a high average latch wait time
(12-20 sec) for LATCH_EX? Total average disk queue depth << 1, CPU at 40%,
not many inserts, client side cursors.If the client side cursors are not detached from the main data source, that
could do it. Also, just the network traffic load to get it into a
scrollable client side cursor could cause excess latches.
What does you estimated execution plan look like?
Sincerely,
Anthony Thomas
"Bob S" <noone@.nowhere.com> wrote in message
news:uhNTPu4yEHA.1652@.TK2MSFTNGP11.phx.gbl...
> Does anyone know the potential causes of a high average latch wait time
> (12-20 sec) for LATCH_EX? Total average disk queue depth << 1, CPU at
40%,
> not many inserts, client side cursors.
>|||You may want to monitor memory (vs disks) for this. You can
find explanations in the Latch_x section of the following
article:
http://www.sqldev.net/articles/WaitTypes.htm
-Sue
On Mon, 15 Nov 2004 22:22:52 -0500, "Bob S"
<noone@.nowhere.com> wrote:
>Does anyone know the potential causes of a high average latch wait time
>(12-20 sec) for LATCH_EX? Total average disk queue depth << 1, CPU at 40%,
>not many inserts, client side cursors.
>
(12-20 sec) for LATCH_EX? Total average disk queue depth << 1, CPU at 40%,
not many inserts, client side cursors.If the client side cursors are not detached from the main data source, that
could do it. Also, just the network traffic load to get it into a
scrollable client side cursor could cause excess latches.
What does you estimated execution plan look like?
Sincerely,
Anthony Thomas
"Bob S" <noone@.nowhere.com> wrote in message
news:uhNTPu4yEHA.1652@.TK2MSFTNGP11.phx.gbl...
> Does anyone know the potential causes of a high average latch wait time
> (12-20 sec) for LATCH_EX? Total average disk queue depth << 1, CPU at
40%,
> not many inserts, client side cursors.
>|||You may want to monitor memory (vs disks) for this. You can
find explanations in the Latch_x section of the following
article:
http://www.sqldev.net/articles/WaitTypes.htm
-Sue
On Mon, 15 Nov 2004 22:22:52 -0500, "Bob S"
<noone@.nowhere.com> wrote:
>Does anyone know the potential causes of a high average latch wait time
>(12-20 sec) for LATCH_EX? Total average disk queue depth << 1, CPU at 40%,
>not many inserts, client side cursors.
>
Latch wait time
Does anyone know the potential causes of a high average latch wait time
(12-20 sec) for LATCH_EX? Total average disk queue depth << 1, CPU at 40%,
not many inserts, client side cursors.If the client side cursors are not detached from the main data source, that
could do it. Also, just the network traffic load to get it into a
scrollable client side cursor could cause excess latches.
What does you estimated execution plan look like?
Sincerely,
Anthony Thomas
"Bob S" <noone@.nowhere.com> wrote in message
news:uhNTPu4yEHA.1652@.TK2MSFTNGP11.phx.gbl...
> Does anyone know the potential causes of a high average latch wait time
> (12-20 sec) for LATCH_EX? Total average disk queue depth << 1, CPU at
40%,
> not many inserts, client side cursors.
>|||You may want to monitor memory (vs disks) for this. You can
find explanations in the Latch_x section of the following
article:
http://www.sqldev.net/articles/WaitTypes.htm
-Sue
On Mon, 15 Nov 2004 22:22:52 -0500, "Bob S"
<noone@.nowhere.com> wrote:
>Does anyone know the potential causes of a high average latch wait time
>(12-20 sec) for LATCH_EX? Total average disk queue depth << 1, CPU at 40%,
>not many inserts, client side cursors.
>
(12-20 sec) for LATCH_EX? Total average disk queue depth << 1, CPU at 40%,
not many inserts, client side cursors.If the client side cursors are not detached from the main data source, that
could do it. Also, just the network traffic load to get it into a
scrollable client side cursor could cause excess latches.
What does you estimated execution plan look like?
Sincerely,
Anthony Thomas
"Bob S" <noone@.nowhere.com> wrote in message
news:uhNTPu4yEHA.1652@.TK2MSFTNGP11.phx.gbl...
> Does anyone know the potential causes of a high average latch wait time
> (12-20 sec) for LATCH_EX? Total average disk queue depth << 1, CPU at
40%,
> not many inserts, client side cursors.
>|||You may want to monitor memory (vs disks) for this. You can
find explanations in the Latch_x section of the following
article:
http://www.sqldev.net/articles/WaitTypes.htm
-Sue
On Mon, 15 Nov 2004 22:22:52 -0500, "Bob S"
<noone@.nowhere.com> wrote:
>Does anyone know the potential causes of a high average latch wait time
>(12-20 sec) for LATCH_EX? Total average disk queue depth << 1, CPU at 40%,
>not many inserts, client side cursors.
>
Latch Time
Hi All,
This might be a bit of a question like "how long is a peice of string" however here goes....
What is considered to be a high average latch time?
We have a SQL2000 machine which has:
700-900 ms average latch time
100% Buffer Cache Hit Ratio
No memory pages / second
Very low (almost always below 5%) cpu usage
Next to no disk transfers / sec
0 average lock time
I am surprised that the latch time is so high. Any ideas, whether I should be looking at something in particular?
Cheers in advance.
TroyAnyone - please?|||It really is kind of a hard question to answer. :) A high latch time generally points to a memory shortage and has a direct correlation to a low buffer cache hit ratio. Since your buffer cach hit ratio is 100%, you obviously don't have a problem with that.
The other thing it can point to is needing more I/O width on your disk subsystem. That's pretty doubtful again with that high of a buffer cach hit ratio. You might want to look at your physical read and write cache to see what those counters are at.
Really though, it doesn't seem to me you have any problems and I wouldn't worry about the 700-900ms average latch time. If you see that number rising dramatically when a load gets put on the system, you need to come back and add to this post so we can look at it. Otherwise, I wouldn't worry about it, especially if your system seems to be running fine and getting the desired response times from the database server.
Hope this helped.|||Thanks for the response!! Yeah that is pretty much what I had thought - but was after some confirmation from others.
Had a feeling it was slow disk/issues with I/O, so will keep an eye on it and if it causes any more concern, I will post back.
Thanks again!
Troy
This might be a bit of a question like "how long is a peice of string" however here goes....
What is considered to be a high average latch time?
We have a SQL2000 machine which has:
700-900 ms average latch time
100% Buffer Cache Hit Ratio
No memory pages / second
Very low (almost always below 5%) cpu usage
Next to no disk transfers / sec
0 average lock time
I am surprised that the latch time is so high. Any ideas, whether I should be looking at something in particular?
Cheers in advance.
TroyAnyone - please?|||It really is kind of a hard question to answer. :) A high latch time generally points to a memory shortage and has a direct correlation to a low buffer cache hit ratio. Since your buffer cach hit ratio is 100%, you obviously don't have a problem with that.
The other thing it can point to is needing more I/O width on your disk subsystem. That's pretty doubtful again with that high of a buffer cach hit ratio. You might want to look at your physical read and write cache to see what those counters are at.
Really though, it doesn't seem to me you have any problems and I wouldn't worry about the 700-900ms average latch time. If you see that number rising dramatically when a load gets put on the system, you need to come back and add to this post so we can look at it. Otherwise, I wouldn't worry about it, especially if your system seems to be running fine and getting the desired response times from the database server.
Hope this helped.|||Thanks for the response!! Yeah that is pretty much what I had thought - but was after some confirmation from others.
Had a feeling it was slow disk/issues with I/O, so will keep an eye on it and if it causes any more concern, I will post back.
Thanks again!
Troy
Latch documentation?
Does anybody know of any good documentation on latches? I remember somebody
mentioning in this group that microsoft were going to release a white paper
on them. Has it been released yet?
Gav :o)
No it hasn't been released yet and there's no plan to release one in the
near future.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Gav" <gavin.metcalfe@.nospam.portakabin.com> wrote in message
news:cl5csv$6r8$1@.sparta.btinternet.com...
> Does anybody know of any good documentation on latches? I remember
somebody
> mentioning in this group that microsoft were going to release a white
paper
> on them. Has it been released yet?
> Gav :o)
>
sql
mentioning in this group that microsoft were going to release a white paper
on them. Has it been released yet?
Gav :o)
No it hasn't been released yet and there's no plan to release one in the
near future.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Gav" <gavin.metcalfe@.nospam.portakabin.com> wrote in message
news:cl5csv$6r8$1@.sparta.btinternet.com...
> Does anybody know of any good documentation on latches? I remember
somebody
> mentioning in this group that microsoft were going to release a white
paper
> on them. Has it been released yet?
> Gav :o)
>
sql
Latch documentation?
Does anybody know of any good documentation on latches? I remember somebody
mentioning in this group that microsoft were going to release a white paper
on them. Has it been released yet?
Gav :o)No it hasn't been released yet and there's no plan to release one in the
near future.
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Gav" <gavin.metcalfe@.nospam.portakabin.com> wrote in message
news:cl5csv$6r8$1@.sparta.btinternet.com...
> Does anybody know of any good documentation on latches? I remember
somebody
> mentioning in this group that microsoft were going to release a white
paper
> on them. Has it been released yet?
> Gav :o)
>
mentioning in this group that microsoft were going to release a white paper
on them. Has it been released yet?
Gav :o)No it hasn't been released yet and there's no plan to release one in the
near future.
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Gav" <gavin.metcalfe@.nospam.portakabin.com> wrote in message
news:cl5csv$6r8$1@.sparta.btinternet.com...
> Does anybody know of any good documentation on latches? I remember
somebody
> mentioning in this group that microsoft were going to release a white
paper
> on them. Has it been released yet?
> Gav :o)
>
Latch documentation?
Does anybody know of any good documentation on latches? I remember somebody
mentioning in this group that microsoft were going to release a white paper
on them. Has it been released yet?
Gav :o)No it hasn't been released yet and there's no plan to release one in the
near future.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Gav" <gavin.metcalfe@.nospam.portakabin.com> wrote in message
news:cl5csv$6r8$1@.sparta.btinternet.com...
> Does anybody know of any good documentation on latches? I remember
somebody
> mentioning in this group that microsoft were going to release a white
paper
> on them. Has it been released yet?
> Gav :o)
>
mentioning in this group that microsoft were going to release a white paper
on them. Has it been released yet?
Gav :o)No it hasn't been released yet and there's no plan to release one in the
near future.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Gav" <gavin.metcalfe@.nospam.portakabin.com> wrote in message
news:cl5csv$6r8$1@.sparta.btinternet.com...
> Does anybody know of any good documentation on latches? I remember
somebody
> mentioning in this group that microsoft were going to release a white
paper
> on them. Has it been released yet?
> Gav :o)
>
Subscribe to:
Posts (Atom)