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

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

Children
No Data