Possible to monitor excessive timeouts?

paschottpaschott Posts: 71 Bronze 4
in Redgate Monitor
We have a couple of apps that irregularly, but often enough to be problematic, start suffering from timeouts. Is there an easy way to get alerted for those cases when queries start timing out?
Tagged:
· Share on Twitter

Best Answer

  • Alex BAlex B Posts: 1,158 Diamond 4
    Answer ✓
    Hi @paschott,

    For the long running query you can't fine tune it to a specific user unfortunately, though you can exclude queries by regex from being alerted on - so you could customize it at the instance level and exclude the maintenance or load queries and set the threshold to be 15 or 30 seconds.  This would still pick up ad hoc queries though.

    If it was a job, then the job duration unusual would fire, but if it's just a query, which it sounds like it is, that wouldn't help there.

    If you have the timing of when the query occurs you could set the server overview focus window to cover that time and see if the query shows up in the Top x queries (be sure to sort by each column header as it populates a different set of queries based on the column it's sorting on).  See https://documentation.red-gate.com/sm9/overview-pages/using-performance-diagnostics for more information on that, though whether it may help I'm not sure.

    Kind regards,
    Alex
    Product Support Engineer | Redgate Software

    Have you visited our Help Center?
    · Share on Twitter

Answers

  • Alex BAlex B Posts: 1,158 Diamond 4
    Hi @paschott,

    It would depend on the circumstance of the timeout - if the application is trying to run a query and the query is taking a long time to run and the application times out because of this, then SQL Monitor may raise an alert for a long running query, but if it's a connection issue that wouldn't necessarily show up.

    There is also this new Simple Talk article on how one could use SQL Monitor to monitor an application that may be of interest: https://www.red-gate.com/hub/product-learning/sql-monitor/monitoring-the-application-with-sql-monitor-website-activity

    Kind regards,
    Alex
    Product Support Engineer | Redgate Software

    Have you visited our Help Center?
    · Share on Twitter
  • paschottpaschott Posts: 71 Bronze 4
    In this case - just over 30s of query time that is timed out when the query normally runs subsecond. There are a handful that do that and some logins we'd likely exclude that only do maintenance or ETL loads or the like.  So - kind of a long-running query, but a little different.  I wouldn't want to pick up someone running an ad-hoc query, just long-running queries from a specific account.  Ideally then only when it's a certain number of long-running queries within a given time range.
    · Share on Twitter
Sign In or Register to comment.