[sims] Command Line Reporter Really Slow

[cn198]

Hi there, I'm running SIMS reports with the SIMS Command Line Report utility. For the last month or two the speed at which the utility runs at is taking way longer than it used to, some 20 to 30 minutes. Before this it used to take around 10 to 15 minutes. I am running many filters (a good few hundred), so am requesting a lot of data, but just not sure why all of a sudden it's taking so much longer. I use the utility over remote desktop, and I have noticed this sort of thing happen before but it's usually when there is a server issue and the server is running slow. But this is not the case at the moment as the server seems to be fine. Does anyone have any ideas of something to try that might help or things I can investigate?

 

Many thanks,

 

Chris

[Esteban_Child_of_the_Sun]

Is it just returning more results as the academic year rolls on?

How many results did you get from the report in late September and how many are you getting now?

[cn198]

Oh yeah that's definitely a factor here. So Y7 reports runs in just a few minutes, and steadily gets slower up the year groups to Y11. And then all of those times get steadily slower from September to July. But usually the max is around 10 to 15 minutes. But I've been doing this process for a few years now and never noticed this sort of problem outside of a server issue. It's definitely down to the volume of data that's coming down, but as I say don't know why this year is any different from last. I wondered if the actual SIMS database could get fragmented or something bizarre like that, or if there is some underlying network issue that isn't immediately obviously.

[Esteban_Child_of_the_Sun]

Do you have an SQL maintenance plan set up to run regularly on your server?

Capita have a document about how to set it up if you don't. I've got one set to run at least every 3 months at our high schools.

I can't currently find the document on either of Capita's support portal so I guess it is in the process of being migrated from one to the other (or the search functionality there isn't fully up to date).

[cn198]

Hmmm, that's interesting, do you know what the SQL maintenance plan does entails? Will have to ask the SIMS manager.

[Esteban_Child_of_the_Sun]

As an example, Capita recommend the following; Check Database Integrity, Reorganise Index, Rebuild Index, Update Statistics, and Maintenance Cleanup Task.

[cn198]

If you asked me what the problem was like, I would describe it like a hard drive that desperately needs defragmenting. I just asked the IT guy and the SIMS manager about an SQL maintenance plan and they didn't have any clue about it. Even if this isn't the problem it very much sounds like it's something we should be doing. Thank you so much for suggesting it, I really appreciate it. Any idea of how to set all this up, or is that detailed in the documentation you couldn't find?

[Esteban_Child_of_the_Sun]

I can only find the SQL2012/14 booklet that Capita sent me a while ago. Thinking about it more, we worked out our own for 2016 as Capita hasn't updated their documentation when we were doing the SQL 2016 migrations in Summer 2018. There aren't any major changes, the wizard screens are laid out a bit differently and I recollect that we had a few issues with permissions but as it's a standard MS SQL routine there is plenty of information online on how to sort it.

SIMSFMS SQL Maintenance Plan.pdf

[cn198]

I can only find the SQL2012/14 booklet that Capita sent me a while ago. Thinking about it more, we worked out our own for 2016 as Capita hasn't updated their documentation when we were doing the SQL 2016 migrations in Summer 2018. There aren't any major changes, the wizard screens are laid out a bit differently and I recollect that we had a few issues with permissions but as it's a standard MS SQL routine there is plenty of information online on how to sort it.

[ATTACH]56511[/ATTACH]

 

Just what I'm looking for! Thank you so much.