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

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.

Ah, I see. In that case, you will want to isolate the queries coming from the front end and see what they are waiting for, as they might not be the same queries you are seeing with CXPACKET waits.

To sqlrockstar's point, if you click on a specific day and set the interval to be 1 day, click on that large aggregated bar.

Then go to waits and click on CXPACKET.  That will show you which SQL statements are contributing most to the CXPACKET wait type.

Having said that, I've done some investigation into CXPACKET waits and it's an interesting one.  If the instance DOP is set to 0, then I've seen cases where the optimizer chooses an inefficient degree of parallelism and workload distribution.  If you isolate the statements that are contributing most to CXPACKET, run the statements outside the app from SSMS in a serial fashion (with maxdop 1) and that may show a more accurate picture of the activities and bottlenecks to focus on for each statement.  Just keep in mind that since you will be running it in a serialized fashion, the optimizer will come up with a different plan (so not apples to apples on that front).

I ran sythentic transactions against the front end before during and after monitoring the counters. Here are the results:

More to follow. Will look at the queries next.

EDIT: The numbers seemed unusually low. It turns out the front end is using ASP.NET v4. Need to rerun tests.

You may have to check out the WMI counters for another set that is used with ASP.NET v4.  It's been a while since I did this and unfortunately the online documentation leaves a lot to be desired.  I do recall that there was a version of ASP.NET that was newer than v2 that were still using those counters.  I also suggest logging the data in a fashion that allows you to trend the values over time rather than just min, max & avg.  You can do that with PerfMon which is included with Windows.  Clearly the zeros you are getting for some of these counters mean that we aren't using the right ones b/c Requests Current, Request Wait Time & Request Execution time should not be zero on an active web site.  The last time I did this analysis was with IIS 6.0 and maybe .NET 3.5.  You may need different ones for .NET 4.0 and IIS 7.0.  The basic concept is to find out if requests are getting stalled by a queue, particularly the Application Queue.  When you find the right counters, you can tease out the total number of requests at polling time as well as any in the Application Queue.  If there are any threads in the Application Queue, then there aren't enough threads to serve them at that time, and you can suspect some SQL queries aren't even being issued.

Since that may be more than you want to take on right now, I'd follow sqlrockstar's point and dig into the queries via Database Performance Analyzer and see if there are any to be concerned with.  If not, then maybe a deep dive into the web server is worth it.

Good luck.  I'll help you as best as I can from my recollection of how to do this.

Oh, and if you are using IIS 7.0, I seem to recall that it has instrumentation to tell you the status of current requests!