• 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

    Hey David,

    Do these issues all involve custom code, or are some of these happening "out of the box"? I wonder if reindexing/regenerating statistics would change the behavior at all. Were you adding the query hint the WP_ impresario procedures to fix your checkout problem?

    It looks like there is a third option of keeping your compat level at 120, but setting a database-level configuration to use the legacy cardinality estimator. Might be a slightly better solution...

  • $organization in reply to Nick Reilingh

    Hi Nick,

    Primarily custom code. However, we did experience a problem with a trigger during web checkouts in our test environment that enabling legacy cardinality estimation resolved. I'm also troubleshooting a performance issue with the print at home stored procedure, WP_GET_ORDERS_FOR_DELIVERY, and extractions that might be related to this; however, I haven't confirmed that yet. 

    Anyway, I would prefer to avoid reverting to the legacy cardinality estimator for the whole database as the new one is supposed to be an improvement in most cases. We'll see how it goes!

    Thanks,
    David

Reply
  • $organization in reply to Nick Reilingh

    Hi Nick,

    Primarily custom code. However, we did experience a problem with a trigger during web checkouts in our test environment that enabling legacy cardinality estimation resolved. I'm also troubleshooting a performance issue with the print at home stored procedure, WP_GET_ORDERS_FOR_DELIVERY, and extractions that might be related to this; however, I haven't confirmed that yet. 

    Anyway, I would prefer to avoid reverting to the legacy cardinality estimator for the whole database as the new one is supposed to be an improvement in most cases. We'll see how it goes!

    Thanks,
    David

Children
  • $organization in reply to David Frederick

    Hi David,

    It would be great to hear how you go with fixing WP_GET_ORDERS_FOR_DELIVERY as we are also experiencing performance problems here since going to V14 and as yet have been unable to resolve.

    Thanks,
    Krystal

  • $organization in reply to Krystal Nolan

    Hi Krystal - we found the WP_GET_ORDERS_FOR_DELIVERY performance issues related to the new cardinality estimator, at least in our environment. Here is how we resolved the issue running Tessitura v14.1.2 on SQL Server 2016 SP1 with the impresario database in 2014 compatibility level...

    I added the query hint I mentioned earlier in this thread to the two "Insert #orders" statements in WP_GET_ORDERS_FOR_DELIVERY. (As a disclaimer, modifying standard procedures is not generally a good idea nor recommended by the Network - so be very careful and this should probably just be a temporary measure.) The procedure performed significantly better after that. Here is that hint again:

    OPTION (USE HINT('FORCE_LEGACY_CARDINALITY_ESTIMATION') )

    For SQL Server 2016 without SP1 and SQL Server 2014, I believe you have to use this instead:

    OPTION (QUERYTRACEON 9481)

    Other options:

    • You can turn on Legacy Cardinality Estimation at the database level in SQL Server 2016. (For SQL Server 2014, you could enable trace flag 9481 but I've seen some warnings against doing this globally.) This means all queries will use the legacy cardinality estimator, but it avoids the need for adding the query hint in the stored procedure. 
    • Shift the database compatibility level to 2012, as Troy pointed out. Again, that's a global change.

    Here is one article describing some of the options for dealing with this: https://www.sqlskills.com/blogs/kimberly/sp_settraceflag/. If we run into more queries that perform poorly with the newer cardinality estimator, we may end up turning on Legacy Cardinality Estimation at the database level.

    Thanks,
    David