| Author |
 Topic  |
|
|
ferrethouse
Constraint Violating Yak Guru
303 Posts |
 Posted - 06/27/2012 : 16:10:34
|
| 41% of the waits in my database are CXPACKET. I currently have 12 physical cores with HT so 24 total cores. I have the max degree of parallelism set to 6. Should I reduce that or is there a way to identify which specific queries are causing the CXPACKET waits and use the MAXDOP hint for them individually? |
|
|
ferrethouse
Constraint Violating Yak Guru
303 Posts |
Posted - 06/27/2012 : 16:43:00
|
| I found some of the queries. I used the maxdop hint on those queries and they ran slower. so apparently the waits associated with the cxpacket are worth it??? maybe it isn't a problem... |
 |
|
|
robvolk
Most Valuable Yak
USA
15559 Posts |
Posted - 06/27/2012 : 17:06:35
|
Run this:
select *, wait_time_ms/1.0/waiting_tasks_count avg_wait
from sys.dm_os_wait_stats
where wait_time_ms>0
order by avg_wait desc, wait_type
See what your average and max wait time is for CXPACKET. If it seems low enough and you're not seeing it impact performance then don't worry about it. Look at other waits with a higher average wait time and try to fix them, unless they have a low count. |
|
|
|
ferrethouse
Constraint Violating Yak Guru
303 Posts |
Posted - 06/27/2012 : 17:13:04
|
quote:
Originally posted by robvolk
Run this:
select *, wait_time_ms/1.0/waiting_tasks_count avg_wait
from sys.dm_os_wait_stats
where wait_time_ms>0
order by avg_wait desc, wait_type
See what your average and max wait time is for CXPACKET. If it seems low enough and you're not seeing it impact performance then don't worry about it. Look at other waits with a higher average wait time and try to fix them, unless they have a low count.
Thanks Rob. The average doesn't seem bad. The max seems a bit high but I don't really know what constitutes high...
quote:
max = 171759
average = 3.91065309
|
|
|
|
ferrethouse
Constraint Violating Yak Guru
303 Posts |
Posted - 06/27/2012 : 17:17:04
|
Interestingly, the database mirroring is the worst wait in terms of total amount...
DBMIRROR_EVENTS_QUEUE
DBMIRRORING_CMD
Wonder if mirroring is worth it as opposed to a DRP that simply involves restores. |
|
|
|
robvolk
Most Valuable Yak
USA
15559 Posts |
Posted - 06/27/2012 : 17:41:16
|
I recommend reading Glenn Berry's blog and using his diagnostic queries:
http://sqlserverperformance.wordpress.com/2010/05/02/recap-of-april-2010-dmv-a-day-series/
He updates them regularly and describes what constitutes a normal wait vs. one that needs investigation. Mirroring waits can typically be ignored, as long as mirroring itself is not causing a significant performance hit (one that you don't need a DMV query to notice). |
|
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29138 Posts |
Posted - 06/28/2012 : 04:52:51
|
CXPACKETS happens when the different streams have to wait for eachother; ie one stream is done earlier than another and have to wait until bering ready and put together again.
Reasons for this could be old statistics, multiple filegroups on different drives for example.
N 56°04'39.26"
E 12°55'05.63" |
|
|
|
jackv
Flowing Fount of Yak Knowledge
United Kingdom
1769 Posts |
Posted - 06/28/2012 : 11:40:41
|
Understanding the nature of the database server usage is imporatnt. For example an OLTP may be more effective - with less HT. A Reporting server, with typical multiple join statements may benefit from higher parallelism
Jack Vamvas
--------------------
http://www.sqlserver-dba.com |
|
|
|
ferrethouse
Constraint Violating Yak Guru
303 Posts |
Posted - 06/28/2012 : 15:45:29
|
| We recently turned on HT at the recommendation of Brett Ozar (performed an analysis of our database). We did see overall waits decrease significantly after the change. Perhaps the CXPACKET increase is simply the price to be paid for reducing the other waits with HT??? |
|
|
|
robvolk
Most Valuable Yak
USA
15559 Posts |
Posted - 06/28/2012 : 16:00:21
|
Yes.
Waits are a normal part of database operations. The goal is to determine which waits are truly detrimental and reduce or eliminate them. Ultimately you'll end up with a list of waits that can no longer be avoided, and hopefully don't impact your server. |
|
|
| |
Topic |
|
CXPacket Waits
