Hi all,
We are running Tessitura v14.1.2 on SQL Server 2016 SP1. We've run into a few queries that perform very poorly, and it seems to be related to cardinality estimation. Specifically, I have a small number of queries that ran fine in SQL Server 2012, but started running 10+ minutes in SQL Server 2016 SP1. When I used the following query hint, the query goes back to running in a few seconds:
OPTION (USE HINT('FORCE_LEGACY_CARDINALITY_ESTIMATION') )
We ran into the same problem with web checkouts today in a test environment; the process to insert T_SUB_LINEITEM rows in WP_SAVE_CART was taking over a minute and the call would timeout. It appears one of the triggers involved when inserting T_SUB_LINEITEM rows is what was hanging things up. Again, using legacy cardinality estimation resolved the issue and WP_SAVE_CART ran normally after that. In this case, I just set the test database to use legacy cardinality estimation rather than using the query hint.
Here is an article that describes what I am referring to in more detail: docs.microsoft.com/.../cardinality-estimation-sql-server
There may be other factors, but I haven't had a lot of time to dig deeper yet. Has anyone run into similar issues?
Thanks!David FrederickSegerstrom Center for the Arts
We ran into it here as well. We had reports the took down the system, blockers that killed the transactions. We ended up going to 2012 level database wide because of this. Now, I'm faced with testing all of our custom objects in a Dev environment by using the query hint or by the OPTION (QUERYTRACEON 2312) at a suspected statement to see if it works with 2014 CE.