Azure SQL slow first query

Question

We are experiencing that the first execution of a query against an index is very slow. It's like there is a cold start of the index. The table is big with millions of rows.

1. execution: 30+sec
2. execution: 4ms

Database script:

CREATE TABLE [Audits] (
    [Id] int NOT NULL IDENTITY,
    [Timestamp] datetime2 NOT NULL,
    [PackageUid] nvarchar(450) NOT NULL,
    [DeviceUid] nvarchar(450) NOT NULL,
    CONSTRAINT [PK_Audits] PRIMARY KEY ([Id])
);

CREATE INDEX [IX_Audits_DeviceUid] ON [Audits] ([DeviceUid]);
CREATE INDEX [IX_Audits_PackageUid] ON [Audits] ([PackageUid]);
CREATE INDEX [IX_Audits_Timestamp] ON [Audits] ([Timestamp]);

Query:

SELECT COUNT(*)
FROM [Audits] AS [f]
WHERE [f].[DeviceUid] = "04B6481955104083"
ORDER BY [f].[Timestamp] DESC

SELECT *
FROM [Audits] AS [f]
WHERE [f].[DeviceUid] = "04B6481955104083"
ORDER BY [f].[Timestamp] DESC
OFFSET 1 ROWS FETCH NEXT 10 ONLY

Possible causes:

• nvarchar is to big for the index to work quickly enough. I could change it down to 50.
• Missing index on DeviceUid and Timestamp

Answer 1

We are experiencing that the first execution of a query against an index is very slow. It's like there is a cold start of the index. The table is big with millions of rows.

SQL Server query plans always read data from the page cache in memory. If a query needs a page that's not in memory, it enters an PAGEIOLATCH wait while the page is fetched from disk. Once database pages are in the page cache they stay there until they are aged out by more recently/frequently used page via the LRU-K page replacement algorithm.

If you have a very large number of rows with WHERE [f].[DeviceUid] = "04B6481955104083" then it could take some time for all those pages to be read from the index for the count(*). But all those pages might fit in your page cache, so the next run doesn't require any physical IO.

If you look at the actual execution plan, it will have the CPU, IO and Wait stats.

And memory management in serverless Azure SQL Database is a bit different than provisioned VCore or DTU model (or regular SQL Server).

Memory for serverless databases is reclaimed more frequently than for provisioned compute databases. This behavior is important to control costs in serverless and can impact performance.

Serverless Overview - Memory Management

So you will more often be in a cold-cache scenario in serverless.