mrbios Posted March 3, 2022 Posted March 3, 2022 I recently migrated our MIS/SQL server from Windows Server 2012R2 and SQL Server 2012 to Windows Server 2019 and SQL Server 2016 and i seem to be hitting some CPU related performance issues, specifically in regards to using Edulink. The SIMS database is roughly 20GB. Total size of all databases on the server is about 28GB. The SQL server has a maximum assigned memory of 28GB, and the VM is set to reserve its entire memory allocation. When on 2012 the server had 32GB ram and 4vCPU, now it has 36GB ram and 8vCPUs. I've set MAXDOP to 4, and cost for parallelism to 20 as a slight increase over the default of 5 (I've tested quite a few settings on these two in an attempt to resolve this, but to no avail) In the morning during registration when we have 30-50 devices all using edulink for registers at the same time the CPU usage on the server will spike up to 70-90% and the edulink integrator app will start reporting an issue that requests are taking too long to process, and people trying to open a register will see a timeout after 45seconds. Anyone have any suggestions? It makes no sense as so many poorly configured aspects of the old server are fine tuned on the new one, and i can't understand what could be causing this.
mrbios Posted March 3, 2022 Author Posted March 3, 2022 Is compatibility mode is set to SQL 2012 It is now, it wasn't previously. However from my testing i don't really see any difference. Is it likely to make a difference to an individual - such as the amount of time it takes to run X report, or is it likely to make a difference in terms of scale, multiple users performing actions for example? The issue I'm seeing appears to pop up when lots of registration queries and writes are going on at once. I can't seem to cause the issue when doing one big report or command myself. Is there any solid way to test this perhaps?
mdrabble Posted March 4, 2022 Posted March 4, 2022 Do you get any errors about deadlocks? We were getting errors and poor performance when registering being taken just with Sims.net We reported issue to ESS and they are aware of performance issues.
mrbios Posted March 4, 2022 Author Posted March 4, 2022 Do you get any errors about deadlocks? We were getting errors and poor performance when registering being taken just with Sims.net We reported issue to ESS and they are aware of performance issues. Where would those errors normally present themselves? I'm not seeing any errors in event logs, sql logs etc. I've got a report i can run as a test that seems to take anywhere from 47 seconds to 90 seconds to run which pulls attendance data for a specific year, but nothing i change seems to make any notable difference to the performance of that report running...so what I'm really struggling with is actually testing the problem, as i don't seem to have a constant i can refer to. And this particular performance issue only seems to be presenting itself in the form of edulink attempting to do things when everyone uses it for registration in the morning via some tablets. No one has complained about sims being slow or not being able to achieve something in sims either, so the issue seems isolated to that heavy use case with morning registration.
mrbios Posted March 4, 2022 Author Posted March 4, 2022 Hmm, well this morning everything went fine. CPU usage on the server didn't go over about 25%, 30mins later the wonde service did a sync and that knocked it up to 40% with some expensive queries but that didn't last long. Maybe it was as simple as the compatibility setting...
willtech Posted March 7, 2022 Posted March 7, 2022 (edited) It is now, it wasn't previously. However from my testing i don't really see any difference. Is it likely to make a difference to an individual - such as the amount of time it takes to run X report, or is it likely to make a difference in terms of scale, multiple users performing actions for example? The issue I'm seeing appears to pop up when lots of registration queries and writes are going on at once. I can't seem to cause the issue when doing one big report or command myself. Is there any solid way to test this perhaps? Sorry for the late reply. We had issues with data extractor software, but as soon as we change the compatibility level to 2012. It was a lot better processing wise. Addressing performance issues with SIMS.net and/or SQL Server SQL Server - SQL 2016 Compatibility Mode Edited March 7, 2022 by willtech 1
mrbios Posted March 7, 2022 Author Posted March 7, 2022 Sorry for the late reply. We had issues with data extractor software, but as soon as we change the compatibility level to 2012. It was a lot better processing wise. Addressing performance issues with SIMS.net and/or SQL Server SQL Server - SQL 2016 Compatibility Mode Thanks, yea it looks like it's done the trick. It's odd that it was a very specific scenario of registrations only that caused me to notice it peak cpu usage. Since changing to 2012 both mornings since have been fine. Although the council sims support line even said to me, and i quote from their email "It is fine to leave as 2016 new. " grrrr
willtech Posted March 7, 2022 Posted March 7, 2022 Thanks, yea it looks like it's done the trick. It's odd that it was a very specific scenario of registrations only that caused me to notice it peak cpu usage. Since changing to 2012 both mornings since have been fine. Although the council sims support line even said to me, and i quote from their email "It is fine to leave as 2016 new. " grrrr The thing that made me laugh on the issue was the line at the bottom of their KB: For us to go through and make SIMS.net fully compliant or fully compatible with Native SQL 2016 mode would be a disproportionate amount of effort on our part for very little performance gain for customers, so we felt the best compromise was to leave in the 110 mode as it is, but we will continue to look at enhancing some of the stored procedures as an ongoing project that we're going to have over each release when we have time and capacity to do so.
mrbios Posted March 7, 2022 Author Posted March 7, 2022 The thing that made me laugh on the issue was the line at the bottom of their KB: Haha, yes! It either means "we can't be bothered to invest any time in benefiting our customers" OR "We have no idea how to do this".......... could well be both though. 1
psydii Posted March 8, 2022 Posted March 8, 2022 I recon they have one guy who you could legitimately call a DBA and the rest are just front end programmers. Looking at the Microsoft documentation, they make this look easy. That said the devil is always in the detail, but given Captia/ESS have "Hosted Sims" and a revenue that must be into the hundreds of millions, they really have no excuse.
jezi55 Posted March 8, 2022 Posted March 8, 2022 We have been in the same situation the last few weeks with lots of SIMs deadlock issues for registers and attendance. This morning everything seems to be going fine and i have completed the following to hopefully help people in the same situation: On our VM we have given 100% allocation to CPU and RAM so this does not get shared with other VMs, updated SQL 2016 to the latest cumulative update, set the sims database to 2012 compatibility, run validate memberships and database diagnostics within SIMs, shrink the log files of the DB and turn off change tracking within SQL. 1
KK20 Posted March 14, 2022 Posted March 14, 2022 do you use SSRS? We moved to a standalone 2017 SSRS and this made a massive difference for me. So much so our VM went from 32Gb down to 12Gb usage overall. Im not sure if it was 2016 SSRS implementation. There are loads of guides migrating, it was easy.
Recommended Posts
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now