First Request on Azure SQL Database Returns Execution Timeout

Robson Soares 1 Reputation point
2022-12-05T20:24:29.643+00:00

Hi @Alberto Morillo We have a SAAS solution on azure based on azure sql database and microservices.
We use multiple standard databases and pricing tiers based on microservices workloads.
We are experimenting a very similar serverless behavior. The first request submitted by an api returns the error:
Execution Timeout Expired. The timeout period elapsed prior to completion of the operation or the server is not responding. Unknown error 258.
After some retries the database "wakeup".
That database was a S6 and after a splitting we scaled down to S2. We have same behavior in both tiers.
That query submitted (created by Entity Framework) has a long wait time based on query store investigation and the query plan shows 82% due a PK clustered index.
How can we fixed that issue?
Best regards

Azure SQL Database

1 answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. Alberto Morillo 32,901 Reputation points MVP
    2022-12-05T23:00:01.153+00:00

    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).

    Sometimes, the poor performance when you run a query the first time may be related the query waiting for a synchronous statistics update to complete. Please consider enabling asynchronous statistics update option as explained here. Sometimes those synchronous update statistics generate some processes and queries that start with "SELECT Statman" that create some spikes on DTU usage and may produce the timeouts. I love updating the statistics with FULLSCAN and defragment of indexes during non-business hours also.

    Make sure the queries where you see the poor performance on the first execution don't need some indexes.

    Hope this helps.

    0 comments