[sims] SQL server(s) instance(s)

[PurpleTentacle]

We've recently migrated to Microsoft SQL 2016 and at the same time implemented Solus 3.

Since the migration we are getting complaints daily that SIMS .net is much slower than it used to be.

I'm wondering how other SIMS .net schools run the server(s) and instance(s).

 

 

One server, one instance, multiple databases

 

One server, multiple instances, one database per instance

 

Multiple servers, single instance, single database

 

Multiple servers, one for SIMS, FMS and Discover and one for SOLUS

 

Other? (Moving from SIMS is not an option)

Edited October 2, 2020 by PurpleTentacle
Shouldn't play with options that cause the post to be published

[howartp]

We moved to 2016 last October and SIMS has been ridiculous all year since. I've worked through all sorts with support.

 

They've today told me to change the Compatibility Mode of the SIMS DB down to 2012 as they have had performance issues on 2016; it's on my list to try tonight as can't be done when people are using SIMS.

 

Peter

[cullingsh]

All our schools each have 1 server, 1 instance with multiple databases for SIMS, SOLUS, Discover

 

Setting the compatibility level in SQL 2016 to SQL 2012 is an old recommendation (which we have done at the schools running SQL 2016) - surprised it's not been mentioned sooner.

 

Don't think we've had too many issues, but then most schools moved from the hosted version to on-prem so it's quicker and more reliable anyways.

[Esteban_Child_of_the_Sun]

I went through this with Capita a few years ago and tracked the issue down to the Cardinality Estimation (SQL Server) in SQL2016. SIMS uses an outdated SQL methodology that doesn't work well with SQL2016 in the way it joins tables which is what produces the slowness. If you have run the re-index patches while it's in 2016 mode then you'll need to re-run them once it's in 2012 mode to re-build them back to the old way. I'd also recommend setting up a regular SQL maintenance plan (I've set our high schools to run it monthly) and Capita provided me with the details for it so just log a call with your SIMS support for the documentation. We also found that any latency on the HDD amplifies the slowness, so if you have a NAS that isn't hosted within your server then even a high bandwidth link to the NAS causes more slowness.

[howartp]

If you have run the re-index patches while it's in 2016 mode then you'll need to re-run them once it's in 2012 mode to re-build them back to the old way. I'd also recommend setting up a regular SQL maintenance plan (I've set our high schools to run it monthly) and Capita provided me with the details for it so just log a call with your SIMS support for the documentation.

Excellent, thanks - i'll get those re-run.

 

Do you have your logging set to Simple or Full?

 

Peter

[Esteban_Child_of_the_Sun]

simple for all databases (SIMS, SOLUS, Discover, FMS)

[PurpleTentacle]

I went through this with Capita a few years ago and tracked the issue down to the Cardinality Estimation (SQL Server) in SQL2016. SIMS uses an outdated SQL methodology that doesn't work well with SQL2016 in the way it joins tables which is what produces the slowness. If you have run the re-index patches while it's in 2016 mode then you'll need to re-run them once it's in 2012 mode to re-build them back to the old way. I'd also recommend setting up a regular SQL maintenance plan (I've set our high schools to run it monthly) and Capita provided me with the details for it so just log a call with your SIMS support for the documentation. We also found that any latency on the HDD amplifies the slowness, so if you have a NAS that isn't hosted within your server then even a high bandwidth link to the NAS causes more slowness.

 

I'm on to our SIMS support because we've already done all of this. I am not going to be happy if I have to move the databases back to SQL 2012 and then be forced to move them again before SQL 2012 runs out of extended support.