I added one annotation to a Java entity class. Two lines of code. The PR
diff was so small that our reviewer almost skipped it.
That change cut the bytes written to our SQL transaction log on every UPDATE
by roughly 90%, eliminated unnecessary index maintenance on 17 nonclustered
indexes, and brought our peak database wait times from minutes down to near
zero during the busiest job of the day.
Here's what happened, why it happened, and how you can check whether the
same thing is quietly hurting your service right now.
The setup
We run a Spring Boot service backed by Azure SQL (SQL Server). One of our
core tables — let's call it MEMBER_PROFILE — stores member profile and
loyalty data. It has 22 columns: ids, names, statuses, timestamps, JSON
blobs, and an optimistic-lock version.
Every day a scheduled cron job processes metric-update events. During peak
hours this job handles tens of thousands of events per minute. Each event
typically changes 2–3 columns on a MEMBER_PROFILE row — say,
RANK_LEVEL, NEXT_RANK_LABEL, and LAST_MODIFIED_AT.
The service ran fine for months. Then the user base grew, traffic climbed,
and we started seeing something ugly in our database dashboards.
The symptom
During the daily cron window our Azure SQL metrics showed:
| Metric | Before | What we expected |
|---|---|---|
| Avg transaction log writes per UPDATE | ~3.2 KB | ~0.3 KB |
| Nonclustered index pages touched per UPDATE | 17 | 2–3 |
WRITELOG waits during cron |
elevated, periodic spikes | near zero |
PAGELATCH waits |
noticeable | minimal |
| Trigger firing overhead | all 22 columns evaluated | only changed columns |
The database was doing roughly 10× more write work than the actual data
changes justified.
Here's what the database wait dashboard looked like during peak cron hours —
WRITELOG at over 2,000 seconds total:
The root cause
Hibernate's default behaviour is to generate a single static UPDATE
statement for each entity class at startup and reuse it for every save. That
statement sets every column, every time, regardless of which fields
actually changed.
Here's what Hibernate was sending to SQL Server on every save() call
(simplified):
-- Hibernate's DEFAULT behaviour: static UPDATE with all 22 columns
UPDATE MEMBER_PROFILE
SET ACCOUNT_ID = @p1,
EMAIL_ADDR = @p2,
REGION_CODE = @p3,
TZ_NAME = @p4,
DEFAULT_TZ = @p5,
BILLING_PERIOD = @p6,
RANK_LEVEL = @p7, -- ← actually changed
RANK_LABEL = @p8,
NEXT_RANK_LEVEL = @p9, -- ← actually changed
NEXT_RANK_LABEL = @p10,
ACCT_STATUS = @p11,
CONSENT_STATUS = @p12,
CONSENT_ACCEPTED_AT = @p13,
CONSENT_VERSION = @p14,
STATS_JSON = @p15, -- a JSON blob, ~2 KB
RANK_HISTORY_JSON = @p16, -- another JSON blob
UPDATED_AT = @p17,
CREATED_AT = @p18,
LAST_MODIFIED_AT = @p19, -- ← actually changed
OPT_LOCK_VER = @p20,
ORG_ID = @p21,
UPDATED_BY = @p22
WHERE ID = @p23
AND OPT_LOCK_VER = @p24;
We only changed 3 values. Hibernate sent all 22.
Why this matters to the database
When SQL Server receives this UPDATE, it doesn't know that 19 of those
column values are identical to what's already on disk. It has to:
┌─────────────────────────────────────────────────────┐
│ What SQL Server does │
├─────────────────────────────────────────────────────┤
│ │
│ 1. Write the FULL before-image of the row │
│ to the transaction log (~3.2 KB) │
│ │
│ 2. Write the FULL after-image of the row │
│ to the transaction log (~3.2 KB) │
│ │
│ 3. For EACH nonclustered index that covers │
│ a column in the SET clause: │
│ • Read the old index entry │
│ • Delete it │
│ • Insert the new entry │
│ (Even if the indexed value didn't change!) │
│ │
│ 4. Fire any UPDATE triggers, passing ALL 22 │
│ columns in the INSERTED / DELETED pseudo-tables │
│ │
│ 5. Update the row version for CDC / replication │
│ │
└─────────────────────────────────────────────────────┘
With 17 nonclustered indexes on the table, every UPDATE touched up to 17
index B-trees even when the indexed columns hadn't changed. That's a lot
of page latches, a lot of log writes, and a lot of wasted I/O.
At tens of thousands of events per minute, this added up fast.
The fix: two lines of code
import org.hibernate.annotations.DynamicUpdate;
@DynamicUpdate // ← this is the fix
@Entity
@Table(name = "MEMBER_PROFILE")
public class MemberProfileEntity extends BaseEntity {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Long id;
@Column(name = "ACCOUNT_ID")
private String accountId;
@Column(name = "RANK_LEVEL")
private Integer rankLevel;
// ... 19 more columns ...
}
That's it. One import, one annotation.
What @DynamicUpdate does
Instead of generating a static UPDATE at startup, Hibernate now compares
the entity's current state with its loaded snapshot (the "dirty check" it
already does anyway) and builds the SQL at runtime with only the changed
columns:
-- With @DynamicUpdate: only the 3 columns that actually changed
UPDATE MEMBER_PROFILE
SET RANK_LEVEL = @p1,
NEXT_RANK_LEVEL = @p2,
LAST_MODIFIED_AT = @p3,
OPT_LOCK_VER = @p4
WHERE ID = @p5
AND OPT_LOCK_VER = @p6;
4 columns instead of 22. (The OPT_LOCK_VER increment is always
included for optimistic locking.)
Before vs. after — the actual SQL
Before (save() after changing 3 fields — Hibernate sends all 22):
UPDATE MEMBER_PROFILE
SET ACCOUNT_ID = @p1,
EMAIL_ADDR = @p2,
REGION_CODE = @p3,
TZ_NAME = @p4,
DEFAULT_TZ = @p5,
BILLING_PERIOD = @p6,
RANK_LEVEL = @p7, -- ← actually changed
RANK_LABEL = @p8,
NEXT_RANK_LEVEL = @p9, -- ← actually changed
NEXT_RANK_LABEL = @p10,
ACCT_STATUS = @p11,
CONSENT_STATUS = @p12,
CONSENT_ACCEPTED_AT = @p13,
CONSENT_VERSION = @p14,
STATS_JSON = @p15,
RANK_HISTORY_JSON = @p16,
UPDATED_AT = @p17,
CREATED_AT = @p18,
LAST_MODIFIED_AT = @p19, -- ← actually changed
OPT_LOCK_VER = @p20,
ORG_ID = @p21,
UPDATED_BY = @p22
WHERE ID = @p23
AND OPT_LOCK_VER = @p24;
After (@DynamicUpdate — Hibernate sends only what changed):
UPDATE MEMBER_PROFILE
SET RANK_LEVEL = @p1,
NEXT_RANK_LEVEL = @p2,
LAST_MODIFIED_AT = @p3,
OPT_LOCK_VER = @p4
WHERE ID = @p5
AND OPT_LOCK_VER = @p6;
22 columns → 4. Same result, ~90% less I/O.
The cascade of savings
┌──────────────────────────────────────────────────────────────────┐
│ │
│ Before: UPDATE sets 22 columns │
│ ───────────────────────────── │
│ • Transaction log: ~6.4 KB per UPDATE (before + after image) │
│ • Index maintenance: 17 indexes touched │
│ • Page latches: high contention │
│ • Trigger cost: all columns evaluated │
│ │
│ After: UPDATE sets 3–4 columns │
│ ────────────────────────────── │
│ • Transaction log: ~0.6 KB per UPDATE │
│ • Index maintenance: 2–3 indexes touched │
│ • Page latches: minimal contention │
│ • Trigger cost: only changed columns evaluated │
│ │
│ Result │
│ ────── │
│ • ~90% reduction in log bytes per UPDATE │
│ • ~85% reduction in index page writes │
│ • WRITELOG wait type: elevated → near zero │
│ • PAGELATCH_EX wait type: noticeable → negligible │
│ • Peak cron throughput headroom: significantly increased │
│ │
└──────────────────────────────────────────────────────────────────┘
And here's the same dashboard after deploying the fix — WRITELOG dropped
from 2,020s to 403s:
How to check if this affects you
Step 1: Find your wide entities
Any JPA entity with 10+ columns where typical updates touch fewer than
half of them is a candidate. Look for:
- JSON/text blob columns (
STATS_JSON,PAYLOAD,CONFIG) - Audit columns (
CREATED_BY,CREATED_AT) that never change after insert - Status fields that change independently (
ACCT_STATUSvs.RANK_LEVEL)
Step 2: Look at your SQL Server wait stats
-- Check current waits (run during your peak load)
SELECT wait_type, wait_time_ms, waiting_tasks_count
FROM sys.dm_os_wait_stats
WHERE wait_type IN ('WRITELOG', 'PAGELATCH_EX', 'PAGELATCH_SH',
'PAGEIOLATCH_EX', 'LOGBUFFER')
ORDER BY wait_time_ms DESC;
If WRITELOG or PAGELATCH_EX are high during your write-heavy windows,
you may be doing unnecessary work.
Step 3: Count your indexes
-- How many nonclustered indexes does your table have?
SELECT t.name AS table_name,
COUNT(*) AS index_count
FROM sys.indexes i
JOIN sys.tables t ON i.object_id = t.object_id
WHERE i.type_desc = 'NONCLUSTERED'
GROUP BY t.name
ORDER BY index_count DESC;
More indexes = more pain from full-row UPDATEs.
Step 4: See what Hibernate is actually sending
Add this to your application.yml temporarily:
spring:
jpa:
show-sql: true
properties:
hibernate:
format_sql: true
Run your update flow and look at the generated SQL. If you see all columns
in the SET clause when you only changed two, @DynamicUpdate will help.
The tradeoffs — when NOT to use it
@DynamicUpdate isn't free. Here's what you're trading:
| Default (static) | @DynamicUpdate | |
|---|---|---|
| SQL generation | Once at startup, cached | Every dirty save() call |
| PreparedStatement caching | One statement per entity, highly cacheable | Many statement variations, lower hit rate |
| CPU cost | Lower (no per-save SQL build) | Slightly higher (dirty-check diff + SQL build) |
| Network payload | Larger (all columns) | Smaller (only changed) |
| DB write I/O | Higher (all columns written) | Lower (only changed columns) |
| Best for | Narrow tables, bulk inserts | Wide tables with partial updates |
Use @DynamicUpdate when:
- Your table has 10+ columns
- Typical updates touch less than half the columns
- You have multiple nonclustered indexes
- You're doing high-throughput writes (batch jobs, event processing)
- You have large columns (JSON blobs, text) that rarely change
Skip it when:
- Your table has 5 columns and every update changes all of them
- You're doing mostly inserts (not updates)
- PreparedStatement plan-cache pressure is already a problem
In our case the math was obvious: 22 columns, 17 indexes, typically 3
columns changing, tens of thousands of writes per minute. The CPU cost of
building SQL at runtime was negligible compared to the I/O savings.
A note on @DynamicInsert
Hibernate also offers @DynamicInsert, which excludes null columns from
INSERT statements. This is useful when your database has column defaults
(like GETDATE() for timestamps) that you want the DB to fill in rather
than Hibernate sending explicit NULLs.
We didn't need it here — our @PrePersist callback sets all required
defaults in Java — but it's worth knowing about.
The broader lesson
The real bug wasn't technical. It was assuming that the ORM was doing
something sensible by default.
Hibernate's static UPDATE is a perfectly reasonable default for most
applications. It reduces startup cost, maximises statement caching, and
simplifies the runtime path. For a CRUD app with 8-column tables and
moderate traffic, you'd never notice the difference.
But defaults don't know about your table shape, your index count, or your
traffic pattern. When those change — when the table grows wider, when you
add more indexes, when traffic scales up — the same default that was fine
last quarter becomes the bottleneck this quarter.
The fix took two lines. Finding it took staring at Azure Monitor dashboards
wondering why WRITELOG waits were spiking during a job that "only updates
a couple of fields."
Check your wide entities. The annotation is right there.
Thanks for reading. If you've run into similar ORM-vs-database surprises,
I'd love to hear about them.
Top comments (0)