SQL 2016 Cardinality Estimator

sql

(PatL) #1

Hi Guys,
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;  

go


(Greg Celentano) #2

Thank you. We are about to start testing of the 10.2 upgrade soon. In my test environment SQL 17 SSRS is a no go for 10.2.100.x. Had to standup a SSRS 16 instance to host the reports. Hopefully 10.2.200 is another story.


(Pierre Hogue) #3

Hello,

We are into preparing for upgrading from 10.0.700 to 10.2.100.
We are using SQL server 2012. You mentionned that you were “forced” to upgrade from 2014 to 2016. Can you elaborate? I read docs that indicated 2012 was “approved”…Is there any functionality not working but that does with the newer version?

thanks to share…

Pierre


(Dave Moon) #4

Isn’t SQL 2012 at end of the Microsoft Support Cycle?


(PatL) #5

I copied my live db to my test server before the upgrade for my backup. I forgot that my test db was 2016 (and live was 2014).
When I had to rollback 2014 wouldn’t load the db because it had been in 2016 thus the forced upgrade.
:slight_smile:


(Greg Celentano) #6

If you have your SQL licenses under software assurance I would highly recommend keeping current.


(Olga Klimova) #7

You set old version of SQL optimizer here. I don’t think Epicor database uses it by default.