Database Performance Tuning Flashcards

Question 1

What does it mean to tune a database for performance?

Accepted Answer

Tuning a database means changing its queries, schema, indexes, or configuration so that work completes faster or uses fewer resources. The goal is measurable improvement for a real workload, not changes made by guesswork. Good tuning starts by finding which operations are slow before any change is made.

Question 2

What is query cost in a database optimizer?

Accepted Answer

Query cost is the optimizer's estimate of the resources a candidate execution plan will use, expressed in a unit rather than seconds. Optimizers compare these estimates across alternative plans and choose the one with the lowest predicted cost. Estimates depend on row counts and statistics, so they may be inaccurate after data changes.

Question 3

Why do full table scans become expensive as tables grow?

Accepted Answer

A full table scan reads every row to find matching data, so its work grows in proportion to table size rather than to the number of rows returned. Even a selective query may read far more pages than it needs. Indexes and filters that limit the rows examined are the usual remedy.

Question 4

What is selectivity and why does it influence index use?

Accepted Answer

Selectivity describes the fraction of rows that a condition matches, with highly selective conditions matching few rows. An index usually helps when the predicate is selective, since reading few matching rows through it costs less than a full scan. When a condition matches a large share of rows, a sequential scan is often faster.

Question 5

Why do database statistics matter for choosing a plan?

Accepted Answer

Statistics are summaries the optimizer keeps about data, including value distributions and distinct value counts. The optimizer uses them to estimate how many rows each step will produce. Stale or missing statistics lead to poor estimates, which can push the optimizer toward a join method or scan type that performs badly.

Question 6

What is the difference between a cardinality estimate and actual rows?

Accepted Answer

A cardinality estimate is the optimizer's prediction of how many rows an operation will return, while actual rows is the count observed when the query runs. Comparing the two in an execution plan reveals where the optimizer misjudged the data. Large gaps often explain why a chosen plan performs poorly.

Question 7

How does a query optimizer choose between join methods?

Accepted Answer

The optimizer weighs join methods such as nested loops and hash joins by estimating each method's cost from input sizes, indexes, and sort order. Nested loops suit small or well indexed inner inputs, while hash joins often suit large unsorted inputs. The cheapest estimate wins, so accurate row counts are essential.

Question 8

Why can a small change in a predicate cause a plan change?

Accepted Answer

A predicate change can move the estimated selectivity across a threshold where the optimizer switches from an index seek to a table scan, or from one join method to another. Plans are chosen from estimates, so crossing that boundary can change the whole execution strategy. Similar looking values can therefore perform very differently.

Question 9

What is a database index and what does it store?

Accepted Answer

A database index is a separate structure that maps column values to the locations of matching rows, usually organized as a balanced tree or hash table. It lets the engine find rows without scanning the whole table. The index must be updated whenever indexed columns change, which adds cost to writes.

Question 10

What is a composite index and why does column order matter?

Accepted Answer

A composite index covers two or more columns, sorted by the first column and then by the next within each value. It serves queries filtering on a leading column prefix, but usually not on a later column alone. Usually place equality filter columns first, considering other query patterns.

Question 11

What is a covering index and how does it reduce work?

Accepted Answer

A covering index contains every column a query needs, so the engine can answer the query from the index alone. This avoids the extra lookup into the base table for each matching row. Covering indexes help most for frequent queries that read a few columns from many rows.

Question 12

Why can too many indexes slow a database down?

Accepted Answer

Every index must be maintained during inserts, updates, and deletes, so each write does extra work for every index on the affected table. Extra indexes also consume storage and memory, and they add pressure to the buffer cache. Unused indexes give no benefit while still adding write cost.

Question 13

What is an index seek compared with an index scan?

Accepted Answer

An index seek navigates directly to rows matching a specific predicate, touching only a small part of the index. An index scan reads a large or entire portion of the index from start to finish. Seeks generally suit selective lookups, while scans suit queries that need many rows or a broad range.

Question 14

How does a clustered index differ from a nonclustered index?

Accepted Answer

A clustered index determines the physical order in which table rows are stored, so a table can have only one. A nonclustered index is a separate structure that holds key values and pointers back to the rows, so a table can have many. The behavior varies among database systems, so check the documentation.

