July 11, 2016 at 2:21 pm
Hello all,
I have received a request from customer "Performance is related to latency and lag time and virtual server not performing well (waiting for CPU time).
Database servers are waiting for the host system to get physical processing power granted to handle the pending tasks. This is usually the case when the host system is over-committed. With a huge gap the "Latch" resource takes very long."
It seems like its not related to CPU issue nor memory issue.
i beileve its related to SQL Latches and their indicate like performance issue.
Could you please help me to how to find the latches and how resolve the issue? ifs its disk related issue how should prove it..
Could you please help me as soon as possible.
Thank you in advance for your attention to this matter
SQL server DBA
July 11, 2016 at 3:22 pm
1) Latches are rarely a true source of performance problems on SQL Server and outside of PFS/SGAM tempdb allocation issues and perhaps a few other circumstances they tend to be fairly complex in nature. I thus recommend you consider getting a professional tuning consultant on board if you can't otherwise debug the issue.
2) IO issues are easy to detect. There is a DMV for this. Take a snapshot, wait for a time, take another snapshot, diff and you get ms per read/write. Scripts can be found online for this.
3) Waits are the same as IO. Differential analysis of those during a period of concern is two of the three main tools I use to find performance issues.
4) sp_whoisactive is the third tool I use on regular basis. It too has a differential option.
5) Glenn Berry's SQL Server Diagnostic Scripts are a great resource.
6) Paul Randal has a great session on latches IIRC.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
July 12, 2016 at 4:04 am
Database admin(DBA) (7/11/2016)
It seems like its not related to CPU issue nor memory issue.i beileve its related to SQL Latches and their indicate like performance issue.
Performance tuning shouldn't be based on beliefs and assumptions. You need hard data.
What are the top (useful) waitstats on the server during the time that it's slow?
Have you investigated the query workload for inefficient queries?
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 12, 2016 at 7:42 am
GilaMonster (7/12/2016)
Database admin(DBA) (7/11/2016)
It seems like its not related to CPU issue nor memory issue.i beileve its related to SQL Latches and their indicate like performance issue.
Performance tuning shouldn't be based on beliefs and assumptions. You need hard data.
THIS ^
* INFINITY
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
July 14, 2016 at 6:29 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_typewaiting_tasks_countwait_time_msmax_wait_time_mssignal_wait_time_ms
PAGEIOLATCH_SH5157300300385841493741839
PAGEIOLATCH_UP35996756212935977804
PAGEIOLATCH_EX 588593321631253125956199
Could you please help me...
SQL server DBA
SQL server DBA
July 14, 2016 at 6:44 am
Over what time period were those values accumulated?
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 14, 2016 at 6:45 am
Assuming those are the top 3 waits, yeah, you have page latch issues. Here is a white paper on the issue from Microsoft. Here is discussion around the issue [/url]from Paul Randal. By and large, this is probably an indication of memory issues, not necessarily disk IO related problems. I'd focus first on identifying and resolving server configuration issues. Then I'd worry about the queries and related data structures as well. It could just be that you don't have enough memory, or it could be that the memory you have isn't being utilized well. You're going to need to gather more metrics. The white paper and Paul's directions should direct you in the right way to go.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
July 14, 2016 at 6:50 am
Grant Fritchey (7/14/2016)
Assuming those are the top 3 waits, yeah, you have page latch issues.
I'm not so sure. The average wait time is around 5ms for the SH, 1.5ms for the UP and 4ms for the EX. That's well into the excellent range for most IO subsystems. The max values are a little high, but if those were just individual spikes, not a huge concern.
Depending on what interval those were accumulated over, could be internal memory pressure, or no problem at all.
Need a lot more data here.
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 14, 2016 at 7:04 am
GilaMonster (7/14/2016)
Grant Fritchey (7/14/2016)
Assuming those are the top 3 waits, yeah, you have page latch issues.I'm not so sure. The average wait time is around 5ms for the SH, 1.5ms for the UP and 4ms for the EX. That's well into the excellent range for most IO subsystems. The max values are a little high, but if those were just individual spikes, not a huge concern.
Depending on what interval those were accumulated over, could be internal memory pressure, or no problem at all.
Need a lot more data here.
I agree. No argument. As I said, gather a lot more metrics using Paul & the white paper as a guide. I'm still inclined to say the latching is an issue, but that doesn't address cause. Cause is as you say, but, completely dependent on the other metrics.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
July 14, 2016 at 7:47 am
Could you please help me as soon as possible.
This seems to be a time-sensitive matter. You have gone back and forth for 3 days now and I promise you that you can go around and around for another week or more and STILL not get to the root cause of this client's performance problems.
If it truly is a "we need to fix this ASAP" scenario, PLEASE do yourself a favor and hire a professional tuner for a few hours or a day or few to quickly get to the bottom of this.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 10 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply