Query Processing Bottlenecks

A comprehensive set of Q&A flashcards covering definitions, causes, and solutions for memory, I/O, processing, and indexing bottlenecks in database systems.

What is a database "bottleneck"?

A degradation in performance that occurs when demand for services or resources exceeds what is available, causing slow queries, longer response times, and reduced scalability.

Which four major categories of bottlenecks were discussed in the lesson?

Memory bottlenecks, Input/Output (I/O) bottlenecks, Processing bottlenecks, and Indexing bottlenecks.

What typical symptoms suggest a MEMORY bottleneck?

Long query execution times, excessive disk paging/​I-O, and out-of-memory messages in the logs.

How can adding more physical RAM help resolve a memory bottleneck?

It gives the database enough working memory, reducing the need for disk paging and eliminating competition among processes for limited RAM.

What role does a QUERY OPTIMIZER play in mitigating memory or processing issues?

It analyzes each SQL statement and chooses the most efficient execution plan, reducing memory usage and CPU time.

Define a "memory leak."

A condition in which a program allocates memory but fails to release it when no longer needed, eventually exhausting available memory.

What is an I/O bottleneck?

A performance problem that arises when bandwidth limits reading from or writing to storage devices.

In I/O terminology, what is "latency"?

The delay between issuing a request for data and the moment the request is fulfilled.

Why might switching from HDDs to SSDs mitigate an I/O bottleneck?

SSDs provide much faster and lower-latency reads/writes than traditional spinning disks.

Explain how RAID can improve disk throughput.

RAID (Redundant Array of Independent Disks) spreads data across multiple disks so reads and writes are split, speeding overall I/O performance.

What is file "fragmentation," and how does a defragmenter help?

Fragmentation is when a file’s pieces are stored in non-contiguous disk sectors; a defragmenter rearranges them contiguously, shortening head-movement times on HDDs.

Why is defragmentation unnecessary on solid-state drives (SSDs)?

SSDs have no mechanical read/write head, so access time is consistent regardless of file contiguity.

How does QUERY PAGINATION ease I/O load?

It retrieves large result sets in smaller pages rather than fetching everything at once, lowering immediate disk and network traffic.

What does an "isolation level" control in a database?

The degree to which one transaction must be isolated from the effects of other concurrent transactions.

Define "database locking."

A concurrency-control mechanism that prevents multiple users from modifying the same data simultaneously.

What is "disk caching" in database systems?

Keeping frequently accessed data in memory so physical disk reads are minimized, greatly speeding access.

Describe an "in-memory database."

A DBMS that stores and retrieves most or all data directly from RAM rather than disk storage.

What causes a PROCESSING bottleneck?

The CPU cannot keep up with workload demands, often due to inefficient queries, lack of indexes, or inadequate hardware.

Give two query-side ways to relieve a processing bottleneck.

1) Rewrite/optimize queries for more efficient joins and logic; 2) Add indexes to frequently searched columns to avoid full table scans.

How can poor database design increase CPU processing load?

Suboptimal schemas lead to costly joins, full table scans, and redundant data access, all of which consume extra CPU cycles.

What is "sharding," and how can it help processing throughput?

Splitting a large database into independent pieces (shards) distributed across servers, parallelizing workload and reducing per-node CPU load.

Explain "connection pooling."

A technique that reuses existing database connections instead of opening/closing them for every request, saving CPU and memory resources.

List three routine maintenance tasks that reduce processing bottlenecks.

Rebuilding indexes, updating statistics, and cleaning up/archiving old data.

What is an INDEXING bottleneck?

Performance problems that occur because maintaining or updating indexes slows operations or because missing/poor indexes hurt query speed.

How can having too many indexes slow data modifications?

INSERT, UPDATE, and DELETE operations must update every index, so excess indexes add significant overhead.

Why do missing indexes on frequently filtered columns cause slow queries?

The DBMS must perform full table scans, increasing disk I/O and CPU work.

How does index fragmentation degrade performance?

Scattered index pages require more reads; reorganizing or rebuilding the index restores contiguous structure and speed.

What is "index cardinality" and why does it matter?

The ratio of unique values in an index versus total rows; very low or very high cardinality can cause the optimizer to ignore the index or make inefficient plans.

Describe a well-designed composite index.

Places the most selective column(s) first so the index aligns with typical WHERE/​JOIN predicates.

Give four general techniques to alleviate database bottlenecks.

Indexing, caching, partitioning/sharding, load balancing, as well as hardware upgrades and query optimization.