Question 15

Why does a partial or filtered index help some workloads?

Accepted Answer

A partial index includes only rows that satisfy a condition, so it is smaller and cheaper to maintain than an index over the whole table. It suits queries that repeatedly filter on one common condition. The query must include a predicate that matches the index condition for the optimizer to use it.

Question 16

What is index fragmentation and how can it affect reads?

Accepted Answer

Index fragmentation occurs when index pages become partly empty or out of logical order after many inserts, updates, and deletes. Fragmented indexes can cause more page reads and less efficient range scans because related data is spread across more pages. Maintenance such as rebuilding or reorganizing indexes can restore compact, ordered pages.

Question 17

What is an execution plan and what information does it show?

Accepted Answer

An execution plan is the strategy the database chooses to run a statement, shown as a tree of operators such as scans, seeks, joins, sorts, and aggregates. It typically reports estimated row counts, costs, and the order in which operations run. Plans can also include actual runtime figures when collected from an executed query.

Question 18

How should you read a plan tree when tracing data flow?

Accepted Answer

Read the plan from the innermost operators toward the root, since rows flow upward from data access to final output. Each operator consumes rows from its children and passes results to its parent. Compare estimated and actual row counts at each step to find where work accumulates.

Question 19

What does a table scan operator indicate in a plan?

Accepted Answer

A table scan operator indicates that the engine reads the entire table to evaluate a condition or gather rows. A scan can be reasonable for small tables or queries returning most rows. It becomes a concern when a scan reads many rows but returns only a few.

Question 20

Why is a sort operator costly when memory is insufficient?

Accepted Answer

When memory is insufficient, a sort must spill intermediate data to disk, which adds slow reads and writes. Sorts appear for ORDER BY, some joins, and grouping operations. Check the plan for sort spills or large sort inputs, and consider whether an index can supply rows already in order.

Question 21

What is a key lookup and why can it be expensive?

Accepted Answer

A key lookup fetches the remaining columns of a row from the base table after a nonclustered index has located matching rows. Each lookup is a separate random access, so it can be expensive when many rows match. Making the index covering or reducing returned rows can remove lookups.

Question 22

How can you tell whether a plan's estimates are reliable?

Accepted Answer

Compare estimated rows with actual rows at each operator, since large consistent differences signal unreliable estimates. Also check how old the statistics are and whether the query uses complex predicates, correlated columns, or parameter values the optimizer cannot predict well. Rerunning the plan after updating statistics can show whether estimates improve.

Question 23

What is a plan regression and how might it occur?

Accepted Answer

A plan regression is a case where a query that was fast starts running slowly because the optimizer now selects a different plan. Common causes include updated statistics, schema changes, new index additions, upgrades to the database engine, and parameter values that differ from those used when the plan was first cached.

Question 24

Why should you compare plans before and after a change?

Accepted Answer

Comparing plans before and after a change shows whether the intended effect actually happened, such as a scan becoming a seek. It also reveals side effects, like a different join order or a new sort that may hurt other queries. Keeping saved plans gives a baseline for judging future changes objectively.

Question 25

Why should queries request only needed columns instead of every column?

Accepted Answer

Requesting only needed columns reduces the data read from storage, the memory used for intermediate results, and the network traffic sent to the client. Wide rows cost more to move, sort, and cache. It can also make covering indexes possible, since fewer columns must be included.

Question 26

What does sargable mean for a query predicate?

Accepted Answer

A predicate is sargable when the database can use an index to evaluate it directly, usually because the indexed column stands alone in a comparison. Wrapping the column in a function or arithmetic usually prevents this, so amount * 2 = 100 is harder to optimize than amount = 50.

Question 27

How do joins on mismatched data types affect performance?

Accepted Answer

When join columns have different data types, the database must convert values, which can prevent index use and force a less efficient join method. Matching types on both sides of a join keeps comparisons direct and lets the optimizer consider indexes. Implicit conversions are easy to miss, so verify types in the schema and the plan.

Question 28

What is a correlated subquery and why can it be costly?

Accepted Answer

A correlated subquery references values from the outer query and is logically evaluated once for each outer row. When the outer row count is large and the inner lookup is not indexed, the total work can grow sharply. Rewriting it as a join often lets the optimizer process rows in bulk.

Question 29

Why do normalization matter, and when might denormalization help?

Accepted Answer

Normalization reduces redundant data so each fact is stored in one place, which protects integrity and keeps updates simple. Denormalization deliberately stores some redundant data to reduce joins for read heavy workloads, at the cost of more complex writes and consistency checks. Choose it based on measured read patterns, not assumptions.

Question 30

Why is pagination with large offsets slow, and what helps?

Accepted Answer

A large OFFSET forces the database to read and discard all skipped rows before returning the requested page, so deeper pages cost more. Keyset pagination instead remembers the last key seen and filters for rows after it, using an index on that key. This keeps the cost of each page roughly constant regardless of depth.

Question 31

What does a transaction's scope have to do with performance?

Accepted Answer

A transaction that stays open for a long time holds locks and resources longer, which can block other sessions and increase contention. Keeping transactions short, committing as soon as the logical unit of work finishes, and avoiding user input or network calls inside them reduces this effect.

Question 32

How can prepared statements improve query performance in applications?

Accepted Answer

Prepared statements let the database parse and plan a query once, then reuse that plan with different parameter values, which saves repeated parsing work. They also reduce the risk of SQL injection because values are sent separately from the statement text. Be aware that a single cached plan may suit some parameter values poorly.

Question 33

What is lock contention and how does it slow applications?

Accepted Answer

Lock contention occurs when one transaction must wait because another transaction holds a lock on the same data it needs. Waiting sessions add latency, and heavy contention can create queues that make a whole application appear slow. Reducing transaction length and accessing rows in a consistent order helps limit it.

Question 34

What is a deadlock and how do databases usually resolve it?

Accepted Answer

A deadlock is a cycle in which two or more transactions each hold a lock the other needs, so none can proceed. Database engines usually detect the cycle and roll back one transaction as the victim, letting the others finish. Applications should retry rolled back work and lock rows in a consistent order.

Question 35

Why do long running queries hurt concurrent workloads?

Accepted Answer

Long running queries can hold locks, pin memory, and consume a large share of CPU or input and output, leaving less capacity for other sessions. They may also block writers or delay maintenance tasks. Separating reporting workloads, adding targeted indexes, or running heavy jobs in off peak windows can reduce the conflict.

Question 36

What should a slow query log or monitoring tool capture?

Accepted Answer

A useful slow query log records the statement text or a normalized form, execution time, rows examined and returned, and the time the query ran. Capturing the parameters, user, and application source helps link problems to their origin. Enable logging with a threshold that catches meaningful slowness without flooding storage.

Question 37

What should a good performance baseline contain for a database?

Accepted Answer

A baseline records typical response times, throughput, resource use, and key query plans under a representative workload, captured over a normal period. With a baseline, you can tell whether a change improved performance or whether normal variation explains the difference. Store the measurements with notes about hardware, data volume, and configuration so comparisons remain meaningful.

Question 38

What does a connection pool do for database performance?

Accepted Answer

A connection pool keeps a set of open database connections ready for reuse, so applications avoid the cost of opening and closing a connection for each request. Pools also cap concurrent sessions, protecting the server from overload. Pool size must be tuned, since too many connections can increase contention rather than throughput.

Question 39

How do you decide whether a bottleneck is CPU, memory, or disk?

Accepted Answer

Compare resource metrics such as CPU utilization, wait statistics, memory pressure, and disk read and write latency during the slow period. The dominant waits and saturated resources point toward the bottleneck. A query using little CPU but waiting heavily on disk suggests physical reads rather than computation, which guides the fix.

Question 40

Why should you test tuning changes on realistic data volumes?

Accepted Answer

Plans and timings can change dramatically with data volume, distribution, and concurrency, so a change that helps on a tiny development copy may fail in production. Realistic test data exposes estimate errors, skew, and locking behavior that small samples hide. Test with representative volumes whenever the stakes are meaningful.

Database Performance Tuning Flashcards

1Core Performance Concepts

2Indexing Strategies and Trade-offs

3Reading Execution Plans

4Query and Schema Design

5Monitoring, Locking and Maintenance

Learning Resources