SQL Server use legacy cardinality estimation on specific query can be mastered easily with this guide. Read more. 

At Bobcares, we offer solutions for every query, big and small, as a part of our SQL Server Support.

Let’s take a look at how our SQL Server Support Team is ready to help customers push use legacy cardinality estimation on a specific query.

How to let SQL Server use legacy cardinality estimation on specific query

The Legacy Cardinality Estimator in the SQL Server is a result of histograms from indexes or statistics. In other words. SQL Server relies on constraint information as well as logical rewrites of queries to nail down cardinality.

The Cardinality Estimator is responsible for predicting the number of rows a query will return. Furthermore, it also determines the memory allocation of the query.

By default, the Legacy Cardinality Estimation is OFF. We have to set it to ON in order to use it in an older version of SQL Server.

Alternatively, we can also do this by changing the property on T-SQL as seen below:

ALTER DATABASE
    SCOPED CONFIGURATION
        SET LEGACY_CARDINALITY_ESTIMATION = OFF|ON;

However, enabling Legacy Cardinality Estimation affects all queries. In some cases, it may also damage the query performance. Fortunately, our SQL Server experts recommend using FORCE_LEGACY_CARDINALITY_ESTIMATION to prevent this from occurring.

Adding FORCE_LEGACY_CARDINALITY_ESTIMATION to the query forces the query optimizer to use an older version of Cardinality Estimation.

If we have a SQL Server database with a compatibility level set to 120 or above, we can activate the legacy cardinality estimator at the database level as seen below:

ALTER DATABASE SCOPED CONFIGURATION
SET LEGACY_CARDINALITY_ESTIMATION = ON;
GO
SELECT name, value
FROM sys.database_scoped_configurations
WHERE name = 'LEGACY_CARDINALITY_ESTIMATION';
GO

As a result, we learned how to use legacy cardinality estimation.

[Need assistance with another query? We are available 24/7.]

Conclusion

In a nutshell, our skilled SQL Server Support Engineers at Bobcares demonstrated how to use legacy cardinality estimation on a specific query.