How to find out how often a specific alert executes?/impact?

TMowbray

We have sql monitor running against target servers and databases over a WAN and were surprised to find out from our network team the amount of transfer per day. How do we determine how frequently an alert executes, and some idea of which ones are more likely to have larger responses back from the remote database(s) ?

Adam W

Hi,

You can see how many times an alert has been raised (depending on purge settings) in Configuration > Alert settings in the Occurrences column.



You can see the most frequent alerts per server using the Alert Summary Report.



Is this the kind of thing you're looking for? Could you give a bit more detail about what you mean regarding "ones more likely to have larger responses"?

Thanks,
Adam

TMowbray

Thanks for the response - what I am after is how to potential fine tune the data collection that occurs perhaps as frequently as every 15 sec for example, as the amount of traffic to/from the sql monitor server to the monitored targets over the slow(err) speed WAN is substantial (2-3 GB) and a potential concern. Ideally there would be a way to find out how frequently the check/data-collection for a particular alert is executed, and create a custom one to execute less frequently.

Adam W

.

Adam W

Ah okay, in that case, you can view and edit the defaults for the different collections by using the Configuration File editor.

First, locate the configuration file on the machine where the SQL Monitor Base Monitor service is hosted. Location: C:\ProgramData\Red Gate\SQL Monitor
File Name: RedGate.Response.Engine.Alerting.Base.Service.exe.settings.config

Make a copy of this file and name it something useful like RedGate.Response.Engine.Alerting.Base.Service.exe.settings.config_backup
This will act as a backup of the configuration file so that your changes can be reversed.

Next, run the SQL Monitor Configuration Editor as an administrator (right click > Run As):
C:\Program Files\Red Gate\SQL Monitor\BaseMonitor\Tools\ConfigurationFileEditor.exe

You will be prompted to "Load a configuration file". Find the .config file detailed in step 1.

You will be presented with a list of all the configuration options available. These relate to each item in the polling schedule. Any check box which is un-checked automatically uses the default - to check what the default is you can check the box, but not make any changes and the value shown will be the default value.

If you make any changes, once complete, click "Save Changes" and restart the SQL Monitor Base Monitor service. If you have any unexpected results then please delete your edited configuration file and rename the copy (then also restart the service). If you are not making changes, just close without saving.

Does that help?

Thanks,
Adam

TMowbray

Thanks Adam - that is more what I might be looking for - since this applies to the base monitor service, I assume it affects all monitored targets - in this case we would want to selectively modify the collections/queries that generate the most network traffic, and ideally only apply those collections/queries for the active alerts for just the target instances that are over the WAN link...the local LAN targets we would want to keep as is - is this possible?

Todd

TMowbray

Adam - I found the config file and made a copy, and opened the config file in the utility - are there any recommendations for settings as the defaults look very frequent, in fact some have an interval of zero hours, minutes and seconds...so continually checking? examples: sql server top queries, sql server error log scanning, database file statistics, SQL user connections....these all seem to have default settings of zero for interval? Others like machine uptime every 15 seconds seems too frequent for this metric too.
The version we are using is 5.0.....and yeah, I know, we should upgrade...(smile).....I assume the repository ones are for the sql monitor repository db/instance, but what are the cluster ones for? I would be most interested in knowing the impacts of setting the intervals higher (other than reducing granularity in the graphs - that's obvious...) especially for the ones that seem to be continual.

Thanks

Todd

TMowbray

I should mention that the "preset" was greyed out....yet when I clicked on default pre-set , I now see the sql server top queries for example now saying interval of a min....almost looks like a glitch from the last upgrade maybe?

This might be explaining a lot - will explore the high-performance preset as well. Are there descriptions/details of what the various settings are doing/alerts they are for etc?

Adam W

Thanks for the additional detail - we'll get back to you with more information...

Russell D

I've moved this to previous versions as its for SQL Monitor 5.

To try and address the question though we don't really have any recommended settings for these. It's virtually impossible for us to know enough about the infrastructure of individual customers, so we don't really want to recommend best practices in this sort of area.

It's mostly a case of experimenting to find out what works for you. As you've mentioned, the granularity of the graphs will suffer if you turn down sampling frequency, but obviously the network usage will go down.

Things to bear in mind are the query related samplers pull query text across the network, eg the top query sampler for example pulls across the complete query text. And this can add up to quite a lot of traffic. In a lot of cases yes the defaults will cause too much network traffic for some, but for others it won't. It's impossible for us to know unfortunately.

It's worth bearing in mind that these sorts of questions are still present in later versions of SQL Monitor, and still need some experimentation on a users part to discover what works for them. Whilst we don't have any descriptions of what these various settings do, I do acknowledge that it's something we probably should document. This configuration editor though was never really intended to be used on a day to day basis, I think there was a plan at some point to integrate this sort of control into the UI, at which point of course we'd absolutely have to document it properly, but this hasn't yet happened (and may never will, I'll try and get some information on that).

TMowbray

a further add - on question - so in order to minimize the impact on the targets over the WAN, we could entertain a second instance of the base monitor...with a customized config file with only what we need to monitor - how would this share licenses with the current existing installation?

Russell D

You can activate the same licence twice, as long as you're running separate repositories. These two repositories cannot talk to each other though, so if you're happy running the sites separately this would be one route you can go down.

Your best bet here actually Todd could be Multiple Base Monitors (https://documentation.red-gate.com/sm7/configuring-sql-monitor/multiple-base-monitors). Your two sites (as basic example, MBM will work with any number of sites) would essentially stay separate, until you want to query things from the other site, at which point when you go to view information for SiteB (lets call it), the query text is pulled across the network, but little else. All the sampling data for example stays on SiteB, because thats where its BaseMonitor is installed. And as sampling data makes up a lot of the information, this could save quite a lot of bandwidth for you.

TMowbray

Thanks for the response. Further question would be does the local base monitor (storing locally), also execute the alert checks, or is that in the console on the primary server. IE where does the alert checking and notification actually run, within the base monitor+repository or within the console app?

We could handle having the site B all handled locally with no interaction to the main Site A as well I assume.


Todd

Russell D

Everything is done locally, on the BaseMonitor you add a server too.

TMowbray

Ok - that's good to know. So the only time the traffic would be pulled from the 2nd basemonitor/repository is for web console sessions.

I assume the documentation details what must be done to split a license or be able to register a license in each base monitor, assuming for license count reasons the monitors talk to each other...

Todd

Russell D

I think to split a licence you'd need to contact Sales. The documentation otherwise covers everything you'd need to know about MBM.

RichardL

Hi @TMowbray

Thanks again for your post.

Just to follow up on @Russell D 's comment, you can contact our sales team on sales@red-gate.com