October 7, 2013 at 10:24 am
Hi,
One of my Servers running on SQL Server 2008 R2 , while monitoring perfmon I can see high values for latch waits/sec. The value keeps fluctuating consistently from 20 to 100. I read somewhere that a value more than 10 for this is considered as a problem.
How do I find where these latches come from ? I want to find which queries running in the database causes these high latches and I am unable to find a good documentation to troubleshoot this. Any help regarding this is highly appreciated.
Thanks in Advance !
October 7, 2013 at 10:26 am
First things first, are those latch waits causing problems?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 7, 2013 at 10:31 am
Yes they are causing performance issues.
I could find high values for latch waits/sec. The page life expectancy is very high(so I came to a conclusion it is not a memory issue).
October 7, 2013 at 10:34 am
Problems as in noticeable slow queries due to latch waits, not a counter that's higher than some random person on the internet said it should be.
If so, what latches are you seeing the most waits on?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 7, 2013 at 10:43 am
Attached screenshot has the top 10 waits.
October 7, 2013 at 12:02 pm
No latch waits in that list, but that wasn't what I asked.
Have you seen queries executing that are suspended with latch waits? If so, what were the wait resources and what were the average wait times like?
If not, why are you worrying about something that is not demonstrably causing a problem?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 8, 2013 at 4:23 am
Checked dm_os_latch_Stats and the result is attached.
October 8, 2013 at 5:53 am
Again not what I was asking.
Are you seeing executing queries that are getting suspended with latch waits? Are these latch waits directly causing performance problems?
There will always be latch waits in a system, it's normal. It's only when the latch waits cause problems (lots of queries waiting for latches or queries waiting for long periods for latches) that they should be investigated.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 11, 2016 at 11:50 pm
Hello GilaMonster,
Thank you for your suggestions,I'm facing similar kind of problem,But still need a question about how to check latches are causing the performance issue? which DMV's need to use and Columns to find the latches or wait?
Need to check long running quires ?
Please help me to find the root cause of this issue.
thank you in advance for your attention to this matter.
Waiting for your response to proceed further.
SQL server DBA
July 14, 2016 at 6:16 am
Hello All,
while running SELECT *
FROM sys.dm_os_wait_stats ,got output below.its seems like IO issue..I do not see the IO releated errors in SQL server errorlog.
wait_type waiting_tasks_countwait_time_msmax_wait_time_mssignal_wait_time_ms
PAGEIOLATCH_SH 515730030038584 1493 741839
PAGEIOLATCH_UP 359967562129 359 77804
PAGEIOLATCH_EX 588593321631253 1259 56199
Could you please help me...
SQL server DBA
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply