Why should you care?

Throughput dies when every writer queues behind wide locks.

See it live — copy this example

Run in SQL Server Management Studio (SSMS) or Azure Data Studio.

USE DataVerse;
-- Prefer precise key updates over scans
UPDATE dbo.Accounts SET Balance = Balance - 1 WHERE AccountId = 1;
-- Instead of: UPDATE Accounts SET Balance = Balance - 1 WHERE AccountNo LIKE 'SB%';

SELECT wait_type, waiting_tasks_count, wait_time_ms
FROM sys.dm_os_wait_stats
WHERE wait_type LIKE 'LCK%'
ORDER BY wait_time_ms DESC;

What happened?

• Updating by primary key locks one row.
• Updating by loose LIKE may lock many.
• Lock wait stats show whether locking dominates the instance.

Practice next

1. Prefer key-based writes in your procs.
2. Review top LCK waits.
3. Ensure supporting indexes exist for WHERE clauses.
4. Split a huge UPDATE into batched TOP (1000) loops with commits.
5. Compare RCSI on vs off under a read/write mix.

Remember

Narrow locks via good keys/indexes. Shorten transactions. Measure lock waits.

Peak hour throughput

DataVerse load test raised TPS after key-based updates + RCSI.

Outcome: Checkout queue length dropped.