Database Monitoring, Troubleshooting, and Diagnostics Questions

Direct answer

A storm of PAGELATCH_UP waits on tempdb almost never means rows are locked. It means many sessions are fighting over a small set of "traffic control" pages that tempdb uses to hand out space (or, on a different pattern, over the system catalog pages that track temporary-object metadata), and a latch (an in-memory, short-duration protection on a page's physical consistency) is a different animal from a lock (a transactional guarantee on a row or table that can be held for the life of a transaction), and a different animal again from a deadlock (two transactions each waiting on a lock the other one is holding, so neither can proceed until the engine detects the cycle and kills one of them), which is a lock-level phenomenon, not the latch queue described here. Under a parallel build-and-insert workload, this usually happens because many concurrent workers are each creating and dropping temporary tables, table variables, or spill work tables (for sorts, hashes, or cursors) fast enough that the pages coordinating space allocation, or the catalog rows describing those objects, become a single point of serialization. The fix path is: identify which page type is hot with the wait DMVs (dynamic management views, the built-in system views that expose live server state), then apply the mitigation that matches that specific cause, because the tempdb-file-count fix and the metadata fix solve two different problems that both surface as the same wait type.

Structured elaboration

Two distinct root causes behind the same wait type

tempdb is the shared scratch database SQL Server uses for temporary tables, table variables, sort/hash spill space, and version-store data for snapshot isolation. Two unrelated mechanisms inside it can both produce PAGELATCH_UP waits:

PFS tracks free space on roughly every 8,000-page range of a data file; GAM and SGAM each track roughly 4 GB of space per page (GAM records which extents are already allocated, SGAM which mixed extents, extents whose 8 pages are shared across several different small objects rather than dedicated to just one, still have a free page). Because SQL Server's allocation scan always starts at a predictable page, when many sessions allocate at once they queue up on the same PFS/GAM/SGAM page even though they're allocating different, unrelated objects. That is allocation-page contention: real work is blocked on a bookkeeping page, not on each other's data.

Metadata contention is a separate mechanism: every temp table create/drop touches rows in tempdb's own system catalog, and those catalog pages can themselves become a latch hotspot under very high create/drop rates, independent of how much data space is available.

Diagnosing which one you have

First confirm the wait itself and who is waiting:

SELECT r.session_id, r.wait_type, r.wait_time, r.wait_resource,
       t.text AS running_query
FROM sys.dm_exec_requests AS r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS t
WHERE r.wait_type LIKE 'PAGELATCH%';

wait_resource (or resource_description in sys.dm_os_waiting_tasks) is formatted database_id:file_id:page_id, for example 2:1:88 (database_id 2 is always tempdb). To classify the page:

• If page_id is evenly divisible by 8088, it is a PFS page.
• If it is a low, fixed page number that recurs across many different waiting sessions on the same file (classically page 2 or page 3 of a file), it is a GAM or SGAM page.
• If neither pattern holds, or the page belongs to a system object, it is likely metadata contention rather than allocation contention.

To directly test for metadata contention, resolve the page to the object it belongs to:

SELECT OBJECT_NAME(dpi.object_id, dpi.database_id) AS system_table_name,
       COUNT(DISTINCT r.session_id) AS session_count
FROM sys.dm_exec_requests AS r
CROSS APPLY sys.fn_PageResCracker(r.page_resource) AS prc
CROSS APPLY sys.dm_db_page_info(prc.db_id, prc.file_id, prc.page_id, 'LIMITED') AS dpi
WHERE dpi.database_id = 2
  AND dpi.object_id IN (3, 9, 34, 40, 41, 54, 55, 60, 74, 75)
  AND UPPER(r.wait_type) LIKE N'PAGELATCH[_]%'
GROUP BY dpi.object_id, dpi.database_id;

Those ten numbers in the object_id IN (...) list are SQL Server's fixed, built-in object IDs for its own system base tables that back the temp-object catalog (tables like sysschobjs and syscolpars); they're the same on every SQL Server instance, which is why they can be hardcoded here instead of looked up by name. If this query returns rows, sessions are contending on tempdb's own catalog tables (metadata contention). If instead you see the PFS/GAM/SGAM page pattern from the previous step and this query returns nothing, you have allocation-page contention. A key diagnostic tell either way: this is a latch queue, not a blocking lock chain, so it will not show up in a sys.dm_exec_requests blocking-session-id chain the way row-lock blocking does; if you go looking for blocking there and find nothing, that itself is a clue you're looking at the wrong mechanism.

Mitigations, by layer

Worked example

The PFS-page test and the tempdb-file-count recommendation are both concrete, checkable arithmetic. Running this reproduces both:

def is_pfs_page(page_id, interval=8088):
    return page_id % interval == 0

print("905856 %% 8088 =", 905856 % 8088, "-> PFS page:", is_pfs_page(905856))

def recommended_tempdb_files(logical_cpus):
    if logical_cpus <= 8:
        return logical_cpus
    return 8  # start at 8; add in groups of 4 if contention persists

for cpus in [4, 8, 16, 32]:
    print(f"{cpus} logical CPUs -> start with {recommended_tempdb_files(cpus)} tempdb data files")

total_gb, n_files = 32, recommended_tempdb_files(16)
print(f"{total_gb} GB tempdb split across {n_files} equally sized files -> {total_gb/n_files} GB each")

Output (executed):

905856 % 8088 = 0 -> PFS page: True
4 logical CPUs -> start with 4 tempdb data files
8 logical CPUs -> start with 8 tempdb data files
16 logical CPUs -> start with 8 tempdb data files
32 logical CPUs -> start with 8 tempdb data files
32 GB tempdb split across 8 equally sized files -> 4.0 GB each

So on a 16-core box with a single 32 GB tempdb data file, the concrete first move is 8 files of 4 GB each, not one 32 GB file: each file gets its own GAM/SGAM page pair, so the allocator's round-robin scan across files spreads what used to be one hot page into eight independently-scanned ones.

For the DMV query shape itself, here is illustrative (not executed, since this workload runs on SQL Server and this sandbox has no SQL Server instance) output showing what the classification query would return if you were hitting metadata contention on the temp-object catalog:

system_table_name   session_count
------------------  -------------
sysschobjs           14
syscolpars           9

A result like that, with zero hits for the PFS/GAM/SGAM page pattern, tells you memory-optimized tempdb metadata (or reducing create/drop rate) is the right lever, not adding more data files.

Trade-offs & pitfalls

• Do not reach for trace flags 1118/1117 on SQL Server 2016 or later: their behavior is the default already, so setting them is harmless but signals you're diagnosing from stale knowledge, and on genuinely old instances (2014 and earlier) 1118 has a real cost, wasting up to 56 KB per tiny object that never grows past one page.
• Memory-optimized tempdb metadata is not a default-on fix: it requires a restart, and its MEMORYCLERK_XTP memory consumer (SQL Server's internal name for the pool of memory the in-memory OLTP / memory-optimized engine uses) can grow unbounded under heavy load, so bind it to a resource governor pool with an explicit memory cap and only turn it on once the catalog-contention diagnostic query above has actually shown session buildup on system tables. Turning it on speculatively is treating a possible symptom as a confirmed diagnosis.
• Adding tempdb files is not free either: more files past what CPU concurrency and I/O bandwidth justify just adds round-robin overhead without reducing real contention, and unequal file sizes defeat the fix outright because the proportional-fill allocator favors the file with more free space, funneling load right back onto one file.
• The highest-leverage fix is frequently on the application side, not the infrastructure side: cutting how many temp objects a parallel workload creates and drops per second (by batching, reusing tables, or lowering the degree of parallelism) is usually cheaper and more effective than any tempdb reconfiguration, because it removes the contention at its source instead of spreading it thinner.

Direct answer

SQL Server transaction log growth almost always comes down to one of a short list of causes: a long-running or orphaned open transaction holding the log from being reused, a broken backup chain under the FULL recovery model (only a log backup, not a full or differential backup, actually lets the log truncate), replication or Change Data Capture (CDC) holding log records for a consumer that hasn't caught up, or a genuine spike in write volume from a bulk operation. SQL Server exposes the exact reason it currently won't reuse space through a single column, log_reuse_wait_desc on sys.databases; check that first, before running anything else.

Structured elaboration

Glossary

A DMV (Dynamic Management View) is SQL Server's term for a built-in system view exposing internal server or database state, queried like any ordinary view, directly analogous to Postgres's pg_stat_* views. The recovery model (SIMPLE, FULL, or BULK_LOGGED) determines whether the log can truncate automatically after each checkpoint (a periodic point where SQL Server writes all data changes currently held in memory out to the data files on disk, so the log records covering those changes are no longer needed for crash recovery), under SIMPLE recovery, or only after a log backup, under FULL or BULK_LOGGED; this single setting drives most "the log keeps growing" incidents on a database running FULL recovery without a working log-backup job.

Diagnosis

The single most important query:

SELECT name, log_reuse_wait_desc, recovery_model_desc
FROM sys.databases WHERE name = 'yourdb';

log_reuse_wait_desc names the exact reason SQL Server currently cannot reuse log space: common values include LOG_BACKUP (no log backup has run since FULL recovery started retaining log), ACTIVE_TRANSACTION (a long-running or orphaned open transaction), and REPLICATION (a replication consumer hasn't acknowledged), among others. This tells you directly which cause applies, rather than inferring it.

SELECT total_log_size_in_bytes, used_log_space_in_bytes,
       used_log_space_in_percent, log_space_in_bytes_since_last_backup
FROM sys.dm_db_log_space_usage;

This gives current size and, specifically, how much log has accumulated since the last log backup, useful both for confirming severity and for quantifying whether a log backup would actually relieve it; a log_space_in_bytes_since_last_backup close to the total confirms LOG_BACKUP as the practical driver.

DBCC SQLPERF(LOGSPACE); is an older, simpler, server-wide command listing log size and percent used for every database on the instance in one call, useful for a quick sweep of whether the problem is isolated to one database or several.

If log_reuse_wait_desc = 'ACTIVE_TRANSACTION', find the actual open transaction by joining the active-transactions and session-transactions DMVs to the session and request DMVs; this identifies the specific session and how long its transaction has been open. An orphaned, forgotten open transaction, for example an application connection that began one and never committed or rolled back, sometimes from a connection-pool bug, is a very common finding here.

DBCC LOGINFO; shows the individual virtual log file (VLF) segments and their status, active versus reusable, useful when the concern is specifically VLF fragmentation: a log that grew in many small increments accumulates a large number of small VLFs, which itself can slow crash recovery and log-related operations, a separate but related operational concern from raw growth.

Safe steps to reduce log size without risking data loss

If the cause is LOG_BACKUP, the common case for a FULL-recovery database, take a log backup; this is the correct way to let SQL Server reclaim internal log space, and it's data-safe, since a log backup is a normal part of the FULL recovery model's backup chain, not a destructive operation. Then set up or fix a recurring log-backup job so this doesn't recur. If the cause is ACTIVE_TRANSACTION, commit or roll back the identified transaction, coordinating with whoever owns that session or application first where possible; killing a transaction mid-flight is always safe for data integrity (a rollback is transactionally consistent) but may have application-level consequences for whatever was in progress. Only after the underlying cause is resolved and log backups are flowing normally, if the log file is now genuinely oversized relative to steady-state need, shrink it once to reclaim disk space and let it regrow naturally to a size that accommodates normal peak activity; shrinking repeatedly as routine maintenance is an anti-pattern, since it just forces the file to grow again on the next busy period, and growth events themselves briefly pause writes while the OS allocates space.

Never switch the database to SIMPLE recovery as a quick fix for log growth without confirming with whoever owns the backup and recovery strategy first: SIMPLE recovery breaks point-in-time restore and invalidates the existing backup chain, a durability and recoverability decision, not a storage-management knob.

Worked example

SQL Server isn't available to execute directly in this environment; the DMV and column names above were verified against Microsoft's current documentation rather than presented from memory (sys.dm_db_log_space_usage reports database_id, total_log_size_in_bytes, used_log_space_in_bytes, used_log_space_in_percent, and log_space_in_bytes_since_last_backup). The arithmetic that view reports is not a black box; given pinned example values it computes directly:

total_log_size_in_bytes = 8_589_934_592     # 8 GiB
used_log_space_in_bytes = 6_764_573_492
used_log_space_in_percent = round(100 * used_log_space_in_bytes / total_log_size_in_bytes, 2)
print(used_log_space_in_percent)

78.75

used_log_space_in_percent is exactly used_log_space_in_bytes / total_log_size_in_bytes * 100; a log at 78.75% used, on an 8 GiB log, is the kind of reading that should already have triggered an alert well before hitting LOG_BACKUP as the confirmed log_reuse_wait_desc cause and taking the corrective backup.

Trade-offs and pitfalls

• Killing the ACTIVE_TRANSACTION session without checking what it was doing is always safe for the database itself, but can be disruptive for whatever application flow was mid-transaction; understand the blast radius before killing it under pressure.
• Repeated DBCC SHRINKFILE as routine maintenance is the single most common SQL Server log anti-pattern: it treats a capacity symptom with a maintenance operation instead of fixing the actual log_reuse_wait_desc cause, and the resulting grow-shrink cycle causes more VLF fragmentation and more autogrowth pauses than simply leaving the file at a stable, appropriately sized value would have.
• Switching to SIMPLE recovery genuinely stops the symptom, the log truncates automatically after each checkpoint, but it silently gives up point-in-time recovery capability. Treat it exactly like disabling WAL archiving on another engine: a durability decision disguised as a quick fix, requiring the same sign-off a backup-strategy change would.

Direct answer

The monitoring has to answer two separate questions in real time: is the maintenance operation itself progressing (so you know whether to keep waiting or intervene), and is it hurting production traffic right now (so you know whether to intervene at all). Those need different signals: the operation's own progress view for the first, and blocking, latency, and error-rate telemetry from the production workload for the second. The rollback protocol is a pre-agreed decision tree with numeric thresholds decided before the window starts, not a judgment call made under pressure mid-incident, and it should default to a paused, resumable state rather than a full abort wherever the engine supports one, because pausing preserves the work already done. Stakeholder communication is a status channel that updates on a fixed cadence whether or not anything is wrong, because silence during a maintenance window is what actually erodes trust, not the maintenance itself.

Structured elaboration

What to monitor, and why each signal earns its place

The first two rows come from the database engine directly; the last four come from your existing production observability (APM, dashboards, whatever already pages the on-call SRE), reused rather than reinvented for the maintenance window. Wiring the maintenance job's start/stop into your existing alerting as an annotation is what lets you correlate a latency spike with "the rebuild started" instead of chasing it as an unrelated incident.

Rollback protocol

1. Define abort/pause criteria before the window, as specific numbers, written down and agreed with stakeholders, not decided live. Example: pause if p99 latency stays above 2x baseline for 3 minutes, or if any table is blocked more than 30 seconds; abort entirely if error rate crosses a hard SLA-breach threshold.
2. Prefer pause over abort when the engine supports resumable operations. Modern engines let you suspend an online index build without losing progress: SQL Server's resumable online index rebuild (ALTER INDEX ... REBUILD WITH (ONLINE = ON, RESUMABLE = ON), available since SQL Server 2017 for ALTER INDEX and 2019 for CREATE INDEX) tracks state in sys.index_resumable_operations so a PAUSE and later RESUME continues from where it stopped rather than restarting; PostgreSQL's CREATE INDEX CONCURRENTLY and REINDEX CONCURRENTLY can simply be cancelled (Ctrl-C or pg_cancel_backend), leaving an invalid index that is dropped and safely retried later without touching the original index. Full abort should be the fallback when the operation type does not support pausing, or when the trigger is severe enough (hard SLA breach, active incident) that waiting for a pause point is itself too risky.
3. Automate the trigger, keep the decision human. Page the on-call operator when a threshold breaches; do not auto-abort a multi-hour rebuild on a single noisy metric spike, since that can waste hours of progress on a transient blip. Require a human confirmation within a short SLA (e.g. 2 minutes) or auto-pause as the safe default.
4. Verify the rollback actually worked by re-checking the same metrics that triggered it (latency back to baseline, blocking chain cleared) before declaring the incident over, since a paused operation can still hold some locks or reserved space depending on the engine.

Stakeholder communication plan

• Pre-window: a written maintenance notice naming the exact window, expected duration, expected user-visible impact (if any), the specific abort/pause thresholds from the rollback protocol above, and who has authority to invoke a pause or abort.
• During the window: a fixed-cadence status update (for example every 15-30 minutes) on a channel stakeholders already watch, even when the update is just "on track, no action needed." A monitoring dashboard alone is not communication if nobody who needs to know is looking at it.
• On threshold breach: an immediate out-of-band alert (not just waiting for the next cadence update) stating what breached, what action was taken (paused/aborted), and current user impact, followed by an estimated time to resolution or next update.
• Post-window: a short close-out, even on a clean run, confirming completion and any deferred follow-up (e.g. a paused rebuild that still needs to be resumed off-peak), plus a brief retrospective if a pause or abort occurred, since the thresholds themselves are worth revisiting if they fired on something that turned out to be benign, or failed to fire on something that hurt.

Worked example

This reproduces a live progress-monitoring and blocking-detection setup end to end (PostgreSQL 16, run in this session):

CREATE TABLE orders (
  id bigint, customer_id bigint, status text, notes text
);
INSERT INTO orders (id, customer_id, status, notes)
SELECT g, (g % 500000),
       (ARRAY['pending','shipped','delivered','cancelled'])[1 + (g % 4)],
       repeat('x', 200)
FROM generate_series(1, 6000000) AS g;
-- Started in a background session:
CREATE INDEX CONCURRENTLY idx_orders_status ON orders (status, customer_id);

-- Progress monitoring query, polled every few seconds:
SELECT p.pid, p.phase, p.blocks_total, p.blocks_done,
       round(100.0*p.blocks_done/nullif(p.blocks_total,0),1) AS pct_done,
       p.tuples_total, p.tuples_done
FROM pg_stat_progress_create_index p;

Actual captured output while the build was running (two samples, seconds apart):

 pid | phase                           | blocks_total | blocks_done | pct_done
  91 | building index: scanning table  |       193549 |       95406 |     49.3

 pid | phase                                   | tuples_total | tuples_done
  91 | building index: loading tuples in tree  |      6000000 |     2580216

This is exactly the "is it progressing" signal from the table above: two distinct phases (scanning, then loading into the tree structure), with a real completion percentage you can alert on if it stalls.

Separately, this is a real captured blocking-chain detection, run while one session held a row lock open and a second session queued behind it:

SELECT blocked.pid AS blocked_pid, blocked.query AS blocked_query,
       blocking.pid AS blocking_pid, blocking.query AS blocking_query,
       now() - blocked.query_start AS blocked_duration
FROM pg_stat_activity AS blocked
JOIN pg_locks AS bl ON bl.pid = blocked.pid AND NOT bl.granted
JOIN pg_locks AS kl ON kl.locktype = bl.locktype
   AND kl.database IS NOT DISTINCT FROM bl.database
   AND kl.relation IS NOT DISTINCT FROM bl.relation
   AND kl.page IS NOT DISTINCT FROM bl.page
   AND kl.tuple IS NOT DISTINCT FROM bl.tuple
   AND kl.pid != bl.pid AND kl.granted
JOIN pg_stat_activity AS blocking ON blocking.pid = kl.pid
WHERE blocked.pid != blocking.pid;

Captured output:

 blocked_pid | blocked_query                                      | blocking_pid | blocking_query                                                                 | blocked_duration
         148 | UPDATE orders SET status='rejected' WHERE id = 1; |          141 | BEGIN; UPDATE orders SET status='reviewing' WHERE id = 1; SELECT pg_sleep(6); | 00:00:01.07

This is the alerting signal that fires the rollback protocol: a query showing a real, non-zero blocked_duration tied to a specific blocking session is what you'd threshold on (for example, page if blocked_duration exceeds 10 seconds for any session touching the maintenance target table), rather than inferring impact indirectly from an aggregate latency graph alone.

Trade-offs & pitfalls

• Polling progress and blocking views too frequently adds its own overhead and log noise; every few seconds is generally cheap, sub-second polling is not, since it competes for the same catalog/lock manager access the maintenance job needs.
• Alerting only on aggregate p99 latency can miss a real problem: a blocking chain on one heavily-used table can hurt a specific feature badly while barely moving the instance-wide percentile. Pair the aggregate metric with the targeted blocking-chain query above, scoped to the tables actually under maintenance.
• A rollback trigger based on an instantaneous reading is noisy; require the breach to hold for a sustained window (the "3 consecutive minutes" pattern above) so you don't abort hours of progress over one transient spike, but don't set the window so long that real damage accumulates before anyone acts.
• Treating the maintenance window as "safe because it's scheduled" is the most common mistake: scheduling controls when the risk happens, not whether it happens, so the monitoring and rollback plan need to be as rigorous as they would be for an unplanned change.
• Silent success is still a communication failure: stakeholders who don't hear anything during a multi-hour window reasonably start to worry or escalate on their own, so the fixed-cadence "still on track" update matters even when, especially when, nothing is going wrong.

Direct answer

Each slow query log entry in MySQL carries a header with four numbers that matter for prioritization: how long the statement took to execute, how long it spent waiting to acquire locks, how many rows it sent back to the client, and how many rows it had to examine internally to produce that result. The gap between rows examined and rows sent is usually the single most useful number in the entry, since a query that examines a million rows to return ten is telling you almost exactly where the missing index is.

Structured elaboration

Reading the entry. A typical header looks like:

# Query_time: 0.500000  Lock_time: 0.000100  Rows_sent: 42  Rows_examined: 10000
SET timestamp=1234567890;
SELECT * FROM users WHERE status='active';

• Query_time: total wall-clock time the statement took, in seconds (fractional, down to microsecond precision on modern MySQL). This is what actually determines whether the query crossed the logging threshold.
• Lock_time: how much of that total time was spent waiting to acquire a lock, as opposed to doing real work. A query with Lock_time close to Query_time is a contention problem (something else is holding a lock this query needs), not a query-design problem; a query with Lock_time near zero and all its time elsewhere is the opposite.
• Rows_sent: how many rows actually went back to the client.
• Rows_examined: how many rows the storage engine had to read through internally to produce that result, before any WHERE filtering or LIMIT cut it down. This is the number that most directly indicates whether an index is missing or not being used: a huge gap between Rows_examined and Rows_sent, as in the example above (10,000 examined to send 42), usually means the storage engine is scanning far more of the table than it needs to, either a missing index on the filter column or an existing index the planner isn't using.

Prioritizing tuning work from the log. I don't prioritize by Query_time alone, because a query that's slow purely due to Lock_time needs a completely different fix (resolve the contention, don't touch the query) than one that's slow due to a large Rows_examined-to-Rows_sent gap (add or fix an index). I'd bucket entries into those two categories first, then within the Rows_examined-heavy bucket, prioritize by how frequently that same query shape recurs in the log (a query that's individually only moderately slow but appears constantly is often worth more effort than a rare, one-off slow query).

Enabling slow query logging safely in production. Set the threshold and destination deliberately rather than accepting defaults:

-- how many seconds a query must take to be logged; default is 10s, often too loose to catch real problems
SET GLOBAL long_query_time = 2;
-- write to a file, not the mysql.slow_log table; writing to a table under load risks its own contention and open-file-descriptor pressure
SET GLOBAL log_output = 'FILE';
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow-query.log';
SET GLOBAL slow_query_log = 1;
-- ignore queries that only touch a handful of rows even if they're slow for other reasons (lock waits mostly), keeps the log focused on genuine scan problems
SET GLOBAL min_examined_row_limit = 100;

Persist these with SET PERSIST (MySQL 8.0+) or in the server's configuration file so they survive a restart rather than silently reverting.

Worked example

Given the entry above (Query_time: 0.5, Lock_time: 0.0001, Rows_sent: 42, Rows_examined: 10000) for SELECT * FROM users WHERE status='active': Lock_time is negligible relative to Query_time, so this isn't a contention problem, it's a scan problem. 10,000 rows examined to return 42 is a 238-to-1 ratio, strongly suggesting status has no usable index, or the table is small enough that the optimizer decided a full scan was cheaper than an index lookup anyway (worth confirming with EXPLAIN (the command that shows the execution plan the database chose, i.e. which index or scan strategy it actually used) before assuming an index is missing, since sometimes it exists but isn't selective enough to be worth using). If this same query shape shows up dozens of times across a day's log with a similar ratio each time, that's a strong, low-risk case for adding an index on status: CREATE INDEX idx_users_status ON users (status);, built with ALGORITHM=INPLACE, LOCK=NONE where the storage engine supports it, to avoid locking the table during the build on a production system.

Correlating with application traces. Log entries carry a timestamp and the raw SQL text, but not, by default, any application-side request ID or trace context; the most reliable way to correlate a slow log entry with the specific application code path that issued it is to have the application inject an identifying SQL comment into the query itself (many object-relational mapping libraries and query builders support this directly, or it can be added manually), since that comment survives into the slow log verbatim and gives you a direct link back to the originating request or endpoint, rather than trying to line up timestamps after the fact.

Trade-offs and pitfalls

Enabling the slow query log is not free on a busy production server: writing each entry is an additional disk write per slow query, and under log_output = 'TABLE' specifically, each entry opens the internal mysql.slow_log table, which under high slow-query volume can itself contribute to file-descriptor pressure, one of the reasons FILE output is the safer production default. log_queries_not_using_indexes looks tempting (it logs every query that doesn't use an index, regardless of long_query_time) but is genuinely dangerous to turn on without log_throttle_queries_not_using_indexes set alongside it, since on a large, poorly-indexed table it can flood the log fast enough to itself become a performance and disk-space problem. MySQL writes each entry to the log only after the statement finishes executing and all its locks are released, so log order can differ from actual execution order; don't use log timestamps alone to reconstruct the precise sequence of events during an incident.

Direct answer

I work outward from the database, since that's where the error surfaced, but the actual cause is just as likely to live in the application, the connection pooler (a lightweight proxy that sits between the application and the database and multiplexes many client connections onto a smaller, reused set of actual database connections instead of opening a fresh one per client), or the network in between. I check each layer for what it would look like if it were the culprit, ruling layers out with direct evidence rather than guessing, and I specifically avoid assuming "authentication failed" means a wrong password, since a handful of other things produce the identical error text.

Structured elaboration

Layer 1: the database itself. Read the full log entry, not just the summary line; PostgreSQL's DETAIL line often names exactly which authentication rule matched. I reproduced this directly: attempting a connection with a wrong password against a real PostgreSQL 16 instance produced

FATAL:  password authentication failed for user "postgres"
DETAIL:  Connection matched file "/var/lib/postgresql/data/pg_hba.conf" line 128: "host all all all scram-sha-256"

That DETAIL line is doing real diagnostic work: it confirms the connection reached the database, matched a specific client-authentication rule (pg_hba.conf line 128, requiring scram-sha-256 password authentication for this combination of database, user, and source), and failed specifically at the password-verification step, not earlier. That single line rules out several other possible causes at once: it's not a network reachability problem (the connection reached the server and matched a rule), it's not a missing pg_hba.conf entry (a rule did match), and it's not an authentication-method mismatch (the client attempted the method the rule required). What's left, given that DETAIL line alone, is either a genuinely wrong password or a wrong username presented as if it were a password problem (MySQL and PostgreSQL both report certain wrong-username cases through the same generic "authentication failed" message rather than a distinct "no such user" error, partly as a deliberate security choice to avoid confirming which usernames are valid to an attacker).

Layer 2: the connection pooler, if one sits between the application and the database (PgBouncer for PostgreSQL, ProxySQL for MySQL, or a managed service's built-in pooler). A pooler can produce the exact same "authentication failed" text for a cause that has nothing to do with the actual database credentials: its own separate copy of user credentials (poolers commonly maintain their own auth file, independent of the database's) going stale after a password rotation, while the database's own credentials are perfectly correct. Check the pooler's own logs separately from the database's; if the pooler logs a clean, successful connection to the database but the client sees an authentication failure against the pooler, the pooler's own credential store is the layer at fault, not the database.

Layer 3: the network. A firewall or security-group rule change, a database migrated to a new host without every client's allowlist being updated, or a source IP address that changed (a redeployed application instance getting a new address, a NAT gateway change) can all cause connections to arrive from an address the authentication rules don't expect, which some database configurations report as an authentication failure rather than a clearly separate "connection refused" or "no route" error, especially when the pg_hba.conf-style rule is matched by source address as part of the same rule that governs the auth method. Check what source address the failing connections are actually arriving from, in the log's DETAIL or equivalent, and compare it against what the connecting application believes its own address is.

Layer 4: the application. Check for a credential that's stale in exactly one place: a secret manager or environment variable that was rotated everywhere except one deployment target, a connection string cached in application memory from before a rotation and never refreshed without a restart, or, for a scheduled batch or extract-transform-load (ETL) job specifically, credentials baked into a config file that's on a different rotation schedule than the interactive application's credentials, so it silently breaks weeks after a password rotation that the interactive path already absorbed.

Narrowing down which layer is actually at fault. The fastest discriminator is trying the exact same credentials directly against the database, bypassing the application and the pooler entirely, from a machine you know can reach the database network-wise (a database client run from a bastion host or the database's own host). If that direct connection succeeds, the problem is in the application or the pooler, not the database's own credential store or pg_hba.conf rules, since you just proved those are correctly configured for this exact username and password. If the direct connection also fails with the same error, the problem is at the database or network layer, and the DETAIL line from that direct attempt tells you which.

Worked example

Errors are showing up for a scheduled nightly batch job specifically, not for the interactive application, which is itself the first useful clue: whatever changed, it changed for this one job's credential path and not the others, which already argues against a database-wide cause (a pg_hba.conf change affecting one rule for all users would hit everyone, not just this one job) and toward something scoped narrowly to this job. Direct connection test with the batch job's configured credentials, run manually from the batch host: succeeds. That single result rules out the database and the network entirely (a direct connection with the same credentials from the same host just worked), narrowing this to the application layer specifically: the batch job's own config, most likely a credential that was rotated in the secret manager but never refreshed in whatever cached copy the batch job reads at startup, since the job likely only reads its credentials once, at startup, and a password rotated after that keeps failing until the job process is restarted.

Trade-offs and pitfalls

The single biggest trap in this kind of triage is assuming the error text describes the actual cause precisely; "authentication failed" is, by design in most databases, deliberately vague about whether the username, the password, or the source address was the specific problem, because a more specific error would leak information useful to an attacker probing for valid usernames. Treat the error text as a starting point, not a diagnosis, and use the DETAIL line and direct reproduction, as above, to actually narrow it down. Jumping straight to "someone changed the password" without checking the pooler and network layers first wastes time on the wrong fix if the real cause is a stale pooler credential cache or a firewall rule, both of which look identical from the client's point of view.