Azure SQL Legacy Cardinality Estimation Not Working Like SQL Server 2016

0
votes

We have two identical databases (exact same data, structure etc.) one in Azure Sql (Premium 500 DTU) and the other on a VM on Azure running SQL Server 2016 SP1 (8 vcore, 64 GB RAM). We have a query that when run with "Legacy Cardinality Estimation" at the database level set to OFF results in a query execution time of about 4 minutes on both databases.

If we change the "Legacy Cardinality Estimation" to ON in both databases the VM running SQL Server 2016 SP1 will run the same query in 30 seconds while the Azure SQL database ends up with even worse performance and takes almost 7 minutes to execute.

Re-writing the query at this point is not an option. I am hoping someone can tell me how I can get Azure SQL to honor the "Legacy Cardinality Estimation" setting and give me similar performance to my SQL Server 2016 SP1 VM.

I did notice that on the SQL Server 2016 SP1 box the CPU's will spike high (90%+) when "Legacy Cardinality Estimation" is ON while in Azure SQL the DTU percentage never climbs higher then 25%.

1
sql-serverazureazure-sql-databasesql-server-2016azure-sql-server
Did you check / compare the plans? - Aaron Bertrand
25% seems like a suspiciously magic number - is it possible other settings are different (like the Azure SQL DB equivalents of maxdop or cost threshold for parallelism, or resource caps of any kind)? Still, the way to see differences is to compare execution plans. - Aaron Bertrand
Other then changing the "Legacy Cardinality Estimation" on the newly created Azure SQL database no other settings are changed. I will try and compare the plans but this query is big so the execution plan is not the easiest thing to sort through. The cap on DTU percentage was odd to me as well and thought maybe Azure SQL had something built in that prevented a single query from chewing up all the resources. - Ken Brannigan
All kinds of things can lead to different plans on two different databases. You can't ignore the plans just because the query is big. You can usually focus in on the major pain points in each plan pretty easily, and when they're different, you can drill into why (or ask us that more specific question). If you're having a hard time doing that in SSMS, give SentryOne Plan Explorer a try. Disclaimer: I work for SentryOne. - Aaron Bertrand
Thanks Aaron. The plans are definitely different. I may try downloading the SentryOne tool you mentioned to see if it can help pin point the problem. I will try and post more info once I dig in deeper. Thanks for the help so far. - Ken Brannigan

1 Answers

0
votes

On Azure SQL Database set compatibility level to 110 to enable the old cardinality estimator.

ALTER DATABASE [YourDatabase] SET COMPATIBILITY_LEVEL = 110

At the query level, use below hint.

USE HINT ( 'FORCE_LEGACY_CARDINALITY_ESTIMATION')

For more information, please read here.