All jobs / Database administrator
Performance interview questions
Performance questions a Database administrator can be asked, from first job to senior. Open one to see the strongest answer and why it works.
In plain words, how does an index make a query faster?
The strongest answer: It is a separate, ordered structure — usually a B-tree — that lets the engine jump to matching rows instead of reading the whole table
Why it works: Exactly — a lookup structure kept beside the data, at the cost of extra writes and space.
- An index is a separate ordered structure.
- It replaces a scan with a seek.
- It costs writes and space.
- It is not a result cache.
A pull request adds eleven indexes to the orders table in one migration.
A developer suggests indexing every column “just in case”. Your response?
The strongest answer: Every index slows inserts and updates and takes space, so we index for the queries we actually run and drop the ones that never get used
Why it works: Exactly — indexes are paid for on every write, so they must earn their place with real queries.
- Index for real queries.
- Every index taxes every write.
- Drop indexes nobody uses.
- “Just in case” is not a design.
A new internal service went live this morning and the connection count graph now saw-tooths with traffic.
An application opens a new database connection for every web request. Concern?
The strongest answer: Yes — connections are expensive to create and each one holds memory on the server; the app should use a pool and the database should have a sane connection limit
Why it works: Exactly — pool on the client, cap on the server, and you avoid the connection storm.
- Pool connections in the app.
- Connections cost server memory.
- Cap the server limit.
- Storms arrive with traffic.
A query is slow. What is the first thing you look at?
The strongest answer: The execution plan — is it scanning where it should seek, are estimated and actual rows far apart, and which operator eats the time
Why it works: Exactly — the plan tells you what the engine chose and why before you change anything.
- Read the plan first.
- Compare estimated vs actual rows.
- Find the expensive operator.
- Do not buy hardware blind.
A developer flags the scan in a code review and asks for an index before merging.
The plan shows a full table scan on a lookup table of about two hundred rows. Problem?
The strongest answer: Almost certainly not — for a tiny table a scan is cheaper than an index seek, so I look at the operators where the actual cost is
Why it works: Exactly — scans are not evil; the planner picks the cheaper path for a table that fits in a few pages.
- Small scans are cheap.
- Cost, not operator name.
- Do not index what is never sought.
- Hints are a last resort.
What are table statistics and why should a DBA care about them?
The strongest answer: They are the optimiser’s picture of row counts and value distribution; if they are stale it estimates badly and picks a poor plan, so I keep them fresh after big changes
Why it works: Exactly — plans are only as good as the estimates behind them.
- Statistics feed the optimiser.
- Stale stats, bad plans.
- Refresh after big changes.
- Every engine relies on them.
Does the column order in a composite index matter?
The strongest answer: Yes — the index is sorted by the leading column first, so queries must filter on that leading column to use it; equality columns go first, ranges last
Why it works: Exactly — leading-column rule, and equality before range.
- Leading column drives usability.
- Equality first, range last.
- Order follows the queries.
- Not alphabetical.
You index a status column that has three possible values and the query is no faster. Why?
The strongest answer: It is not selective — most rows match, so following the index would still touch nearly every row and a scan is cheaper; indexes pay when the lookup eliminates most of the table
Why it works: Right — selectivity, not the existence of an index, decides whether a seek beats a scan.
- Selectivity decides seek or scan.
- Matching most rows is not selective.
- Low cardinality helps rare values.
- Do not hint around the planner.
The checkout page has been timing out for five minutes and the on-call is asking for help.
Dozens of sessions are waiting and the app has slowed to a crawl. How do you find the culprit?
The strongest answer: Query the lock and session views to find the head of the blocking chain, see what that session is doing and who owns it, then decide whether to wait, contact them or kill it
Why it works: Exactly — find the root blocker, understand it, then act with a decision rather than a reflex.
- Find the head of the chain.
- Understand before you kill.
- Waiters are victims.
- Restarts are not lock management.
Both code paths update the `accounts` and `ledger` tables; the errors cluster around payroll runs.
The application logs show deadlocks a few times a day. What do you do?
The strongest answer: Capture the deadlock graph, find the two code paths that lock the same rows in opposite orders, and fix the ordering or shorten the transactions; the app should also retry the victim
Why it works: Exactly — deadlocks are a design symptom; the graph names the culprits.
- Read the deadlock graph.
- Fix lock ordering.
- Shorten transactions.
- Retry is hygiene, not the cure.
The sessions belong to a reporting service that opens a transaction, then waits for a slow external API.
Several sessions sit “idle in transaction” for hours. Why does that matter and what do you do?
The strongest answer: They hold locks and stop clean-up of old row versions, so tables bloat and others block; I find the code path that opens a transaction and then waits, and set an idle-in-transaction timeout
Why it works: Exactly — an open transaction is a lock holder and a vacuum blocker even when it is doing nothing.
- Idle transactions hold locks.
- They block old-version clean-up.
- Find the code path.
- Set an idle-in-transaction timeout.
A data migration loaded three years of history into a table that had held one month.
A query that ran in milliseconds now takes minutes, right after a large data load. First suspect?
The strongest answer: Stale statistics — the optimiser still thinks the table is small and chose a plan that no longer fits; refresh statistics and compare the plans
Why it works: Exactly — a big load is the classic trigger for estimates that no longer match reality.
- Big load, stale stats.
- Compare old and new plans.
- Refresh statistics first.
- Do not guess at hardware.
The order-history page issues one query per line item, and some customers have thousands.
The database shows thousands of identical tiny queries per page load. What is happening and what do you suggest?
The strongest answer: An ORM loads a parent then one query per child — N+1; the fix is eager loading or a join so a page is a few queries, and I show developers the log as proof
Why it works: Exactly — round trips are the cost; collapse them, and bring evidence to the developers.
- Recognise N+1 from the pattern.
- Eager load or join.
- Round trips are the cost.
- Show developers the log.
What is a covering index and when is it worth it?
The strongest answer: An index that holds every column the query needs, so the engine never touches the table; worth it for a hot, narrow query, at the cost of a wider index on every write
Why it works: Exactly — no table lookup, but a wider index to maintain; reserve it for hot paths.
- Index holds all needed columns.
- No table lookup.
- Wider index, more write cost.
- For hot, narrow queries.
Autoscaling added pods during a campaign and the database began refusing new connections.
Hundreds of application pods each keep a pool of twenty connections. The database is out of connections. Fix?
The strongest answer: Put a connection pooler between the app and the database so thousands of client connections multiplex onto a small server-side pool, and shrink the per-pod pools
Why it works: Exactly — a server-side pooler decouples client count from database connections.
- Pooler between app and database.
- Multiplex many clients onto few connections.
- Shrink per-pod pools.
- Raising the limit defers the crash.
The largest tenant’s dashboard times out every morning while every other tenant loads instantly.
The same parameterised query is fast for most customers and terribly slow for one large customer. What is going on?
The strongest answer: The cached plan was built for a typical parameter value and is wrong for the skewed one — parameter sniffing; I can recompile that query, optimise for a representative value, or split the code path
Why it works: Exactly — skew plus plan reuse; fix it at the query, not the whole cache.
- Skewed data, reused plan.
- Compare the two plans.
- Fix the one query.
- Do not flush the world.
The plan cache is full of thousands of near-identical statements that differ only by literal values. Impact and fix?
The strongest answer: Every statement compiles from scratch and evicts useful plans, wasting CPU and memory; the fix is parameterised queries in the application, with the engine’s forced parameterisation as a stopgap
Why it works: Exactly — unparameterised SQL is the root cause, and it usually also means injection risk.
- Literals defeat plan reuse.
- Parameterise in the application.
- Forced parameterisation as a stopgap.
- Also an injection smell.
The job recalculates loyalty points for all active customers in a single statement each night.
A nightly job updating a few million rows locks the entire table and blocks the application. Why, and what do you change?
The strongest answer: Many row locks escalate to a table lock or a long scan holds a range; break the job into batches small enough to stay under the escalation threshold and commit between them
Why it works: Exactly — smaller units of work keep locks granular and let others through.
- Row locks escalate at volume.
- Batch under the threshold.
- Commit between batches.
- Priority is not a fix.
A single `site_stats` row is updated on every page view to keep a live visitor count.
One row holding a global counter is updated by every request and has become the bottleneck. Options?
The strongest answer: Remove the serialisation point: shard the counter across many rows and sum, batch increments in the application, or derive it from an append-only table — and ask whether it must be exact in real time
Why it works: Exactly — a single hot row cannot be tuned around; it must be spread or removed.
- Spread the counter.
- Batch or append instead of update.
- Question real-time exactness.
- A hot row cannot be indexed away.
The query filters on `DATE(created_at) = ?` and takes minutes on a table partitioned by `created_at`.
A table is partitioned by month but queries still scan every partition. Why?
The strongest answer: The predicates do not let the planner prune — the partition column is wrapped in a function, compared to another type, or missing from the WHERE clause; fix the queries to filter it directly
Why it works: Exactly — pruning needs a sargable predicate on the partition key.
- Filter on the partition key directly.
- No functions around the key.
- Match the data type.
- Check the plan for pruning.
The server is busy and nobody can say why. How do you use wait statistics to answer that?
The strongest answer: They say what sessions actually waited on — storage, locks, log flushes, memory grants, CPU — so I compare the top waits with my baseline and follow the biggest back to the queries causing it
Why it works: Exactly — waits name the resource, the baseline gives the number meaning, and the queries are where the fix actually lives.
- Waits name the resource.
- Compare against a baseline.
- Filter the benign waits.
- Follow the wait to the queries.