Azure SQL database very slow on first load

Tomasz Kornacki 26 Reputation points
2021-03-04T14:37:19.89+00:00

Hello guys,
I have an issue with recently deployed App Service with Azure SQL database underneath. I have the application deployed on two different tenants.
On one tenant the application loads very fast and all the initial queries are running smoothly, on the other one - first load is slow as hell .
Trying to find a root cause, I created simple app with one piece of code opening connection to the SQL server and running one simple query:
SELECT COUNT(*) FROM [User] WHERE Id = 10
Again, after deployment I can see the same behavior - on one of the tenants initial load takes 15 sec (and all of this time is taken by SQL code) all subsequent calls are running smoothly. Other tenant runs without any problems.
When I close the browser, wait some time and I come back to the application in the next session - it looks exactly the same. First call - around 15 seconds, then when I refresh it works fine.
Do you have any idea where to start looking?
Regards
Tomasz

Azure SQL Database
0 comments

2 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. KalyanChanumolu-MSFT 8,316 Reputation points
    2021-03-05T03:40:10.787+00:00

    @Tomasz Kornacki Based on what you described, it appears that the Azure SQL database is deployed as a serverless compute tier. The database is automatically paused during inactive periods and automatically resumes databases when activity returns.
    The delay you are experiencing in the first request is the "warm up" time when the database is being resumed.

    Screenshot below for reference. I have mine configured to Auto-Pause if the database doesn't have any activity for 1 hour.

    74509-image.png

    If this is not the issue in your case, please let us know and we will troubleshoot further.

    ----------

    If an answer is helpful, please "Accept answer" or "Up-Vote" for the same which might be beneficial to other community members reading this thread.

    1 person found this answer helpful.
    0 comments
  2. Tomasz Kornacki 26 Reputation points
    2021-03-09T21:01:26.887+00:00

    Sorry for late answer - many things to do.
    After your post, I've started to look around different service configuration.
    Changing database settings actually didn't help - the databases are all part of the elastic pool.
    There is one strange thing which I discovered.
    I created another App Service in the same location, the same resource group, just different App Service Plan. To make it even more strange - new App Service Plan is set on very basic level, one of the weakest in terms of computing power. And this new application is performing much better!
    I studied settings of both App Services, both App Service Plans and they are identical. So this is how it looks like:
    App Service plan "A":

    • Location = Germany West Central
    • Pricing Tier = PremiumV2
    • 3 App Services provisioned and only one of them actually has the application deployed, other 2 are created for future use
      App Service plan "B":
    • Location = Germany West Central
    • Pricing Tier = Shared
    • 2 App Services provisioned - both with deployed application for testing purpose
      The thing is that application in service plan A is having this delays I was mentioning in the original post. Both applications in service plan B are working without any problems. Now all of them point to the same database so I think I can assume this is not the DB issue.
      I was also trying to move original App Service from Service plan A to Service plan B and this didn't make any difference.
      Is there any logical explanation for this behaviour?
      Regards
      Tomasz
    1 person found this answer helpful.