Red Gate forums :: View topic - Can I Get Analysis On Built-In Alerts
Return to www.red-gate.com RSS Feed Available

Search  | Usergroups |  Profile |  Messages |  Log in  Register 
Go to product documentation
SQL Monitor 3
SQL Monitor 3 forum

Can I Get Analysis On Built-In Alerts

Search in SQL Monitor 3 forum
Post new topic   Reply to topic
Jump to:  
Author Message
EdCarden



Joined: 25 Nov 2008
Posts: 90

PostPosted: Fri Apr 26, 2013 6:31 pm    Post subject: Can I Get Analysis On Built-In Alerts Reply with quote

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
Back to top
View user's profile Send private message
Brian Donahue



Joined: 23 Aug 2004
Posts: 6673

PostPosted: Mon Apr 29, 2013 2:02 pm    Post subject: Reply with quote

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.
Back to top
View user's profile Send private message
EdCarden



Joined: 25 Nov 2008
Posts: 90

PostPosted: Tue Apr 30, 2013 9:03 pm    Post subject: Reply with quote

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
Back to top
View user's profile Send private message
chriskelly



Joined: 19 Apr 2010
Posts: 329
Location: Cambridge, UK

PostPosted: Thu May 02, 2013 7:19 pm    Post subject: Reply with quote

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
Back to top
View user's profile Send private message
EdCarden



Joined: 25 Nov 2008
Posts: 90

PostPosted: Wed May 29, 2013 8:02 pm    Post subject: Re: Reply with quote

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
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