[sims] SIMS Server performance query

[timbo343]

Since we've returned, the processor on the VM that SIMS is sat on is through the roof and flat lining at 100% for most of the time. I don't think it was ever this bad before the summer break. Infact, it's that bad that users are starting to complain about the slowness.

 

The VM has 3 logical processors assigned to it which are running at 2.1Ghz. It has a 11Gb of RAM assigned to it but i've limited the SQL database to only 8gb as it kept eating the assigned RAM.

 

The main question i'm asking is, is your SIMS server flat lining at 100% processor for the SQL Server process. We are running SQL2014.

[Jobos]

Ours ticks over with low CPU demand. Perhaps a restart of the VM is in order.

[timbo343]

Possibly - i plan to give it another logical virtual processor taking it up to 4 as the licenses we have are bought i pairs - it got a restart just before we came back as Windows Updates were installed however the SIMS CTF clean up patch has been applied too. I wonder if this has anything to do with it.

 

The other thing i was thinking was SQL updates. I need to check the version that were are running to see if i've got any outstanding CUs that need installing.

[timbo343]

Ah! i've found out our version is 12.2.5000.0 which is the initial SQL 2014 SP2 release without any CU updates from July 2016. They have released CU13 in August. Looks like i might need to apply that to see what happens... ooops. I would have thought MS updates would have found the updates for SQL automatically.

 

I might need to go through each CU individually and apply each one rather than jumping straight to CU13.

[bald_pig]

Our SIMS server has been noticeably slower than last term, I wonder if there's an update that's caused a slowdown or overhead?

[timbo343]

I'm glad it's not just me then. I see the updates that were installed on our server 29th August. I wonder if it's the 2018-08 which might have borked something up.

[bobsmith]

We've been having hell with SIMS since coming back. Attendance registers taking up to 45 minutes to actually complete a SAVE.

 

Looking on SQL I'm getting locks that timeout for 15 minutes or more.

 

I'll have a look at some of the things you've mentioned in this thread.

 

the thing is we're still on SQL 2012 - nothing should have changed....

[timbo343]

School finished an hour ago and still the processor is maxing out at 100% every now and then. The office staff who use SIMS have all gone home - something is not right with the server. It will be getting rebooted tonight to see if it makes any difference.

 

It is sqlservr.exe that is hogging all the CPU resources. The network, memory and disk are all what you'd expect them to be.

Edited September 7, 2018 by timbo343

[matt40k]

Why don't you contact Capita and get them to have a look. Its what you pay them for!!

 

I might need to go through each CU individually and apply each one rather than jumping straight to CU13.

 

You'll be ok to jump to the latest CU.

[timbo343]

Updates to the latest CU (CU13), restarted the server and restarted all client machines that connect to the SIMS server. Also added a 4th virtual processor to see what that does on monday.

 

After a few log in tests the CPU seems to be running low.

[timbo343]

So it would seem that the updates, adding a 4th Virtual Processor and doing a restart to the server has made no difference. I have been watching the CPU usage of the sql exe and it seemed that at 7:30am ish this morning it was fine. Then when i came into work at 8:10am ish it was through the roof.

 

Could it be that this virtual server is now under powered? As i mentioned in an earlier post, i have capped the SQL database at 8gb - has anyone else capped their SQL database from running off with all the allocated memory? If so, what are your capped limits?

 

[ItsOggy]

What compatibility level is your SIMS sql database, wasn't there something about when running on SQL 2014 you had to have the DB in SQL 2012 compatability level?

 

I'm not working with SIMS any more so my memory is rusty!

[timbo343]

I've just checked the compatibility level on our SIMS database and it's running at SQL2012:

 

 

SchoolLook is running at SQL2008. Do you think this database would be better running SQL 2012 or 2014?:

 

 

Solus3 is running at SQL2014? Should this be at SQL2012?

 

 

Discover is running at SQL2012.

 

 

Here is a blog about the compatibility issues and if they matter https://www.enhansoft.com/blog/does-sql-server-database-compatibility-level-matter

Edited September 10, 2018 by timbo343

[timbo343]

hmmmm... maybe im jumping the gun. Things seems to have calmed down now that registration is over and first period is now under way.

 

