Top Waits CXPACKET max degree of parallelism

Deltona, the answer is a great big, "it depends".  CXPACKET is one of those mysterious wait types.  It doesn't necessarily mean there is a problem but you can experiment with reducing the Max Degree of Parallelism on the server.  Sometimes reducing the degree of parallelism helps, sometimes it doesn't.  Then there is also a setting that adjusts the threshold for when the server chooses to parallelize query plans (the name of the setting escapes me at the moment).  Sometimes adjusting that can help and sometimes it doesn't.

Let's see if sqlrockstar, rob.hock or jmorrill have anything to add.

Well, my first question here would be "is there a problem"? I can see the aggregate amount of wait in the chart, but how many executions and sessions are running? Also, are you seeing any CPU pressure? What do the Signal Waits look like?

It could very well be that you simply have a lot of queries running in parallel, but your CPUs are handling the load just fine.

Also worth asking, what version and edition of SQL Server are we looking at?

Hi,

The main problem is the front end isn't as spiffy as I'd expect.

285 executions avg /day

1 session

CPU avg load is 6%

Signal Waits are at 2% avg

SQL 2008 R2 SP1 Enterprise

Deltona, I'm wondering if it is even the SQL Server response time that is the issue.  Have you investigated the IIS side?  I've seen thread starvation issues where everyone assumed that the problem was database response time b/c the web server had low resource utilization (CPU, Mem & disk).  You might try testing in a time window that you can be the only user (if possible), trace the SQL calls, count them, check their response time etc.  I know that's tedious but it's a pretty quick task if you can isolate your utilization.  If you can't, there are other roads to travel but they get a bit more complicated.  The following counters are ones I've found very useful to diagnose IIS thread starvation.  The definitions PerfMon provide should help but if not, I'd be happy to lend some guidance.

ASP.NET v2.05727\Requests Current

ASP.NET v2.05727\Requests Queued

ASP.NET v2.05727\Request Wait Time

ASP.NET v2.05727\Request Execution Time

.NET CLR LocksAndThreads\# of current physical Threads

ASP.NET Apps v2.05727\Requests In Application Queue

System\Processor Queue Length

Thanks,

I'll create some jobs to monitor and trend these counters. Will get back with my findings once done.

Thanks,

I'll create some jobs to monitor and trend these counters. Will get back with my findings once done.