SQLMonitor 2.1 - High CPU Utilization

jalfano

We have been running a trial of SQLMonitor for the past week or so and are seeing that the process called Redgate.Response.Engine.Alerting.Base.Service is consuming a lot of the CPU time on the server.

In our main test environment it is consistently utilizing about 85% of the server CPU. The server we are using for testing is a VM provisioned with a single CPU running at 2.83 GHz. We've dedicated this server to testing this product and the repository is located on a separate server on the same network.

Even when I run the base monitor on my local workstation machine it seems to have similar behavior. It's running a consistent average of about 45% cpu on a dual 2.4 GHz intel Xeon processor.

Is this typical behavior that should be expected?

jalfano

Here's a sendspace link to a saved, short ANTS profiler trace of the base monitor service on my local machine. I also included the html output.

http://www.sendspace.com/file/s0o579

RBA

Hi Jalfano,

SQL Monitor will use CPU resources in proportion to the number of monitored entities (and their complexity). It does a fair bit of work per entity so what you're seeing is to be expected for a reasonable deployment (15+ machines, many SQLs, many databases).

I'll have a look at the profiler trace.

How many machines are you monitoring and how roughly many databases are being hosted on them?

Regards,

jalfano

RBA,

Right now we're just testing so we have 6 servers on one test environment with about 50-120 databases which would be similar to production (except in production we'll have about 20 servers) and we have another test environment with about 10 servers and about 10-25 databases per server which is more similar to our dev environment.

The databases are all fairly small (< 8 GB) but they have a large number of objects in them (procedures, tables, etc...).

So I think once we purchase we're looking to get about 30 servers monitored.

The trace I sent you was for the smaller environment that mimics our testing environment but i'm seeing similar CPU usage on both.

RBA

Hi,

To monitor 30 servers you will need a dedicated machine with more than one core.

A test server of mine has 23 machines, 39 SQL instances and uses about 85% of an Intel Core2 Duo 2.66GHz.

I suspect that your test deployment is working as intended given a single core and the desired work load.

Sql Monitor logs its behaviour during operation (Documents and Settings\All Users\Application Data\Red Gate\Logs\SQL Monitor 2 on XP or in ProgramData on Vista/Win7/2008). If there is a problem with the deployment, you may see errors in the logs (they'll be full of informational messages, but errors will be searchable with the word 'error').

Regards,

PDinCA

I'm monitoring ONE SQL Cluster with only EIGHT User databases, RS and a Distribution DB and I'm between 44% and 69% when I ask SQL Monitor to do "anything"
This is shocking!

I wait for MINUTES to get a response and often get none, just a progress bar in the IE8 window that's stuck.

The machine is a 64-bit VM with 2 x 2.26GHz CPUs and 16GB memory and the User databases are rarely referenced by a UI as this box isn't made public yet and there are only four folks who would even be on it. All that's going on is a simple every-minute suck of production data into a single table that's then materialized into an Indexed View then replicated to the Report DB - job takes 45 to 50 seconds of each minute. sqlserver is running at a VERY constant 9% to 20%. RS is barely touched and has no scheduled reports.

We're moving the IIS7 website off into the DMZ but that's hardly likely to be a major impactor when the Alerting.Base.Service is sucking up so much CPU while monitoring probably the simplest cluster any reader of this post could imagine.

What's Up, please? Instead of the joy of finally having "eyes on the server", I have to endure non-responsiveness all the time... I will persist with the Monitor as it's all I have and going to predecessor-brewed scripts with unintelligible-to-me output is untenable. As noted in another thread, Quest's Spotlight on SQL Enterprise doesn't have these performance issues - I agree, having been a very satisfied user of that product for over a year at my prior job and I truly wish the current Company had the funds to buy it... Sorry to be a downer, but realism is warranted.

Update 1
This is crazy! I tried to clear a "Long-running query" alert and it took THREE MINUTES to change the status. I have 36 Alerts to clear - and I don't have 1.5 HOURS just to clear this piffling backlog...

I threw Idera's Quick Reindex at the database that has only been on the machine for three weeks and already shows, for indexes over 2 pages with a purge setting of 1 week across the board, an AVERAGE fragmentation of 54%. The worst frag% was 99.76% on the 820 page Cluster_Machine_Network_UnstableSamples_Id_CollectionDate index, just edging out two further "UnstableSamples" indexes at 99.66% over 693 pages and 99.56% over 1,354 pages. 34 of the 88 indexes are over 75% fragmented, a further 30 are between 25% and 75% and the remaining 24 are under 25% of which 18 are under 10%. Is there a need for some "regular maintenance" on the database that Red-Gate could either incorporate as an "automatic feature" or provide as some kind of Maintenance Plan that we can leverage...? After rebuilding all 88 indexes, the average is down to 22.61% fragmented, with 8 at 75% to 87.5%, 27 between 25% and 75% and the remaining 53 under 25% of which 51 are below 10%, 39 below 1% and 18 at 0.00%.

Would a tech at Red-Gate please take an interest and run some diagnostics so SQL Monitor 2.1 becomes a usable product.

RBA

Hi PDinCA,

Yes, that is quite shocking and certainly not what I would expect to see.

I'll be looking into my own repositories to see if I can reproduce performance issues with index fragmentation.

We have less powerful machines that have been monitoring more servers with months of data, so I'm hoping that the bottleneck on your system will be resolvable.

I'll send you an email later today.

Regards,

jalfano

RBA,

We are very pleased with the product but we are also seeing that this CPU issue is very bad. Are there some steps we can take to troubleshoot or at least provide your team with the necessary information to investigate some of this?

I ran the ANTS profiler on the environment that's monitoring our dev servers and saw (what i thought was a lot) of exceptions being thrown.

Our other environment was pegged at 99% CPU almost constantly after running for about a week and then when I tried to stop the base monitor process it crashed.

I think if we're going to move forward on this product we'd need to know that this CPU issue was being seriously looked at and there are some hard steps being taken to resolve some of it.

Let me know what information I can provide you to start investigating these issues.

Thanks!

RBA

Hi,

I've looked at your ANTS profile run and have found at least one area of contention that may well be addressable.

I'll try and get an optimisation in for 2.2 that will easy things up for your configuration.

Thanks,

Robin

PDinCA

If there's something I can put in place to help you profile why displaying a "Long-running query" (LRQ) alert takes minutes, and clearing it takes minutes, and why the regular expression to exclude "EXECUTE master..sqlbackup" from popping out a LRQ Alert doesn't work, I'm more than happy to be a guinea pig...