[sims] Sims slow after upgrade to SQL 2014

[arron]

Just an FYI, we found that our sims ran slower after upgrading from sql 2008 to sql 2014.

 

We found that we had to change the compatibility level to sql 2012 for it to return to its previous speed.

[eddyc]

Capita rebuilt our SIMS server last Friday and all seemed well until it was under-load on Monday when the staff returned from half term.

 

I've applied 5 patches that they've provided today to try to resolve the issue and there was no improvement...

 

Validate memberships was taking 22:00 minutes to run and Database Diagnostics wouldn't run at all.

 

Since changing the compatibility level to SQL 2012 we seem to back to our normal speed... Validate memberships will run in 2:30 again and database diagnostics takes around 5 minutes.

 

Out of interest, did Capita ask you to change the compatibility level or did you discover this for yourself?

Edited February 25, 2015 by eddyc

[arron]

I tried it myself out of curiosity.

 

They gave us 5 patches as well... they also "defragged" (reindexed) our database that made it better until it was under load again. They currently have a copy of our database that they are looking at to try and work it out.

[eddyc]

I've just had an email back from the engineer who was working on our case to say that a small number of schools are having this issue...

 

Your post saved my bacon. I'm on leave from last night and had the whole school after me yesterday due to SIMS being unusable... Phew!

[forkies]

We were told to upgrade direct to 2014SQL from 2008R2 SQL. We did the migration over half term and when staff came back on Monday it was unbelievably slow. Whenever going into "Take Register" sims took about 40 seconds to 2 mins to load, same with assessment.

 

After using dbattach to unattach the DB and run it back through the migration tool it worked well for about 2 hours and then same thing. Our LA Sims support said there was no known issues or slowness, I ended up having to restore our 2008R2 SQL and manually reinput all attendance marks from Monday!!!!!!

 

I think I will just go to 2012SQL until these slowness issues are resolved. I stayed until midnight on Monday trying every which way of migrating from 2008 to 2014 but everytime it ended in disaster. I was so annoyed as per usual we got the blame!

 

I would strongly recommend moving to SQL2014 on a test database and checking thoroughly that it works before moving your live database over.

[jinnantonnixx]

SQL 2014 uses different algorithms for the query optimiser.

 

The SIMS-supplied reindexing tool is unlikely to be of much help as it doesn't recreate the statistics (last time I looked at it, anyway - but correct me if I'm wrong). However, rebuilding the indexes might have some benefit (as rebuilding creates stats from scratch). You'll also need to use UPDATE STATISTICS to update the column statistics, as rebuilding will update the index stats but not the column stats. Clear as mud?

 

Anyway, this particular problem with SQL 2014 is to with the 'cardinality estimation'. In other words, how many rows does the server think is in a table. This information is gleaned from the SQL statistics, which is essential for the SQL query optimiser. If you restore a database created with a version below SQL 2014, the cardinality estimation will be significantly inaccurate, and the query optimiser will produce inefficient algorithms.

 

This excellent article explains it very well. Cardinality Estimation for Correlated Columns - SQLpassion

 

As a workaround (and probably the best idea for the time being), setting the compatability to an older version of SQL is a good idea as suggested by @arron

Edited February 26, 2015 by jinnantonnixx

[vikpaw]

I'm surprised Capita have yet to buy-out @jinnantonnixx Ltd.

[the_quick]

Hi Guys, I'm in exactly same position. Upgraded from SQL2008R2 to slq2014 runs really slow, take ages for register to load etc.

 

Of course capita says they don't know about any issues and looking at our traces.

 

But anyway, How do I change compatibility level? Is it done on database? Should we do it on both sims and fms?

 

Thanks

 

Kris

[forkies]

I ran this on the DB using Studio and Query.

 

ALTER DATABASE sims

SET COMPATIBILITY_LEVEL = 110;

GO

 

We dont run FMS so not sure on that side?

That fixed it for me.

[arron]

Go to SQL server 2014 management studio -> find your sims database -> right click properties -> options (top left of properties) -> compatibility mode -> sql server 2012.

 

It can be done while the database is in use and no you don't need to touch FMS.

[bossman]

