How do you track performance of [top] SQL queries over time?

Matthew_Sontum

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.

squigley

Hello Matthew,

Thanks for the update on your end.

Can you try the steps from the below article to see if that helps?

https://redgatesupport.zendesk.com/hc/en-us/articles/360009784854

squigley

Thanks for your inquiry with Redgate!

If you click into a top X query and scroll down, there is a Query history section where you can see the history of the different metrics.
​

Matthew_Sontum

I am already aware of that method, and have used it in the past. That method works well when a particular query is already on my radar. What I'd like is more along the lines of a report that lists the top 10 queries and stored procedures whose average duration has increased over the last week. I don't want to click individually on each top 50 query I suspect may have gotten worse over time.

And again, I am mainly concerned about relative performance, queries whose performance has slipped, not just the top 50 now.

Matthew_Sontum

Also, on a related note, how do you turn the _querytext column in data.Cluster_SqlServer_TopQueries_Instances into plain text?

Matthew_Sontum

Thanks for the utils.GZipToString. I should be able to use this to build my own query. I am still hopeful that there is a feature built into the product that can do this, or will be in the future.

Russell D

I'd recommend posting it on the SQL Monitor Uservoice here: https://sqlmonitor.uservoice.com/forums/91743-suggestions in order to help us prioritise any future work.