[sims] SIMS reporting slow and locking users - HELP!

[MrMat]

Plee for help but also mini rant!

 

So let me start at the beginning…

 

Last month a couple of our Secondary Schools started logging calls advising that SIMS was running slow (specifically reporting) and it’s locking the database up (when big reports are run) and kicking users out since migrating over to SQL2014. I’m no SQL expert but I had a little look and couldn’t see any issues with their setup etc so logged a call with Capita.

 

They responded advising to run 5 database patches and then run System Diagnostics followed by Validate Memberships. They attached 5 patches (3 of which were out of date and couldn’t be ran on the latest version even though I had entered into the call notes that they were all on 7.166. So I asked for them on S3 which took 3 attempts and about 2 weeks to get them authorised but still they only managed to authorise 4 of them so had to deploy 4 via S3 and then 1 manually but that’s a story for another time). Here are the patches that were run with a brief description (as I mentioned, I’m no SQL expert but opened each one up and tried to get somewhat of an idea what each was doing etc).

 

 

• 20580 – This sets the SQL compatibility mode to SQL2012. While you are all already running in compatibility mode I believe we should still run this patch. This is because if this routine doesn’t improve the situation, Capita then can’t come back and advise that we missed a step etc.
  
• 20647 – Updates all table statistics with fullscan. Each table within your database has a set statistics on it’s contents. SQL uses these statistics to decide the fastest method of querying the data. The statistic can get muddled over time so by the looks of things, this patch will scan each table within the database and update the statistics accordingly hopefully leading to fastest querying of the data etc.
  
• 21158 – Patch to fix performance issues with SQL 2014. This is also related to statistics. SQL 2014 adds new statistics for querying against etc, this patch adds the new statistics to the tables where they do not currently exist.
  
• 14265 – Truncates the transaction log and re-indexes the database. Truncating the log file won’t reduce the size of the LDF (unlike the Shrink routine we often run) instead it marks unnecessary areas of the log file as inactive allowing them to be overwritten. It also re-indexes all of the tables, which should also help with the speed of querying the data.
  
• 15589 – General Maintenance: Update internal statistics for all tables in the database. Again, this appears to refresh all of the internal statistics.
  

 

Anywho, ran that patches and routines as advised and it made no difference, the issue still remains so again I raised it with Capita.

 

They then asked to remote onto the server of one of the problem schools so I obliged. The guy logs on, opens up Task Manager and says it looks okay to me (CPU not maxing and RAM to spare – SQL instance allocated 15gb RAM). Then has me log into SIMS, open up the clock and time how long it takes to run a basic report (4 minutes for one year group – name and religion!) to which he advises that yes it is indeed slow. He checks the size of the database (MDF 17gb) and then tells me for about 20 minutes that it’s slow because the database is big… and that’s it. That’s all I got out of a 30-40 minute conversation. The database is big so it’s expected. Nothing they can do. I keep badgering on advising that I get the size will make it slow but shouldn’t be locking out users etc to which his response is the database is a bit big.

 

Now they would like a copy of the database which i will of course provide but wondered if anyone else could help.

 

So any advice from you guys? Anything useful I can try? Anyone expereinced this in the past etc? I know it's a really difficult thing to narrow down as it could be environmental etc but I’ve completely lost confidence with Capita and naturally, the school are getting twitchy so I need to try and find resolution.

 

Thank you,

Alex

Edited January 18, 2016 by MrMat

[MrMat]

Also, heres details of the error others users are getting when they get locked/kicked out of SIMS:

 

at SIMS.Processes.Database.RetrieveDataSet(IDbCommand command, Boolean AsCP320)

at SIMS.Processes.MaintainSIMSServicesCache.Repopulate()

at SIMS.Processes.MaintainAddressInformation..ctor()

at SIMS.UserInterfaces.SearchAddressControl..ctor()

at SIMS.UserInterfaces.AddressControl..ctor()

at SIMS.UserInterfaces.AbstractStudentEdit..ctor()

at SIMS.UserInterfaces.EditStudent..ctor()

at SIMS.UserInterfaces.MaintainStudentInformation.InitializeComponent()

at SIMS.UserInterfaces.MaintainStudentInformation..ctor()

at SIMS.UserInterfaces.MainContainer.MenuItemEditStudent_Click(Object sender, EventArgs e)

at System.Windows.Forms.MenuItem.OnClick(EventArgs e)

at System.Windows.Forms.MenuItem.PerformClick()

at SIMS.UserInterfaces.Launcher2.focusBarButton_click(Object sender, EventArgs e)

at NETXP.Controls.Bars.CommandBarItem.RaiseClick()

at NETXP.Controls.Bars.CommandBar.ProcessMouseUp(Point e)

