Your insert rate doubles, and suddenly every session is waiting. High-frequency inserts can queue behind locks, the transaction log, or one hot B-tree page. The fix depends on which queue actually forms when concurrency rises.
Name the Kind of Waiting
Blocking is a lock relationship between transactions. Last-page contention is a latch wait on an in-memory page of an index, commonly visible as PAGELATCH_EX. Transaction log pressure can show up as WRITELOG. These are different mechanisms. Treating all of them as blocking can send the investigation toward the wrong setting.
I capture active waits during the actual insert surge. Check blocking_session_id, wait_type, wait_resource, and transaction duration. A session waiting on a lock needs a different response from one waiting to modify the same index page. Do not rebuild an index merely because many inserts are slow. Are your insert sessions waiting on locks, a page latch, or the log?
Commit Fast, Batch With Limits
An insert transaction that performs remote calls, user interaction, or long reads before commit holds resources longer. Commit the database work promptly after the required validations. Avoid keeping an application transaction open across a queue of unrelated requests. Shorter transactions reduce lock duration and simplify recovery after a failure.
Batching can help by reducing per-row round trips, but huge batches can increase lock and log pressure. Test bounded batch sizes with the application’s durability requirements. I compare throughput and high-percentile latency, not only total batch time. A system that processes one giant batch quickly can still make every interactive request wait.
Diagnose Last-Page Contention from High-Frequency Inserts
A sequential clustered key such as an identity value sends concurrent inserts toward the end of the B-tree. Under enough concurrent writers, the final page can become a hotspot. The key pattern is useful for locality and low fragmentation, so changing it without evidence can create other costs. First confirm PAGELATCH contention on that index and page.
SQL Server 2019 and later support OPTIMIZE_FOR_SEQUENTIAL_KEY, which can improve throughput under this specific pressure. It does not replace diagnosis and does not eliminate every latch. Test before and after with the same concurrency.
SELECT i.name, i.index_id,
i.optimize_for_sequential_key,
os.leaf_insert_count,
os.page_latch_wait_count,
os.page_latch_wait_in_ms
FROM sys.dm_db_index_operational_stats
(DB_ID(), OBJECT_ID(N'dbo.Events'), NULL, NULL) AS os
JOIN sys.indexes AS i
ON i.object_id = os.object_id AND i.index_id = os.index_id
WHERE i.object_id = OBJECT_ID(N'dbo.Events');Try the Sequential-Key Option
After confirming a hot sequential index, enable the option on that specific index in a controlled test. It manages flow into the contended page. It does not change the key order or the application contract. Some workloads gain little because their bottleneck is elsewhere. Measure insert throughput, tail latency, and the wait profile.
Keep a rollback statement and do not apply the option to every index as a default. The example assumes an index named PK_Events. Verify its actual name and that the supported SQL Server version is in use.
ALTER INDEX PK_Events ON dbo.Events
SET (OPTIMIZE_FOR_SEQUENTIAL_KEY = ON);
SELECT name, optimize_for_sequential_key
FROM sys.indexes
WHERE object_id = OBJECT_ID(N'dbo.Events');
Reduce Index Maintenance on High-Frequency Inserts
Every insert writes the base table and each relevant nonclustered index. Wide and overlapping indexes can multiply log records and page changes. Review index usage and query value before removing any structure, especially unique or foreign-key support indexes. A narrow write path can improve throughput without touching the clustered key.
Check defaults, computed columns, triggers, and constraints too. A trigger that performs a lookup or a synchronous audit write can dominate insert time. I trace the whole insert transaction, not just the INSERT statement. A slow queue can be hidden in a second table or an external call.
Watch the Transaction Log
WRITELOG waits can indicate commits waiting for log writes. Confirm log device latency, log file growth, and transaction rate. Put the log on storage that meets the workload’s latency needs and pre-size it to avoid frequent growth. Do not weaken durability casually to make a benchmark faster. The application can depend on committed rows surviving a restart.
Batching reduces commit overhead when the business process permits it, but each batch still needs enough log throughput. Monitor availability group send and redo queues when replicas are involved. A primary can accept rows faster than a secondary replays them, creating another operational limit.
Distribute Work Only When Needed
Partitioning can help manage large data ranges and purge old data, but it does not automatically divide a hot last page. All new rows can still land in one current partition. Hashing or changing the key to spread writes can reduce a hotspot, yet it complicates order, range queries, and uniqueness. Such changes require measured tradeoffs.
I try simpler fixes first: shorter transactions, index review, batch sizing, and the sequential-key option when latch evidence supports it. If distribution is necessary, test read patterns and retention along with insert throughput. A design that writes beautifully but makes every report expensive is not balanced.
Measure Concurrency of High-Frequency Inserts Honestly
Use many independent sessions with realistic arrival patterns and row sizes. Track inserts per second, p95 or p99 latency, timeouts, log flush waits, page latch waits, and blocking. Recreate enough of the surrounding workload to expose contention. A single connection issuing millions of rows tests throughput, not concurrency.
After each change, repeat the same load and compare results. Watch for a bottleneck moving from page latch to log or CPU. That movement can be progress, but it changes the next decision. The useful answer is which resource limits today’s workload, not a permanent claim that inserts are solved.
Keep the Insert Path Predictable
High-volume systems benefit from a clear write contract: bounded transactions, limited indexes, stable key semantics, and monitored queues. Include error handling and idempotency so retries do not create duplicates. If consumers can accept asynchronous writes, a queue can smooth bursts, but it adds latency and operational complexity.
High-frequency inserts Without Blocking comes from matching a fix to the observed wait. A sequential-key option is excellent when the last page is the problem and irrelevant when the log is the problem. The database is giving a clue. Read it before buying a bigger server.
Related reading on this blog: Resolving Last Page Insert PAGELATCH_EX Contention with OPTIMIZE_FOR_SEQUENTIAL_KEY and Last Page Insert PAGELATCH_EX Contention Due to Identity Column.
An insert bottleneck is not fixed by one hint, it is removed by addressing the real queue.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
