Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Rovastar
Starting Member
38 Posts |
 Posted - 2005-11-03 : 09:24:27
|
| 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.mspxBut 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.mspxand 2 references to latchesa) “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
145 Posts |
Posted - 2005-11-04 : 14:23:32
|
| 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. |
 |
|
|
Rovastar
Starting Member
38 Posts |
Posted - 2005-11-07 : 06:37:19
|
| 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? |
|
|
|
richard75013
Starting Member
19 Posts |
Posted - 2007-10-25 : 13:10:34
|
| 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. |
|
|
|
Rovastar
Starting Member
38 Posts |
Posted - 2007-12-10 : 10:21:57
|
| 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. |
|
|
|
mountainaus
Starting Member
1 Post |
Posted - 2013-05-29 : 21:18:57
|
| 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. |
|
|
|
jackv
Master Smack Fu Yak Hacker
2179 Posts |
|
|
|
|
|
|
|
Latch Wait Time over 1 second? Why?