[sims] Sims slow after upgrade to SQL 2014

[the_quick]

Martin, what a brilliant observation. Just changed dynamic TCP/IP port in our firewall settings (as it now use some ridiculous 49xxx , instead of 1433) and now sims is around 8-10 sec to open and FMS is lighting quick at around 4 seconds.

 

Capita still haven't figured that out and I did not until no as well.

 

Thanks Martin

[MartinT]

Happy to help

[divadiow]

just had to do this for a secondary I migrated on Monday. was near unusable before compat. change.

 

[PhilNeal]

We are looking into this issue. Thanks to @MartinT for a possible lead.

[RobFuller]

Any news @PhilNeal to the cause of the SQL 2014 issues, was very much hoping to migrate SIMS this Easter!

[PhilNeal]

I raised this today but no progress on this one yet I'm afraid. We believe it is caused, as reported in this thread, by a change in the way in which queries are analysed by SQL. We are in discussions with MS.

I would hold off moving to SQL2014.

[Hardeep_Sco]

@PhilNeal, is it worth while putting something on my account to advise Local Authority's to put off Migrating to SQL2014. Speaking with a few other LAs some have migrated and are having these speed issues.

[PhilNeal]

I thought we had. I'll check.

[Hardeep_Sco]

Sorry @PhilNeal, it is on there.

[gjirving]

@PhilNeal Any progress to report?

[PhilNeal]

We can confirm that tests show that compatibility mode restores the speed. MS are due to visit next week.

[ass17]

We don't have any issues with our upgrade as yet, software runs ok, speed as per before, running our 2008 R2 server on a VM. We paid for SQL 2014, thus no limitations that could trip us up.

[forkies]

We don't have any issues with our upgrade as yet, software runs ok, speed as per before, running our 2008 R2 server on a VM. We paid for SQL 2014, thus no limitations that could trip us up.

 

We paid for our SQL but it still had issues due to the indexing changes not due to version limitations. The compatibility fixed it for us so far so good. SQL2014, server 2012 r2 VMware. DB on SSDs. Everything in SIMS is still about 2 seconds slower then SQL 2008 r2 running on same server.

[5tu]

There has been an update on this issue posted here - https://myaccount.capita-cs.co.uk/hot-topics/sims-sql-2014/

 

In summary....

 

If you have already migrated to SQL2014 and are experiencing performance issues it is recommended the following tasks are completed:

 

Use patch ID 20580 to place each SQL 2014 SIMS databases into mode 110 (SQL 2012 mode)

Use patch ID 14265 to ReIndex each of your SIMS databases

Use patch ID 20647 to recreate the statistics on each of your SIMS databases

[Boredguy]

However if you ask for patch 20647, they send you 15589!

[PhilNeal]

Originally MS suggested we re-write 5m lines of code. We didn't think that was a great idea but fortunately MS have a growing number of software houses with the same issue and they are are investigating further.

[Hardeep_Sco]

@PhilNeal, Should we still hold off Migrating Schools to 2014? If MS are still looking into the issue?

[PhilNeal]

It's OK to go over to SQL2014 as compatibility mode runs fine. Our tool auto-sets into this mode.

[5tu]

It's OK to go over to SQL2014 as compatibility mode runs fine. Our tool auto-sets into this mode.

@PhilNeal When you say "our tool" do you mean the migration utility? If so, has the tool been updated with the auto compatibility mode function after it was first released? If yes, should schools that have already downloaded the tool previously but not used it, download it again?

[PhilNeal]

@gybe78 I do mean the migration utility. I think it must have been updated but I will check

[PhilNeal]

I've just checked - there is a new migration utility but it has only just completed test. Should be out by end of next week.

[pastymaster]

I've requested patch ID's 20580, 14265 and 20647 from our LA but so far I can only see 14265. Hopefully the others will be available to me soon.

 

Has anyone experienced any problems after using these patches? I'm in on my own this week (and some of next) so could do with not slowing it down further

[PhilNeal]

Update - MS are producing a patch ETA end of May. They think it will solve the slowing down issue but we have them coming in to demonstrate.

[forkies]

@PhilNeal thanks for keeping us informed.

[vikpaw]

Updated migration utility available that will put the DB in a workable mode. https://myaccount.capita-cs.co.uk/Notifications/sql-migration-tool-update-may15/