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

[Olliesaurus]

Has anyone experienced any issues with SIMS recently where it will gradually get slower and slower until it just halts?

 

Let me set the scene,

 

April 2013: New SIMS install, all running perfectly and nice and fast, 8GB RAM

October 2013: Start seeing decrease in speed, namely on larger reports.

November 2013: Capita suggest upgrading the RAM to 16GB, upgrade the server RAM to 24GB, restrict SQL to 16GB RAM, still slow speed on larger reports

December 2013: Capita taking screenshots of RAM usage but still slow speed on larger reports

 

A friend of mine is experiencing something a-like it, just wondered if there was something that I am doing glaringly wrong...

 

Needless to say, very grumpy SIMS manager at the moment...

[Chockster]

We've had a very similar experience. When arrived this time last year sims was playing nicely on an 8GB single core VM, then gradually through the year (mainly after the spring update) larger reports have brought the server to its knees. During the summer we gave the VM 16GB and 3 more cores, but that made no difference. The pinnacle of this was running a change of holidays a couple of weeks ago, that took nearly ten hours to run. Not fun.

[LosOjos]

We've had an issue that seems to have started with the summer update whereby clicking anything in course manager results in an egg timer and 5-10 minute wait. Local support said we need more RAM, which helped a little but not much. The Autumn census was a nightmare!

[nickbro]

Same here, SIMS is getting slower and slower at the moment

[mrbios]

Not sure if it's related but i had performance issues up until i ran a patch which reindexed all the tables in the sims database.

 

Might be worth asking for, can't hurt to run it even if it doesn't fix it.

[Olliesaurus]

I have run that reindexing patch not long ago, worked alright for the morning then went back to slowness, I have been given a patch relating to Discovery, however it apparently does some "internal maintenance", I just nodded and accepted, running tonight.

 

It does look like its an issue with SIMS as opposed to peoples servers, which was my worry, it is definitely a relief to know that others are experiencing the same issue that I am.

 

Fingers crossed the Autumn update will address some of these issues, not all that confident that it will though...

[rj_mayer]

SIMS doesn't manage the SQL log files for you... Might be worth taking a look there.

[Olliesaurus]

SIMS doesn't manage the SQL log files for you... Might be worth taking a look there.

 

Yeah, I shrunk the logs but still slow :/

 

Will see what Autumn brings.

[Cache]

I'm having the same issue, very noticable with reports where today we are back at the situation we were 2 months ago where all reports say they are going to take a long time to run.

 

Going to try a server restart tonight, if that doesn't help then it's the reindex patch again I guess!

 

Edit: Server rebooted, reindexing patch applied - still can't run any reports wihtout it saying it's going to take a long time

Edited December 2, 2013 by Cache

[hallb15]

We're getting the same thing here too.

[danrhodes]

Were having the same issues also, I manage Sims at a few sites and its same all over, very sluggish even loading. We run sims at one site on Dual 8 Core Xeon 48GB Ram and its still slow What ever we have tried seems to make no difference.

[jinnantonnixx]

I'm convinced the problem is to do with indexes and statistics. The reindex patch doesn't update the stats.

 

Hint to make marks run faster: att_mark_normalised could do with some more indexes to stop RID/bookmark lookups. e.g. create an NCI on att_mark_normalised (session_start) INCLUDE person_id, mark, session_number.

 

 

 

I've noticed an improvement after running the following maintenance plan. Also, if you do this regularly the 100% fill factor is less of an issue.

 

Try setting up a maintenance plan to rebuild (not reorganise) your indexes.

 

Usual disclaimers, at your own risk and so on. If you're not confident with this procedure, do not proceed.

 

 

Start SQL Management studio

Expand 'Management'

Right-click 'Maintenance Plans'

Click 'Maintenance Plan Wizard'

 

Name you plan something like 'Reindex SIMS'.

On the same screen, click the 'Change' button next to Schedule

Set a suitable schedule (Frequency should be daily, or weekly if you prefer, at a time that fits your setup - choose a quiet time at night.)

 

On the next screen,

Under 'Select Maintenance Tasks', check the 'Reuild Indexes' box

 

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.

 

Optionally, choose to write a report to file.

 

Bask in the inevitable glory of a supefast system.*

 

 

*This step optional, and not guaranteed.

 

 

 

If you try this, please post back with your results, whether successful or not.

Edited December 6, 2013 by jinnantonnixx

[wurzel]

We have had it reported 3 times in the last weeks where reports just freeze when they normally take 20 seconds to run. If left they normally generate after about 30 minutes or restarting the machine seems to clear it.

[zag]

Yep similar experience here, are Capita listening to this?

 

