One Annotation Cut Our SQL Write Amplification by 90%

M Shahid — Sun, 25 Oct 2026 16:20:26 +0000

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_STATUS vs. 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.

Streamline Your JSON Workflow: A Free Tool & Tips for API Debugging

M Shahid — Mon, 08 Sep 2025 16:40:33 +0000

Hey Dev.to community,

As developers, we’ve all faced the chaos of unformatted JSON—whether it’s debugging an API response, cleaning up config files, or converting data for analysis. I’ve been tinkering with ways to make JSON handling less painful and wanted to share a tool and some tips that have helped me out.

I built a free JSON Formatter tool that formats JSON with clean indentation, validates syntax in real-time with clear error messages, and converts to formats like CSV, XML, or YAML. It’s browser-based, privacy-first (no data leaves your device), and works on any platform. I use it for quick API debugging or prepping data for reports, and it’s been a time-saver.

Here are a few tips for working with JSON:

Validate Early: Always check JSON syntax before sending to an API to avoid 400 errors. Tools like this highlight issues like trailing commas or missing quotes.
Use JSON Schema: For complex APIs, define schemas to enforce data structure.

Chunk Large Files: For huge JSON datasets, process in smaller chunks to avoid browser lag.

What’s your go-to setup for JSON formatting or debugging? Any favorite tools, scripts, or workflows for handling JSON in your projects? I’d love to hear your thoughts, and if you try the tool, let me know what features would make it even better!
Happy coding! 🚀

Tags: #javascript #webdev #json #apitools #developertools

DEV Community: M Shahid