Right-size the buffer pool so the working set stays in memory

The buffer pool is Azure SQL’s foundational cache: pages read from storage are held in memory so subsequent reads avoid I/O entirely. Sub-millisecond response begins with ensuring your hot working set — the data and index pages your frequent queries actually touch — fits comfortably in the memory your service tier provides. When it does not, the engine evicts pages under pressure and the same query oscillates between a fast in-memory read and a slow storage read, producing exactly the inconsistent latency that breaks an SLA.

The levers are tier and design. The Business Critical and Premium tiers provide more memory and local SSD than General Purpose, which is why latency-critical workloads belong there; the choice of tier is documented across the Azure SQL vCore service tiers. On the design side, narrower rows and well-chosen indexes mean more of the working set fits in the same memory. Monitor the buffer cache hit ratio and Page Life Expectancy, and treat a falling PLE as the early warning that the working set no longer fits.

There is an important interaction with every other strategy in this guide: anything that reduces the on-disk size of your hot data increases how much of it the buffer pool can hold. Data compression, columnstore, narrow data types, and the removal of unused indexes all act as force multipliers on the buffer pool, raising the hit ratio without buying a single gigabyte of additional memory. This is why a tuning engagement that begins with schema and index design so often improves cache residency as a side effect — reading less data and caching more of it are two sides of the same coin.

-- Buffer cache hit ratio and Page Life Expectancy
SELECT object_name, counter_name, cntr_value
FROM sys.dm_os_performance_counters
WHERE counter_name IN
  ('Buffer cache hit ratio', 'Page life expectancy');

-- Which objects dominate the buffer pool right now?
SELECT TOP 10 obj.name, COUNT(*) AS cached_pages
FROM sys.dm_os_buffer_descriptors bd
JOIN sys.allocation_units au ON bd.allocation_unit_id = au.allocation_unit_id
JOIN sys.partitions p ON au.container_id = p.hobt_id
JOIN sys.objects obj ON p.object_id = obj.object_id
GROUP BY obj.name ORDER BY cached_pages DESC;

Adopt In-Memory OLTP for the hottest tables

When the buffer pool is not enough — when even an in-memory page read carries too much latching and locking overhead for an extreme-throughput hot path — In-Memory OLTP is the Azure SQL answer. Memory-optimized tables live entirely in memory in a lock-free, latch-free structure, using optimistic multi-version concurrency control instead of locks. For session state, hot counters, ingestion buffers, and other high-contention OLTP patterns, the result is genuinely sub-millisecond access with dramatically higher throughput than disk-based tables.

In-Memory OLTP is available on the Premium and Business Critical tiers, and adopting it is a deliberate design choice rather than a switch: you create a memory-optimized table type, size the data carefully against your memory budget, and choose durability. The capability and its constraints are described in the Microsoft documentation on In-Memory OLTP. Reserve it for the genuinely hot tables that justify the memory; it is a precision tool, not a default.

-- A memory-optimized table for an extreme-throughput hot path
CREATE TABLE dbo.SessionState
(
    session_id  UNIQUEIDENTIFIER NOT NULL,
    payload     VARBINARY(8000)  NOT NULL,
    updated_at  DATETIME2      NOT NULL,
    CONSTRAINT PK_Session PRIMARY KEY NONCLUSTERED HASH (session_id)
        WITH (BUCKET_COUNT = 1048576)
)
WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA);

Natively compile the hottest stored procedures

Memory-optimized tables remove the storage and locking overhead; natively compiled stored procedures remove the interpretation overhead. A conventional T-SQL procedure is interpreted statement by statement at execution time, whereas a natively compiled procedure is translated into machine code when it is created, collapsing the per-statement cost to almost nothing. For short, high-frequency transactions against memory-optimized tables, the combination is what delivers consistent sub-millisecond execution at scale.

The trade-off is flexibility: natively compiled procedures support a focused subset of T-SQL and are best suited to the tight, well-defined hot paths that run millions of times rather than to sprawling business logic. Identify the handful of procedures that dominate your transaction volume and compile those, leaving the rest interpreted. The feature is detailed in the documentation on natively compiled stored procedures.

-- Native compilation for a tight, high-frequency transaction
CREATE PROCEDURE dbo.UpsertSession
    @id UNIQUEIDENTIFIER, @payload VARBINARY(8000)
WITH NATIVE_COMPILATION, SCHEMABINDING
AS BEGIN ATOMIC WITH
  (TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = N'us_english')
    UPDATE dbo.SessionState SET payload = @payload, updated_at = SYSUTCDATETIME()
      WHERE session_id = @id;
END;

Cache at the application tier with Azure Cache for Redis

The fastest database query is the one never issued. The top of the caching hierarchy lives outside the database entirely: an application-tier cache that serves frequent reads before they ever reach Azure SQL. Azure Cache for Redis is the managed, in-memory store designed for exactly this, delivering microsecond-to-low-millisecond reads and removing load from the database so the queries that must reach it run faster too.

