One specific query runs slower in Azure DB than on-premises SQL Server?!

[Dandan_Zhang]

We often got complaints from customers that they found some specific query runs slower in Azure SQL DB than on-premises. Here are some steps that I normally check:

First of all, we need to know if we are comparing apple to apple here. Are you using the same parameters when running the query in both environments? Are the database/table size and row counts exactly the same? Are the table schema/indexes the same or not? Consider to export the database to the other environment to compare if possible.

Secondly, I would get the query actual execution plans if possible from both on-premises and Azure SQL DB so that we can compare. This will help us know what direction that we need to go next.

A. If the query plans are the same,

• get wait type and wait time from the plan itself.
• or check the query wait type during query execution by running this dmv continuously while the slow query is running. We need to confirm if there is any problematic wait:
  select * from sys.dm_exec_requests  where database_id= [databaseid]   (you will need to identify the problematic query request id)

For example, if the wait type is LCK, you will want to check the blocker and kill it.

If the wait type is SOS_SCHEDULER_YIELD, check the database usage as mentioned below.

If the wait type is ASYNC_NETWORK_IO, ensure your application and azure sql db are in the same region. Also you can capture network trace or psping to understand if there is any latency. In addition, you can get the client statistics to see the network statistics, query execution statistics and et.

You may see other wait types as well and troubleshoot based on what you see.

• Check database resource usage by running: Select * FROM sys.dm_db_resource_stats. If any resource is hitting the 100%, consider to scale the database to next performance level or tune the top resource usage queries. Also ensure the statistics maintenance is done which can possibly improve the overall database performance.

B. If the query plans are different,

• run the update statistics with fullscan against all the tables touched by the problematic query. Then check the performance and query plan again.
• compare the compatibility level on both environments: SELECT name, compatibility_level FROM sys.databases;
• check the azure sql server configurations by select * from sys.database_scoped_configurations

look for cardinality estimation. If your onpremise sql server is running on relatively old version, consider to turn on LEGACY_CARDINALITY_ESTIMATION for testing.

Also you can compare the MAXDOP settings which can also been seen in query plan.

• Some other settings might be different too. For example, tempdb setting, encryption, traceflag and etc.
• In addition, the query plan can also change over the time. You can look into Query Store for this.

Hope it helps. Your feedback is very welcome!