RVid RVid - 4 months ago 6
SQL Question

What is causing the Execution plan difference between Azure SQL and SQL Server?

I am running a same query on a same copy of the database on a

Azure SQL (Tier S3) and SQL Server (12.0).
The execution plan differs and the performance on Azure SQL seems worse. The DB is an exact copy and includes the same Indexes. When observing the execution plan, the
Estimated Subtree Cost on SQL Server is 0.325 while on Azure SQL it is 1.431
. The largest percentage of the execution time is taken by a clustered index on one of the tables. The relative proportion that this part of query takes to execute differs between
Azure SQL (84%) and SQL Server (52%)

Now my ask is :

Should I be concerned about the performance of the query on Azure SQL?

If you think the difference in the execution plans is a concern, could you please advise if you have any ideas how to diagnose further or find a way to improve the Azure SQL query? If there is any more info I can provide, please let me know.

SQL Server Execution plan:

SQL Server XML file

enter image description here

enter image description here

Azure SQL Execution plan:

Azure SQL XML file

enter image description here

enter image description here


Plan is same in both the instances.Total plan cost always rounds upto 100%.So you may see one operator having high cost compared to another,but all summing upto 100%.

I am not saying keylookups are bad,but those are Random reads .From your plan i could see a single table was accessed twice (seek and lookup).So i recommend adding altering below index after testing

This index [IX_InstantMessageThreadAccesses_ProfileId] should have one more key column( archived) and include this column (kastreadon).By this way you will be to avoid nearly 44% of total query cost and it will result SQL in choosing a new plan.

Further i could see your statistics are not to update.I recommend doing an update stats for all the tables with fullscan(this may cause huge I/O activity if the tables are very big)...


Update question if you have any questions further with steps done now and any diefferences you are seeing