Red Gate forums :: View topic - Query Question
Return to www.red-gate.com RSS Feed Available

Search  | Usergroups |  Profile |  Messages |  Log in  Register 
Go to product documentation
SQL Monitor 4
SQL Monitor 4 forum

Query Question

Search in SQL Monitor 4 forum
Post new topic   Reply to topic
Jump to:  
Author Message
DonnyD1974



Joined: 16 Jun 2014
Posts: 1

PostPosted: Mon Jun 16, 2014 5:14 pm    Post subject: Query Question Reply with quote

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.
Back to top
View user's profile Send private message
JoeGT



Joined: 13 Jun 2012
Posts: 40

PostPosted: Mon Jun 16, 2014 8:28 pm    Post subject: Reply with quote

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.
Back to top
View user's profile Send private message
Colin Millerchip



Joined: 31 Oct 2007
Posts: 65
Location: Cambridge, UK

PostPosted: Wed Jun 18, 2014 5:56 pm    Post subject: Reply with quote

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.
Back to top
View user's profile Send private message Send e-mail
LarisaD



Joined: 27 Aug 2014
Posts: 1

PostPosted: Wed Aug 27, 2014 3:49 pm    Post subject: Reply with quote

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/viewtopic.php?p=57357&sid=0e52b0a2e384cec6ab4cf6458c03f49d . 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
Back to top
View user's profile Send private message
Display posts from previous:   
Reply to topic All times are GMT + 1 Hour
Page 1 of 1

 
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group