We are continuing to experience extremely poor response times from the client manager GUI and we are monitoring a very heavy CPU load on our DB server. The load on the DB server increased after the upgrade and creation of the second DB for the client manager. We see no apparent improvement in GUI performance from the previous version that did not use the second DB for the client manager.
We are using separate VMs for DB, Master and Client Manager.
TES 184.108.40.206 w/patch to .151
~ 4300 jobs per day
SQL 2008 standard
Clients connecting to the GUI have tested Firefox, Chrome and IE on Windows and Firefox and Chrome on Linux.
SQL server has 8GB RAM
Master server has 8GB RAM
Client Manager has 16GB RAM
While trying to scroll down through the list of jobs in the job activities screen, we experience a blank page while it fetches new data and a wait of about 20-30 seconds for the screen to populate.
In the Job Activities screen the filter function is not working properly. It is unable to find Job Group names that contain blank spaces.
We are testing 220.127.116.11 in a similar environment to yours but our test load is much lower that your production.
Our reponse time after switching the client manager DB to SQL improved significantly. But like I say we're running a much lighter load in test than you are in production.
We did put our client manager database on the same DB server as the master DB. I did see in the doc for moving the client manager DB to SQL they recommend it be on it's own DB server not on the same one as the master. Maybe that would help you.
I will have to check the documentation and see if we overlooked that part about the 2nd DB server. Our job count will be much higher once we are finished the conversion from our old product. The unusable part is specifically in the job activities screen, "While trying to scroll down through the list of jobs in the job activities screen, we experience a blank page while it fetches new data and a wait of about 20-30 seconds for the screen to populate." All the other screens are acceptable, not snappy but acceptable.
In my opinion the requirement for a separate DB server is overkill and unnecessary. The CPU is not pegged at 100% and SQL is using available RAM as it should be. We see the same thing on our other SQL servers that host other applications. We are not overly concerned with high utilization on our SQL servers as long as we get a usable interface for our users.
We have applications that are much harder on the DB servers and have larger DBs and they do not suffer from lag at the user interface.
I agree the requirements for a separate DB server for ther CM database seems like overkill. I just thought it might be worth trying. The referenece guide under Troubleshooting the client manager has this suggestion:
For ideal performance, it is recommended that the stand
alone Enterprise Scheduler cache database be located exclusively on its own server and have optimal network connection between the Client Manager server and the Enterprise Scheduler cache database server.
You might also work with your DBAs to put the Master database on a different set of drives that the client Manager database. I haven't played with it, but I would guess that that same inserts and updates that the Master database receives, the client database receives as well. When your web client does the next "select 30" query to the client database, it might be contending with write commands on the master. If this is the case, you won't see CPU or memory spikes, but you would see I/O spikes on the drives. Just a thought.
Reporting through the Hypervisor tools shows very little I/O for the DB VM. Reporting from Win2k8 task manager shows sqlserver I/O fluctuates from 15Kb to 75Kb per second. Hasn't peaked above 250Kb for the entire server. Network utilization is basically less than 1% of a 1GigE connection.