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.

When you say local do you mean your local machine or a machine that's on premise on the same network? — univ

Alberto Morillo Alberto Morillo · Accepted Answer · 2017-07-06T19:51:10

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://docs.microsoft.com/en-us/sql/relational-databases/system-catalog-views/sys-query-store-wait-stats-transact-sql

Hope this helps.

Slow performance Azure 'Web App' + Azure SQL DB

2 Answers