Gongalong Posted February 20, 2012 Posted February 20, 2012 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
vikpaw Posted February 20, 2012 Posted February 20, 2012 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. 1
Gongalong Posted February 21, 2012 Author Posted February 21, 2012 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. 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. 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.
ChrisMiles Posted February 21, 2012 Posted February 21, 2012 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. 1
3s-gtech Posted February 21, 2012 Posted February 21, 2012 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!) 1
Gongalong Posted February 21, 2012 Author Posted February 21, 2012 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. 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).
GeeHubb Posted February 21, 2012 Posted February 21, 2012 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 and 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 1
matt40k Posted February 21, 2012 Posted February 21, 2012 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). 1
3s-gtech Posted February 22, 2012 Posted February 22, 2012 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). 1
vikpaw Posted February 22, 2012 Posted February 22, 2012 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. 1
Gongalong Posted February 22, 2012 Author Posted February 22, 2012 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. 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. 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. 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... 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. 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. 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.
Gongalong Posted February 22, 2012 Author Posted February 22, 2012 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.
jinnantonnixx Posted February 22, 2012 Posted February 22, 2012 (edited) 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. http://www.edugeek.net/forums/mis-systems/78288-sql-server-cpus-100-a-2.html#post698678 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. Edited February 22, 2012 by jinnantonnixx 2
jinnantonnixx Posted February 22, 2012 Posted February 22, 2012 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
Gongalong Posted February 22, 2012 Author Posted February 22, 2012 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. As an aside, we run 100 schools on just one virtual server (we have another on warm standby via log shipping). For SIMS? 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.
jinnantonnixx Posted February 22, 2012 Posted February 22, 2012 For SIMS? I lied. Only 97 for SIMS. I counted test databases too. But seriously, yes all running happily on one server. It's a bit of a beast, mind you. 3> select count(*) from sys.databases where name like 'SIMS%' 4> go ----------- 97 (1 row affected)
jinnantonnixx Posted February 22, 2012 Posted February 22, 2012 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. Not 64 bit? Why on earth not?
Gongalong Posted February 22, 2012 Author Posted February 22, 2012 It's a bit of a beast, mind you. It must be! Not 64 bit? Why on earth not? No idea. Part of the pre-requisites
3s-gtech Posted February 22, 2012 Posted February 22, 2012 2008 R2 has been supported for a good while now, I can't believe they insisted on a 32 bit OS. I'm sure many on here are running it on 64 bit host, with good reason!
jinnantonnixx Posted February 22, 2012 Posted February 22, 2012 2008 R2 has been supported for a good while now, I can't believe they insisted on a 32 bit OS. I'm sure many on here are running it on 64 bit host, with good reason! Absolutely. Unless it's a licensing issue, and times are hard, there's no reason to NOT be on 64 bit, for both OS & SQL.
matt40k Posted February 22, 2012 Posted February 22, 2012 Only thing I can think of, other then licensing, is they've got muddlied up about the fms workstation recommendation - ie don't mix. Servers don't count as you don't work on them. 64bit should be standard now. I know @Polski been doing a fair bit of work on getting the best out of 32bit sql server (licensing issue among other practial problems why the servers stuck on 32bit.)
vikpaw Posted February 22, 2012 Posted February 22, 2012 It's probably because either the cc don't run 64 bit yet, or just think it will be an extra factor for them to support, that they don't want to bother with.
matt40k Posted February 22, 2012 Posted February 22, 2012 I've heard some LA buy secondaries the SQL licenses, could be they didn't get software assurance.
Gongalong Posted February 23, 2012 Author Posted February 23, 2012 Yep, we didn't install SQL, the LA did. We have a Datacenter license(s), but I don't know whether that includes SQL Server. I'm frustrated that we didn't get the best OS for the job, as I don't want to have migrate SIMS again!
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