Automated SQL query to test performance

Question

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.

gershonb · Answer

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!

mandevil · Answer

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

gershonb · Answer

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!