Azure Web App / Azure SQL - Latency

Question

I have an Azure Web App which runs a MVC 5 applications. The database for this applications is Azure SQL database.

This application is still in it's beginnings, so it has virtually no traffic and very few items in the database. Even so, there are a lot of times when the web application is slow to respond. Sometimes a request which only makes a single DB query takes 4-5 seconds (maybe more).

As I use the applications it's gets faster; the requests eventually take under 100ms. But then, for unknown reason, it starts being slow again. It doesn't follow an exact pattern.

I have set "AlwaysOn = true" on the Web Apps, but I don't see that big of an improvement.

Is there something that can be done or is there something that I'm missing?

Thanks

Answer 1

If you did not that already, try to place your DB and WebSite in the same region. AlwaysOn is the good way to do, but it looks like the problem is more sophisticated and there should be a more troubleshooting. As Sam suggested, the mode is important as well because of various limits and throttling, but if you say that the load is low, it should not be the issue.

I would propose you to go and fire some SQL troubleshooting, for example, that (thanks to Liam Cavanagh) query will show some top queries that take a longest elapsed time.

Did you try a local database or a database in the VM, by the way?

SELECT TOP 10 qs.total_elapsed_time / qs.execution_count / 1000000.0 AS average_seconds, qs.total_elapsed_time / 1000000.0 AS total_seconds, qs.execution_count, SUBSTRING (qt.text,qs.statement_start_offset/2, (CASE WHEN qs.statement_end_offset = -1 THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2 ELSE qs.statement_end_offset END - qs.statement_start_offset)/2) AS individual_query, o.name AS object_name, DB_NAME(qt.dbid) AS database_name FROM sys.dm_exec_query_stats qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt LEFT OUTER JOIN sys.objects o ON qt.objectid = o.object_id where qt.dbid = DB_ID() ORDER BY average_seconds DESC;