Azure elasticpool query is slow on first execution

Question

Hello,

I am facing a weird problem using Azure Elasticpool with pricing tier Standard: 100 eDTUs

We have a table named X ( has around 4m rows ) Along other columns we have a columns named OperationId ( Guid ) with a non-clusted index

After some idle time ( usually in the morning ) when we do a simple query like 'Select * from X where OperationId = 'GUID'' the processing time of the query takes a lot of time ( around 30 seconds ).

If I execute the same query immediatly after, it gives me the result in miliseconds.

Also, if I try to clear the cache to reproduce the slow query and run

• DBCC DROPCLEANBUFFERS; or DBCC FREEPROCCACHE or DBCC FREESYSTEMCACHE;

And then execute my query again, it still gives me the result in miliseconds.

Here is also client statistics with Trial 2 the query ran for the first time and is slow and Trial 3 and 4 afterwards when its running fast.

Does anyone have any idea of what is going on ? Why is that slow in the first execution ? And why if I clear the cache is not slow again and runs fast ?

Thanks in advance!

Best Regards,

Bogdan Lapusan

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

Another possible reason is queries are waiting for a synchronous statistics update to complete, before the compilation and execution can resume. Please try go enable Async update statistics as explained here. Regularly updating statistics can improve the performance also.