Red Gate forums :: View topic - Long running query
Return to www.red-gate.com RSS Feed Available

Search  | Usergroups |  Profile |  Messages |  Log in  Register 
Go to product documentation
SQL Response 1
SQL Response 1 forum

Long running query

Search in SQL Response 1 forum
Post new topic   Reply to topic
Jump to:  
Author Message
chambem9



Joined: 28 Jun 2010
Posts: 1

PostPosted: Mon Jun 28, 2010 4:09 am    Post subject: Long running query Reply with quote

Hi guys,

I have just installed SQL Response. The first thing I tried was to execute a long running query to see if I would receive an alert. I tried a few different long running queries, such as:

select * from a_complex_view (13 seconds, 11 rows returned)
select count(*) from a_big_table (19 seconds, counted 78055990 rows)

but none of these queries raised an alert. I have left Response with the all the default settings, the threshold for long running queries remaining at 10 seconds.

I do receive other errors, namely for low memory, so SOME are working. but I want to know why the long running query alert is not being raised.
Back to top
View user's profile Send private message
Anu Deshpande



Joined: 20 Apr 2009
Posts: 662
Location: Cambridge

PostPosted: Tue Jun 29, 2010 11:22 am    Post subject: Reply with quote

Thank you for your post

SQL Response does not send an alert for every alert generated. It will only send an e-mail the first time that alert is generated, until that particular alert has been cleared. For example take this scenario,

The Long Running Query alert is generated for the is first time, (providing the e-mail notifications have been configured) an e-mail is sent and successfully received. Another 9 Long Running Query alerts are generated, however no e-mail notifications will be sent as the alert has not been cleared. The user now clears all the Long Running Query alerts. later in time, another Long Running Query alert is generated, this will send an e-mail notification as all the previous alerts have now been cleared. Any subsequent Long Running Query alerts will not generate an e-mail notification, until the all the alerts generated for the Long Running Query alert have been cleared.

A better explanation is available in the SQL Response help which I have copied below:

"SQL Response can send an e-mail to one or more e-mail recipients whenever an alert is raised.

An e-mail is sent when an alert is first raised, and whenever an alert changes from cleared to active status. If you do not clear an alert, no further emails will be sent for that alert, regardless of any additional occurrences of the alert.

No emails are sent for recommendations."

I hope the above answers your question and also let us know if you do not receive e-mail even after clearing the alerts also send us the screenshot of the alert screen.
_________________
Anuradha Deshpande
Product Support
Redgate Software Ltd.
E-mail: support@red-gate.com
Back to top
View user's profile Send private message Send e-mail
Ian Gill



Joined: 30 Jul 2010
Posts: 2

PostPosted: Fri Jul 30, 2010 10:27 am    Post subject: Reply with quote

Hi

I have the same question as chambem9.

I have read Anu's reply but it doesn't seem to answer the question. Anu's answer explains why an e-mail alert will only be sent for the first alert, but it does not explain why the alert itself is not created.

I have the alert setting at 10 seconds. I run a query that takes 15 seconds. No alert is created. I have not configured the e-mail notification and that is not a problem for me - I am just looking at the alerts generated in SQL Response.

I have also read the following thread.

http://www.red-gate.com/MessageBoard/viewtopic.php?t=11394&highlight=long+running+query

This explains how often the system checks for long running queries, but it doesn't explain why a query that runs for longer than 10 seconds does not generate a query.

I do get some alerts for long running queries, but these all related to queries running for at least 45 seconds.

What am I doing wrong, i.e how do I ensure that I get an alert for any query that runs for more than 10 seconds?

Ian
Back to top
View user's profile Send private message
priyasinha



Joined: 03 Jan 2007
Posts: 530

PostPosted: Wed Aug 04, 2010 2:24 pm    Post subject: Reply with quote

Hi,

You are not getting Long Running with duration of 10 seconds because by default we poll only every 45 sec (for Long Running Query). So for a query to qualify for 'Long Running Query' alert it should ideally pass at least twice when we poll. So SQL Response can at best catch a query of duration 46 sec but it will always catch anything which is running for more than 90 sec (which is double the polling frequency). E.g. if you always want to catch a query which is of duration 40 sec or more then you have to reduce the polling frequency to 20 sec.

You can look at the config.xml (which is in install directory where you have installed your Alert Repository). This is by default should be at this location C:\Program Files\Red Gate\SQL Response 1\Alert Repository. Then look at this particular section:

<!-- This will affect the polling frequency for these alerts; Long running query and Blocked SQL Process - setting to every 45 seconds-->
<SysProcessesTime>45</SysProcessesTime>

You can change this to modify the polling frequency. But I would not recommend to set this to a very low value as this will have impact on the performance of your server.

If you want to test SQL Response for a Long Running Query then may be the easiest thing would be to use a waitfor delay statement. Something like

Code:

Waitfor delay '00:02:00'


Thanks,
Priya
_________________
Priya Sinha
Project Manager
Red Gate Software
Back to top
View user's profile Send private message
Ian Gill



Joined: 30 Jul 2010
Posts: 2

PostPosted: Wed Aug 04, 2010 2:38 pm    Post subject: Reply with quote

Thanks Priya

I see you repeated the answer that was already on this thread Wink

Anyway, I do now understand the position. Providing an option to "configure" these "long running query" alerts is very misleading. I think that when you try to configure the alert you should be warned that unless the .xml file is changed then the minimum query length to trigger an alert is 45 seconds.

Also, for any other novices (like me) reading this thread, note that you have to stop the SQL Alert Service and restart it for the changes to the .xml file to take effect.

Ian
Back to top
View user's profile Send private message
priyasinha



Joined: 03 Jan 2007
Posts: 530

PostPosted: Wed Aug 04, 2010 3:01 pm    Post subject: Reply with quote

Apologies Ian for copying the answer. I remember it writing on forum sometime back so copied it from my old answer!! 'Embarassed'

Yes, you are correct in saying that it is not clear. Ideally, we shouldn't let user configure anything which is less than our polling frequency. We will improve this in V 2.0.

Regards,
Priya
Back to top
View user's profile Send private message
jschoen



Joined: 03 Nov 2010
Posts: 3
Location: NL

PostPosted: Wed Nov 03, 2010 3:56 pm    Post subject: false alerts Reply with quote

When I activated SQL Response to our server i got loads of long running query alerts. After experimenting I found out that during a single logon onto the sql server, I did a 30 sec query, and within a minute again a 37 query, I got an alert for 1.07 long running query.
So the monitor actually does a calculation between the 'Login time' and 'Last Batch' time. Which in our case isn't representive as the query running time, because the software often doesn't close it's connection with the database. This results in false alerts. Don't think these alerts are usefull for our environment ?

Thanks a lot!
Jeroen
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