Options

Can I Get Analysis On Built-In Alerts

EdCardenEdCarden Posts: 137 Silver 2
edited May 29, 2013 3:02PM in SQL Monitor Previous Versions
When in the ANALYSIS tab I can see my own custom metrics and get a chart of the history of the alerts occurrence. Where can I get the same for the built-in alerts like the LRQ (Long Running Query)?

Thanks
· Share on Twitter

Comments

  • Options
    Brian DonahueBrian Donahue Posts: 6,590 Bronze 1
    Hello,

    Metrics in SQL Monitor are always samples of numeric data taken over time. You can't logically create a metric from an alert, which is a notification about a particular state at that moment on the server. You could do something like create another custom metric like average query execution time (if something like that is available). If you can be specific about your needs, I can probably come up with something.
    · Share on Twitter
  • Options
    EdCardenEdCarden Posts: 137 Silver 2
    Thanks for the offer Brian.

    What I’d like is a simple aggregation of LRQ's (LONMG RUNNING QUERY's) over 2 time periods (comparing the 2 if possible) to show a drop or an increase in the number of LRQ's between the 2 periods.

    In our environment we use an enterprise level accounting software application that has a higher than normal occurrence of LRQ alerts (normal being how many LRQ alerts it would take for most DBA's using SQL Monitor to say "Man that’s a lot of LRQ's") . I'd like to compare the number of LRTQ alerts this past month to the same month last year when we were operating under our old DB server. I can’t do that because there’s no LRQ alert on the Analysis tab.

    That said this may all be a moot point as it appears that you can’t access information stored in your Red gate DB for any SQL Server instances you are no longer actively monitoring. When we switched to our new DB server it came with a new name and so we had to switch in SQL Monitor the SQL Server instance we were monitoring. Now that we’ve switched over I can no longer retrieve data on anything captured from the old server e3ven though the info is in my DB (I have never purged the DB).

    If you have any suggestion son how I can get some basic counts on LRQ's that would be great but keep in mind it would need to work for a SQL monitor we no longer monitor.

    Thanks
    · Share on Twitter
  • Options
    chriskellychriskelly Posts: 378
    You could query the view alert.Alert_Current and filter out the LRQ alerts, where the AlertType = 12. This would get you some of the way there. But this would only work for alert that have not been purged yet. My suspicion is that you will find that the data for your older database has been purged.
    · Share on Twitter
  • Options
    EdCardenEdCarden Posts: 137 Silver 2
    chriskelly wrote:
    You could query the view alert.Alert_Current and filter out the LRQ alerts, where the AlertType = 12. This would get you some of the way there. But this would only work for alert that have not been purged yet. My suspicion is that you will find that the data for your older database has been purged.

    Chris,

    The data is still there and I was able to do some rough comparisons with the following changes to your suggestion:

    SELECT --TOP 1000 *
    Count(AlertId),
    Left(TargetObject,30)
    FROM alert.Alert_Current
    where 1 = 1
    AND AlertType = 12
    AND WorstSeverity = 3
    AND TargetObject LIKE'%MyOldDBserver%'

    GROUP BY Left(TargetObject,30)


    SELECT --TOP 1000 *
    Count(AlertId),
    Left(TargetObject,30)
    FROM alert.Alert_Current
    where 1 = 1
    AND AlertType = 12
    AND WorstSeverity = 3
    AND TargetObject LIKE'%MyNewServer%'

    GROUP BY Left(TargetObject,30)


    I now just need to refine this by Date which will require using a UDF to convert the date values as stored in the DB into something human readable and I can compare the number of HIGH level LRQ alerts between the 2 systems.


    Thanks
    · Share on Twitter
Sign In or Register to comment.