1

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

  • What size Azure SQL database are you using? – Sam Cogan Apr 8 '16 at 16:06
  • Standard S0 - should that matter :) ? – Mihai Apr 11 '16 at 8:53
  • yes it matters, the database performance varies based on what SKU you choose. – Sam Cogan Apr 11 '16 at 8:54
  • At the moment there's very little data. Few tables with at most 100 rows – Mihai Apr 11 '16 at 9:02
  • 1
    I've seen aspx simply take 800ms or more for no reason - no cpu load, no network, no disk. – Patrick May 11 '16 at 5:41
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;
  • Hi. Thanks for the answer. I will give this a try, but the problem I think is more related to Web Apps. I have simple pages which make no requests to the database take 3, 4, 5 even 6 seconds to load. But this happens somewhat at random. I sometimes takes 4 seconds and after that it takes 200ms. I have "AlwaysOn" enabled. – Mihai May 14 '16 at 15:44
  • @Mihai then, add Application Insights to your application - that is very simple to do, and you will be able to see what is going on with your requests etc, longest request, etc. – Alex Belotserkovskiy May 15 '16 at 9:25
  • I'll have a look at that. Just tried one thing now - I loaded a page which makes 2 ajax calls. First one took 477ms and the second one 137ms. I loaded another page then I went back to the first one. The second time around the 2 ajax calls took 20.68 seconds and 12.24 seconds. – Mihai May 15 '16 at 10:45
  • 1
    Ok, that needs more work than expected i think - to profile what is going on with the web app. Please try to use one of the site extensions for web performance analysis - you can go and install it on the Kudu console (websitename.scm.azurewebsites.net, note SCM) in the Site Extensions page, Gallery. – Alex Belotserkovskiy May 16 '16 at 10:01
  • 1
    Did you ever find out something? I am facing the same behaviour like you ... – Mr. Muh Jan 31 '17 at 12:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.