1

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:

Link

https://www.brentozar.com/pastetheplan/?id=S1cfDgpzD

Execution plan of the azure sql server:

Link2

https://www.brentozar.com/pastetheplan/?id=HyyshlTMw

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.

  • I've noticed the execution plan is the same after doing a sp_updatestats, however, it does take a lot longer on azure. – Stefan Schrey Aug 18 at 13:28
1

If the plan is the same (which it looks to be), and the query speeds up when you bump up to more DTUs, it's likely you're simply being limited by the Azure tier you're on.

You can confirm this by looking in the execution plan at the wait stats - if you're being CPU-capped by your Azure tier, there will be RESOURCE_GOVERNOR_IDLE waits (and related SOS_SCHEDULER_YIELD waits as well).

I've blogged about this here: RESOURCE_GOVERNOR_IDLE in Azure

Looking at the Azure plan you provided:

<WaitStats>
  <Wait WaitType="SOS_SCHEDULER_YIELD" WaitTimeMs="597" WaitCount="686" />
  <Wait WaitType="RESOURCE_GOVERNOR_IDLE" WaitTimeMs="570" WaitCount="77" />
</WaitStats>
<QueryTimeStats CpuTime="2756" ElapsedTime="3351" />

The difference between elapsed and CPU time is 595 - which is almost exactly the amount of RESOURCE_GOVERNOR_IDLE waits that were incurred. Without those waits, the query would have finished in 2.7 seconds in theory, which is still slower than the 1.1 seconds it took the SQL Server 2014 query.

The main solutions are to either increase the service tier, or change the query / indexes so it uses less CPU.

My guess would be that the other hosting provider machine has a faster clock speed on the CPU, as well as not being limited by the resource governor.

To be clear though, you're client may not be considering the manageability "wins" with Azure, even at a more expensive / higher tier. You'll get many advantages (including high availability, automated backups, automated tuning, all the latest database engine and security patches, and not having to deal with SQL Server instance level or VM maintenance) that are included in the slightly higher cost.


For this specific query, you might try creating an indexed view of this data if it is needed frequently, to "pre-calculate" this distinct list of user groups that have placed orders. Then the query would only need to read the 20 rows from that view, which would be less likely to be impacted by the service tier.

Note that you would have to use GROUP BY instead of DISTINCT in the view definition due to the limitations on indexed views.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.