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,
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.
B. If the query plans are different,
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.
Hope it helps. Your feedback is very welcome!
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.