10 Replies Latest reply on Aug 15, 2014 2:00 PM by brianflynn

    Top Waits CXPACKET max degree of parallelism

    Deltona

      Hi guys,

       

      Reviewing my top waits graph, I am seing a lot of CXPACKET waits. I am looking for a suggestion pertaining to this particular SQL server and that one database running on it.

      It's a bare metal box with 12 physical cores running the Orion database and nothing else. Since Orion is an OLTP database, would disabling parallelism make sense or should we focus on the queries?

      I hope to get some joint input from the DPA and Orion core boffins.

       

      NetPerfMon_Top_Waits_CXPACKET__SolarWinds_DPA_Report.png

        • Re: 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.

            • Re: Top Waits CXPACKET max degree of parallelism
              sqlrockstar

              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?

                • Re: Top Waits CXPACKET max degree of parallelism
                  Deltona

                  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

                    • Re: Top Waits CXPACKET max degree of parallelism

                      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

                        • Re: Top Waits CXPACKET max degree of parallelism
                          Deltona

                          Thanks,

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

                          • Re: Top Waits CXPACKET max degree of parallelism
                            Deltona

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

                            WEB_SERVER_WMI_COUNTERS.png

                             

                            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.

                              • Re: Top Waits CXPACKET max degree of parallelism

                                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.

                            • Re: Top Waits CXPACKET max degree of parallelism
                              sqlrockstar

                              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.

                                • Re: Top Waits CXPACKET max degree of parallelism
                                  mandevil

                                  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).