[sims] SIMS gradually getting slower and slower...

[jinnantonnixx]

Important point - SQL Express edition doesn't have the SQL Agent and SSIS. Both of these are needed for maintenance plans.

 

It's possible to script this as TSQL script which can be run on the server or remotely, but this wouldn't be as trivial to set up.

Edited January 17, 2014 by jinnantonnixx

[siuko]

I am wondering if we could get an Ok you can do this from @PhilNeal ?

 

The script does sound like it would be helpful - but I really don't want to be doing anything with the sims database without capita oking it first.... (yes I'm a chicken... buck buck)

[SpaceInvader83]

We also seem plagued by this , would also be curious to hear if the reindexing management plan as kindly posted by jinnantonnixx helps! Tempted to bite the bullet and give this a go anyway

[CAM]

I'll look in to that, don't suppose there's a guide anywhere? Not a part of the system I use but the timetabler will be very appreciative if it helps!

 

I totally missed this reply! Sorry.

 

LA support team ran a patch or something to fix it for us.

[jinnantonnixx]

We also seem plagued by this , would also be curious to hear if the reindexing management plan as kindly posted by jinnantonnixx helps! Tempted to bite the bullet and give this a go anyway

 

Well, before the reindexing plan was in place, SIMS reporting was like a sloth with gout. After the reindexing plan it's reasonable. Going on this, I'd say it's a worthwhile measure. If you do it, post back with your result either way.

[matt40k]

I am wondering if we could get an Ok you can do this from @PhilNeal ?

 

The script does sound like it would be helpful - but I really don't want to be doing anything with the sims database without capita oking it first.... (yes I'm a chicken... buck buck)

 

He won't. Reindexing is a most basic DBA task, it's not rocket science to do - however it assumes a number of things - like it's done out of hours, that you have a correct backup before you start, that if your system is in a poor state at the start, you have a pragmatic approach to tackling it - ie not doing everything at once. You might find that your log file grows to the point that it fills your disc, so SQL won't start, or if you've been silly enough to put SQL onto your OS drive, stop your computer from booting.

 

The safe option is to hire a DBA to do it for you.

 

Performance wise it really depends. If it's in a pretty poor state you'll is a absolute massive increase, well worth any risk.

[SpaceInvader83]

Hi just to report back, did this on our SIMS db last night and for example on a small report I ran before took around 4 minutes and 1 minutes 30 seconds after reindexing.

 

Thankyou!

[mrbios]

The reindexing patch also fixed the issue (somewhat) for myself quite a while back. However SIMS Discover is starting to take a very long time to do transfers again....so question is should we be running this patch every 6-12 months or something to keep things fast?

[zag]

Hi just to report back, did this on our SIMS db last night and for example on a small report I ran before took around 4 minutes and 1 minutes 30 seconds after reindexing.

 

Thankyou!

 

Thats good enough for me!

 

Will give it a go now.

[jinnantonnixx]

Hi just to report back, did this on our SIMS db last night and for example on a small report I ran before took around 4 minutes and 1 minutes 30 seconds after reindexing.

 

Thankyou!

 

Did you reindex (with the SIMS patch) or rebuild (with the maintenance plan)?

[SpaceInvader83]

Did you reindex (with the SIMS patch) or rebuild (with the maintenance plan)?

 

Maintenance plan

[zag]

In the 'Define Reuild Index task' window, choose your SIMS database(s) to reindex.

For 'object', choose 'tables and views'

On the same window, check the box that says 'sort results in tempdb', keep the box that says 'keep index online...' unchecked.

 

I'm using SQL server 2012 and don't have the 'sort results in tempdb' check box or 'keep index online'. Will that make any difference?

 

[jinnantonnixx]

You've chosen the 'Reorganise Index' task. Try the 'Rebuild Index' task instead. This will ensure that the query optimiser has the best stats.

[SpaceInvader83]

Well my success was short lived, 3 hours in and performance seems to have taken a nose dive when the admin girls are running big reports, they were all running pretty speedily first thing, meh! Server looks to be ticking over not doing alot, anyone have any advice on any tell tale signs it's a server performance issue?

[jinnantonnixx]

Oh no!

 

See if there's anything useful here. Get to know SQL Activity Monitor.

 

http://www.edugeek.net/forums/mis-systems/112841-speeding-up-sims.html

Edited January 29, 2014 by jinnantonnixx

[zag]

You've chosen the 'Reorganise Index' task. Try the 'Rebuild Index' task instead. This will ensure that the query optimiser has the best stats.

 

Thanks, I've set it up properly now

[Olliesaurus]

My SIMS manager had a huge great big go at Capita regarding a load of cases that werent being followed up, one of them being the speed issue.

 

They have connected through and have setup a trace report through SQL profiler and have said that they will send it off to the developers to see if there are any issues, I dont know if its worth anyone else pushing for one of these, if the issue appears to be the same then they might do something about it.

[simpsonj]

