Slow performance Azure 'Web App' + Azure SQL DB

Question

I've moved an existing asp.net web-api over from an Azure VM (with IIS and MSSQL installed locally) to an Azure web app and Azure SQL database (both west-europe). I'm noticing a dramatic increase in processing duration between the two for the same workload. Tasks that take 10-15ms on the VM, take at least 150ms in the hosted service environment. Both internal processing, not including network latency to/from the vm or web app. Both with empty databases and the same code-base. Same database schema with the same indexing.

The VM was a simple 2-core/7GB SSD machine. But even when I set my web app to a premium 4-core/7GB and my database to a premium 250DTU, the performance does not increase at all. In fact, its the same on a basic 2-core web app and 10DTU database. So the bottleneck has to be somewhere else.

How would I go about finding out what causes the slow processing? The web app and database show no performance alerts and don't advise scaling up either.

Answer 1

Run the following query per session/workload to know what is the type of wait occurring during those workloads:

--your query here

go

select * 
from sys.dm_exec_session_wait_stats
where session_id = @@spid
order by wait_time_ms desc

The last SELECT statement will provide you all waits for current session.

You can also enable Query Store.

http://www.sqlcoffee.com/Azure_0010.htm

And run the following query to know what makes queries wait during execution

select  wait_category_desc, sum(total_query_wait_time_ms) as [sum ms], count(*) as [interval cnt],  avg(avg_query_wait_time_ms) as [avg ms], sum(total_query_wait_time_ms / avg_query_wait_time_ms) as [wait cnt]
from sys.query_store_wait_stats
group by wait_category_desc
order by [sum ms] desc

The following article will tell you the meaning of each wait category.

https://learn.microsoft.com/en-us/sql/relational-databases/system-catalog-views/sys-query-store-wait-stats-transact-sql

Hope this helps.