Long running query

chambem9chambem9 Posts: 2
edited November 3, 2010 10:56AM in SQL Monitor Previous Versions
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.

Comments

  • Anu DAnu D Posts: 876 Silver 3
    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
  • 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/vi ... ning+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
  • 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
    Waitfor delay '00:02:00'
    

    Thanks,
    Priya
    Priya Sinha
    Project Manager
    Red Gate Software
  • 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
  • Apologies Ian for copying the answer. I remember it writing on forum sometime back so copied it from my old answer!! ':oops:'

    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
    Priya Sinha
    Project Manager
    Red Gate Software
  • 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
    "The significant problems we face cannot be solved by the same level of thinking that created them" - Albert Einstein
Sign In or Register to comment.