Just had a nightmarish couple of days trying to upgrade to 10.2.100.
It’s all my fault but i just thought I would share something which i am sure Bart and Nathan know but I had to work out myself due to me being forced to run an unsupported configuration.
Basically I was forced to upgrade my sql server from 14 to 16. After which I ran my usual reindex but come Monday the system was running like a dog.
I tried a recalc on all table stats monday night thinking it might be the new query processing stuff introduced but that didn’t fix it.
I then found some information on the new cardinality estimator. So thinking ‘what have i got to loose’ i tried setting this back to legacy and bang my speed issue where resolved!
I am sure epicor set’s this on sql 2016 databases when it sets them up it’s just I had an unplanned restore/upgrade event (oh shit moment) so this flag didn’t get turned on and my performance suffered.
Hopefully this helps someone if you ever get stuck in this situation!
Here is the command for sql 2016
ALTER DATABASE SCOPED CONFIGURATION SET LEGACY_CARDINALITY_ESTIMATION = ON;