cancel
Showing results for 
Search instead for 
Did you mean: 
Create Post
Level 8

Automated SQL query to test performance

I know what I want to accomplish, I am not sure how I can and if DPA has the capability. What I would like is to have a simple test query to run on every SQL instance once a minute and have it logged as a metric, I want to be able to baseline performance and get alerted on slowness without any applications being involved. Any help would be appreciated.

0 Kudos
4 Replies
Level 14

A custom metric is limited to a numeric value returned. Having said that, there is already a default metric called Round-trip Time that measure a response from a "SELECT 1" in ms.

Are you thinking something along those lines?

This would be an indication of network latency though from the DPA server to the monitored instance.

For "slowness" within the database, that could come in the form of many causes that may or may not get "caught" by your query.

Let me know thoughts on this, but I do think we can get you to where you want to achieve.

Appreciate the response.

Here's a scenario, we have a SQL cluster, physical box sql1 is hosting clustered instance vsql1 and vsql2, vsql1 is hosting a inventory db, vsql2 is hosting a shipping db. Now shipping starts complaining that their app has slowed down, I would like to know if there is a issue on the physical level, in which case the test query time for both instances would be impacted, or is it on the instance level (for example the max memory is not sufficient or there's an issue with the underlying storage for this instance), in which case the test query would have a different result for the 2 instances, or perhaps its not a resource issue but a db issue (for example it's not indexed properly) or an issue between the app and the db, or with the app server itself, in these cases the test query running against a system db would not be impacted at all.

I don't expect this test query to be able to pinpoint exactly where the issue lies but to tell me which direction to look. Also, this will allow me to setup an alert if the query takes longer than usual (assuming it is an issue with the SQL server) and not have to wait until the I hear complaints that the app has slowed down.

Thanks. Looking forward to your reply!

0 Kudos

There is a built in alert in DPA that allows you to receive notification if a specific SQL statement is experiencing longer than normal wait time (taking longer than normal).

If you can identify a sensitive query that could act as the canary in the coal mine, you wouldn't have to come up with a synthetic one. DPA will watch it OOTB.

Go to Alerts -> Manage Alerts -> select Wait Time radio button -> "Average Wait Time for a Single SQL".

You could even choose your canary for each database and run two alerts.

Let me know if that fits your needs. I'd think that choosing an actual query that would be a proxy or indicator for when end-users feel slowness would be ideal.

If you are running a 3rd party app, you can also reach out to the vendor and ask them which queries for the app are sensitive to slowness that will be felt by end-users and use that query...

HTH

Thanks for the info. This looks like something that I can make work. Will play around with it and let you know if I can use further help. Appreciate!