How to Prepare for a Database Administrator Interview

“A query that ran in two seconds now takes two minutes. How do you diagnose it?”

What they're really asking: Whether you have a diagnostic order, or whether you start adding indexes hopefully.

How to answer: Ask what changed first — data volume, a release, statistics, or the plan itself. Then examine the current execution plan against the expected one, looking for a scan where a seek used to be and for cardinality estimates that diverge sharply from actual rows. From there branch: stale statistics, parameter sniffing producing a plan suited to a different value, or genuine data growth past a tipping point. Say what you would do to mitigate now versus fix properly.

“How do you decide whether to add an index?”

What they're really asking: Whether you weigh write cost and existing coverage, or add one index per slow query.

How to answer: Say you start by checking whether an existing index can be extended rather than duplicated, since near-identical indexes are a common estate problem. Justify the column order from the predicates and the sort, consider whether covering the query avoids a lookup, then weigh the write and storage cost on a table with heavy inserts. Finish by saying you would verify with the plan and actual runtime afterwards, not assume improvement.

“You discover data corruption that has been present for a week. What do you do?”

What they're really asking: Whether you understand that backups age out and that replication copies corruption faithfully.

How to answer: First establish scope and whether the database is still serving — the immediate question is whether to stop writes. Then check whether the corruption predates your oldest clean backup, since that determines whether restore is even available. Explain the options in order of data loss: restore to a clean point and replay, repair affected pages if the engine supports it, or reconstruct from an upstream source. State plainly that a replica will have the same corruption, and that you would involve the data owner in the loss decision rather than making it alone.

“A developer needs to add a column to a table with a hundred million rows. How do you handle it?”

What they're really asking: Whether you are a bottleneck or an enabler, and whether you know your engine's locking behaviour precisely.

How to answer: Start with what the operation actually does in that engine and version — adding a nullable column without a default is often metadata-only, while adding a default or changing a type may rewrite the table and hold a lock. Propose the safe path: metadata-only change, then a batched backfill in small transactions with monitoring for replica lag, then the constraint applied afterwards. Offer to run it during a low-traffic window and to abort cleanly if lag rises.

“How do you know your backups work?”

What they're really asking: Whether you test restores or trust a green tick on a backup job.

How to answer: State clearly that a successful backup job proves nothing and that only a completed restore does. Describe scheduled restores to a separate instance with verification of both integrity and data content, plus timing the restore so your recovery time objective is measured rather than assumed. Mention the parts that catch people out: the log chain being broken by an ad hoc backup, and encryption keys or credentials not being available at restore time.

“How would you approach a database server that is generally slow?”

What they're really asking: Whether you start from evidence about waits, or from a checklist of configuration settings.

How to answer: Begin with what the server is waiting on, using wait statistics to distinguish CPU pressure, IO, locking and memory. That tells you where to look rather than guessing. Then find the highest-cost queries by total resource consumption rather than by individual duration, since a cheap query run constantly often dominates. Address the top offenders through indexing or rewriting, and treat configuration and hardware as the last resort after the workload is understood.