at NETXP.Controls.Bars.CommandBar.OnMouseUp(MouseEventArgs e)

at System.Windows.Forms.Control.WmMouseUp(Message& m, MouseButtons button, Int32 clicks)

at System.Windows.Forms.Control.WndProc(Message& m)

at System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m)

at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m)

at System.Windows.Forms.NativeWindow.Callback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)

Edited January 18, 2016 by MrMat

[Banjo]

I've seen huge .ldf's that filled the harddrive and crashed the the server, but not a 17GB SIMS mdf file, even in schools with over 1600 students. Was it this size before the move to SQL2014?

[SimonCygnetIT]

Perhaps look at Archiving Attendance marks from previous years? Not sure if that will reduce the size of the database itself but it may improve performance..

[MrMat]

I've seen huge .ldf's that filled the harddrive and crashed the the server, but not a 17GB SIMS mdf file, even in schools with over 1600 students. Was it this size before the move to SQL2014?

 

I'm not too sure unfortunately. It wasn't me that did the migration, the guy before me did the switch over etc.

 

On that note however, i've just ran a backup via System manager and the MDF has now shrunk!! It's now 14GB. Also, if i go to SQL manager and select Tasks > Shrink > Files. The 'Data' i.e. the MDF is allocated 14337MB but can be shrunk 10%?

 

It is a big school, 1200 kids.

 

[Banjo]

I'm not too sure unfortunately. It wasn't me that did the migration, the guy before me did the switch over etc.

 

On that note however, i've just ran a backup via System manager and the MDF has now shrunk!! It's now 14GB. Also, if i go to SQL manager and select Tasks > Shrink > Files. The 'Data' i.e. the MDF is allocated 14337MB but can be shrunk 10%?

 

It is a big school, 1200 kids.

 

[ATTACH=CONFIG]34419[/ATTACH]

 

I'd say a DB size of 2-3GB would be the average for a school of that size. When you run the SIMS backup from system manager how big are the .bak files?

 

If these were around couple of gig mark, I'd be tempted to restore one of these backups and check the file size. If the restored DB remains at a resonable size, switch the live and newly restored over and see if you get a speed boost.

Edited January 18, 2016 by Banjo

[MrMat]

I'd say a DB size of 2-3GB would be the average for a school of that size. When you run the SIMS backup from system manager how big are the .bak files?

 

If these were around couple of gig mark, I'd be tempted to restore one of these backups and check the file size. If the restored DB remains at a resonable size, switch the live and newly restored over and see if you get a speed boost.

 

.BAK is 13GB

[bobsmith]

How many years of data do you have?

[Banjo]

Do you store student/staff photos in SIMS? If so what size were the images that were uploaded?

[localzuk]

We are a 550 pupil school and our database is 3GB. So, it wouldn't be unheard of to see much larger databases, if those schools have been using a lot of the different functionality for a long time. Our database was tiny until we started using things like lesson monitor etc...

 

