Using activity monitor to locate user running reports in SIMS

[dazza007]

I have been using activity monitor in the SQL admin studio on our SIMS server to find the most expensive queries.

There has been virtually 100% cpu on the SIMS server, with 10 times the CPU resources going to this insert command being performed.

My mate tells me I can run a trace to find out the process code but it is all above me.

Is there a way of determining which user is causing high cpu load on our server....

Cheers,

Darren

[dazza007]

Any ideas on how to locate users who are running large reports?

[vikpaw]

purportedly if you say this name 3 times and click your heels magic sometimes happens @jinnantonnixx

[jinnantonnixx]

Four times, Vik. Four times. My rate has gone up.

 

I'm snowed under today, but this thread covers a lot of topics, including Activity Monitor.

http://www.edugeek.net/forums/mis-systems/112841-speeding-up-sims.html

Edited November 11, 2013 by jinnantonnixx

[dazza007]

Thanks for that, I am getting to grips with SQL manager - slowly. That is a good resource. We keep getting a recent expensive query - and insert into command consuming significant amounts of CPU and users are reporting issues accessing and performing simple tasks in SIMS. I had to restart the SIMS server at lunchtime today and the problem has seemed to disappear. Is anyone aware of this issue.

[vikpaw]

It is common for big routines and updates to slow the system down. A lot of the processes for starting the new academic year, promotion, even validating membership can take their toll on the system. As well as bulk updates.

Are there lots of people changing data? Possibly even the same data? Maybe a lot of report writing. These could be slowing down the system, or creating locks while they wait for other processes to finish.

Do you have a lot of people with high level permissions?

If you can track down the procedure that is being run, it might help you find the user / type of user that would run it and work out what is going wrong.

Are you on the latest version of SIMS, spring had some issues with CPU usage, which could easily be fixed with a patch, or updating, also, the homepage graphs take up a lot of resource and maintain open connections to the SQL server.

[dazza007]

If you can track down the procedure that is being run...... I have been trying to do this but as we have had 101 things to do it is difficult to follow. When I sort the processes by task state no one individual keeps popping up.. I have restarted the server and am not seeing large CPU requests (but that may not mean anything)

[matt40k]

If you keep closing and open SIMS and rebooting the server you'll be having to keep rebuild the cache so it'll be doing more harm then good.

 

Have you looked at the disc queue length within performance monitor? Could be you can't write to the disc fast enough so the CPU is locked waiting. Be worth logging with Capita and getting your moneys worth.

[dazza007]

I will have a look in the morning, makes sense I am getting tunnel vision!!

We are having meltdown and the boss is away and muggings is the only one onsite knows about SIMS. I have been adding the timeout line to clients as they are getting kicked out of SIMS with the database timeout error - and we have 100 users.

We are managed by the local council and have no capita report..

To resolve the users being kicked out of sims as it is slow I was adviced to detach and reattach the database. I tried disabling the tcpip pipes in sims and restarting the sql in services for the connections to take effect. however I got error messages, the sql would not start with the tcp ip pipes disabled...

So in trying to detach the database and reattach the database using dbattach would not work unless the sql was online.

We do have a script to stop access to the database however this is used when upgrading the sims but this method causes issues.

How can I kick people off the database - we have connect.ini installed locally in c:\program files\SIMS\SIMS .net\connect.ini

My disclaimer is the setup is not my work.........

So if I detach and reattach the database this would "clean up" the sql - according to our support. I was also advised to limit the sql to 4gb as it is capitas recommendation - we have the full SQLSERVER version....