I'm pleased I held back after all, due to circumstances beyond my control I have been unable to migrate to a new server so still on 2008R2 and SQL 2008R2 (Virtual) all seems well and no speed issues in fact it seems slightly quicker.

 

Will eventually migrate when we have everything else sorted but I'm not in any hurry if this is what happens

 

Sims

FMS

Discover

 

Happy day's

[the_quick]

Aaron Are you saying I can do this in the middle of the day without affecting users?

 

Oh and more think, should I change to compatibility level of 2008r2? as this what we had before?

Edited March 4, 2015 by the_quick

[jinnantonnixx]

Here's a good summary about compatibility levels. Be sure to scroll down to the bit titled "Differences Between Lower Compatibility Levels and Level 120" for a run-down of the problem.

https://msdn.microsoft.com/en-us/library/bb510680.aspx

 

Now, if SIMS switched on Trace Flag 9481 (which they could do with a simple patch) this would force SQL 2014 to use the old version of the Cardinality Estimator, saving people the hassle of changing compatibility modes.

 

Another good article on the new CE:

http://michaeljswart.com/2014/05/enabling_the_new_ce/

Edited March 4, 2015 by jinnantonnixx

[arron]

We went from 2008 -> 2014 and we are running it in 2012 mode at the moment. We have changed it a few times while its in use with no effect, it seems to apply the new settings on queries made after the change without effecting previous queries.

[the_quick]

Arron did you see improvement straight after you did change compatibility?

 

I can answer myself, as I just did it. I can see it is working so much better now.

Edited March 4, 2015 by the_quick

[arron]

Yes, register loading speed was the most noticeable change for us.

[the_quick]

Still loading sims takes a bit of time. But when it's loaded everything working as it should. FMS take a long to load as well. Maybe something with the logging in process.

[vikpaw]

Still loading sims takes a bit of time. But when it's loaded everything working as it should. FMS take a long to load as well. Maybe something with the logging in process.

Do you have to apply the setting on that DB separately...?

[the_quick]

So far applied on sims db. But both sims and fms take a while to load.

[Hardeep_Sco]

All,

 

Has the issue with speed been resolved?

 

Can someone confirm this?

 

The reason I ask is that I have scheduled a few migrations during the Easter Break and I dont want to go a head with them if there are still issues.

[Hardeep_Sco]

Also has this been tested on the Full Version of SQL 2014 or is it the express version? Edited March 4, 2015 by Hardeep_Sco

[the_quick]

Mine is running on SQL 2014 standard - so full version. I don't think there Capita official fix, but changing compatibility mode to SQL 2012 works really well. When sims loads (30 seconds in my school) it is working well.

 

To be honest I would wait with upgrade. But if you have to upgrade, I just would do it to SQL2012

[Ghostgame]

One of the things we have noticed is that the newer versions of SQL2012 and SQL2014 are really RAM hungry even when they don't need it.

 

We have found that sometimes restricting the amount of RAM the SQL instance can access (needs SQL Management Studio) has speed up SIMS.

[Mark_S]

Hi all. I've been around here for a while but don't believe I've ever posted (I'm just a shy Northern bloke!) Thanks very much for your on going and valuable contributions.

 

Here's my offering to the current discussion, I hope it's useful...

 

Changing the compatibility level while users are connected to the database can produce incorrect result sets for active queries.

If the compatibility level changes while a query plan is being compiled, the compiled plan might be based on both the old and new compatibility levels, resulting in an incorrect plan and potentially inaccurate results.

 

The problem can then be further compounded if the plan is then placed in the plan cache and reused for subsequent queries.

 

The recommended procedure to change the compatibility level of a database is as follows:

 

- Set the database to single-user access mode.

- Change the compatibility level of the database.

- Put the database in multiuser access mode.

 

.. but do read the note of caution here...

https://msdn.microsoft.com/en-us/library/ms345598.aspx

 

In the short term, I think Capita could perhaps come up with a reworked migration tool for those yet to migrate and a patch, or series thereof, for those that have migrated. Changing Compatability mode can be an aid to migration but isn't putting things right at root.

 

Best regards.

[MartinT]

If you find SIMS takes longer to start after upgrading SQL, don't forget (as I did) to modify the firewall rule to allow a program through, as it still points to the old SQL executable. This results in 30s rather than 8s startup as it tries then falls back the connection method.