Query Question
DonnyD1974
Posts: 2
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.
What I would be looking for it
Server:
Time raised:
Time ended:
Query duration:
SQL process fragment:
Thank you any help in advance.
Comments
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.
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.
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
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.
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