signal_wait_time_ms和wait_time_ms的关系

Question

我查资料说的是signal_wait_time_ms是出于runable的状态的等待时间

而wait_time_ms是进程suspended和runable的等待时间

当signal_wait_time_ms/wait_time_ms大于20%说明cpu有压力

可是我的等待时间中CXPACKET占了44，是不是存在cpu压力呢，如果我取消cpu关联呢，会有帮助吗？

ps：我的服务器情况：24cpu 64 bit  16g内存 磁盘阵列 dell的 4个盘 raid5

等待事件中CXPACKET 占等待时间占总等待时间的44,LATCH_EX占27

总等待时间是：wait_time_ms-signal_wait_time_ms

 

 

Answer 1

CXPACKET is parallelizm related wait, you can reduce it by setting maxdop to smaller number (<=8 in your case).

Answer 2

我发现取消关联cpu选项后再并发100时性能提升了。降低并行度能提高性能吗？

Answer 3

Depends, we found that setting maxdop to <= 8 on machine with 16 and more processores results better performance for oltp system. 

Answer 4

我比较纳闷的是我们的中层服务号称做300个并发，其实就是50个session 可是这50个session居然有10个以上都在sleeping真正运行的只有几个 根本就不是oltp 我在想有必要取消cpu关联吗？并行读倒是可以降低到8

Answer 5

We usually don't disable parallilizm completely at server level, lot of good queries benefit from it. What you said sounds connection pooling but parallilizm.