I shall have a look at lesson change to see what the CPU looks like and check again tomorrow morning.

 

[matt40k]

Again, you need to contact your SIMS support team \ Capita. SIMS isn't the same as FMS\SOLUS3. It could be your indexes need rebuilding (which if you've been messing about with comp level is likely) - which is literary a patch which you could have applied 3 days ago.

[Banjo]

I've found that its worth stopping and starting SQL overnight as it does tend to be a memory hog.

[timbo343]

Again, you need to contact your SIMS support team \ Capita. SIMS isn't the same as FMS\SOLUS3. It could be your indexes need rebuilding (which if you've been messing about with comp level is likely) - which is literary a patch which you could have applied 3 days ago.

 

Was about to contact our support team about the issues we've been having. I wanted to rule out everything first otherwise they will tell me to to what i've already tried.

[AlanD]

The VM has 3 logical processors assigned to it which are running at 2.1Ghz. It has a 11Gb of RAM assigned to it but i've limited the SQL database to only 8gb as it kept eating the assigned RAM.

 

SQL will always use ALL the available RAM. It should release it as other tasks and processes make demands... I think I would have make 16GB the lowest limit...possibly even 32GB.

 

Then I would check what routine maintenance tasks are set up is SQL manager. Particularly after the year end roll - I'd want to make sure it had run things re-index and tidy of data files. That can make a significant difference.

[kennysarmy]

I've also had complaints from my Data Manager about SIMS performance when adding in the new students this year.

 

I've been following the points here:

 

Advice on system optimisation ?

 

But day to day operations seem to be OK, it's just seems to be the more intensive operations.

[timbo343]

Last night i made some more changes to the SIMS server and yesterday afternoon messaged our SIMS support team (the LEA) however i've not had anything come back from that as of yet - i seem to think they might be busy updating all the primaries to the latest version of SIMS.

 

Anyway, i've given the VM a bit more RAM. I've now taken it up to 20480 mb (20Gb) and capped SQL at 16Gb, so far so good. The SQLServr process has been high when staff are logging into SIMS on the client machines but this is to be expected.

 

The server it's self so far is only using 10.3Gb of RAM with SQL using about 7065mb. Also, it seems no matter how much processing power is thrown at the Server the SQLServr.exe process will just use it all.

[kennysarmy]

Last night i made some more changes to the SIMS server and yesterday afternoon messaged our SIMS support team (the LEA) however i've not had anything come back from that as of yet - i seem to think they might be busy updating all the primaries to the latest version of SIMS.

 

Anyway, i've given the VM a bit more RAM. I've now taken it up to 20480 mb (20Gb) and capped SQL at 16Gb, so far so good. The SQLServr process has been high when staff are logging into SIMS on the client machines but this is to be expected.

 

The server it's self so far is only using 10.3Gb of RAM with SQL using about 7065mb. Also, it seems no matter how much processing power is thrown at the Server the SQLServr.exe process will just use it all.

 

I've found our LEA Sims support team to be very good at general SIMS queries but anything more complicated like SQL tweaks....not so useful.

[timbo343]

I've found our LEA Sims support team to be very good at general SIMS queries but anything more complicated like SQL tweaks....not so useful.

 

Same with me - anything general about SIMS - ok, but when tasked with anything a little more technical they struggle.

 

I thought by upgrading all teacher machines to SSDs would make the client's SIMS work a little quicker, it seems it's not made a difference as all the processing is done on the server.

[jonbd]

I had a similar problem back in April, and our LEA SIMS support team sent me some patches (15589, 20647, 14265, and re-index patch 22573) which seemed to sort the problem out here. They also had to remote in and shrink the log file at the same time, but not sure if that was incidental to the CPU issue.

[timbo343]

The log file for us is tiny, 1200kb. Years and years ago we had an issue with the log file.

 

Will see our LEA about the patches that have been mentioned.

[kernits]

Moving the SIMS server to SSD storage made a big difference for us. The queries which took an age to run and locked the DB now take a fraction of the time to run which then mostly stopped the complaints.... if your not that familiar with SIMS definitely worth getting the support provider to check the patches, and that the housekeeping stuff is being run.

 

We are on SQL 2014 and have had no issues since the CTF patch dramas.