Open for Voting

Group individual statements/batches in stored procedures

It is nice to report on the execution characteristics of all the statements/batches in a stored procedure.  The individual hashes have to be selected.  We make this a little easier by our naming convention.  The first part of the name identifies the procedure and the last part contains the statement begin and end positions.  DPA should be able to identify all the statements that are included in a stored procedure and present that as an option to include in SQL reports.

  • One of the biggest advantages of DPA is it's ability to tell a story to management.  It is simple to explain the value of what items to work on. Where it loses in that story telling, is showing the results of tuning a particular statement.  The overall graph may go down, but correlating the before and after is difficult for an individual statement, especially when a tuned proc completely drops off the top 25 list.  For example, I just released a new version of a proc.  The old one averaged 5 secs/execution for 2500 a day.  During peak periods, this was 6% of total wait time.  The new version averages 100 milliseconds per execution and has completely fallen off the charts.  I had to expand the # of queries in the charts to get the new version to show up so I could run the historical analysis.  And the result is 2 charts that have different scales, which does not have the same visual impact.


  • hi,

    I was about to create a request for this feature and the request is already here.. emoticons_happy.png

    I definitively need this feature.

    Other tools like "oraChrome" have already this feature (see picture).

    this allow to highlight all sql started by a package..Capture.PNG


  • Having DPA create the names, and allowing for grouping/versioning would be a huge help. I ended up coming up with a naming convention as well, but often run out of characters to adequately name an item. If the name field could be longer, or have a sub-field for the code section/version that would be helpful.

  • I agree that "grouping "similar" statements as one" is a great idea. I am going to paraphrase a quote from mandevil (Aug 24, 2016 10:51 AM, Historically Associate Queries):

    Recommendation: You can create a report that will look for specific SQLtext (some identifying pattern) that will pull back all of the hashes that match that pattern.

    Now, to actually change the hash in the repo would not be recommended as there are a LOT of dependencies that don't necessarily follow RI (think summary tables, dimensions, plans, etc.).

    We have a lot of the "same" query, but various parts of the "filter"/WHERE Clause are different (date parameters, perhaps an additional filter), but the query is fundamentally the same. For analysis, we would like these queries to all have the same name so we can focus on the queries that are causing the highest waits, etc. on our systems.

    As a bonus, it would be nice to be able to apply the same naming across multiple servers.

    Thanks! emoticons_happy.png

  • I echo the comment by ericp.  Reporting at statement level is great but it doesn't allow you to track the performance of a stored procedure over time. When changes are made to the application/database it would be good to answer questions like what SPs are running slower/more frequently/consuming more CPU/IO etc.  We've had to supplement DPA with our own monitoring using Extended events to be able to answer these types of questions - it would be great if DPA did it all though.