Game Lag White Paper › L12 Database

Query slowdown from a query plan change Query plan regression (stats, parameter sniffing)

Cause ID db-plan-flip · Primary owner Infra team (DB infrastructure) · Also Game team (Server development)

Open the interactive card with figures and simulations →

Even with the code unchanged, if the DB changes how it executes a query (its query plan), a query that took 2 ms yesterday takes hundreds of ms today.

Why Automatic statistics updates, a DB restart, or shifts in data distribution make the DB build a new query plan → Effect A plan that skips the index gets picked, the same query becomes tens to hundreds of times slower, and connections get tied up → On screen With no deploy at all, loading for a specific feature suddenly slows down and other requests wait too

Symptoms

Input lag, Can’t connect / infinite loading

Factors

Latency, Stall

Who’s affected

One feature only, Whole server

When

Randomly

Owner

Primary owner Infra team (DB infrastructure) · Also Game team (Server development)

Game team action items

For queries whose result count varies widely by parameter value, split them or consider plan hints, design queries that reliably use an index.

Infra team action items

Watch slow queries and query plan history, pin good plans (Query Store in SQL Server, and so on), manage when statistics get updated.

On the graph

Step change · Average execution time per query

Where to look

Collect the average time per normalized query periodically and watch the trend. MySQL: AVG_TIMER_WAIT in events_statements_summary_by_digest. PostgreSQL: mean_exec_time in pg_stat_statements (mean_time on 12 and earlier). Compare query plans from before and after the slowdown with EXPLAIN or PostgreSQL auto_explain, and in SQL Server with the Regressed Queries view in Query Store

Confirmed if

With no deploy at the time, one query’s average time steps up tens of times, the moment lines up with a statistics update or a DB restart, and the query plan has changed

Ruled out if

Slower with the query plan unchanged: data growth, lock waits (db-hot-row), or the disk

Check with

Infra tools (no game code needed)

Learn more

SQL Server reuses a plan built for the first value it sees (parameter sniffing). A plan built for a new character with only a few items gets very slow when used for an old character with tens of thousands of items, and the reverse is common too. It may recover when a restart clears the plan, then go bad again.

Sources

1. Query Processing Architecture Guide Microsoft SQL Server
   Parameter sniffing: the query plan is built for the parameter values passed at compile or recompile time
2. Parameter Sensitive Plan Optimization Microsoft SQL Server
   When data distribution is uneven, one cached plan doesn’t fit every parameter value
3. Monitor performance by using the Query Store Microsoft SQL Server
   Plans change with statistics, schema, and index changes, and the plan cache keeps only the latest plan; Query Store’s plan forcing pins a good plan; the Regressed Queries view compares slowed-down queries and their plans
4. Statement Summary Tables MySQL
   events_statements_summary_by_digest: COUNT_STAR and AVG_TIMER_WAIT (average time) per normalized query
5. pg_stat_statements — track statistics of SQL planning and execution PostgreSQL
   calls, total_exec_time, and mean_exec_time (average execution time) per statement
6. pg_stat_statements (PostgreSQL 12 Documentation) PostgreSQL
   Up to 12, the columns are named total_time and mean_time
7. auto_explain — log execution plans of slow queries PostgreSQL
   Logs the query plans of queries that took longer than auto_explain.log_min_duration

See also

Same layer: L12 Database

• Queries with no index
• Hot row lock contention
• DB deadlock
• Connection pool exhaustion
• Replication lag
• Checkpoint / log flush
• Cold cache (right after a restart)
• Login storm and N+1 queries
• Bulk batch jobs
• DB failover
• Lost progress from a long save interval
• Cache stampede
• Transaction left open too long
• Slow Redis commands
• Schema change (DDL) lock during live service

Same symptom (Input lag), other layers

• Rendering load from large crowds L1 Client game process
• Packet processing bottleneck on the main thread L1 Client game process
• V-Sync and the render queue L1 Client game process
• Power saving and thermal throttling L2 Client OS and device
• Other apps on the same device using up bandwidth L2 Client OS and device
• Display, input device, and frame generation latency L2 Client OS and device
• Congested Wi-Fi channel L3 Home network
• Bufferbloat (router queue) L3 Home network

View the interactive card with figures and simulations