SIMS Reports - Issues

[BFCIT]

Hi All,

 

When running SIMS reports other SIMS users appear to face some difficulties. Some of these reports are using quite a large amount of assessment data. When a report is being run, some users complain about not being able to log in to SIMS due to time out errors and other users are unable to modify data within SIMS.

 

Some of our reports can take over an hour to complete and it is getting to the point in which running a report at any time during the school day is rendering SIMS useless for our office team.

 

Is this something that is quite common when a SIMS report is being run? If a report is using particular data, is that data locked so that it cannot be manipulated by any other user?

 

Currently our only solution appears to be to run these reports out of school hours, as not to affect the office and teaching staff's SIMS experience.

 

We are using SQL Server 2012 Express and have been advised that moving towards SQL Enterprise or Business Edition would be beneficial to utilise all of our Server hardware - but I am not 100% convinced whether doing this will solve our problems or whether these flaws make up how the SIMS database actually operates.

 

SIMS Server Spec: Physical Server, 16GB RAM, 2.4GHz E5-2609 (8 cores) 64 bit, SQL Server 2012 Express 11

 

I look forward to any information/advice on this,

 

Thank you,

 

[3s-gtech]

Normally I'd start by questioning your server spec, but that should be more than up to the task (though what is your disk configuration? This can make a difference especially with Express, as you won't necessarily be able to load the whole DB into RAM). Are you a secondary with a larger number of students? If so, SQL Express will be at its limits for you, I'd be fairly certain of that.

 

We are often running large reports - currently ones which take hours - to extract various data. The server is still useable during this time, and it's a barely higher spec than yours.

[PhilNeal]

If you're a secondary Express is not up to the job; I'd only deploy in small primary schools.

[notalot]

We have the similar problems when users run large complex reports on exam data or attendance data.

 

We run in to table locks that then cause a backlog of requests that are all waiting.

 

We don't have it locked for as long as you only normally 5-10 minutes any more than that we locate the person and crash the report or kill the connection to the database.

 

Edit

 

Just thought I'd add the server specs for interest

 

Windows Server 2008r2 with SQL 2008R2 standard (due to update it shortly for the summer release)

2x AMD Opteron 6234 (12 core 2.4ghz)

32gb ram

4x 300gb 15k SAS drives in Raid 5 with a hot spare

Edited June 16, 2015 by notalot

[PhilNeal]

Please log cases for long report times - bear in mind some reports may be collecting masses of data though

[notalot]

Always do but the issue is the time taken to go from reporting this to local LA Support to Capita back to us is longer than the lock lasts and nothing comes of it.

 

The main issue I see when looking at the actual SQL query is normally accessing the Person table and pulling every bit of data out of it, sadly when this happens the Person table gets locked and nothing else happens in Sims as almost everything accesses the Person table.

 

The problem isn't the report taking a long time the problem is the report locking certain key tables grinding Sims to a halt across the whole network.

[BFCIT]

Thank you for your responses, I think we are using RAID1 on 3 x 300GB SAS drives. We are a secondary school with approx 780 students on roll.

 

Which version of SQL are you using 3s-gtech?

 

I was wondering about the locking out aspect of the database too, in this instance the 'person table' - no matter what version of SQL we run, or how much CPU and RAM power we have - the lock out problems may still persist?

[3s-gtech]

RAID 1 needs pairs of drives - perhaps you have a hot spare too. Your disks should be fast enough - we went further with ours, running our SIMS VM across 8 15k SAS disks in RAID 10, with separate VMDKs for OS and SQL. Gives the best disk speed of any of our servers, but obviously it restricts what else the host can run!

 

We're running SQL Server 2012 (Standard). Seems to be the sweet spot right now; 2008 R2 is getting a bit old and SQL 2014 has some performance issues with SIMS at present. You can't persist with SQL Express though - with the huge amounts of data that secondaries now collect and hold it just won't be even close to being up to the task.