Difference in performance between two Azure SQL databases

Question

My .Net Core 2 application has a difference in performance between two Azure installations while having the same pricing tiers configured.

The TST 'envirnoment' is hosted in our Azure subscription. The ACC 'envirnoment' is hosted in the subscription of the client.

In both envirnoments, the solution is configured as follows:

• service app with a pricing tier: S1
• the database runs at S2 standard (50DTU).
• Both in region W-Europe.

Things we checked:

• Usage statistics of database (and webapp) never go over 10%. (Most of the time zero.)
• Solutions are automatically deployed using a build server. So the applications version is 100% the same
• Database schema and indexes are the same. Using EF Core with migrations to deploy database updates.
• Used the tool Redgate to compare the two databases. (no differences)
• TST database is 360MB in size. ACC is 180MB.

One other oddity is that using "Microsoft SQL Server Management Studio" on the ACC database is very slow. Up to 30 seconds to display the context menu (right mouse click) on tables etc. We do not have this on our TST database.

Any idea's what I have missed or things I can check?

Answer 1

My suggestion here is to rebuild indexes and statistics on the sluggish database since Azure SQL Database does not do that automatically for now. Please try the solution provided on this article.

If the above does not help, enable Query Store and focus on the query plans of those queries showing high I/O consumption. What is different on those plans on both environments.