Find Queries / Stored Procs whose Performance has Decreased Over Time
In preparation for changing the database compatibility level for our primary database to 2014, we would like to see which queries and stored procedures are performing poorly after the switch is made in the staging environment. While I can do this to a limited extent by looking at top queries in Red Gate Monitoring, I was wondering if there was an automated way to alert on this. For example, alert on any stored procedures / queries that appear in the top 100 costliest queries by duration / CPU usage, whose average duration / CPU usage has more than doubled since a particular date (in this example, the date of the cut over)
In the general sense it would also be helpful to have this type of alerting in place at the stored procedure level, to see if any change in stored procedure functionality led to a significant decrease in performance.
Simon Wong commented
I would like to see an option for me to monitor critical SP and queries with pre-defined threshold so we can find those with performance degraded over time.