I've been having some problems with moving databases from on-premises (2014 sql server) to Azure sql server. It's using the exact same database structure and data, but for some reason the query uses a different execution plan and takes much longer.
On the on-premises server it returns almost immediately and on the Azure server it takes about 6 seconds when using standard with 50 DTU's and when using e-DTU's it takes about 3 seconds, which is better, but still much longer.
Any ideas why the execution plan is different (even the rows are returned in another order), or how I can make it use the same query plan?
The query (generated by entity framework) is:
SELECT DISTINCT [Extent4].[Id] AS [Id], [Extent4].[Name] AS [Name], [Extent4].[Background] AS [Background], [Extent4].[Code] AS [Code], [Extent4].[DeactivatedOn] AS [DeactivatedOn] FROM [dbo].[Orders] AS [Extent1] INNER JOIN [dbo].[Users] AS [Extent2] ON [Extent1].[User_Id] = [Extent2].[Id] INNER JOIN [dbo].[UserRegistrationGroups] AS [Extent3] ON [Extent2].[UserRegistration_Id] = [Extent3].[UserRegistration_Id] INNER JOIN [dbo].[UserGroups] AS [Extent4] ON [Extent3].[Group_Id] = [Extent4].[Id]
execution plan of the on-premises server:
Execution plan of the azure sql server:
The waits from the Azure plan are:
<Wait WaitType="SOS_SCHEDULER_YIELD" WaitTimeMs="597" WaitCount="686" /> <Wait WaitType="RESOURCE_GOVERNOR_IDLE" WaitTimeMs="570" WaitCount="77" />
I've already tried to set the compatibility level the same (100).
My customer pays 10 euro per month for his current service provider, which provides a web and sql server in the cloud as well.
I'm trying to convince him to go to azure, but the azure sql, which costs 60 euro per month for an s2 is a lot slower in most queries, which makes it difficult for me to convince him. I would have expected about the same performance.
This query was just an example.