8 Replies Latest reply on Dec 12, 2014 12:25 PM by brianflynn

    Trend chart doesn't reflect Daily chart

    momart

      Hi, I have been running DPA for a month now, and we have been working on getting our (pretty appalling) SQL Wait times down.

       

      We have been making progress, and released a new version of our app early on Tuesday 10th December.

       

      I looked at the Total Wait Chart on the trend screen this morning (11th Dec) and thought "Yay! we've cracked it" - total wait for Tuesday 10th Dec is shown as 20mins, down from around 80mins the previous day. Have a look at this screen grab:

       

      TotalWait.jpg

      Then I looked at the Daily Wait chart for yesterday and thought "Hang on, this looks much like it did the day before":

      HourlyWait09DEC2014.jpg

      Indeed, the Total Wait time during JUST the one hour slice at 3pm (highlighted) was over 17 minutes, and a rough add-up of the total wait time for the whole of the 10th December indicated that the total wait was indeed still around 80 minutes, the same as the day before.

       

      So. What is going on here? Why is the Total Wait trend chart misleading or just plain wrong??

       

      Richard

        • Re: Trend chart doesn't reflect Daily chart

          Maybe it could be worded better in the hover/tool-tip.  In the second view, "Total Wait Time for Time Period" is reporting the wait time for the whole bar you are hovering over (including all swatches of the histogram).  17 minutes and 45 seconds is 1,065 seconds and the y axis label seems to agree.

            • Re: Trend chart doesn't reflect Daily chart
              momart

              Yes, I know.

               

              My point was that the total bar on the first image (for the whole of Dec 10th) says I had 20 minutes of wait time during the whole day, whereas the sum of the individual bars on the second image adds up to over 80 minutes.

               

              The bar for Dec 10 on the first image is wrong, and should show 80 minutes.

               

              There is nothing wrong with the bars or the hover tip on the second image.

               

              Richard

                • Re: Trend chart doesn't reflect Daily chart

                  I see.  There's a nuance that must not be clear to you.  The bars don't represent all of the wait time.  They represent wait time for the top X queries with the most significant wait time for that time period.  In other words, your biggest targets for optimization.  The wait for the other queries not shown do not get swatches in the histogram nor entries in the legend.  Those missing waits account for the difference you've identified. 

                    • Re: Trend chart doesn't reflect Daily chart
                      momart

                      Hmm, I see what you're saying, but that still doesn't make any sense to me.

                       

                      I'm not at work at present, so I can't grab another screenshot, but my daily chart for the 9th Dec looks much like the daily chart for 10th Dec (second screen shot) similar height bars, similar number of swatches of colour of a similar size, yet the bar for Dec 10th on the trend chart is tiny compared to the 9th.

                        • Re: Trend chart doesn't reflect Daily chart
                          mandevil

                          When looking at the 30 day trend, we pick out the top 15 SQL statements with the highest wait time to show taking into account the entire 30 days.

                          When you drilled into the 24 hour period on the 10th, that shows the top 15 SQL statements for that time period taking into account that 24 hours.

                          What I suspect happened between the 9th and the 10th is that some queries changed and we now see it as a different hash (if SQL Server sees it as a different query, we will also).

                          So, when you look at the 30 day trend, only a few of the queries from prior to the 10th that had been running with significant waits are represented because the new queries haven't run enough time to accumulate waits to get into the top 15 (thus not making the top 15 for the 30 day trend).

                           

                          Were there any changes that happened to the SQL code between the 9th and 10th?  Even adding another space or carriage return will result in the SQL hash changing.

                          Clear as mud?  8 )

                          1 of 1 people found this helpful
                            • Re: Trend chart doesn't reflect Daily chart
                              momart

                              Thank you. I understand now, and I also understand what @brianflynn was trying to explain so I've give him the 'Correct Answer' because he got there first (thanks Brian).

                               

                              I'm presuming therefore that a more meaningful chart to show the progress (or not) that we are making in addressing slow-running queries would be the Top Waits trend chart, which is more the shape I would expect - we've made some progress in addressing some of our worst-performing queries, but we still have some way to go.

                               

                              Richard

                               

                              WaitsTrend.jpg

                                • Re: Trend chart doesn't reflect Daily chart
                                  mandevil

                                  I would agree from an overall perspective.  Also, time spent in memory/cpu is what's ideal (could still be going after too much data due to missing indexes or inefficient queries).  Time in memory/cpu means you are finding the data you are going for in cache.  Basically means your processing and not in a suspended state.

                                    • Re: Trend chart doesn't reflect Daily chart

                                      Of course if you're looking for at a glance pictures of progress, you might look at the dashboard (home page) and use the biggest winners/losers graphs.  They show the instances that have seen the greatest increase and decrease in wait time.  These can be helpful in proving to peers and managers that you are making big improvements or showing where you need to invest time making improvements.