Competition: What’s your favorite Redgate tool? Enter now.

Sql Response; High CPU Load

henkmeulekamphenkmeulekamp Posts: 14
edited October 13, 2008 4:48AM in SQL Monitor Previous Versions
I have installed the Sql response on machine A and it monitors machine A itself and B.

Config:
both machines have dual xeon, 4 GB ram; Win2k3, sql2k5 all SP en windows update patches.

Both machines have 2 sql2k5 instances, 1 running standby for the other, so A has around 8 active databases and B also. Using logshipping and some mirroring are these database synced with the second instance which contains 8 passive databases.

I installed sql response yesterday around 14 oclock, and CPU load was high on both machine (sql server on 80% on 1 CPU), mostly sql server and on A around 15% sql response service. I thought it was normall (collecting data, starting some sort of baseline), so did not pay any attention to it.

This morning I logged on to the machines and still the same cpu usage was shown. I checked the activity monitor on both machines and did not see any strange behaviour. I stopped sql response service on A, and all the cpu load went back to normal for this time of the day (6 am). (which is 5% max on all CPUs )

Our machines really need the power during the day, so I cannot spend 1 CPU (50% of the machines power) just for monitoring..

Is this normal behaviour? Is someone else also seeing this high load on the machines?

Is there a way I can check (log files, traces, profile) what is causing this high load?

I have some screenshots of the taskmanager, backing up this claim of high load. I'm willing to do some more research to fixing this issue, but this will have to be done in the late hours when the machines are not stressed with their daily job..

The tool is exactly what we need, I already gathered some really usefull information on how we (most developer shop, and we have our DBA hat on in these cases) can optimize and better administrate our sql services.

TIA,

HJ Meulekamp

Comments

  • Hi,

    We would strongly recommend that you run the SQL Response Alert Repository on a dedicated monitoring machine that isn't running SQL Server.

    15% CPU sounds about right, but you also mention using an entire CPU at times. I would also recommend that you don't enable the trace feature of SQL Response if performance is a problem for you.

    Let me know if this doesn't resolve your issue.

    Cheers,
    --
    Daniel
  • I have tried again this weekend. Without the trace enabled the CPU resource usage increase is a lot lower. hoovering around 15% on the server running the monitor software (and sql server) and 5% on the second monitored server.

    I will try to move the sql response monitor software/ repository to a machine which is not used a lot.

    I'm wondering how many licenses will we need for this? One machine just running the sql response monitor/repository and two machines which are monitored?
  • Hi,

    you will only need a license per physical SQL server you are monitoring, irrespective of the number of instances on that machine.

    For a cluster you will need a license for each cluster node.

    In your case, with two machines, you will need two licenses.

    Regards,
    --
    Daniel
Sign In or Register to comment.