cancel
Showing results for 
Search instead for 
Did you mean: 
Create Post

Group individual statements/batches in stored procedures

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.

10 Comments
Level 7

Having the ability to arbitrarily group hashes together and have DPA treat those as a single "query" would allow for this and also some other flexibility when you may want to group up statements that are all related.

Level 11

Thanks for the feedback Brandon.  We appreciate it!!!

Kathy Gibbs

Level 9

I think grouping similar statements is an excellent idea.  It could be combined with filtering. a "Group Filter" could be defined as a like expression or regular expression. A checkbox on the Group filter "automatically add new statements" would set the group field on a new statement if it was blank and it matched a filter.  The Group Members within a time period would be excludable or includable as members of the group.  A Checkbox next to individual statements could help with that and then "Apply to Selected".  the net effect of this would be to set the "Group" field on the the dialog that allows the SQL Name to be edited.  Also, a new group name could be added or modified there. The trend display "Show Groups" option would aggregate statistics for a group of statements into a single bar block and sort them according to the total.  The entire group could be included or excluded from the trend display.  Include basically means exclude everything else.

Level 8

It kills my ability to report historically on a stored procedure. Every time it changes I have to give it a new name. Really need a way to group these as being the "same" despite SQL server thinking they're different.

Level 7

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.

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!

Level 9

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.

Level 8

hi,

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

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

Rgds

Level 8

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.

BeforeTuning.pngAfterTuning.png

Community Manager
Community Manager
Status changed to: Open for Voting