SQL Performance Tuning - Indexing, Query Optimization & Execution Plans Flashcards

Question 1

What is a database index?

Accepted Answer

A special lookup table that the database search engine can use to speed up data retrieval. It's a data structure that improves the speed of data retrieval operations on a database table at the cost of additional writes and storage space to maintain the index data structure.

Question 2

How do indexes improve query performance?

Accepted Answer

Indexes allow the database to quickly locate data without scanning every row in a table. Similar to a book's index, they provide direct pointers to the physical location of data, significantly reducing I/O operations and CPU usage for `SELECT` queries.

Question 3

What is the primary trade-off of using indexes?

Accepted Answer

While indexes speed up `SELECT` queries, they add overhead to `INSERT`, `UPDATE`, and `DELETE` operations. Each modification to the indexed table requires the index itself to be updated, consuming additional CPU, I/O, and storage.

Question 4

Explain a Clustered Index.

Accepted Answer

A clustered index determines the physical order of data storage in a table. A table can have only one clustered index because the data rows themselves can only be stored in one order. It's typically built on the primary key.

Question 5

How does a Clustered Index store data?

Accepted Answer

The leaf nodes of a clustered index *are* the actual data rows of the table, sorted according to the clustered index key. This means that when you retrieve data via a clustered index, you're directly accessing the data itself.

Question 6

Explain a Non-Clustered Index.

Accepted Answer

A non-clustered index does not alter the physical order of table rows. Instead, it creates a separate structure containing the indexed columns and pointers (row locators or clustered index keys) to the actual data rows in the table. A table can have multiple non-clustered indexes.

Question 7

How does a Non-Clustered Index store data?

Accepted Answer

The leaf nodes of a non-clustered index contain the indexed column values and a pointer to the corresponding data row. This pointer can be a Row ID (`RID`) for tables without a clustered index (heap) or the clustered index key for tables with a clustered index.

Question 8

What is a Covering Index?

Accepted Answer

A covering index (or "index-only scan") is a non-clustered index that includes all the columns required by a query, both in the `SELECT` list and the `WHERE`, `JOIN`, `GROUP BY`, or `ORDER BY` clauses. This allows the database to retrieve all necessary data directly from the index without accessing the base table, significantly improving performance.

Question 9

Differentiate between an Index Seek and an Index Scan.

Accepted Answer

An `Index Seek` is a highly efficient operation where the database directly navigates to a specific subset of rows using the index structure (like looking up a word in a dictionary). An `Index Scan` involves reading all or a significant portion of the index's leaf nodes to find the required data, which is less efficient than a seek but more efficient than a `Table Scan`.

Question 10

When should you consider creating an index?

Accepted Answer

Consider indexing columns frequently used in `WHERE` clauses, `JOIN` conditions, `ORDER BY` clauses, `GROUP BY` clauses, or `DISTINCT` operations, especially on large tables with low data modification rates.

Question 11

What types of columns are good candidates for indexing?

Accepted Answer

Columns with high cardinality (many unique values, e.g., `UserID`, `ProductID`), columns frequently used in `WHERE` or `JOIN` clauses, foreign keys, and columns used in `ORDER BY` or `GROUP BY` clauses.

Question 12

What types of columns are generally poor candidates for indexing?

Accepted Answer

Columns with low cardinality (few unique values, e.g., `Gender`, `Boolean flags`), columns frequently updated, very wide columns (large data types), or columns rarely used in queries. Over-indexing can degrade write performance and consume excessive storage.

Question 13

What is a Composite Index (Multi-Column Index)?

Accepted Answer

An index created on two or more columns of a table. The order of columns in a composite index is crucial, as it's sorted by the first column, then the second, and so on. It's effective for queries filtering on multiple columns or a prefix of the indexed columns.

Question 14

How can `JOIN` operations benefit from indexing?

Accepted Answer

Indexing the columns used in `JOIN` conditions (typically foreign keys and their corresponding primary keys) significantly speeds up the join process. The database can use index seeks to quickly find matching rows in the joined tables instead of performing costly table scans.

