Incident Library

IL-0441 ZOZO · 2021-03-04 (published)database

A ZOZO SQL Server database stalled and applications hit timeout storms whenever batch executions peaked in sales

ZOZO investigated the failure with three sources: SQL Server metrics from Performance Monitor, the blocked_process_report extended event, and DMV (dynamic management view) data saved to a table every minute. The metrics only showed that queries piled up for some reason until the worker threads ran out and timeouts followed. Possible reasons considered were SOS_SCHEDULER_YIELD waits from high CPU, non-page latch contention, and long locks on a busy area causing blocking. The latch data suggested that ACCESS_METHODS_HOBT_VIRTUAL_ROOT waits might come from many last-page inserts into one table, and that ACCESS_METHODS_ACCESSOR_CACHE waits rise with compilations. ZOZO did not find a definite root cause.

Instead, ZOZO looked for things observed during the failure that could be removed or reduced. When the failure happened again on another day, counting blocked_process_report events by waitresource showed blocking caused by waits for the compilation of specific stored procedures, so ZOZO fixed the plan guides of those four stored procedures. Compilations and recompilations were also reduced: the sql_statement_recompile extended event showed that the recompile_cause of most recompilations was "Statistics changed". ZOZO listed the tables whose statistics were updated during the failure window, disabled automatic statistics updates on them only for periods of expected high traffic with `UPDATE STATISTICS tablename WITH NORECOMPUTE`, and enabled them again with `UPDATE STATISTICS tablename` once traffic calmed down. Because plan cache evictions are controlled by SQL Server's memory management, ZOZO compressed the large indexes held in the data cache as a best-effort step.

After these measures the failure stopped. With the same rise in batch executions, queries no longer piled up, connections stayed low, errors disappeared, peak CPU fell from close to 100% to about 60%, compilations and recompilations dropped clearly, and no lock contention was seen even at the highest batch rate. ZOZO's hypothesis for the drop in compilations is that compression shrank the data cache, which stabilised the plan cache.

Severity
–
Scope
Applications using one SQL Server database got many timeout errors during traffic peaks
Users affected
–
Region
–
Time to detect
–
Time to resolve
–

Symptoms

From around mid-2020, a SQL Server database behind one ZOZO feature made the applications throw many timeout errors whenever batch executions per second passed a certain threshold.

Traffic rises at events such as sales and lucky-bag (fukubukuro) launches. A batch in SQL Server is a group of one or more queries. At the moment the errors began, batch executions per second dropped sharply while total batch execution time rose steeply, so each query was taking much longer.

The Performance Monitor metrics for SQL Server showed CPU usage close to 100% at the peak, driven mainly by batches using 20 to 100 msec of CPU in Batch Resp Statistics. They also showed blocking, errors on the SQL Server side, a spike of non-page latch waits early in the failure, compilations rising with batch executions, more queries granted workspace memory, and many waits to obtain a worker thread, which suggested the worker threads had run out.

Looking at a shorter time window around the start of the errors, CPU usage fell when the Attention events appeared, so CPU was unlikely to be the cause. The first changes were Page Latch, then Non-Page Latch, then blocking. In the blocked_process_report extended event, the first blocking event was recorded about 15 seconds after the failure began, because the event is only written after blocking passes a threshold; the first blocker was a background process that was itself waiting on a latch of type ACCESS_METHODS_HOBT_VIRTUAL_ROOT, and what it was doing could not be found. The DMV data showed no CPU-heavy query worth tuning; the largest total latch waits were ACCESS_METHODS_ACCESSOR_CACHE, then BUFFER, then ACCESS_METHODS_HOBT_VIRTUAL_ROOT, and apart from the secondary THREADPOOL wait most waiting time was non-page latches, page latches and locks.

Symptom Intermittent timeoutsDatabase overloadLock contention or deadlock

Root cause

Root cause unknown

Component Database
Contributing factor –
Trigger
Traffic increases during sales and lucky-bag launches pushed batch executions per second on the database above a threshold.

Detection

–

Remediation

Action items

–

Lessons

When the root cause of a failure cannot be found, choosing among the things observed during the failure those that can be removed or reduced, and acting on them, can still create a state in which the failure no longer happens.

Sources

Related cases

Check your cluster for this

kubegap audits a Kubernetes cluster for conditions like the ones in this case, Database among them, read-only, from Claude Code or Codex. Its checks cite public reports like this one.

Open kubegap

For AI tools and scripts

The API needs no key. The MCP server offers search_incidents, get_incident, list_incidents, get_topic and vocab; both are explained on API and MCP.