The average INSERT took under 5 ms. The database had CPU to spare and the log was half idle. Yet every few minutes throughput dipped, because a handful of INSERTs into one table took more than a second. This is the story of how we found the cause, a single hot page at the end of a clustered index, and the two changes that removed it.
01 The setup
| Item | Detail |
|---|---|
| Database | Azure SQL Database Hyperscale, 128 vCores, premium-series hardware |
| Application | Java API services (Microsoft JDBC driver) on Kubernetes, inserting one row per call |
| Load | JMeter at a fixed target rate of about 2,200 TPS, nothing else running on the database |
| Monitoring | Dynatrace for the application side, Query Store and DMVs on the database side |
| Problem table | A staging table: one string ID plus the record payload, written on every transaction |
02 The symptom: a good average hiding bad seconds
When we compared tables, one staging table stood out. Its INSERT had a higher average and a much higher maximum than other high-traffic tables with similar row sizes and call counts. The average was still low (under 5 ms), so dashboards that showed averages looked fine. But the occasional 1.3-second INSERT held up the API call, and the TPS graph showed regular dips.
03 The evidence: PAGELATCH_EX on the same few pages
A snapshot of running requests during a dip showed this (68 rows; first few shown, table renamed):
session_id wait_type wait_resource wait_sec status sql_text 617 PAGELATCH_EX 23:48:8332904 0.009 suspended INSERT INTO TXN_ENTRY_PENDING(PAYLOAD, REC_ID) VALUES (@P0, @P1) 1594 PAGELATCH_EX 23:49:365059 0.009 suspended INSERT INTO TXN_ENTRY_PENDING(PAYLOAD, REC_ID) VALUES (@P0, @P1) 941 PAGELATCH_EX 23:50:4152341 0.007 suspended INSERT INTO TXN_ENTRY_PENDING(PAYLOAD, REC_ID) VALUES (@P0, @P1) 1517 PAGELATCH_EX 23:49:365059 0.006 suspended INSERT INTO TXN_ENTRY_PENDING(PAYLOAD, REC_ID) VALUES (@P0, @P1) 2281 PAGELATCH_EX 23:45:14050730 0.006 suspended INSERT INTO TXN_ENTRY_PENDING(PAYLOAD, REC_ID) VALUES (@P0, @P1) 1285 PAGELATCH_EX 23:45:14050730 0.006 suspended INSERT INTO TXN_ENTRY_PENDING(PAYLOAD, REC_ID) VALUES (@P0, @P1) ...
Counting the full snapshot told the story:
| Observation | What it means |
|---|---|
| 68 sessions, all PAGELATCH_EX, all the same INSERT | One statement on one table, not a general slowdown |
| Only 29 distinct pages; the hottest had 7 sessions queued on it | Inserts were piling onto a few pages instead of spreading out |
| Each wait only 0–9 ms (average ~4 ms) | Not slow storage; a queue of many short waits for the same page |
| DB CPU max 37 %, log IO max 51 % | No resource was exhausted. The sessions were waiting for each other |
Two waits are easy to confuse. PAGELATCH_* is a wait for a page already in memory: a short physical lock while a thread changes the page. PAGEIOLATCH_* is a wait for a page to be read from disk. Seeing PAGELATCH_EX means storage is not the problem; concurrency on the same pages is. A wait_resource of 23:48:8332904 reads as database_id : file_id : page_id. (Hyperscale spreads data over many files, which is why the file numbers vary.)
04 The table and how it was used
CREATE TABLE dbo.TXN_ENTRY_PENDING
(
REC_ID nvarchar(255) NOT NULL,
PAYLOAD xml NULL,
PRIMARY KEY CLUSTERED (REC_ID ASC)
WITH (OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF)
);
Only three statements ever touched it:
INSERT INTO TXN_ENTRY_PENDING(PAYLOAD, REC_ID) VALUES (@P0, @P1) -- every transaction SELECT COUNT(REC_ID) FROM TXN_ENTRY_PENDING -- full scan SELECT REC_ID FROM TXN_ENTRY_PENDING -- full scan
Two details matter. The IDs are generated by the application and mostly increase (they start with a date or sequence). And nothing looks a row up by its ID: the table is a staging area that another process reads in full and empties. Keep that second point in mind; it is what makes the fix safe.
05 Why it happens: last-page insert contention
A clustered index keeps rows in key order. When every new key is larger than the last one, every INSERT goes to the same last leaf page. To add a row, a session takes an exclusive latch (PAGELATCH_EX) on that page for a fraction of a millisecond. With hundreds of sessions inserting at once, they form a queue for that page. Each wait is tiny, but the queue never clears, and when a page fills and splits, the queue briefly gets longer. That is when an INSERT takes a second.
Here we saw 29 hot pages rather than exactly one. The most likely reason is that the IDs came from a few increasing sequences, so the index had a few "ends" instead of one, plus pages being split. The effect is the same. More CPU would not help; it only adds more sessions to the same queue.
06 Options we weighed
| Option | What it does | Verdict here |
|---|---|---|
OPTIMIZE_FOR_SEQUENTIAL_KEY = ON | SQL Server 2019+ and Azure SQL. Controls how fast threads enter the latch queue, so it doesn't collapse into a convoy | Helps throughput, but the single hot page is still there. Cheap first step on other tables |
| Random key (NEWID) | Inserts land all over the index | The ID comes from the application and can't change. Random inserts also cause page splits everywhere |
| Hash partitioning | Partition function on a hash column, so each partition has its own last page | Works, but adds a partition scheme and function to manage for one staging table |
| Hash bucket as the leading key | A persisted computed column (hash % N) placed first in the clustered key | Chosen. Same effect as partitioning with one column; safe because nothing seeks by ID |
07 The fix: a 32-bucket clustered key
CREATE TABLE dbo.TXN_ENTRY_PENDING
(
REC_ID nvarchar(255) NOT NULL,
PAYLOAD xml NULL,
InsertBucket AS CONVERT(tinyint, ABS(CONVERT(bigint, CHECKSUM(REC_ID))) % 32) PERSISTED,
CONSTRAINT PK_TXN_ENTRY_PENDING
PRIMARY KEY CLUSTERED (InsertBucket, REC_ID)
);
| Piece | Why it is written this way |
|---|---|
CHECKSUM(REC_ID) | A fast, deterministic hash of the ID. The same ID always gets the same bucket |
CONVERT(bigint, …) before ABS | CHECKSUM returns int. ABS(-2147483648) overflows in int; in bigint it is safe |
% 32 and tinyint | 32 buckets, values 0–31; one byte added to each key |
PERSISTED | The value is stored, so it can be part of the primary key |
(InsertBucket, REC_ID) | Bucket first. The index now has 32 ranges, each growing at its own end. Still unique, because the bucket comes from the ID |
WHERE REC_ID = @id can no longer seek; it has to check all 32 buckets. If you have ID lookups, add the bucket to the predicate: WHERE InsertBucket = CONVERT(tinyint, ABS(CONVERT(bigint, CHECKSUM(@id))) % 32) AND REC_ID = @id. And do not add a separate index on REC_ID alone; it would recreate the hot spot in that index.Why 32? Enough insert points that hundreds of concurrent sessions rarely meet on the same page, but few enough that each bucket still fills pages in order. Scale it to your concurrency; it is a single number to change.
08 Three tests, one change at a time
| Test 1: copy of original | Test 2: 32-bucket key | Test 3: + XML → NVARCHAR | |
|---|---|---|---|
| Purpose | Control: same structure, fresh table | Remove last-page contention | Remove XML conversion cost |
| INSERT delays | Still present | Not observed | Better average (early telemetry) |
| Max INSERT time | 1.3 s | 0.5 s | Not captured |
| DB CPU max | 37 % | 75 % (average < 40 %) | Not captured |
| Log IO max | 51 % | 49 % | Not captured |
| TPS | 2,200–2,300 with repeated drops | ~2,200, easing to ~2,100, stable | Target held with no dips; ~150 TPS more (team estimate) |
Test 1 was the key control. A brand-new copy of the table, with no old data and no fragmentation, still showed the delays. That proved the problem was the design, not the state of the table. In Test 2, the platform team confirmed from their telemetry that the last-page contention was gone.
09 Reading the results correctly
Two results looked strange at first, and both were questioned in the review. Both have good explanations:
| Surprise | Explanation |
|---|---|
| CPU rose from 37 % to 75 % peak after the fix | Before, sessions spent their time waiting for a latch, which uses no CPU. With the queue gone, they do real work instead. Higher CPU here is a sign of health, not a new problem |
| Max latency fell to a third, but TPS did not rise | JMeter was set to a fixed target rate. A capped load test can show stability and latency, but never higher throughput. To measure the ceiling, raise the load until something else saturates |
10 The second fix: the XML column
Comparing this table with other busy tables that did not have the problem, one difference remained: this table stored the payload as xml, while the others used plain text columns. The application sends the payload as an nvarchar parameter (the JDBC driver declares @P0 nvarchar(4000)). Into an xml column, SQL Server must parse and validate every document and convert it to its internal binary XML format on every INSERT. That work buys nothing if no query ever reads inside the XML, and here none did.
-- same 32-bucket design, payload stored as plain text
CREATE TABLE dbo.TXN_ENTRY_PENDING
(
REC_ID nvarchar(255) NOT NULL,
PAYLOAD nvarchar(max) NULL,
InsertBucket AS CONVERT(tinyint, ABS(CONVERT(bigint, CHECKSUM(REC_ID))) % 32) PERSISTED,
CONSTRAINT PK_TXN_ENTRY_PENDING PRIMARY KEY CLUSTERED (InsertBucket, REC_ID)
);
With that change, the target TPS held for the whole run with no dips, roughly 150 TPS more than before, and the database-side average INSERT time already looked better in the first telemetry. These come from the first part of the run, and the max, CPU and log numbers were not captured, so treat this result as a strong signal rather than a full benchmark.
.value(), .nodes(), .exist()) or XML indexes on the column, and that the application did not rely on the database to reject badly formed XML. The application reads the payload back unchanged either way.11 Diagnostic queries you can reuse
1. Who is waiting on page latches right now, and on which page? (SQL Server 2019+ and Azure SQL)
SELECT r.session_id, r.wait_type, r.wait_time AS wait_ms,
OBJECT_NAME(pi.object_id, pi.database_id) AS object_name,
pi.index_id, pi.page_type_desc, pi.page_level,
pi.next_page_page_id, -- 0 = last page at this level (the "right edge")
t.text AS sql_text
FROM sys.dm_exec_requests AS r
CROSS APPLY sys.fn_PageResCracker(r.page_resource) AS pc
CROSS APPLY sys.dm_db_page_info(pc.db_id, pc.file_id, pc.page_id, 'DETAILED') AS pi
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS t
WHERE r.wait_type LIKE 'PAGELATCH%';
2. Which indexes have collected the most latch waits since startup?
SELECT TOP (10)
OBJECT_NAME(s.object_id) AS table_name, i.name AS index_name,
s.leaf_insert_count, s.page_latch_wait_count, s.page_latch_wait_in_ms
FROM sys.dm_db_index_operational_stats(DB_ID(), NULL, NULL, NULL) AS s
JOIN sys.indexes AS i ON i.object_id = s.object_id AND i.index_id = s.index_id
ORDER BY s.page_latch_wait_in_ms DESC;
3. Average and maximum duration of one INSERT over time (Query Store, microseconds → ms)
SELECT rsi.start_time, q.query_id, rs.count_executions,
rs.avg_duration / 1000.0 AS avg_ms,
rs.max_duration / 1000.0 AS max_ms
FROM sys.query_store_runtime_stats AS rs
JOIN sys.query_store_runtime_stats_interval AS rsi ON rsi.runtime_stats_interval_id = rs.runtime_stats_interval_id
JOIN sys.query_store_plan AS p ON p.plan_id = rs.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 qt.query_sql_text LIKE N'%INSERT INTO TXN_ENTRY_PENDING%'
ORDER BY rsi.start_time;
4. Was the INSERT waiting on latches or doing work? (Query Store wait categories)
SELECT ws.plan_id, ws.wait_category_desc,
SUM(ws.total_query_wait_time_ms) AS wait_ms
FROM sys.query_store_wait_stats AS ws
JOIN sys.query_store_plan AS p ON p.plan_id = ws.plan_id
WHERE p.query_id = 12345 -- query_id from query 3
GROUP BY ws.plan_id, ws.wait_category_desc
ORDER BY wait_ms DESC; -- PAGELATCH waits show as 'Buffer Latch'
5. Is the database itself short of CPU or log throughput? (Azure SQL, 15-second samples, last hour)
SELECT end_time, avg_cpu_percent, avg_log_write_percent, avg_data_io_percent FROM sys.dm_db_resource_stats ORDER BY end_time DESC;
12 Rolling the change out safely
In the test environment the table was simply dropped and recreated. On a live system, swap it in a short pause instead, so no staged rows are lost:
-- 1. create the new table under a temporary name (DDL above, named TXN_ENTRY_PENDING_NEW)
-- 2. pause the writers and the process that drains the table
BEGIN TRAN;
EXEC sp_rename 'dbo.TXN_ENTRY_PENDING', 'TXN_ENTRY_PENDING_OLD';
EXEC sp_rename 'dbo.TXN_ENTRY_PENDING_NEW', 'TXN_ENTRY_PENDING';
COMMIT;
-- 3. move any rows still waiting (the bucket is computed automatically)
INSERT INTO dbo.TXN_ENTRY_PENDING (REC_ID, PAYLOAD)
SELECT REC_ID, CONVERT(nvarchar(max), PAYLOAD) FROM dbo.TXN_ENTRY_PENDING_OLD;
-- 4. resume, verify counts, then drop TXN_ENTRY_PENDING_OLD
-- also rename the PK constraint if you keep a naming standard
13 What is still open
Fixing one bottleneck reveals the next. Later in the same run there was a single short TPS dip, and the team traced it to an INSERT into a different queue-style table that took about 5 seconds at that moment. The same questions apply to it: is its payload column XML, would compression help, and does it have an increasing key? That is the next thing I would check on any system like this.
14 Checklist
- Look at max/p99 INSERT latency next to the TPS graph, not only the average.
- During a dip, capture
sys.dm_exec_requests. Many PAGELATCH_EX waits on a few pages, all from one INSERT, means insert contention. - Decode the page with
sys.dm_db_page_info;next_page_page_id = 0shows it is the last page. - Run a control test on a fresh copy of the table to separate design problems from data problems.
- If nothing seeks by the key, lead the clustered key with a hash bucket. Otherwise include the bucket in lookups, or try
OPTIMIZE_FOR_SEQUENTIAL_KEYfirst. - Do not store opaque payloads as
xmlif nothing queries inside them. - Change one variable per run, and know whether your load test is capped.
Thanks to my team and the platform support engineer for a sharp, data-driven investigation. Questions or similar war stories? Leave a comment.
Written by Tamilselvan Perumal, Oracle ACE Apprentice · techstaradmin.blogspot.com
