SQL Performance Tuning & Indexing Strategies Flashcards

Question 1

Shared Lock (S) vs. Exclusive Lock (X)

Accepted Answer

- **Shared Lock (S)**: Placed during read operations (`SELECT`). Multiple transactions can hold shared locks concurrently, but writes are blocked.
- **Exclusive Lock (X)**: Placed during write operations (`INSERT`, `UPDATE`, `DELETE`). Blocks all other concurrent reads and writes.

Question 2

Intent Locks (IS / IX)

Accepted Answer

Hierarchy-level locks (placed at Table or Page levels) indicating that a transaction holds or intends to acquire fine-grained locks (Row level) beneath it. Prevents coarse high-level locks (e.g., full table lock) from conflicting with ongoing row-level transactions.

Question 3

Deadlock

Accepted Answer

A circular dependency where Transaction A waits for a resource locked by Transaction B, while Transaction B waits for a resource locked by Transaction A. The DBMS automatically detects deadlocks and terminates one transaction (the 'deadlock victim') to allow the other to complete.

Question 4

Optimistic vs. Pessimistic Concurrency Control

Accepted Answer

- **Pessimistic**: Locks resources aggressively upfront assuming conflicts will happen; best for high-contention write workloads.
- **Optimistic**: Uses row versioning/timestamps without upfront locks; validates changes at commit time and rolls back on conflicts. Ideal for high-concurrency read-heavy workloads.

Question 5

Filtered (Partial) Index

Accepted Answer

An index created with a `WHERE` condition to index only a specific subset of rows (e.g., `CREATE INDEX idx_unprocessed ON jobs(created_at) WHERE status = 'PENDING'`). Reduces index size, lowers write maintenance overhead, and speeds up selective queries.

Question 6

Refactoring `OR` Conditions to `UNION ALL`

Accepted Answer

Queries using `OR` across multiple columns often fail to utilize individual indexes efficiently, falling back to full scans. Rewriting as two separate `SELECT` statements joined by `UNION ALL` enables separate, optimized index seeks for each predicate.

Question 7

Subquery Performance: `EXISTS` vs. `IN`

Accepted Answer

`EXISTS` short-circuits and returns true as soon as a single match is found in the subquery. `IN` evaluates the entire subquery result set before filtering and can produce incorrect results or poor plans if the subquery returns `NULL` values.

Question 8

Execution Plan Join Operators: Nested Loop vs. Hash vs. Merge

Accepted Answer

- **Nested Loop**: Iterates over outer rows matching inner rows; ideal for small sets and indexed inner lookups ($O(N 	imes M)$ worst-case).
- **Hash Match**: Builds an in-memory hash table for large, unsorted datasets.
- **Merge Join**: Blazingly fast $O(N + M)$ join that requires both input datasets to be pre-sorted on join keys.

Question 9

Over-Indexing Overhead

Accepted Answer

While indexes accelerate `SELECT` operations, every added index incurs maintenance costs during `INSERT`, `UPDATE`, and `DELETE` operations. The engine must synchronously write changes to all corresponding index trees, increasing I/O and storage consumption.

Question 10

Index Fragmentation and Page Splits

Accepted Answer

Inserting non-sequential data (such as random UUID primary keys) causes index leaf pages to fill up and split non-contiguously (**Page Split**). This produces logical fragmentation, inflates disk usage, and degrades sequential I/O performance during scans.

Question 11

B-Tree Index vs. Hash Index

Accepted Answer

A **B-Tree index** maintains a balanced tree hierarchy, making it versatile for exact matches (`=`), range queries (`<`, `>`, `BETWEEN`), sorting, and prefix matching with $O(\log N)$ complexity. A **Hash index** uses a hash function to map keys to buckets, offering $O(1)$ lookups for equality matches (`=`), but completely fails to support range queries or sorting.

Question 12

Clustered Index vs. Non-Clustered Index

Accepted Answer

A **Clustered Index** physically reorganizes and stores actual table data at its leaf nodes (only 1 per table). A **Non-Clustered Index** is a separate structure containing index key values along with pointers (row locators or primary key values) back to the base table rows (multiple allowed per table).

Question 13

Index Seek vs. Index Scan

Accepted Answer

An **Index Seek** uses the index B-Tree structure to directly locate specific rows matching a predicate (highly efficient). An **Index Scan** reads through every leaf page of an index sequentially because no selective predicate exists or the query requires unindexed data.

Question 14

Leftmost Prefix Rule (Composite Indexes)

Accepted Answer

In a composite index defined on columns `(A, B, C)`, the database engine can only utilize the index if search predicates filter starting from the leftmost column. Valid index searches include filtering on `A`, `A, B`, or `A, B, C`. Filtering solely on `B` or `C` skips the index structure.

Question 15

Composite Index Column Ordering Strategy

Accepted Answer

Order columns based on query patterns and selectivity using the standard rule: **Equality columns first, Range columns second**. Place high-selectivity columns (columns with many unique values) upfront unless a leftmost column is strictly required to satisfy `GROUP BY` or `ORDER BY` clauses.

Question 16

Covering Query (Index-Only Scan)

Accepted Answer

A query where all columns requested in `SELECT`, `JOIN`, `WHERE`, and `ORDER BY` are contained within the index itself. The database engine executes an **Index-Only Scan**, completely eliminating expensive table lookups (heap/clustered index reads).

Question 17

INCLUDE Clause in Non-Clustered Indexes

Accepted Answer

The `INCLUDE` statement attaches non-key columns strictly to the leaf nodes of a non-clustered index (e.g., `CREATE INDEX idx ON orders(customer_id) INCLUDE (total_amount)`). This satisfies covering queries without inflating the B-Tree root/intermediate levels or key size limits.

Question 18

SARGable Predicates

Accepted Answer

**Search Argument Able** predicates allow the engine to perform an index seek. Wrapping indexed columns in functions, expressions, or wildcards makes them non-SARGable:
- Non-SARGable: `WHERE YEAR(created_at) = 2023`
- SARGable: `WHERE created_at >= '2023-01-01' AND created_at < '2024-01-01'`

Question 19

Implicit Data Type Conversion Penalty

Accepted Answer

Occurs when comparing columns and parameters of mismatched data types (e.g., comparing an indexed `VARCHAR` column to an `NVARCHAR` input variable). The database applies an implicit `CAST` or `CONVERT` function to every row in the column, destroying SARGability and triggering an index scan.

Question 20

Parameter Sniffing

Accepted Answer

The process where the optimizer inspects stored procedure parameters during compilation to build an optimized execution plan. If initial parameters represent rare, non-representative data distributions, the cached execution plan may perform poorly for subsequent common requests.

SQL Performance Tuning & Indexing Strategies

Cards in this deck

Related Flashcard Sets

TypeScript - Modern JavaScript Development & Type Safety

Monash FIT9132 - Data Mining & Machine Learning Fundamentals

Biochemistry - Metabolic Pathways & Regulation

JLPT N2 - Advanced Japanese Grammar, Vocabulary & Kanji