Apologies for raising this thread from the dead, but I thought it as worth mentioning that our simple reports (Name, reg, address block, previous school for one year group) would time out and not run at all. Ran the 'Rebuild index' as stated by @jinnantonnixx and they now run within a few minutes. Still warns the report will take a long time to run, but at least I'm getting the reports now!

[PhilNeal]

We've done some work on the report generator (included in summer) that will speed up areas that have been reported as slow.

[michael2k6]

Hi all,

 

I've not used SIMS, nor am I familiar with implementation, recommended specs / configuration etc, and I currently (recently) work for an alternative MIS, so I can't comment on SIMS technically, but I've got a bucket of SQL experience, so I've popped up to help out if it's possible. Obviously, the usual caveats, it's at your own risk, don't do anything that Capita do not approve of, back it all up, twice, etc. This is none MIS related general SQL advice. If anyone has an issue with anything in the post please let me know and I'll change / remove it.

 

I've seen performance issues before with SQL databases and the replies here don't surprise me. What I have noticed previously though is that rebuilding or reorganising indexes will help to a point but when you've used the same software with a slowly growing db over so many years, and not done that previously, it can't hurt to do it, but a solution like that sometimes masks an issue rather than solves it.

 

SQL will cache as much data as it can (including whole DB's) to RAM, where the RAM is available and the O/S isn't sending out pressure warnings for memory to be released. Have a look at performance monitor and look at the page life expectancy counter. The higher the number the better, essentially, the higher the number, the longer a piece of data can be cached in RAM. I'm keeping this fairly basic as I have no idea how big these databases are or can grow to.

 

Something that might be common across these issues is that I've spotted a few virtual machines running on various types of host. In these cases, where data is stored on a SAN, look at disk contention. Where are you storing the db's? Ideally, you should be storing them away from anything with high disk contention - don't bang em on the same RAID 5 as your file storage on a different partition, for example. Ideally, store them on their own disks, and on a different LUN. Even more ideally, store tempdb on a different LUN to your user databases, but I think that's probably overkill for this use.

 

The fastest cheapest way people tend to achieve that is to add a set of local disks in a RAID 5 to the host machine and move all databases to that (including system DB's). Obviously in the greater realms of database tuning, you ideally need to keep tempdb away from the user databases, and look at keeping log files (if you're using full recovery model) on a different drive to the database files - more on log files in a moment. You can then look at storing tables and views in different mdf's and storing them separately, usually based on their usage (amount, and read / write). Again, all of this is probably overkill for this kind of purpose. But I can fully recommend putting them on their own set of disks, if you have that luxury, and seeing if it drastically improves performance.

 

Pay particular attention to your log files. Again, not knowing the SIMS architecture, I don't know whether the database is setup in full or simple recovery model.

A log file isn't a never ending log of what's happened in the database. The log file processes all queries to the database, and if they are successful, they remain and are shifted to the mdf. If they are unsuccessful they are rolled back and never make it to the mdf. By default a database commit is made at each checkpoint or where a specific query is written ending COMMIT TRANSACTION. So a hell of a lot depends on the ldf file. Many people think it's some sort of audit trail in case someone deletes a student, for example, which is not the case.

A database has a few different options for recovery model. Full and Simple are the most common. When set to full, the LDF file grows until it you backup and shrink it. You cannot shrink without a backup. Usually where that's the case it's built into a maintenance plan. The other option is simple. Simple looks empty data from the LDF at each check point. It does not release that free space back to the filesystem unless 'auto shrink' is checked. So that means that if may be 2gb in size, for example, but only 1gb is in use, so the next 50% of the log file already has space allocated on disk, it's just not using it yet.

If we begin simple and consider the impact that it will have on a single disk, it becomes obvious quickly that if you have the mdf and ldf (or multiples) of your user database on the same disk, and you also have your tempdb on there, and potentially other databases too (which are all using the same tempdb), that disk is going to be crazy busy. Then consider that when it needs to grab some space for db1 it does so at whatever rate it sets. Then the same for db2. Etc. Data very quickly becomes fragmented across a disk. Not only does it become fragmented across a disk but it becomes fragmented within its own pages. More on that in a moment, lets stick to disks for now. A log file writes serially. So if a log file is on it's own disk, the read/write heads stay in the same place, if it's on a disk contended with the other files, the heads are all over the place.

So if you have your db set to full, and the log file is forever growing, you have the data getting further apart and becoming more fragmented. If you set it to simple it will use quite a bit of resources constantly clearing it, but then at least it is using the same free space over and over (unless you've got it set to shrink, or it needs to grow beyond its currently allocated space).

 

So now we've been over disks and fragmentation of db files over the disks and disk contention, the other thing to consider if the fragmentation of the indexes themselves. Within Consider a dictionary. The dictionary is index, the bookshelf is the hard drive or array. It's in alphabetical order, if you're looking for N, it's between M and O, regardless of what page that's on you find it quickly. If we suddenly think of some new N words, we cant bang in a new word onto a full page. So it creates a new page for it on the end. That's where the index fragmentation comes from. So you end up with free space then on that new page too.

When you rebuild an index, it will do what it says on the tin. Which is why it's important. The pages then all end up in order. What you have to consider at the point of rebuild is the fill factor. It's 1 - 100 (warning - 0 is unlimited and same as 100) and used as a percentage. If you set it to 0 it will fill every page to its capacity. If you set it to 50, it will half fill each page. Which means when it needs to add something, it's got room to add it. The downside of that is you end up with a load of free space.

So... most issues here are with running reports, which is a read activity. The fastest read activity is going to be where those pages are full, so i'd say use 0 and fill every page. If you set it to 50, for example, the read performance may degrade by two times as it's looking through all the free space. But the writes will be improved.

So that's why rebuilding / recreating your indexes is helping.

 

A tip for those creating new databases (away from SANs) is to defrag the disks first, then have the initial size of the database around 150% of the necessary size do that for the log file too. As it will grab the free space at that time, it will be contiguous on the disk.

 

Another tip is ensure that the log file is set to grow by the necessary rate. If it's set to grow 1mb at a time for example, in heavy use, it may be growing, waiting, growing, waiting, etc. Use a % instead, usually about 10.

Also check that there isn't a size limit on the log file. If it reaches a limit in full recovery model, it will simply stop working. If it reaches a limit in simple recovery model it will usually perform a commit and gain enough free space in time for the query to be successful, but it will slow things down considerably. Limits to log files were necessary in the days of smaller disks but these days and on databases of these sizes its a bit mad to apply one. Monitor it by all means, but don't limit it.

 

As I said at the start of the thread I hope I'm not treading on anyone's toes or going against anything Capita would advise, in which case, definitely take their instruction over mine! My knowledge is indeed limited and more from the times of SQL 2005 into 2008 than 2012. But if any bit of it helps one school, it was worth writing.

But that did take quite a bit of writing and I do feel like I deserve a corona, so that's where I'll be if anyone wants me!

 

There are more things springing to mind, but I think that will do for tonight. Especially for this size of implementation, it's probably overkill as it is. As usual, if anyone has any questions or wants to chat further you're welcome to PM me.

 

Mic

[GREED]

Awesome response Mic, really great advice on how to maximise services and performance of servers. I am sure most elements can be used as many setups I have worked with have a SQL Server setup that is NOT dedicated to just SIMS. Was reading down your recomendation list and I remember doing about half of those things (in particular spreading out the physical location of the default and required databases, and the log file setup) when I was still in schools. Really really useful.

 

It would be great if a Capita bod could comment on things that should NOT be done from this list, due to SIMS not being able to cope with it, and/or offering alternatives.

[michael2k6]

Thanks Graham, absolutely agree; if someone from Capita wants to pop along and comment on things which they would disagree with, I'll Subtitle that section of the post with a note which says "don't apply on an instance of SIMS as capita do not recommend this"; that way it remains relevant for anyone with a SQL instance of something else running, but doesn't hinder anyone with a SIMS install.

I've already had a PM thanking me which is nice, as I said to him if it helps one school to get information out to those who need it faster than previously, and kills some earache for our colleagues in the server rooms, it was worth the post

 

- - - Updated - - -

 

On another note, I have no EduLime, so I won't waste a Corona.

[pcstru]

Good Stuff @michael2k6.

 

Not to troll, but it is almost always the case that database performance issues are due to database design issues. It really is that simple.

[michael2k6]

This is generally true. As I said though, I have no idea about the database, having never used it let alone written it, I shan't cast aspersions. It's also quite difficult to build a database for this kind of use in a generic performance tuned manner as they perform such a vast array of tasks and each school will use each bit of it to a different extent. So it's about finding the best balance across the board.

But hopefully regardless of the db design and structure, the best practice principles in my post will apply to anything; if you buy a 400bhp car and put £50 a corner rubber on it, you're coming off at the next roundabout. At least you are as soon as you start to push it

[pcstru]

It's also quite difficult to build a database for this kind of use in a generic performance tuned manner as they perform such a vast array of tasks and each school will use each bit of it to a different extent. So it's about finding the best balance across the board.

While it is difficult to some degree, I would hope that suppliers employ people who have skill, knowledge and experience enough that what is difficult for most, is fairly mundane for them. For a supplier to turn round and say "your DB is slow because your use is 'unusual'" sounds like an excuse. Their job is to provide a product that copes with the demands of their customers - even 'unusual' demands.

 

So when you have :

 

"Hi just to report back, did this on our SIMS db last night and for example on a small report I ran before took around 4 minutes and 1 minutes 30 seconds after reindexing."

 

The fact that a "small report" is taking a minute and a half not seconds, should ring alarm bells. Not that it is not possible to ask an SQL database questions which involve scanning hundreds of millions of records to collect and aggregate data to give the answer, but in a school, it should be ... 'unusual'. And in these days of multicore multi threaded processors capable of executing 10^9 instructions per second, and with enough RAM to hold the entirety of the data in memory, mundane everyday queries should be dispatched by the DB server in fractions of a second.