During a bulk-load job, log_write_percent stays near 100 percent for long periods while CPU stays around 30 percent. The loader commits each row individually. The team wants the load to finish faster without changing the data. Which change is most effective?
Show answer and explanation
Answer: B
log_write_percent measures how close the database is to its transaction log write throughput limit. Committing each row separately forces a log flush for every row, which saturates log writes even while CPU is idle. Batching rows into fewer, larger transactions reduces the number of flushes, and moving to a service objective with more log throughput raises the ceiling. Together these address the actual bottleneck. Adding indexes adds more log writes, and disabling Query Store does not materially free log capacity.
Why the other options are wrong
- A. Each extra nonclustered index must be maintained on every insert, which adds log records and makes the bottleneck worse. Indexes speed up reads and do not help a write-bound load.
- C. Committing every row maximizes the number of log flushes, which is the very behavior causing the saturation. Durability is already guaranteed at commit, so committing sooner does not help the load.
- D. Query Store collects telemetry in its own tables and does not consume the transaction log throughput that the load is saturating. Disabling it would not meaningfully speed up the inserts.