[Company Logo Image] 

Home Up Contents Coffee Break Credits Glossary Links Search

 
How to enable the old cardinality estimator on SQL Azure

 

 

Home
Analysis Services
Azure
CLR Integration
High Availability
Open Source
Security
SQL Server 2008
SQL Server 2012
SQL Server 2014
SQL Server 2016
SQL Server 2017
SQL Server 2019
Tips
Troubleshooting
Tuning

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


 

 

 

.Send mail to webmaster@sqlcoffee.com with questions or comments about this web site.