Page Comparison

Is it safe to configure MSSQL to use legacy cardinality estimation?

The answer is yes, configuring Configuring MSSQL to use legacy cardinality estimation is safe to do.This , and this setting does not alter the functional behaviour of the database or how it processes queryqueries.

Why does it perform better?

...

Totara is a big large product , one that tries attempts to fully utilise the database, and contains countless queries in all shapes and forms. One of the techniques that Totara has adopted in developing its solution is to leverage the database engine to the best of its ability.
Because of this it's not uncommon to encounter queries that make use things such as built-in functions and string concatenation, or that operate on data sets that are not uniform. MSSQL documentation on the topic notes that the above (and issues outlined here (as well as others) as predicates that may lead to inaccurate calculations.

...

The queries within Totara are written to work with all supported database engines. As such the queries themselves are not written specifically to perform in MSSQL, nor do they currently use any MSSQL-specific features or functionality. On rare occasions we will prepare database-specific queries, however that this is only done in exceptional circumstances.

Additionally there are many areas in Totara where extensibility and configurability lead to SQL queries being built by code. MSSQL does provide the ability to provide hints to influence the calculation, however Totara does not currently utilise this functionality present. At present this can only be influenced for the database as a whole.

It is worth noting that we do invest a lot of effort in ensuring that our queries perform as best as they can in all supported databases. ( We will investigate how we could provide hints to the MSSQL cardinality estimator in the future.)

How do I determine whether it should be on or off for the database?

The LEGACY_CARDINALITY_ESTIMATION setting is either on or off.

Totara has countless queries in all shapes and forms we can imagine that it will have , so there are likely to be a mixture of positive and negative impacts across the board. There is no correct answer as to whether it should be on or off. What Which parts of the Totara system you use, and how you use them, will impact the queries that get run. Some queries will resolve better with the modern CE cardinality estimation levels, and some will resolve better at the original (legacy) level.

...

The immediate answer is no. We are not planning to change the existing queries, or how we write new queries. There will for For the foreseeable future there will be a mix of queries.

Related articles

...