First queries on Azure SQL server take a very long time

Question

Hello everyone,

Relatively new to Azure environments, I have built an app using Azure App Service in a container working with an Azure SQL database. I am using the basic tier (DTU model), however I am observing extremly long query times when connecting to my app for the first time (first time withint a couple hours usually).

As an example, here are the two first request sent when connecting to the app when it has not been used for a few hours :

The response times are over 30 seconds

When refreshing the page after completion of these request, the response time goes back to a normal duration (~400ms) :

I have searched online for advices and on all settings of the database but can't find anything to correct this...
Any help would be very appreciated

Thank you
Victor Hamel

Answer 1

This is a standard SQL Server pattern. The first time you run a query it has to do a lot of physical IO, memory allocation raises and it's slow. You may take a look at queries performing slow and they may be showing the PAGEIOLATCH_SH and MEMORY_ALLOCATION_EXT waits and that corresponds to pages being pulled from disk to the buffer. The second time you run the query the data is in buffers and it's fast.

After a period of inactivity memory allocation drops. Azure SQL Database shrinks memory allocation after the database has not been used for some time or the database tier has been scaled up or down. You will see this happening on Azure SQL Database but not on SQL Server instances (IaaS).