Tuning cardinality estimation

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

Configuring MSSQL to use legacy cardinality is safe to do, and this setting does not alter the functional behaviour of the database or how it processes queries.

Why does it perform better?

Totara is a large product that 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 issues outlined here (as well as others) 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 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. 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, so there are likely to be a mixture of positive and negative impacts. There is no correct answer as to whether it should be on or off. 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 cardinality estimation 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. For the foreseeable future there will be a mix of queries.