Question 15

What is a Query Execution Plan?

Accepted Answer

A Query Execution Plan (or Explain Plan) is a graphical or textual representation of the steps a database system takes to execute a SQL query. It shows the order of operations, the algorithms used (e.g., join types), and the estimated cost for each step, helping identify performance bottlenecks.

Question 16

How do you typically generate an Execution Plan?

Accepted Answer

In SQL Server, use `SET SHOWPLAN_ALL ON;` or `SET SHOWPLAN_TEXT ON;` before the query, or use `DISPLAY ESTIMATED EXECUTION PLAN` / `INCLUDE ACTUAL EXECUTION PLAN` in SSMS. In PostgreSQL/MySQL, prefix the query with `EXPLAIN` (for estimated plan) or `EXPLAIN ANALYZE` (for actual plan with timings). In Oracle, use `EXPLAIN PLAN FOR`.

Question 17

What key metrics should you look for when interpreting an Execution Plan?

Accepted Answer

Look for: `Cost` (estimated relative cost, lower is better), `Rows` (estimated/actual number of rows processed), `Operations` (e.g., `Table Scan`, `Index Seek`, `Hash Match`, `Nested Loops`), `Warnings` (potential issues like implicit conversions or missing indexes), and `I/O` (disk reads/writes).

Question 18

What does a high "cost" in an Execution Plan typically indicate?

Accepted Answer

A high cost usually points to a performance bottleneck. It often indicates that the database is performing expensive operations like `Table Scans` (reading every row), sorting large datasets, or using inefficient join algorithms, suggesting a need for better indexing or query rewriting.

Question 19

How can `EXPLAIN ANALYZE` (PostgreSQL) or `SET STATISTICS IO ON` (SQL Server) help in tuning?

Accepted Answer

`EXPLAIN ANALYZE` (PostgreSQL) executes the query and provides actual runtime statistics, including execution time for each step, number of rows, and buffer usage. `SET STATISTICS IO ON` (SQL Server) displays information about the number of logical and physical reads performed by the query, helping to identify I/O-bound queries and the impact of indexes.

Question 20

Strategies to optimize queries involving `LIKE` with leading wildcards (`%keyword`)?

Accepted Answer

`LIKE '%keyword'` prevents index usage. Strategies include: using full-text search capabilities (e.g., `CONTAINS`, `MATCH AGAINST`), re-evaluating if a leading wildcard is truly necessary, or creating a computed column with reversed strings and indexing it for `LIKE 'keyword%'` on the reversed column.

Question 21

How can `OR` conditions sometimes hinder index usage?

Accepted Answer

If an `OR` condition involves columns that are not all indexed, or if the optimizer estimates that combining results from multiple index scans would be more expensive than a single table scan, it might opt for a full table scan. Rewriting with `UNION ALL` or `IN` might sometimes help, or ensuring all columns in the `OR` are indexed.

Question 22

What is the impact of `SELECT *` on query performance?

Accepted Answer

`SELECT *` retrieves all columns, even those not needed. This can increase network traffic, increase I/O operations (reading more data), prevent covering indexes from being used (forcing a lookup to the base table), and lead to unnecessary memory consumption. Always specify only the columns you need.

Question 23

How can `ORDER BY` and `GROUP BY` clauses be optimized with indexes?

Accepted Answer

An index on the columns used in `ORDER BY` or `GROUP BY` can eliminate the need for costly sort operations. If the index's sort order matches the query's criteria, the database can retrieve data already sorted, significantly improving performance. For `GROUP BY`, a composite index on the grouping columns can also help.

SQL Performance Tuning - Indexing, Query Optimization & Execution Plans

Card Preview

Related Flashcard Sets

SQL Indexing, B-Trees & Query Optimization

French A1/A2 - Core Vocabulary & Beginner Grammar

Pharmacology - Drug Action, Pharmacokinetics & Pharmacodynamics

AWS Solutions Architect Associate (SAA-C03) - Core Services & Architecture