SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Administration (2000)
 Latch Wait Time over 1 second? Why?
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Rovastar
Starting Member

United Kingdom
38 Posts

Posted - 11/03/2005 :  09:24:27  Show Profile  Reply with Quote
Can someone explain what are an unacceptable level of “Average Latch Wait Time (ms)” is?

I sort of understand the differences between locks and latches. http://www.sqldev.net/articles/locklatch.htm

“SQLServer:Latches – Average Latch Wait Time. Defined as the average latch wait time (in milliseconds) for latch requests that had to wait. If this number is high, your server may be facing contention for its resources.”
From MS documentation: http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sqlops5.mspx

But I am totally miffed as to what is acceptable or not?

Here is a link to a Microsoft chat

http://www.microsoft.com/technet/community/chats/trans/sql/sql1023.mspx

and 2 references to latches

a) “Host Guest_Djana_MS:
Q: SueW: What is a reasonable rate for average latch/waits per second?
A: It depends on the server/load, s/b less than 50 or so. But don't take this number as Gospel. Why do you ask?”
b) “Host Guest_Djana_MS:
Q: sqletom: Can someone explain "Average Latch Wait Time (ms)" and what range I should be looking at.
Host Guest_Djana_MS:
A: A latch is a lightweight version of a lock. Latches do not hinder performance or concurrency. Use PerfMon latch wait timeouts to determine if you have issues of concern.”
So I am more confused?

I am getting on one web database server (4 x 2GHz CPU) that is not busy atm (low CPU usage 30% average, 70 connections) the average latch wait time of 1300 ms. 1.3 seconds!! Far over 50ms or so!

So am presuming that this is a problem. Yet “Latches do not hinder performance or concurrency” so *shrug* there is no perfmon counter for “latch wait timeouts” that I can see can anyone point them out to me…

Could someone if this is a problem and could they explain more please?

Also what might cause it such high levels of actictivy and if I can blame the monkey dev in Las Vages for this….ploease say there is something…please. :)

Thanks in advance folks. Free cookies and beer for all replies…….well as much will fit down a broadband connection.

bakerjon
Posting Yak Master

USA
145 Posts

Posted - 11/04/2005 :  14:23:32  Show Profile  Visit bakerjon's Homepage  Reply with Quote
That's a pretty tough question. From my experience it really depends on things like disk performance and whatever the application is OK with. I've seen acceptable performance with Avg. Latch Waits from 400ms to 35ms. Taking a look at my prod servers right now, I see 71ms on one, 382ms on another, 98ms on yet another. No performance issues noted for any of those apps. If you find a solid answer, post it here.

If you are concerned about app performance based on this, you could do a waitstats analysis using DBCC SQLPERF(WAITSTATS) or some of the great scripts from Gert Drapers' site (www.sqldev.net). If you see latch waits causing DB performance issues, you might want to investigate further.

I will say, 1 sec seems to be rather high!

Jon
-Like a kidney stone, this too shall pass.

Edited by - bakerjon on 11/04/2005 14:24:25
Go to Top of Page

Rovastar
Starting Member

United Kingdom
38 Posts

Posted - 11/07/2005 :  06:37:19  Show Profile  Reply with Quote
Thanks for the reply Jon.

(Strangely my name too is John Baker (with a 'h') and more strange I am prone to having kidney stones......*shudder*)

In a way I am glad that it is a "pretty tough question" as I means I am not asking dumb questions.

I have inherited a messy server with databases all over the place, multiple DTS junk running for hours everyday, no doubt very inefficient queries, etc. I'm more of a web server/general admin than a specialist in SQL Server.

I have read things that point to IO activity so maybe down that line of thought. Also some articles imply it is to do with tempdb.

I'll investigate more.

It is also all to do with providing decent documentation. I have a list of counter in perfmon to monitor but counters are not a very useful tool to more newbies in my team when you don't have anything to quantify them with. I started writing documentation saying ‘If counter CPU is constantly over 80% then it may be a problem.’ That one is easy but some of the others like ‘average damn latch time’ are not as easy at all.

Anyone else any thoughts?
Go to Top of Page

richard75013
Starting Member

USA
19 Posts

Posted - 10/25/2007 :  13:10:34  Show Profile  Click to see richard75013's MSN Messenger address  Reply with Quote
latch wait time is a good indication of how your drives are performing. It is telling you that it takes that long to access data, read and write from the disk. A latch wait time of 1.3 seconds is telling you that your disk subsystem is a bottleneck wheather it is from poor design of the disk subsystem, or from table scans or from choosing the improper RAID type or from just a lack of spindles to achive the IO that SQL is asking for.

If you look I bet your average disk sec/transfer is high and your avg disk queue length is more than 1.5 to 2.0 x number of spindles that make that drive. There are other things to coinsider as well like the 64k to 1MB offset that should be used with hardware RAID using the diskpart utility as well as the HBA driver may not be the optimal driver.
Go to Top of Page

Rovastar
Starting Member

United Kingdom
38 Posts

Posted - 12/10/2007 :  10:21:57  Show Profile  Reply with Quote
Thanks for the reply.

Sadly moved on from that that company when I first posted this so I cannot check to see if that is the case but the info is useful to know for future reference.
Go to Top of Page

mountainaus
Starting Member

1 Posts

Posted - 05/29/2013 :  21:18:57  Show Profile  Reply with Quote
This maybe a really basic error to professional DBA... but this is what I found with our high latch problem, and this thread ranks very high in search results. I thought I'd share our bit that it may help someone else.

on newer dual / multi processor server using NUMA memory architecture, the max degree of parallelism should be set to the actual core number per processor. in our example we had dual xenon with 4 cores each, and with hyperthreading it appears as 16 logical processors to SQL.

Locking this value from the default 0 to 4 cut the high latch on all queries down immediately.

Our latch ran 1000ms+ up to 30,000ms on some occasions.
Go to Top of Page

jackv
Flowing Fount of Yak Knowledge

United Kingdom
2067 Posts

Posted - 05/30/2013 :  01:51:01  Show Profile  Visit jackv's Homepage  Reply with Quote
@mountainaus - that's an interesting observation . In your tests did this only apply to hypethreading?

As far as investigating latches - some notes http://www.sqlserver-dba.com/2013/01/sql-server-latch_ex-and-how-to-reduce-it-.html

Jack Vamvas
--------------------
http://www.sqlserver-dba.com
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.12 seconds. Powered By: Snitz Forums 2000