Can I Get Analysis On Built-In Alerts

SQL Server performance monitoring and alerting

Moderators: eddie davis, priyasinha, Adam, chriskelly, Chris Lambrou, Chris Spencer

Can I Get Analysis On Built-In Alerts

Postby EdCarden » Fri Apr 26, 2013 6:31 pm

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
EdCarden
 
Posts: 90
Joined: Tue Nov 25, 2008 6:26 pm

Postby Brian Donahue » Mon Apr 29, 2013 2:02 pm

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.
Brian Donahue
 
Posts: 6670
Joined: Mon Aug 23, 2004 10:48 am

Postby EdCarden » Tue Apr 30, 2013 9:03 pm

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
EdCarden
 
Posts: 90
Joined: Tue Nov 25, 2008 6:26 pm

Postby chriskelly » Thu May 02, 2013 7:19 pm

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 Kelly
Technical Support Engineer
chriskelly
 
Posts: 330
Joined: Mon Apr 19, 2010 1:44 pm
Location: Cambridge, UK

Re:

Postby EdCarden » Wed May 29, 2013 8:02 pm

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
EdCarden
 
Posts: 90
Joined: Tue Nov 25, 2008 6:26 pm


Return to SQL Monitor 3

Who is online

Users browsing this forum: No registered users and 0 guests