| Author |
Topic |
|
ferrethouse
Constraint Violating Yak Guru
352 Posts |
 Posted - 2012-06-27 : 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
352 Posts |
Posted - 2012-06-27 : 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
15732 Posts |
Posted - 2012-06-27 : 17:06:35
|
| Run this:select *, wait_time_ms/1.0/waiting_tasks_count avg_waitfrom sys.dm_os_wait_stats where wait_time_ms>0order by avg_wait desc, wait_typeSee 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
352 Posts |
Posted - 2012-06-27 : 17:13:04
|
quote:
Originally posted by robvolk
Run this:select *, wait_time_ms/1.0/waiting_tasks_count avg_waitfrom sys.dm_os_wait_stats where wait_time_ms>0order by avg_wait desc, wait_typeSee 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
352 Posts |
Posted - 2012-06-27 : 17:17:04
|
| Interestingly, the database mirroring is the worst wait in terms of total amount...DBMIRROR_EVENTS_QUEUEDBMIRRORING_CMDWonder if mirroring is worth it as opposed to a DRP that simply involves restores. |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2012-06-27 : 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
30421 Posts |
Posted - 2012-06-28 : 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
Master Smack Fu Yak Hacker
2179 Posts |
Posted - 2012-06-28 : 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 parallelismJack Vamvas--------------------http://www.sqlserver-dba.com |
|
|
|
ferrethouse
Constraint Violating Yak Guru
352 Posts |
Posted - 2012-06-28 : 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
15732 Posts |
Posted - 2012-06-28 : 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. |
|
|
|
|
CXPacket Waits