Consumption statistics / Exporting Alert Settings

jtrumbuljtrumbul Posts: 6
edited February 17, 2016 6:38AM in SQL Monitor Previous Versions
Hello All,

Has anyone had an issue with the consumption stats posted by the dev group being roughly 10x off in just about every category? One example "Total network traffic (inbound plus outbound) is 10 KB/sec per server." I am at a little over 100 kbps. Monitoring 2 servers with single instance on them. single AG running with one database on it. Here are the specs and setup:

Server - MS Server 2008 R2 Standard / SP1
Processor - Intel E5-2665 0 @ 2.40GHz - 2.40 GHz
Ram - 8GB
System Type - 64 bit OS

Base monitor and web ui installed on the same machine. I am monitoring 2 of these servers. The server contains a single availability group with one database that is very high volume OLTP. This is a re-installation where I am currently adding back all of the alerts slowly as it stands right now there are only 10 alerts on. (no AG alerts) No custom metrics.

AG Setup is as follows:
AG1 - db1 (read/write) -> db2 (read only) I still have 2 more production servers with about 30x more throughput to monitor that need to be added back ASAP. Can anyone tell me is there something i should or should not be doing? perhaps removing monitoring on the system databases (one or all) seems bad but is it common?

The second question if the above answer is yes, I will need to remove the AG and add it again, is there an easy way to export my current alert settings, and re add them after I re-add the server. I am currently getting memory pegging several times a day. very high latch waits, the longest running queries and highest consumption processes are coming from SQL Monitor. I need to be able to manage this and its very urgent. Any suggestions appreciated.

Dr. John Trumbul
skype : john.trumbul.jr

Backups? where we're going, we don't need any backups.

Comments

  • Alex BAlex B Diamond 1 Posts: 773 Diamond 1
    Hi Dr. Trumbul,

    First, as a disclaimer, the Hardware and performance guidelines is based on testing at Redgate. The performance might be different in your environment.

    Second, what version of SQL Monitor 5 are you currently using? You can find this by going to Configuration > About.

    Also, I'm slightly confused by your statement - You say you have base monitor and UI installed on the same machine, but then say you are monitoring two of these servers. Is it that you have Base Monitor and UI on machine X and you are monitoring machine Y and machine Z, Y and Z having the single AG with one db that is high volume OLTP? Is the data repository (RedGateMonitor by default) for SQL Monitor on either of the servers Y or Z that you are monitoring?

    Disabling the Alerts does not disable the collections for the alerts, so the data will still be gathered and therefore still taking up the bandwidth. We have a small unsupported utility for disabling the actual collections if you would like to try that. I can provide the details for it in the call that was raised for you if you would like.

    I can't really comment on the setup of the AG, but you won't be able to disable the monitoring of specific databases, the monitoring is controlled at the instance level (i.e. what is added to SQL Monitor) so you would need to suspend the instance to stop monitoring of specific databases. In that vein, the only thing you could do would be, with the above mentioned utility, to disable the database collections which would affect all databases for all monitored entities.

    In answer to your second question - there is not currently an official way to import the alert settings. I think there may be a script somewhere that could help with this, I will try to find it and send it to you via the call that was raised, but given the above - if you are turning off/removing the alerts to affect the collections it will not help to remove or disable the alerts.

    Kind regards,
    Alex
    Product Support Engineer | Redgate Software

    Have you visited our Help Center?
  • Hello Alex,

    Thanks for the prompt response. I am sorry for the delay in my response, as we had a holiday at the office and for once in my life as a DBA i got to sleep a bit.

    Here are the answers to your questions:

    I am currently running SQL Monitor 5.0.7.2391 My apologies, I thought i posted my version in the initial post.

    What I meant by my setup details is this:
    Server A->RG BaseMonitor->IIS->RGWebUI->SQLServer->RedGateDb

    Server B->AG1 being monitored. There is a single database on a single SQL instance that is the primary DB of AG1. Subsequently there is another server Server C-> with the secondary which is a read only replica of the AG

    That is on the servers / AG being monitored no parts of SQL monitor are installed there. Does that paint a more clear picture?

    The last question about removing system databases, I was more just asking what the standard practice is. For example, I do not see a reason to monitor the "Model" system DB. If it were to go down, we would know about it in other ways rather than constant pinging to see if its alive. I know in SQL Monitor I can add the instance names to monitor. i.e. SERVERBSQLNAMEDINSTANCEFOODBBar as opposed to adding the AG where all the databases including system on that instance get pulled in. I was more just asking what common practice was, and if there was any noticeable difference to doing things this way?

    The script and tool would both be great. I am not sure which i will use. The script, only if I am planning on adding a database back one at a time ignoring the system databases. The tool is likely the way I will go. That is once I add back the other servers I need to monitor. If the stats are crazy, I will go from there.

    Thank you for all your help.
    -John

    Dr. John Trumbul
    skype : john.trumbul.jr

    Backups? where we're going, we don't need any backups.
  • Alex BAlex B Diamond 1 Posts: 773 Diamond 1
    Hi Dr. Trumbul,

    Thanks for the details! That does clarify the setup, thank you - it was to ensure you were not monitoring the SQL Monitor db (data repository) as this can cause/increase performance issues when there is a lot of data coming in (sort of like a feedback loop).

    I replied on Monday to the call that was raised with details for the Configuration Editor and also included the scripts as well - it will be to the email that was used to create your forum account so if you are using a different one please send an email in to [email protected] mentioning this forum post and the configuration editor and we can get the information to you.

    In SQL Monitor as of v5 you can no longer specify the Instances you wish to monitor; all instances are automatically discovered along with the cluster if you have entered a single node of the cluster and any AG's related to the cluster. All of the databases for each instance will be (and as far as I know have always been) added and there would not be a way to pick and choose what is monitored by SQL Monitor.

    Kind regards,
    Alex
    Product Support Engineer | Redgate Software

    Have you visited our Help Center?
Sign In or Register to comment.