How to enable the old cardinality estimator on SQL Azure
On on-premises instances of SQL Server you may find that some query plans may
show a wrong estimated number of rows and you find the elapsed execution time
for those queries is high, then you make use of trace flag 9481 or Database
Scoped Configuration setting LEGACY_CARDINALITY_ESTIMATION=ON and the elapsed
execution time is greatly reduced, but we do not have those options available on
SQL Azure Databases.
On SQL Azure Database you can enable the old cardinality estimator on a single
query by using below hint.
USE HINT ( 'FORCE_LEGACY_CARDINALITY_ESTIMATION')
More information about this hint is available on the
Hints (Transact-SQL) - Query article.
To enable the old cardinality estimator at the database level on SQL Azure you
can change the compatibility level as shown below:
ALTER DATABASE [YourDatabase] SET COMPATIBILITY_LEVEL = 110