Include Top N Worst Performing Stored Procedures and Functions
At my organization, we do a lot of tuning at the stored procedure and function level.
I would like a tab similar to the existing top 10 queries tab, that lists the CPU utilization, memory utilization and IO consumption of the top 10 worst performing stored procedures, and another tab that lists the top 10 worst performing functions. For each of the fields I would like to see both the totals, and the percent of total during the specified time period.
This will allow us to identify the stored procedures and functions to tune, and once tuned, it will allow us to see the performance improvement as a result.
The current top 10 queries tab is somewhat helpful in this regard, since the worst performing stored procedures often have some of the worst performing queries, but it isn't a 1-1 relationships. A lot of the worst performing stored procedures are problematic as a whole -- each query is efficient, but they are thousands of lines long and perform unnecessary steps. And the functions don't appear at all, since the same functions are reused across dozens of queries, we can't tell from the 10 top queries tab which functions are the problematic ones.