Query Question

DonnyD1974

Is it possible to pull out all the long-Running Query Alert details from the DB Back end?

What I would be looking for it

Server:
Time raised:
Time ended:
Query duration:
SQL process fragment:

Thank you any help in advance.

JoeGT

Not to put a dampener on things here but I have a feeling that based on previous experience with this tool, the official RedGate answer is likely to be something along the lines of "we have a custom schema that is forever changing and therefore are not happy to give you information about running queries direct against the repository".

I will watch this with interest to see if this is indeed still the case. I too have in the past wanted to be able to mine information such as this direct from the repository. I guess it makes sense - if you allow your users to bypass the UI then the UI becomes potentially less useful and therefore harder to upsell the new features.

Sorry for the thread hijack and I hope you get the answer you are looking for.

Colin Millerchip

A quick comment on why we've not published the schema: it's nothing as nefarious as wanting to in some way protect SQL Monitor's value, but for the practical reason that it takes a lot of effort to manage a public schema, it introduces requirements like version controlling the schema (via public views) and can hamper future development (by introducing a restriction that we must try to ensure we don't break backwards compatibility).

We did publish a short series of SimpleTalk articles about the SQL Monitor schema (https://www.simple-talk.com/blogs/author/198217-chris-lambrou/). These haven't been updated for a while, but actually the schema has been fairly stable since these were written, so these articles should still be helpful in understanding the schema. I believe they will help you pull out some of the data you mention ('though query duration and SQL process fragments might be trickier).

Hope this helps,


Colin.

LarisaD

The series from Simple Talk is great, especially for introducing the function for "decoding" the collection date. I also found a helpful piece of code here: http://www.red-gate.com/MessageBoard/vi ... 458c03f49d . Still, it doesn’t answer the question regarding query duration, which I was not able to find it in the data repository, despite all my efforts.

In one of my servers we are monitoring, there are a lot of long running query alerts (up to 2000 per day) and I would like to change the configuration (to exclude some of the stored procedures which are constantly long running and we cannot improve them). I was thinking to use COUNT, with group by on query text, but I cannot find the query text as a part of the alerts tables presented by Chris Lambrou in his series.

Also, I would like to be able to prioritize the analysis of these alerts depending on how long the query takes, this is why I need to find the query duration in the data repository (we have to do such a prioritization taking in consideration the number of alerts raised).

If you decide to keep this policy of not publishing the schema, I think you should consider to introduce in the UI more options to filter or sort alerts.

Thank you in advance for your help,
Larisa

Colin Millerchip

Hi Larisa,

Thanks for the follow-up post. FYI, we have a UserVoice feedback site, at https://sqlmonitor.uservoice.com/, which would be a great place to post your suggestions and see what reaction you get.

BTW, are you aware of the configuration options for Long Running Queries, which allow you to exclude queries by SQL process name, and/or exclude queries that contain SQL commands or objects, based on regex matching: does this help with any of your needs?

Best regards,


Colin.

LarisaD

Thank you for answer, Colin!

Yes, I am aware of the configuration option you specified, I've use it for some queries . But in order to use it more effectively (to exclude the queries which lead to long running alert every time they run), I wanted to search in the data repository the queries which appear more often.

Thanks