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.

Parents
  • 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.

  • 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

Comment
  • 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

Children
No Data