Yes, 17GB is a large database for SIMS, but at the same time the whole point of relational databases is being able to store large volumes of data and being able to access it quickly. If a database's size is causing issues it is usually down to lack of resources to handle the database (you should have as much RAM as the database is in size ideally, so SQL Server can put the entire database into RAM), if you can't have large amounts of RAM, you need fast disks/SSDs to handle the swapping back and forth from RAM to disk (I'd advise this anyway, regardless of how much RAM you are running).

 

The SIMS database has always been a bit inefficient (and poorly designed, but it is getting better), so pure CPU power is key too - on the client and on the server, as some of the business logic is performed on both. However, you are going to come up against a hard limit - how the software is designed. It can only run as fast as the software allows.

 

So in essence, if you're getting slowness issues, this can only really be down to the design of the database. Our SIMS database is hosted on a very fast server, our clients are very well spec'ed and our network low latency. Yet some reports take a long time to run for no real reason.

[superfletch]

I'm wondering if it is the speed of the connection to the reporting engine, or the amount of work the report engine has to run?

 

What kind of a report is it, and for how many pupils, does it include graphics/logos?

Does a really basic report for a class worth of pupils run at a sensible speed?

 

If you run Process Explorer (Sysinternals) and watch the SIMS (pulsar.exe) process while you run the report can you check on the different tabs to highlight any bottleneck or lag? Sometimes the client will try to access the report engine across a proxy server which slows it down.

[splattthecat]

Are you using Discover? We have a school who have tried to make a lot of use of it with a MDF that is around 60Gb and growing with each overnight transfer!

[Banjo]

Are you using Discover? We have a school who have tried to make a lot of use of it with a MDF that is around 60Gb and growing with each overnight transfer!

 

What gets pulled back into the SIMS db during the transfer? I thought this was mostly 1 way traffic into the Discover db not the other way round. Otherwise what was the point of having a second db in the first place?

[dapaulio]

Just check that the compatibility setting in sql did apply to your sql database. We are a 1600 strong school and Our sims.mdf db is 15gb with discover and fms running in the same imsrance and it runs fine. We experience the odd crash every now again but nothing as to what you sound like you are experiencing.

Try housekeeping techniques. Archiving attendance marks etc which will shrink the db abit.

 

Does your staff have access to designing reports? Check for old reports and obe We found a report that had been created by stupid staff that slogged the server resources filtering rules that weren't valid criteria. When the staff member ran the report it would lock up and sometimes crash everyone out of sims. We had this issue for 3 months before the teacher decided to ask for help because this report was not outputting what was wanted.

When I investigated the reports I was annoyed at the number of outdated reports and the faulty report.

[MrMat]

I'm wondering if it is the speed of the connection to the reporting engine, or the amount of work the report engine has to run?

 

What kind of a report is it, and for how many pupils, does it include graphics/logos?

Does a really basic report for a class worth of pupils run at a sensible speed?

 

If you run Process Explorer (Sysinternals) and watch the SIMS (pulsar.exe) process while you run the report can you check on the different tabs to highlight any bottleneck or lag? Sometimes the client will try to access the report engine across a proxy server which slows it down.

 

Only a small reports, it's Capita's own 'Religion' report we were testing.

 

We have actually identified a proxy issue this morning. SIMS login was taking an age as well, turned off the proxy and now everyone can log on in about 10-15 seconds which is ace. Not sure of the reason for that, passed it back to the school to have a look at the proxy etc. Hasn't actually resolved the freezes/crashes though unfortunately.

 

Are you using Discover? We have a school who have tried to make a lot of use of it with a MDF that is around 60Gb and growing with each overnight transfer!

 

They are using Discover but the file size isn't anything major. Couple of gig tops.

 

Just check that the compatibility setting in sql did apply to your sql database. We are a 1600 strong school and Our sims.mdf db is 15gb with discover and fms running in the same imsrance and it runs fine. We experience the odd crash every now again but nothing as to what you sound like you are experiencing.

Try housekeeping techniques. Archiving attendance marks etc which will shrink the db abit.

 

Does your staff have access to designing reports? Check for old reports and obe We found a report that had been created by stupid staff that slogged the server resources filtering rules that weren't valid criteria. When the staff member ran the report it would lock up and sometimes crash everyone out of sims. We had this issue for 3 months before the teacher decided to ask for help because this report was not outputting what was wanted.

When I investigated the reports I was annoyed at the number of outdated reports and the faulty report.

 

Just double checked SQL and it is indeed set to compatibility mode 2012 (110).

 

That not a bad shout, i'll have a look through all the reports and see if there's anything lying around that might be causing issues etc.

 

Thanks all for the tips!!

[splattthecat]

What gets pulled back into the SIMS db during the transfer? I thought this was mostly 1 way traffic into the Discover db not the other way round. Otherwise what was the point of having a second db in the first place?

 

Assessment aspect results are re-generated before the transfer, which seens to involve deleting them all (the deletions get stored in a table in the database, which is what is growing) before re-calculating them all, even though the majority will not have changed since the last time they were generated.

[PhilNeal]

Discover feeds back Discover Groups so they can be used as filters throughout SIMS

[superfletch]

Only a small reports, it's Capita's own 'Religion' report we were testing.

 

Strange then - if it is just a quite basic report you'd expect that to run fast. What is the response time like when you try to do "Tools | Setups | Document Management Server | Test Server Connection"? If it's slow then it sounds more like network connection to the SIMS Server, if it is quick then it's more like a bottleneck once the report query hits the database. (It may also be connected to the proxy settings).

 

We have actually identified a proxy issue this morning. SIMS login was taking an age as well, turned off the proxy and now everyone can log on in about 10-15 seconds which is ace. Not sure of the reason for that, passed it back to the school to have a look at the proxy etc.

 

If it's a GfL proxy rather than local this would explain why it takes absolutely ages - the communication for SIMS going all the way out to that proxy and then coming all the way back to school!

 

How do reports respond on the SIMS server itself with no proxy set?

 

Also check your Document Server Firewall Exceptions on the SIMS Server and make sure NetBIOS is enabled on the network card the SIMS Server uses (Network Card Properties | Advanced | TCPIP v4 | Wins | Enable NetBIOS)

[dapaulio]

Just a thought when you say takes you 15 secs to logon...

try sticking a process exception in your anti virus software for sims load. Exe and pulsar.exe.

Also an exception for your s: drive. You should have av on your sims server.