0

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%.

Ken Brannigan
  • 607
  • 8
  • 16
  • Did you check / compare the plans? – Aaron Bertrand Jul 05 '18 at 18:53
  • 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 Jul 05 '18 at 19:05
  • 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 Jul 05 '18 at 19:22
  • 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](https://sentryone.com/plan-explorer/) a try. *Disclaimer: I work for SentryOne.* – Aaron Bertrand Jul 05 '18 at 19:30
  • 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 Jul 05 '18 at 19:45
  • I can see that the SQL Server 2016 SP1 VM shows 8 degrees of parallelism with 267 parallel operations while the Azure SQL plan does not list parallelism at all. I used the SentryOne tool as Aaron recommended. The read count on Azure SQL has almost 56 million reads where the SQL Server 2016 SP1 VM shows just under 8 million reads. Any recommendations on other things to look for in the execution plan? – Ken Brannigan Jul 06 '18 at 00:22
  • When I changed to Premium 1000 DTU the query went parallel and completed in 22 seconds. – Ken Brannigan Jul 06 '18 at 01:22

1 Answers1

0

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.

Alberto Morillo
  • 13,893
  • 2
  • 24
  • 30
  • We tried the compatibility level and it gives the same slow results as the "Legacy Cardinality Estimation" to ON. Also Azure SQL does not like the "USE HINT ( 'FORCE_LEGACY_CARDINALITY_ESTIMATION')" since it doesn't support the "USE" keyword. – Ken Brannigan Jul 06 '18 at 00:08
  • Return to the original compatibility level and use only the hint at the statement level. Please let me know the results. – Alberto Morillo Jul 06 '18 at 00:35
  • SELECT CustomerId, OrderAddedDate FROM OrderTable WHERE OrderAddedDate >= '2016-05-01'; OPTION (USE HINT ('FORCE_LEGACY_CARDINALITY_ESTIMATION')); – Alberto Morillo Jul 06 '18 at 00:37
  • No difference in speed. Same results as setting the "Legacy Cardinality Estimation" to ON as mentioned in the original post. – Ken Brannigan Jul 06 '18 at 12:47