I reported this in my server benchmarking thread a while ago.

[PhilNeal]

We have taken in some school's data to investigate particular cases of the reporting engine running slowly so that we can improve specific cases.

[CAM]

We've had an issue that seems to have started with the summer update whereby clicking anything in course manager results in an egg timer and 5-10 minute wait. Local support said we need more RAM, which helped a little but not much. The Autumn census was a nightmare!

 

You have imported too many QANs into Course Manager. Get rid of the ones you don't need and only import the courses you are using. Mine ran like treacle as my predecessor imported every single QAN.

[LosOjos]

You have imported too many QANs into Course Manager. Get rid of the ones you don't need and only import the courses you are using. Mine ran like treacle as my predecessor imported every single QAN.

 

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!

[Cache]

Just to update mine, after leaving it over night from doing the reindex patch, everything has been working reasonably well again since. However I know that in 2 months time this issue will definitely crop up again.

[MACIT]

Just to add my own experience to this, we also have been experiencing this decent into slowness even just logging into sims take twice as long as in used to. even though the server is eating up CPU's and Memory.

[jinnantonnixx]

Apologies for quoting myself, but I've seen a distinct benefit from regularly rebuilding the indexes (not reorganising/reindexing). This also creates fresh stats so that the query optimiser can generate better execution plans.

 

I'm convinced the problem is to do with indexes and statistics. The reindex patch doesn't update the stats.

 

Hint to make marks run faster: att_mark_normalised could do with some more indexes to stop RID/bookmark lookups. e.g. create an NCI on att_mark_normalised (session_start) INCLUDE person_id, mark, session_number.

 

 

 

I've noticed an improvement after running the following maintenance plan. Also, if you do this regularly the 100% fill factor is less of an issue.

 

Try setting up a maintenance plan to rebuild (not reorganise) your indexes.

 

Usual disclaimers, at your own risk and so on. If you're not confident with this procedure, do not proceed.

 

 

Start SQL Management studio

Expand 'Management'

Right-click 'Maintenance Plans'

Click 'Maintenance Plan Wizard'

 

Name you plan something like 'Reindex SIMS'.

On the same screen, click the 'Change' button next to Schedule

Set a suitable schedule (Frequency should be daily, or weekly if you prefer, at a time that fits your setup - choose a quiet time at night.)

 

On the next screen,

Under 'Select Maintenance Tasks', check the 'Reuild Indexes' box

 

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.

 

Optionally, choose to write a report to file.

 

Bask in the inevitable glory of a supefast system.*

 

 

*This step optional, and not guaranteed.

 

 

 

If you try this, please post back with your results, whether successful or not.

[matt40k]

@jinnantonnixx Did you ever get Bob Carswell document about maintenance plans? Basically it was a pretty word doc covering what your talking about but also some SIMS store procedures that are worth running regularly - like the valid memberships. It also covered other bits like renaming the sa account, testing your SQL backups and having a test system.

 

edit: forgot to say it also included recommend frequencies as well - ie this every month, that ever day, and this, this you only want to run yearly.. about this time of year.

Edited December 20, 2013 by matt40k

[jinnantonnixx]

@jinnantonnixx Did you ever get Bob Carswell document about maintenance plans? Basically it was a pretty word doc covering what your talking about but also some SIMS store procedures that are worth running regularly - like the valid memberships. It also covered other bits like renaming the sa account, testing your SQL backups and having a test system.

 

edit: forgot to say it also included recommend frequencies as well - ie this every month, that ever day, and this, this you only want to run yearly.. about this time of year.

 

Never heard of it - but it sounds good. Any links? This stuff should be wiki-fied.

 

The queries involving marks are crying out for more indexes.

Edited December 20, 2013 by jinnantonnixx

[matt40k]

Afraid not, still trying to find my copy he sent me, the joys of only having a 100mb mailbox He's retired now so I've got no hope of getting another copy to make it worse.

 

I think setting up a separate DW for reporting would be a start. At least then you wouldn't have to fight reporting speed (less joins\logic) vs operational speed (less writes).

[zag]

Hmm it wont let me run the Maintenance plan for some security reason.

 

Is this worth going through the hassle of setting this up?

[jinnantonnixx]

Hmm it wont let me run the Maintenance plan for some security reason.

 

Is this worth going through the hassle of setting this up?

 

I would say so. Our reports were practically unusable until I did this. Now they're back to normal. Not brilliant, but normal.

 

The plan only takes five minutes to set up. Set it to run it over the weekend and see if there's an improvement on Monday. If you do try it, can you let us know the result?

Edited January 17, 2014 by jinnantonnixx