high concurrency
1 TopicHigh Latency, Normal CPU: Diagnosing PAGELATCH and Buffer Latch Contention in Azure SQL
A database can have spare CPU and still spend most of its time waiting. Your application is crawling. You open the Azure portal and CPU is moderate, storage I/O looks fine, worker usage is normal and there's plenty of space. Nothing on the Overview page looks wrong. So, what is the database actually waiting for? We recently worked through a case like this with an Azure SQL elastic pool. Latency was severe, but there was no sign of CPU, memory, worker, storage I/O, disk space or tempdb exhaustion. The evidence pointed somewhere less obvious: contention on in-memory pages under very high concurrency. This post explains how to recognize that pattern and what to do about it. Normal CPU doesn't mean a healthy database CPU shows how much work the database is doing. It doesn't show how long requests spend waiting to do that work. A request's elapsed time is roughly its CPU time plus its wait time. When wait time dominates, response times can get much worse while CPU stays flat or even drops. That's why wait statistics are the missing piece. They tell you what requests were waiting for, not just how busy the server was. PAGELATCH vs. PAGEIOLATCH These two wait types look alike but point to very different problems: Wait type What it means Typical cause PAGEIOLATCH_* Waiting for a page to be read from storage into memory I/O pressure, large scans, not enough memory PAGELATCH_* Waiting to access a page that is already in memory Many sessions competing for the same small set of pages A latch is a short, lightweight lock that protects a page while it's being read or changed. Usually, it's held for microseconds. When hundreds of sessions want the same page at the same moment, they line up, and those microseconds add up to seconds. In Query Store these waits are grouped under the Buffer Latch wait category, separate from Buffer IO (PAGEIOLATCH) and Latch (non-page latches such as LATCH_EX). Why concurrency multiplies the problem Latch contention doesn't grow in a straight line. Adding sessions that all compete for the same hot page doesn't add throughput. It adds queueing. Past a certain point, total throughput falls as concurrency rises, while CPU stays well below its limit. Common hot spots include: Last-page inserts: many sessions inserting into a table keyed on an ever-increasing value (IDENTITY, sequence, timestamp). Small, frequently updated tables: counters, status tables or queue tables. Metadata-heavy patterns at scale: for example, many application instances starting at once and each running schema or metadata discovery queries against the same system catalog pages. tempdb allocation or metadata pages under heavy temporary-object churn. A troubleshooting workflow Latency is high → CPU is normal → check waits → latch waits dominate → find the statements and pages → understand the workload pattern → reduce contention Step 1: Rule out resource exhaustion sys.dm_db_resource_stats keeps about the last hour of usage in 15-second intervals: SELECT TOP (40) end_time, avg_cpu_percent, avg_data_io_percent, avg_log_write_percent, avg_memory_usage_percent, max_worker_percent, max_session_percent FROM sys.dm_db_resource_stats ORDER BY end_time DESC; For elastic pools, also check sys.elastic_pool_resource_stats in the master database of the logical server. A healthy pool can still contain one database that is struggling. Step 2: Find out which waits dominate right now sys.dm_db_wait_stats is cumulative, so take two snapshots and compare them: SELECT wait_type, waiting_tasks_count, wait_time_ms INTO #w1 FROM sys.dm_db_wait_stats; WAITFOR DELAY '00:01:00'; -- sample during the slow period SELECT TOP (10) w2.wait_type, w2.waiting_tasks_count - w1.waiting_tasks_count AS waits, w2.wait_time_ms - w1.wait_time_ms AS wait_ms FROM sys.dm_db_wait_stats AS w2 JOIN #w1 AS w1 ON w1.wait_type = w2.wait_type WHERE w2.wait_time_ms - w1.wait_time_ms > 0 AND w2.wait_type NOT LIKE '%SLEEP%' AND w2.wait_type NOT LIKE 'XE%' AND w2.wait_type NOT IN ('WAITFOR', 'BROKER_TASK_STOP', 'SOS_WORK_DISPATCHER') ORDER BY wait_ms DESC; If PAGELATCH_EX or PAGELATCH_SH is at or near the top, keep going. Step 3: See who is waiting, and on which page SELECT r.session_id, r.wait_type, r.wait_time, r.wait_resource, s.program_name, s.host_name, pi.page_type_desc, OBJECT_NAME(pi.object_id, pi.database_id) AS object_name, pi.index_id, t.text AS sql_text FROM sys.dm_exec_requests AS r JOIN sys.dm_exec_sessions AS s ON s.session_id = r.session_id OUTER APPLY sys.dm_exec_sql_text(r.sql_handle) AS t OUTER APPLY sys.fn_PageResCracker(r.page_resource) AS pc OUTER APPLY sys.dm_db_page_info(pc.db_id, pc.file_id, pc.page_id, 'DETAILED') AS pi WHERE r.wait_type LIKE 'PAGELATCH%'; Look for patterns: Many sessions on the same object and page: a classic hot page. The same statement repeated many times: a high-frequency pattern. Database ID 2: the contention is in tempdb. A system object: metadata contention. Step 4: Find the statements that accumulate the most waiting When elapsed time is far higher than CPU time, a statement is mostly waiting: SELECT TOP (10) qs.execution_count, qs.total_elapsed_time / 1000 AS total_elapsed_ms, qs.total_worker_time / 1000 AS total_cpu_ms, (qs.total_elapsed_time - qs.total_worker_time) / 1000 AS approx_wait_ms, SUBSTRING(st.text, qs.statement_start_offset / 2 + 1, (CASE qs.statement_end_offset WHEN -1 THEN DATALENGTH(st.text) ELSE qs.statement_end_offset END - qs.statement_start_offset) / 2 + 1) AS statement_text FROM sys.dm_exec_query_stats AS qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st ORDER BY approx_wait_ms DESC; To investigate after the fact, Query Store keeps waits for each query: SELECT TOP (10) q.query_id, qt.query_sql_text, SUM(ws.total_query_wait_time_ms) AS buffer_latch_wait_ms FROM sys.query_store_wait_stats AS ws JOIN sys.query_store_plan AS p ON p.plan_id = ws.plan_id JOIN sys.query_store_query AS q ON q.query_id = p.query_id JOIN sys.query_store_query_text AS qt ON qt.query_text_id = q.query_text_id WHERE ws.wait_category_desc = 'Buffer Latch' GROUP BY q.query_id, qt.query_sql_text ORDER BY buffer_latch_wait_ms DESC; Step 5: Build a timeline Put the following side by side by minute: Request rate and connection count Latch wait time CPU Application events such as deployments, restarts, scale-outs and batch jobs Contention that starts exactly when connections surge, and not when CPU climbs, tells you a lot. Why scaling can look like the fix In our case, latency recovered around the time the pool was scaled up. It's tempting to conclude that more vCores fixed it. That conclusion isn't proven. Scaling an Azure SQL database or pool does much more than add CPU: The workload usually moves to new compute. All connections are dropped and re-established. Memory and caches start fresh. Time passes, and the peak may simply end. Any of these can break a contention pattern, whatever the vCore count. To tell correlation from causation, compare latch waits at similar load before and after the change. If the same waits come back at the next peak on the larger size, capacity wasn't the root cause. Reducing contention Database side Last-page insert hot spots: consider OPTIMIZE_FOR_SEQUENTIAL_KEY = ON on the index, or a less sequential key design where appropriate. Small hot tables: spread or partition them, or reduce how often they're updated (batching, caching). tempdb churn: reduce heavy temporary-table creation in high-frequency code paths. Application side Limit concurrency. Size connection pools and worker counts on purpose. More parallel sessions isn't always faster. Cache metadata. Run schema or metadata discovery once, not on every connection or request. Avoid startup storms. Stagger instance startup, and use retry with exponential backoff and jitter. Batch small operations instead of sending thousands of tiny concurrent ones. Try it yourself: a concurrency lab CREATE TABLE dbo.HotInsert ( Id bigint IDENTITY(1,1) NOT NULL, Payload char(200) NOT NULL DEFAULT 'x', CreatedAt datetime2 NOT NULL DEFAULT SYSUTCDATETIME(), CONSTRAINT PK_HotInsert PRIMARY KEY CLUSTERED (Id) ); Run the same insert at three concurrency levels, for example with the RML Utilities ostress tool: ostress -S"<server>.database.windows.net" -U"<user>" -P"<password>" -d"<labdb>" ^ -Q"INSERT dbo.HotInsert DEFAULT VALUES;" -n1 -r20000 -q Repeat with -n16 and then -n200, adjusting -r so the total number of rows stays similar. For each run, record: Rows per second CPU from sys.dm_db_resource_stats The PAGELATCH_EX delta from the Step 2 query Then enable OPTIMIZE_FOR_SEQUENTIAL_KEY and run the test again: ALTER INDEX PK_HotInsert ON dbo.HotInsert SET (OPTIMIZE_FOR_SEQUENTIAL_KEY = ON); Watch how throughput levels off, or drops, as concurrency rises while CPU stays well below its limit. That's latch contention. Quick decision tree Is latency high but CPU, I/O, workers and memory normal? → Check waits. Is PAGEIOLATCH dominant? → Storage or memory pressure. Look at scans and indexing. Is PAGELATCH dominant? → In-memory contention. Find the hot pages and objects (Step 3). Is the hot page in a user table? → Insert pattern, key design or hot rows. Is it in tempdb or a system object? → Temporary-object churn or metadata-heavy patterns. Did it start with a connection surge? → Fix concurrency on the application side. Did scaling help? → Confirm at the next comparable peak before treating it as the fix. Key takeaway Not every Azure SQL performance problem needs more vCores. When latency is high and CPU is normal, look at what the database is waiting for, not just how busy it is. Latch contention is a concurrency problem, and it's usually solved by changing the workload pattern rather than buying more compute.