Tuning cardinality estimation

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

The answer is yes, configuring MSSQL to use legacy cardinality estimation is safe to do.

This setting does not alter the functional behaviour of the database or how it processes queries.

Why does it perform better?

An MSSQL expert could probably advise you better than we can on this. However from researching the cardinality estimation functionality within MSSQL I would be comfortable in explaining it in the following way:

Totara is a big product, one that tries 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 the above (and others) as predicates that may lead to inaccurate calculations.

To what degree does Totara influence cardinality estimation in MSSQL

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 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 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 positive and negative impacts across the board. There is no correct answer as to whether it should be on or off. What 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 levels, and some will resolve better at the original (legacy) level.

Will this change in upcoming versions of Totara?

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