SIMS server becomes unresponsive. Fine for a while after reboot.

[TwistedHelixis]

Starting on Friday morning last week I got a call saying that nobody could log into SIMS. I rebooted the server and everything was fine.

 

Got a similar call on Monday morning. Logged in and could see SQL server Windows NT 64bit > (SQL 2016) was using 95% of the RAM, this server has 16gb assigned to it from Hyper-V.

 

I also look after a few other primary schools with similar setup, so logged onto some of those during the day and the same process is only using a few % of the Ram.

 

Might be a red herring, but after I reboot the SIMS server the memory seems to stay low (ish) during the day, its seems to jump up at some point during the night, but I have only tested this last night so might not be the case.

 

We do have lots of third party apps that access SIMS during the night, such as Wonde, MiSAPP, Groupcall etc, could it be possible one of those is causing the issue.

Could it be our hyper-v backup that runs during the night?

 

Any ideas?

Edited January 24, 2023 by TwistedHelixis

[jthompson]

Do you have SIMS Discover? If so, try disabling the SIMS Discover transfer service for a few days to see if it might be that.

[TwistedHelixis]

Do you have SIMS Discover? If so, try disabling the SIMS Discover transfer service for a few days to see if it might be that.

yes they do. Ill give that a go, thanks.

[jthompson]

Would be worth checking the Discover transfer history, though. If it's all working okay, it's unlikely to be your problem. I only mention it because we've had it basically not work but chew up resources for hours on end.

[RLR]

I've been having the same issue with our veeam server for about a week now. After a restart it's fine for about 5 minutes and then grinds to a halt. Struggling to even copy log files off of it even when booting in Windows PE or safe mode. We're going to try setup our old san and migrate it to a different datastore.

 

Task manager doesn't show any CPU or High Memory usage.

[3s-gtech]

SQL Server is supposed to hoover up RAM as it loads the DB into it. If you don't have a memory cap for the instance, it will take what it can get and potentially impact the server itself.

 

Perhaps your SIMS DB has swollen and is now pushing the server's limits?

[Koldov]

Has anything changed? How long has this set-up been running fine?

 

Is your 2016 SQL running in 2012 mode (I think it should be for SIMS)?

 

I only ask because iirc somebody here posted a similar problem and I seem to remember this was the cause.

 

Someone on here will also know if there are also options in SQL to limit RAM use, so at least it doesn't kill the server (only the SIMS bit) maybe?

[bknaggs]

Back in the day Capita set my SIMS SQL instance to use 50% of the RAM in the server - this was their recommendation. Depending on what else your server is doing you could potentially raise that to 75% and give SIMS 12GB and leave the server 4GB.

 

I can dig out what you need to do to restrict the RAM used by SQL if it will help.

[TwistedHelixis]

Has anything changed? How long has this set-up been running fine?

No, its been fine for almost 2 years

 

Is your 2016 SQL running in 2012 mode (I think it should be for SIMS)?

It is SQL 2016, I don't think its running in 2012 mode. Ill need to workout how to do this. Is anyone else doing this already???

 

I can dig out what you need to do to restrict the RAM used by SQL if it will help.

Yes please that would be really helpful.

 

Just an update:

I have uninstalled SIMS discover

I have uninstalled Wonde

I have Stopped a bunch of tasks from running during the night that interact with SIMS

I have found Groupcall Emerge on the server that nobody knows anything about, so might uninstall this also.

Ill give the SIMS server a reboot at about 8pm tonight

Lets see what happens tomorrow morning.

[Koldov]

It may not be your issue (but it is recommended by Capita/ESS). Never done the compatibility mode thing myself as we are still on an older version of SQL, but from the technical roadmap:

 

"SQL Compatibility Mode

We are aware that some customers experience issues with performance when running SQL2016 in its native mode. Following on guidance and direction from the EPG group, if you are experiencing this, we recommend changing the compatibility level to 110 within SQL Server Management Studio. We have made KB0036825 available for more information."

 

and...

 

"SQL Compatibility Mode

We are aware that some customers experience issues with performance when running SQL2016 in its native mode. Following on guidance and direction from the EPG group, if you are experiencing this, we recommend changing the compatibility level to 110 within SQL Server Management Studio. We have made KB0036825 available for more information."

 

EDIT: I'd be careful about uninstalling random software, it is probably on there to pass SIMS info direct to cloud services that rely on it (I've forgotten the reason half of them are installed but I think we have Groupcall/Exporter/Emerge/Wonde/USO update various learning platforms, B2B, LGfL with children's data).

 

EDIT 2: 'Discover Data Transfer' and 'Discover User Synchronisation' are set on our server as Windows Scheduled Tasks if you can see a particular time for it happening in the event logs.

Edited January 24, 2023 by Koldov

[jthompson]

While you're at it, check that you are on the latest service pack (SP3) for SQL 2016. It's not going to have any bearing on your performance issue, but SP2 went EOL last month, so worth checking that your server isn't on an unsupported version.

 

Reindexing the SIMS db and updating table statistics might be worth a go as well. There's a SIMS patch that ESS can provide for doing that, but I think (happy to be corrected) that doing that with an SQL Server maintenance plan will do the same thing. Might be best to request the patch from ESS if you're unsure, as well as ask for any other pointers from them regarding compatibility mode and memory settings (since that will depend mainly on the size of your db).

[mrwoberts]

Open Microsoft SQL Server Management Studio (SSMS).

Login to the instance with SIMS.

Right click on the instance name at the top of the left-hand panel.

In the Memory section, the maximum should be set to 75% of the Total RAM or (Total RAM - 4GB).

 

Also, for the SIMS database, go to the Properties windows and switch compatibility to 2012 (110) - I've tried 2016, 2014 and ended up staying on 2012 (the ESS recommended setting)

 

What is the size of the database files (mdf and ldf) ?