January 16, 2015 at 7:48 pm
Hi
I Cleared Wait statistics Buffer 5 Days ago.
Then now I run a query from sys.dm_os_wait_stats and this is the result (2 top Records)
--------------------- Waiting_Tasks_Count -------------- Wait_time_ms
CXPACKET >> 3607793060-------------- 3201069642
LATCH_EX>> 104523763-------------- 965788831
please :
- say about these two waits (CXPACKET and LATCH_EX) and
- what is the meaning of them (which Performance problem is on my server)
- what can I do for this performance problem?
Thankyou
January 17, 2015 at 6:40 am
CXPacket waits are just an indication of waits on threads in SQL Server. They don't indicate a performance problem at all really. A lot of people think they mean that you're experiencing problems with parallelism, but that's not true. They're largely and indication that parallelism is occuring, but not that it's causing problems.
LATCH_EX is a potential problem, but you need to get more information. Here's a great post[/url] on how to gather the information about latch issues on the machine.
But, I wouldn't focus on just the top 2 waits. I'd get like the top 10 or 15. You want to be able to spot patterns of behavior and the aggregation of different wait types will show you what's going on.
"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
January 17, 2015 at 6:57 am
Thanks a lot
I will read about Latchs
and these are 15 top records :
Waiting tasks Count waiting time ms
CXPACKET 3616055215 3303706702
LATCH_EX 128298706 1209517953
SP_SERVER_DIAGNOSTICS_SLEEP 1103963 303590421
HADR_FILESTREAM_IOMGR_IOCOMPLETION605770 303493870
DIRTY_PAGE_POLL 3034136 303493287
XE_TIMER_EVENT 8277 303480357
XE_DISPATCHER_WAIT 2529 303476822
SQLTRACE_INCREMENTAL_FLUSH_SLEEP 75862 303467465
LAZYWRITER_SLEEP 303181 303465285
LOGMGR_QUEUE 12534229 303117982
CHECKPOINT_QUEUE 311869 302842933
REQUEST_FOR_DEADLOCK_SEARCH 60513 301933940
BROKER_EVENTHANDLER 25 230466696
LCK_M_S 19828 227459380
SOS_SCHEDULER_YIELD 254848649 161156504
January 17, 2015 at 7:28 am
OK. You may want to filter those results and eliminate some stuff that's not telling you anything. Here's a good article on that from Paul Randal. Most of your top waits are in the ignore category. Here's a great resource on waits and wait types. It's not maintained any more, but is still largely applicable to everything we do.
The LCK_M_S is an indicator that you're waiting on shared locks. But by itself doesn't give us enough information to suggest there's an issue. SOS_SCHEDULER_YIELD might indicate a problem if you're also seeing issues around CPU use best captured using performance monitor counters. Other than that, you've got the latches there at the top.
Do you have a particular performance issue you're trying to address or are you just exploring the state of the system?
"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
January 17, 2015 at 10:55 pm
Hi
Thank you For your anwser.
No we dont have specific Performance Problem.
But we have an online bussiness and we should recognize problems and stay Performance in good state.
then I try to know about this parameters to do good monitoring.
January 18, 2015 at 4:44 am
OK then. Hopefully those resources I linked to will help you out then.
"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
January 18, 2015 at 7:47 pm
Hi
one of my top list is Latch_ex. (that you said It's maybe a problem)
I saw the waiting_tasks list and focus on Waittype. ((where waittype = latch_ex))
From this result :
I think the column resource_description is important :
that 98% of all value is : ACCESS_METHODS_DATASET_PARENT (000000082B9D8DF0)
with this information , is that a problem. or not.
January 19, 2015 at 3:22 am
That's related to parallel table/index scans. Probably means you have a lot of inefficient queries which are doing large scans. Worth looking into
btw, aggregated wait stats over 5 days is hard to work with, because you don't know when the waits were incurred, whether it was over-night jobs, maintenance or business-time queries. I'd suggest looking at wait stats per hour at the most and looking at the trends over a few days.
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
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply