+ Post New Thread
Page 1 of 2 12 LastLast
Results 1 to 15 of 24
MIS Systems Thread, Optimising Edit Marks (Lesson Monitor) in Technical; Hi folks, Although I'm relatively new to the school (and education full stop) we have a long-standing problem here with ...
  1. #1
    Gongalong's Avatar
    Join Date
    Oct 2011
    Location
    United Kingdom
    Posts
    868
    Thank Post
    776
    Thanked 17 Times in 15 Posts
    Rep Power
    9

    Question Optimising Edit Marks (Lesson Monitor)

    Hi folks,

    Although I'm relatively new to the school (and education full stop) we have a long-standing problem here with Edit Marks.

    A number of teachers still use paper registers, and one of the administrators has to manually enter these using the above function. The function is also used to view and then print registers in the case of a fire (and a fire drill).

    The problem is that opening this view for a year group takes about 40 seconds, and opening for the whole school typically crashes the SIMS client. This is causing the administrator who works with Edit Marks to be driven barmy.

    Coincidentally we migrated our SIMS server over half term to a Hyper-V VM (quad core, with 8GB RAM. The CPU is definitely seeing usage, and the current memory footprint is around 3GB). I had hoped this would improve the performance as it has been running previously on a 5-year old physical server, albeit a reasonable specced one. Over half term the search results were halved, but testing today the search speeds are more or less the same as they were.

    Is anyone aware of a way to improve the Edit Marks search speed, and/or improve SIMS performance within Hyper-V?

    TIA

  2. #2

    vikpaw's Avatar
    Join Date
    Sep 2006
    Location
    Saudi Arabia
    Posts
    5,848
    Thank Post
    671
    Thanked 1,383 Times in 1,145 Posts
    Rep Power
    350
    The issue has been mentioned a few times on SupportNet, it's just accepted that Edit Marks is a slow area to work in, especially if you're pulling back a weeks worth of attendance, possibly lesson marks too for the whole school. Easier to do it by year.

    You could look at the client, as it will be caching that data, and pulling it down, i'm sure the query itself isn't taxing, but what sims does with the data is what makes the screen freeze for the client.

    There are changes afoot, in coming release, regarding protecting marks entered via edit marks, and you never know the changes may help performance too.

  3. Thanks to vikpaw from:

    Gongalong (21st February 2012)

  4. #3
    Gongalong's Avatar
    Join Date
    Oct 2011
    Location
    United Kingdom
    Posts
    868
    Thank Post
    776
    Thanked 17 Times in 15 Posts
    Rep Power
    9
    Quote Originally Posted by vikpaw View Post
    The issue has been mentioned a few times on SupportNet, it's just accepted that Edit Marks is a slow area to work in, especially if you're pulling back a weeks worth of attendance, possibly lesson marks too for the whole school. Easier to do it by year.
    When you say easier to do it by year... you mean open a year at a time? The problem is this is still taking our administrator 40 seconds, and she switches backwards and forwards between different years a lot.

    I must profess I'm not a user though, so it may be she's not working using the most efficient process.
    Quote Originally Posted by vikpaw View Post
    You could look at the client, as it will be caching that data, and pulling it down, i'm sure the query itself isn't taxing, but what sims does with the data is what makes the screen freeze for the client.
    It doesn't seem to cache the search, curiously. If I run the same search it takes pretty much the same amount of time to run.

    The query does seem to tax the SIMS server. If I run a whole school query the CPU will jump to 100% for the bulk of the search, so it's in part processor dependent. On the face of it the query wouldn't seem to be that complex, and DA friends say it should take milliseconds to run. I'm also told though that the table structure within SIMS is overly complex, which may be exacerbating the problem.
    Quote Originally Posted by vikpaw View Post
    There are changes afoot, in coming release, regarding protecting marks entered via edit marks, and you never know the changes may help performance too.
    Fingers crossed it does.

    We've raised this with Capita via the CC, but it remains to be seen whether this effects any program change.

  5. #4

    Join Date
    Mar 2011
    Location
    Bournemouth
    Posts
    280
    Thank Post
    16
    Thanked 74 Times in 64 Posts
    Rep Power
    21
    Quote Originally Posted by Gongalong View Post
    The query does seem to tax the SIMS server. If I run a whole school query the CPU will jump to 100% for the bulk of the search, so it's in part processor dependent. On the face of it the query wouldn't seem to be that complex, and DA friends say it should take milliseconds to run. I'm also told though that the table structure within SIMS is overly complex, which may be exacerbating the problem.
    If running the queries maxes out the CPU then providing more resources for your server will obviously speed up the process. I know you cannot increase the number of processors on a hyper-v guest to more than 4 which might mean that you should either get faster processors for your hyper-v host or run the sims database on a physical server. While I am sure that the sims client struggling to handle all the data is a large part of the problem, there is still time to be saved on the database backend.

    Our marks tables contain millions of rows and from since forever. Its possible the query is overly complex or the indexing in the database is incorrect or insufficient. There are loads of ways I could suggest to improve IO performance for sims in hyper-v but it doesn't sound like an IO problem since the cpus are maxing out. The sims database is overly convoluted in several areas including marks and since none of the data ever seems to get archived out of the day-to-day working tables it doesnt take too long before the simplest reg marks query can have to search through millions and millions of rows every time. Its a shame you can't really run the sql profiler on the query and see what's really holding you up.

    SO anywya, since fiddling round with the indexing of the sims database is likely to get your support cancelled if nothing else there's not much you can do except for throw more resources at the server and the client.

  6. Thanks to ChrisMiles from:

    Gongalong (21st February 2012)

  7. #5

    3s-gtech's Avatar
    Join Date
    Mar 2009
    Location
    Wales
    Posts
    2,712
    Thank Post
    144
    Thanked 548 Times in 492 Posts
    Rep Power
    149
    I hosted our SIMS server briefly on an ESXi VM for a short while, while I prepped the old one (HP DL140 G3) to take the role back but with 2008 R2 and 8GB of RAM. The VM host is a good specced server, but the physical box destroys it - we found these crashes happening a lot on the VM, never (touch wood) on the new physical box. Are you running full SQL server and not SQL Express too, as that will nail performance (you say the memory footprint is 3GB so I guess so!)

  8. Thanks to 3s-gtech from:

    Gongalong (21st February 2012)

  9. #6
    Gongalong's Avatar
    Join Date
    Oct 2011
    Location
    United Kingdom
    Posts
    868
    Thank Post
    776
    Thanked 17 Times in 15 Posts
    Rep Power
    9
    Quote Originally Posted by ChrisMiles View Post
    If running the queries maxes out the CPU then providing more resources for your server will obviously speed up the process. I know you cannot increase the number of processors on a hyper-v guest to more than 4 which might mean that you should either get faster processors for your hyper-v host or run the sims database on a physical server.
    It was on a physical server until half term, with the same behaviour. It was an older server, but I get the feeling that even if we put the latest and greatest processors in a physical server it would still max them out. I was sure on previous searches that the searches would only run on one core, but having tested recently on the VM they seem to be multicore. Confusing!

    It's possible with Hyper-V to shift the priority of the server, so I might try that. Our Hyper-V servers are hardly taxed. Changing the CPU type has had no effect at all.

    Following the CC's advice we have already archived all irrelevant attendance data. It had no effect whatsoever.
    Quote Originally Posted by 3s-gtech View Post
    I hosted our SIMS server briefly on an ESXi VM for a short while, while I prepped the old one (HP DL140 G3) to take the role back but with 2008 R2 and 8GB of RAM. The VM host is a good specced server, but the physical box destroys it - we found these crashes happening a lot on the VM, never (touch wood) on the new physical box. Are you running full SQL server and not SQL Express too, as that will nail performance (you say the memory footprint is 3GB so I guess so!)
    We're running it on 2008 SP2 Enterprise 32 bit, as we were told it hasn't been certified for R2 and there are issues.

    I suppose physical will always win over virtual where power is an issue, which it is for these searches at least. We've yet to have any crashes though, at least no more than when it was on the physical server.

    Yes, running SQL Server 2008 (installed by the CC).

  10. #7
    GeeHubb's Avatar
    Join Date
    Aug 2010
    Location
    Suffolk
    Posts
    182
    Thank Post
    17
    Thanked 37 Times in 32 Posts
    Rep Power
    57
    Quote Originally Posted by Gongalong View Post
    Hi folks,

    Although I'm relatively new to the school (and education full stop) we have a long-standing problem here with Edit Marks.

    A number of teachers still use paper registers, and one of the administrators has to manually enter these using the above function. The function is also used to view and then print registers in the case of a fire (and a fire drill).

    The problem is that opening this view for a year group takes about 40 seconds, and opening for the whole school typically crashes the SIMS client. This is causing the administrator who works with Edit Marks to be driven barmy.


    TIA
    Hi Gongalong,

    We don't have SIMS in the classroom, so I too, enter all attendance marks via the edit marks screen.

    The way I do it is to enter the marks a registration group at a time (our registration groups are typically between 25 and 30 pupils). From the dropdown box, choose reg group, and highlight all the reg groups that you want to access. I have the paper registers in the same order and then use the <next> and <previous> buttons to scroll in order between the groups. Only takes 4 or 5 seconds to save a reg group, by which time I've opened up the next paper register.

    HTH, Gee

  11. Thanks to GeeHubb from:

    Gongalong (22nd February 2012)

  12. #8

    matt40k's Avatar
    Join Date
    Jun 2008
    Location
    Ipswich
    Posts
    4,384
    Thank Post
    368
    Thanked 637 Times in 519 Posts
    Rep Power
    158
    It's important to point out the key with SQL is disc IO, which normally is poor if you've virtualized everything onto a SAN. If you run perfmon then look at the average disc queue length, your aiming for 3. If your getting hundreds it's going to lock up\timeout - remember your network is only as good as your weakest point.

    With regards SIMS lesson monitor - have you archive the old attendance marks? Have you ran the reindexing patch? This is the first port of call. Next would be the PAE fixes (as it's 32bit).

  13. Thanks to matt40k from:

    Gongalong (22nd February 2012)

  14. #9

    3s-gtech's Avatar
    Join Date
    Mar 2009
    Location
    Wales
    Posts
    2,712
    Thank Post
    144
    Thanked 548 Times in 492 Posts
    Rep Power
    149
    I know ours would seriously struggle on a 32bit OS, and we're not a huge school. SQL will load into RAM as much as possible - this seriously helps if you have plenty of it as it should reduce the disk IO (which still needs to be good as above).

  15. Thanks to 3s-gtech from:

    Gongalong (22nd February 2012)

  16. #10

    vikpaw's Avatar
    Join Date
    Sep 2006
    Location
    Saudi Arabia
    Posts
    5,848
    Thank Post
    671
    Thanked 1,383 Times in 1,145 Posts
    Rep Power
    350
    Interesting.
    I can load a year group in 5-10 secs. The whole school in under a minute.

    When i mentioned caching, i was referring to the data that is retrieved. If you look at the client it's not the CPU that necessarily rockets, but the memory used goes up a lot. So the client needs decent spec too as i think it will be holding all that data. Also the golden rule with SIMS clients especially when you have high level permissions, close SIMS and give it a 5 minute break at least every hour to claw back the memory - and take it as the health & safety break on the eyes of the operator

    Look at what the operator is doing too, flicking between year groups doesn't make sense, i'd re-arrange the paper side so your input / editing is more efficient.

    On the server front i've found virtual is more efficient, and gives you all the other benefits too even if it isn't any faster. I've never really needed to look at that side because it seems to load fairly quickly for us. With regards to CPU, i think you'll find that anything will spike your CPU, as it's designed to take all the resource you can give it, so if nothing else needs it, it will take all the juice you can give it, so it finishes quicker. I don't think you'll ever get to the stage where it doesn't spike, it will just spike for a shorter period with increased resource. If you search around this forum for posts from @jinnantonnixx formerly jinnantonnix he is the SQL ninja and has a lot of info regarding SQL performance tuning, there are quite a few threads on the topic too.

    Try @matt40k 's suggestions and see what the cc come back with.

    I'd try the same on a client installed on the SIMS server itself and see if that makes any difference.

  17. Thanks to vikpaw from:

    Gongalong (22nd February 2012)

  18. #11
    Gongalong's Avatar
    Join Date
    Oct 2011
    Location
    United Kingdom
    Posts
    868
    Thank Post
    776
    Thanked 17 Times in 15 Posts
    Rep Power
    9
    Quote Originally Posted by GeeHubb View Post
    The way I do it is to enter the marks a registration group at a time (our registration groups are typically between 25 and 30 pupils). From the dropdown box, choose reg group, and highlight all the reg groups that you want to access.
    Ah, interesting! I didn't realise you could select multiple groups. Thanks for the tip! I'll pass this on to the administrator.
    Quote Originally Posted by matt40k View Post
    It's important to point out the key with SQL is disc IO, which normally is poor if you've virtualized everything onto a SAN. If you run perfmon then look at the average disc queue length, your aiming for 3. If your getting hundreds it's going to lock up\timeout - remember your network is only as good as your weakest point.
    Average disk queue length (ADQL) is all over the place. Having monitored it for a couple of minutes I saw values of 30, all the way down to fractional figures.

    I ran a whole school query, which again maxed out the processor (mostly) and then I saw peak ADQL values of around 70, but with average values around 1-2.
    Quote Originally Posted by matt40k View Post
    With regards SIMS lesson monitor - have you archive the old attendance marks? Have you ran the reindexing patch? This is the first port of call. Next would be the PAE fixes (as it's 32bit).
    Yes, old attendance marks have been archived, but with zero benefit. We were asked to run a patch by Capita, so it seems likely this was the reindexing patch. Not sure what PAE fixes are.
    Quote Originally Posted by 3s-gtech View Post
    I know ours would seriously struggle on a 32bit OS, and we're not a huge school. SQL will load into RAM as much as possible - this seriously helps if you have plenty of it as it should reduce the disk IO (which still needs to be good as above).
    I'm confused then as to why the CC have told us we can't run it on a 64 bit OS...
    Quote Originally Posted by vikpaw View Post
    Interesting. I can load a year group in 5-10 secs. The whole school in under a minute.

    When i mentioned caching, i was referring to the data that is retrieved. If you look at the client it's not the CPU that necessarily rockets, but the memory used goes up a lot. So the client needs decent spec too as i think it will be holding all that data. Also the golden rule with SIMS clients especially when you have high level permissions, close SIMS and give it a 5 minute break at least every hour to claw back the memory - and take it as the health & safety break on the eyes of the operator
    Yes, I've noticed a memory leak in PULSAR.EXE. It can easily get close to 1 GB of RAM for that process alone. The PCs are just about coping with this.
    Quote Originally Posted by vikpaw View Post
    Look at what the operator is doing too, flicking between year groups doesn't make sense, i'd re-arrange the paper side so your input / editing is more efficient.
    Our administrator is probably not doing this. I suspect the blame is being attributed more to SIMS, without actually trying to think through workarounds. The difficulty though is she's inputting paper registers from teachers that are unable to access a laptop, so the classes are probably quite random.
    Quote Originally Posted by vikpaw View Post
    On the server front i've found virtual is more efficient, and gives you all the other benefits too even if it isn't any faster. I've never really needed to look at that side because it seems to load fairly quickly for us. With regards to CPU, i think you'll find that anything will spike your CPU, as it's designed to take all the resource you can give it, so if nothing else needs it, it will take all the juice you can give it, so it finishes quicker. I don't think you'll ever get to the stage where it doesn't spike, it will just spike for a shorter period with increased resource. If you search around this forum for posts from @jinnantonnixx formerly jinnantonnix he is the SQL ninja and has a lot of info regarding SQL performance tuning, there are quite a few threads on the topic too.

    Try @matt40k 's suggestions and see what the cc come back with.

    I'd try the same on a client installed on the SIMS server itself and see if that makes any difference.
    Thanks for all that.

    It's rare that anything spikes any of the server CPUs, although SIMS is clearly a different animal to a file server (I'm a little naive in the server world). These Lesson Monitor searches do seem to really push the server though. My thinking is that if there's any way we can get the physical host to give more resource to the SIMS VM then it's a good thing, as it will cut down these search times, and the frustration of the administrator.

  19. #12
    Gongalong's Avatar
    Join Date
    Oct 2011
    Location
    United Kingdom
    Posts
    868
    Thank Post
    776
    Thanked 17 Times in 15 Posts
    Rep Power
    9
    Ah, my ADQL figures may have been way off. The perfmon scale is a little difficult to understand, but it seems like the values are all fractional and typically very small.

    Here are a couple of graphs from perfmon. The first shows searches for three different years (the three different "spikes"). The second shows a whole school search, which doesn't finish within the graph. You can see the CPU loading. Curiously the year searches don't max out the processor, but still take a long time relatively speaking.

    perfmon1.jpgperfmon2.jpg

  20. #13

    jinnantonnixx's Avatar
    Join Date
    Mar 2011
    Location
    In the Calamatorium.
    Posts
    1,954
    Thank Post
    109
    Thanked 482 Times in 331 Posts
    Blog Entries
    2
    Rep Power
    281
    Quote Originally Posted by ChrisMiles View Post
    If running the queries maxes out the CPU then providing more resources for your server will obviously speed up the process. I know you cannot increase the number of processors on a hyper-v guest to more than 4 which might mean that you should either get faster processors for your hyper-v host or run the sims database on a physical server. While I am sure that the sims client struggling to handle all the data is a large part of the problem, there is still time to be saved on the database backend.

    Our marks tables contain millions of rows and from since forever. Its possible the query is overly complex or the indexing in the database is incorrect or insufficient. There are loads of ways I could suggest to improve IO performance for sims in hyper-v but it doesn't sound like an IO problem since the cpus are maxing out. The sims database is overly convoluted in several areas including marks and since none of the data ever seems to get archived out of the day-to-day working tables it doesnt take too long before the simplest reg marks query can have to search through millions and millions of rows every time. Its a shame you can't really run the sql profiler on the query and see what's really holding you up.

    SO anywya, since fiddling round with the indexing of the sims database is likely to get your support cancelled if nothing else there's not much you can do except for throw more resources at the server and the client.
    Too true. It really could do with a tidy-up. Hands up who still has Core Integrator tables in their databases left over from 2004? Having a fill factor of 100 doesn't help too much either.

    ---

    The first thing to do it get on to 64 bit.

    Most likely is a self-imposed bottleneck. Running your OS, data, log and temp on the same disk is like running a car with the brakes stuck on. Yes, you can add a bigger engine as a "solution", but fix the brakes first. I'll post another link when I find it.

    That said, it could also be deadlocks, blocked SPIDS, etc. SQL diagnostics is a bit of a mess unfortunately.

    As an aside, we run 100 schools on just one virtual server (we have another on warm standby via log shipping).

    My current projects have me working on Linux servers these days so I'm already very rusty on the old Windows+SQL. There must only be a certain amount of RAM in the old noggin. Pity.

    Anyway, here's a bit of code that shows the CPU 'hogs' on your system.

    SQL Server CPUs at 100%

    If you have SQL Server Management Console, get yourself acquainted with the Activity Monitor (Right-click the server>Activity Monitor). It's a great tool.

    Look at Processes, see if you can see anything under 'Block by'. Also look under 'Recent Expensive Queries', this is quite likely to give you a clue as to what's chewing up your system.
    Last edited by jinnantonnixx; 22nd February 2012 at 10:39 AM.

  21. 2 Thanks to jinnantonnixx:

    Gongalong (22nd February 2012), vikpaw (22nd February 2012)

  22. #14

    jinnantonnixx's Avatar
    Join Date
    Mar 2011
    Location
    In the Calamatorium.
    Posts
    1,954
    Thank Post
    109
    Thanked 482 Times in 331 Posts
    Blog Entries
    2
    Rep Power
    281
    Another thing - database auto-grow (and worse, auto-shrinks) can cause seemingly unexplained performance drop outs. Check to see that your databases are properly sized.


    This is a great article and should be essential reading for all lumbered with setting up SQL systems. It's a well written piece and even if it's not applicable, you'll learn a lot about what's going on under the hood.

    Poor Disk Performance, Time Outs, Database and the SQL Server Error Log

  23. #15
    Gongalong's Avatar
    Join Date
    Oct 2011
    Location
    United Kingdom
    Posts
    868
    Thank Post
    776
    Thanked 17 Times in 15 Posts
    Rep Power
    9
    Quote Originally Posted by jinnantonnixx View Post
    The first thing to do it get on to 64 bit.
    Unfortunately we were told by the CC that it can't be 64 bit. The database was just migrated onto the (32 bit) VM at the highest specced OS, which was 2008 SP2 Enterprise.
    Quote Originally Posted by jinnantonnixx View Post
    As an aside, we run 100 schools on just one virtual server (we have another on warm standby via log shipping).
    For SIMS?
    Quote Originally Posted by jinnantonnixx View Post
    If you have SQL Server Management Console, get yourself acquainted with the Activity Monitor (Right-click the server>Activity Monitor). It's a great tool.
    We had this installed coincidentally trying to resolve a problem with a VLE export. I've opened it up, but don't pretend to understand all of it.

    Generally speaking SIMS is functioning acceptably, aside from these Lesson Monitor queries.

SHARE:
+ Post New Thread
Page 1 of 2 12 LastLast

Similar Threads

  1. PARs vs Lesson Monitor
    By TechMonkey in forum MIS Systems
    Replies: 8
    Last Post: 15th January 2008, 11:19 AM
  2. Lesson Monitor Error
    By Gatt in forum MIS Systems
    Replies: 14
    Last Post: 6th October 2006, 07:41 AM
  3. Lesson Monitor - ODBC Error
    By Gatt in forum MIS Systems
    Replies: 6
    Last Post: 6th June 2006, 02:39 PM
  4. SIMS/Lesson Monitor Error
    By Gatt in forum MIS Systems
    Replies: 7
    Last Post: 12th May 2006, 10:43 AM
  5. DIY Lesson Monitor
    By mark in forum Educational Software
    Replies: 7
    Last Post: 3rd March 2006, 03:09 PM

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •