• Replies 6 replies
  • Subscribers 336 subscribers
  • Views 144 views
  • Users 0 members are here
Related

SQL Server 2016 SP1 and Legacy Cardinality Estimation

David Frederick
$organization

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 Frederick
Segerstrom Center for the Arts

Parents
  • $organization

    Hi Nancy,

    Thanks for your response! Your reply didn't end up part of this thread for some reason, so I'm pasting your message below. We are using SQL Server 2014 compatibility level, which is where the new cardinality estimation comes into play. Thankfully, there are ways to go back to the legacy approach without lowering the compatibility level.

    At the database level, set the "Legacy Cardinality Estimation" setting in Options to ON.

    At the query level, use the query hint I mentioned in my original post.

    One reason I'm bring it up is that Tessitura's requirements document appears to suggest running SQL Server 2016 at SQL Server 2014 compatibility level, but there is no mention of this issue. At the moment, we're applying the query hint as-needed, but if too many of these issues come up, I may need to turn Legacy Cardinality Estimation on at the database level. 

    Thanks again!
    David

    From: Nancy Sheleheda [mailto:bounce-nancysheleheda3263@tessituranetwork.com]
    Sent: Wednesday, June 20, 2018 1:14 PM
    To: forums-technical@tessituranetwork.com
    Subject: [Tessitura Administration & IT] RE: [Tessitura Administration & IT] SQL Server 2016 SP1 and Legacy Cardinality Estimation

    Hi David,

     

    I forwarded your issue to my DBA consultants (RDX),  because we are planning a v14 and SQL 2016 upgrade in the next 6 months or so.  What’s your compatability level?  They say if you set it back to SQL 2012 SQL will use the old cardinality estimator.

     

    “For the most part SQL 2014/2016 builds better query plans. But occasionally it can get it wrong and produce a query that is much worse.  There isn’t a quick fix solution other than doing normal query tuning to fix it on a case by case basis.”

     

    Good luck,

    Nancy

Reply
  • $organization

    Hi Nancy,

    Thanks for your response! Your reply didn't end up part of this thread for some reason, so I'm pasting your message below. We are using SQL Server 2014 compatibility level, which is where the new cardinality estimation comes into play. Thankfully, there are ways to go back to the legacy approach without lowering the compatibility level.

    At the database level, set the "Legacy Cardinality Estimation" setting in Options to ON.

    At the query level, use the query hint I mentioned in my original post.

    One reason I'm bring it up is that Tessitura's requirements document appears to suggest running SQL Server 2016 at SQL Server 2014 compatibility level, but there is no mention of this issue. At the moment, we're applying the query hint as-needed, but if too many of these issues come up, I may need to turn Legacy Cardinality Estimation on at the database level. 

    Thanks again!
    David

    From: Nancy Sheleheda [mailto:bounce-nancysheleheda3263@tessituranetwork.com]
    Sent: Wednesday, June 20, 2018 1:14 PM
    To: forums-technical@tessituranetwork.com
    Subject: [Tessitura Administration & IT] RE: [Tessitura Administration & IT] SQL Server 2016 SP1 and Legacy Cardinality Estimation

    Hi David,

     

    I forwarded your issue to my DBA consultants (RDX),  because we are planning a v14 and SQL 2016 upgrade in the next 6 months or so.  What’s your compatability level?  They say if you set it back to SQL 2012 SQL will use the old cardinality estimator.

     

    “For the most part SQL 2014/2016 builds better query plans. But occasionally it can get it wrong and produce a query that is much worse.  There isn’t a quick fix solution other than doing normal query tuning to fix it on a case by case basis.”

     

    Good luck,

    Nancy

Children
No Data