The dominant pattern is cache-aside: the application checks Redis first, and on a miss reads from Azure SQL and populates the cache with a sensible expiry. The engineering discipline is in invalidation — deciding how and when cached values expire so users never see stale data — and in choosing what to cache, which should be the read-heavy, slowly changing, high-traffic data that yields the largest hit ratio. Done well, the application cache absorbs the majority of reads, and the database is left to do only the work that genuinely requires it.

Two design decisions determine whether the cache helps or hurts. The first is the eviction and expiry policy: too aggressive and the hit ratio collapses, too lax and users see stale data, so the policy must reflect how tolerant each piece of data is of staleness. The second is the failure mode — the application must degrade gracefully to the database when the cache is unavailable, never treating a cache outage as a data outage. Treat the cache as an accelerator that the system can live without, size it so the working set of hot keys fits in memory, and monitor its hit ratio as closely as you monitor the database itself.

Use columnstore and batch mode for analytical hot paths

Caching is not only for point lookups. When the hot path is analytical — aggregations and scans over large tables for dashboards and reporting — the right cache is a columnstore index. Columnstore stores data by column rather than by row, compresses it heavily so far more of it fits in memory, and engages batch-mode execution that processes rows in vectorized chunks. The combination routinely delivers order-of-magnitude gains on analytical queries while shrinking the memory footprint, which in turn improves cache residency.

For hybrid workloads that serve both transactions and analytics from the same data, a nonclustered columnstore index over a rowstore table gives the analytical queries their columnar cache without disturbing the OLTP path. The compression effect is itself a caching strategy: by reducing the bytes each query must read, columnstore increases the share of the working set that stays resident in memory, pushing more reads up the hierarchy.

-- Columnstore for analytical queries over a large table
CREATE NONCLUSTERED COLUMNSTORE INDEX NCCI_Orders
  ON dbo.Orders (order_date, region, product_id, amount);

-- Confirm batch-mode execution in the actual plan
SET STATISTICS XML ON;

Serve cached reads from read-only replicas

Sub-millisecond response under load requires that read traffic does not contend with writes for the same memory and CPU. Azure SQL’s read scale-out capability, available on the Business Critical and Hyperscale tiers, routes read-only workloads to replicas that each maintain their own warm buffer pool. Directing reporting, dashboard, and analytical queries to a replica leaves the primary’s cache dedicated to the write path, so both improve.

Routing is a connection-string change — set the application intent to read-only — which makes this one of the highest-leverage, lowest-effort caching strategies available. Each replica becomes an independent cache of the data, multiplying the effective memory available to serve reads. For read-heavy applications, distributing reads across replicas is often the difference between a primary that thrashes its cache under mixed load and a fleet that keeps every tier of the hierarchy warm.

-- Route read-only workloads to a replica's warm cache
Server=tcp:myserver.database.windows.net;Database=mydb;
ApplicationIntent=ReadOnly;
Authentication=Active Directory Default;

Pre-compute results with indexed views

Some expensive results are worth computing once and caching in the database itself. An indexed view — a view with a clustered index — materializes its result set on disk and in the buffer pool, and the engine maintains it automatically as the underlying data changes. For aggregations and joins that the same dashboard runs thousands of times, an indexed view turns a repeated scan-and-aggregate into a fast read of a small, pre-computed, cached result.

The cost is write-time maintenance, so indexed views suit read-heavy data where the aggregate is queried far more often than the base rows change. Used judiciously on the right high-frequency aggregates, they are a powerful in-database cache that the optimizer can even apply automatically to queries that did not name the view. The requirements and rules are covered in the documentation on creating indexed views.

Stabilize the plan cache with Query Store

The procedure and plan cache is a caching layer in its own right: Azure SQL compiles an execution plan once and reuses it, so a stable, good plan is itself a form of caching that avoids recompilation and guarantees consistent latency. The risk is plan instability — parameter-sensitive plans and recompilations that swap a fast plan for a slow one with no code change, producing the intermittent latency spikes that are hardest to diagnose. Sub-millisecond targets cannot tolerate that variance.

Query Store, enabled by default on Azure SQL Database, records plans and runtime statistics over time and lets you force a known-good plan when the optimizer drifts. It is the mechanism that keeps the plan cache trustworthy: identify the regressed query, compare the plans, and pin the one that meets your latency target. Pair this with sound parameterization so the engine caches and reuses plans rather than compiling a new one for every literal value.

-- Find regressed queries, then force the good plan
SELECT q.query_id, p.plan_id, rs.avg_duration, rs.count_executions
FROM sys.query_store_runtime_stats rs
JOIN sys.query_store_plan p ON p.plan_id = rs.plan_id
JOIN sys.query_store_query q ON q.query_id = p.query_id
ORDER BY rs.avg_duration DESC;

EXEC sys.sp_query_store_force_plan @query_id = 42, @plan_id = 7;