7 Replies Latest reply on May 17, 2016 4:03 PM by m60freeman

    Query Execution Count in DPA

    gharrison

      I swear I used to be able to see the number of executions of a query in DPA, but I can't seem to find that any more.  Can someone direct me, as I've got a SQL Server query being executed (I believe) many, many times per day by an application, and it's causing me issues.

       

      Thanks,

      Glen

        • Re: Query Execution Count in DPA
          mandevil

          Glen, you should still have that functionality.

          What do you see when you click into a specific timeframe (let's say drill into 1 hour), click on the SQL statement in question, and look at stats? Execution counts should be part of that data.

            • Re: Query Execution Count in DPA
              gharrison

              Thanks!  I was trying to look at an entire day, not an hour interval.  The fields are their when I look at the hour interval, although they are blank.  I'll look into possible statistics updating to see if I can get them populated.  Thanks again for pointing me to the hour interval.  I wonder why these are not displayed when you're looking at an entire day.

               

              Weird ... looks like these stats are available for every query and stored procedure, except the one I'm interested in.

                • Re: Query Execution Count in DPA
                  mandevil

                  You can see them for an entire day in one of two ways:

                  • When you are clicked within a day from the trend view, select the interval dropdown and click on 1 day. Then click on the bar representing the entire day. Then click on the sql hash in question.
                  • If the sql hash in question was one of the top 5 for the day, you can go into the advisor tab below the trend graph for the day and click on the more link for that hash. Scroll to the bottom and it should show you executions for the entire day if you are looking back in history. If it's not in the top 5, but you know the hash, you can click on the "Analyze Query" button and select the hash in question and do a customized analysis which again should show the executions for the time frame chosen at the bottom of the page.
                  1 of 1 people found this helpful
                    • Re: Query Execution Count in DPA
                      gharrison

                      Mandevil,

                       

                      Thanks again!  You're advice works great, seemingly for every query but the one I'm actually interested in.  Not sure why, but this one doesn't display the execution statistics, even though it's the highest consumer of resources, and responsible for the most amount of wait time.  I don't get it, but I'll just work with the info I do have.  It'd sure be nice to have those stats on this procedure though, to highlight to the vendor.  I'm convinced the root cause of our issues are A) the number of times the vendor is executing this massive procedure and B) the fact they are creating huge temporary tables that are being used in complex queries, but have neglected to create a single index on these temporary tables.  Argh!!!

                        • Re: Query Execution Count in DPA
                          mandevil

                          You are most welcome. Here is how we go after the raw data (we grab the offsets and sql handle during our quick poll, then go back much less frequently to get the stats.

                           

                          SELECT sql_handle, statement_start_offset, statement_end_offset, plan_generation_num, plan_handle,

                            SUM(execution_count) sum_execution_count, SUM(total_logical_writes) sum_total_logical_writes,

                            SUM(total_physical_reads) sum_total_physical_reads, SUM(total_logical_reads) sum_total_logical_reads

                          FROM master.sys.dm_exec_query_stats

                          GROUP BY sql_handle, statement_start_offset, statement_end_offset, plan_generation_num, plan_handle

                           

                          Now, there could be a reason your specific statement does not have statistics. The most likely reason is that for some reason the SP is getting recompiled (resets the counters for that statement). If you can get the entire code set, can you see if a "with recompile" is being called? Also, if any underlying objects are getting recompiled that the SP is dependent on, it will force a recompile. Just some thoughts.

                            • Re: Query Execution Count in DPA
                              gharrison

                              Unfortunately I don't have access to the vendor code calling the stored procedure, but the procedure itself is very long and complex, using multiple temporary tables (although not a single index is created on any of them ... argh).  Whether the vendor is explicitly calling the procedure with "WITH RECOMPILE ", or SQL is doing that automatically due to how the temporary tables are being used, I agree, that's the likely cause of the missing information.

                               

                              Thanks so much for your assistance.

                               

                              Glen

                      • Re: Query Execution Count in DPA
                        m60freeman

                        FYI, I see blanks when looking at either a one hour interval or a one day interval and click on the hash (or name for ones with names) in the SQL tab. It's only when I pick a hash to Analyze that the data is actually present and not just the labels.