Physical Design and SQL Performance

Introduction

Logical database design describes entities, relationships, constraints, and normalization. Physical design determines how that logical model is stored and accessed by a database management system (DBMS). It includes page organization, indexes, partitioning, storage parameters, materialized results, statistics, and execution-plan choices. Physical design is therefore the bridge between a correct data model and a system that can serve analytical and operational workloads at acceptable cost.

SQL is declarative: a user specifies the result required rather than the exact access procedure. The optimizer converts SQL into an execution plan, selecting operations such as table scans, index scans, joins, sorts, aggregations, and parallel execution. Performance engineering consists not merely of adding indexes, but of understanding data distribution, workload patterns, optimizer estimates, hardware constraints, and the trade-offs between read speed, write cost, storage, and maintainability.

A well-designed solution satisfies business requirements while remaining observable, explainable, secure, and resilient as data volume and concurrency increase.

Learning Objectives

By the end of this chapter, students should be able to:

1. Explain how pages, rows, storage structures, and access paths affect SQL performance.
2. Select and evaluate clustered, nonclustered, composite, covering, and specialized indexes.
3. Explain partitioning strategies and identify when partition pruning is effective.
4. Read execution plans and distinguish estimated from actual performance.
5. Explain the role of optimizer statistics and cardinality estimation.
6. Apply query-writing and schema-design techniques to improve scalability.
7. Evaluate denormalization, materialized views, and pre-aggregation as controlled performance strategies.
8. Diagnose performance problems using evidence rather than intuition.
9. Discuss governance, security, cost, and operational risks in physical design.

1. The Physical Design Problem

A DBMS stores data in fixed-size pages or blocks. A table may be represented as a heap, in which rows have no particular physical order, or as an organization in which rows follow a key order. Indexes provide additional access paths. The DBMS must also manage logging, locking, buffering, free space, and recovery.

The cost of a query depends on more than the number of rows. Relevant factors include:

• rows and pages read;
• whether requested columns are available in an index;
• selectivity of predicates;
• join order and join algorithms;
• sorting and grouping requirements;
• memory available for intermediate results;
• concurrent transactions and contention;
• storage latency and throughput;
• data distribution and optimizer statistics.

A useful conceptual model is:

This is an engineering approximation, not an exact universal equation. The terms represent elapsed contributions that may overlap in parallel systems. It is useful for diagnosis because improving one component may not improve total response time if another component is dominant.

Workload classification

Physical design begins with workload analysis. An online transaction processing (OLTP) workload commonly contains short, selective, concurrent inserts, updates, and point lookups. An online analytical processing (OLAP) workload commonly scans large volumes, joins dimensions and facts, aggregates data, and benefits from columnar storage, partitioning, compression, and parallelism. Hybrid workloads require special care because indexes and materialized results that help reporting may harm transactional writes.

Important workload questions include:

• Which queries are frequent or business-critical?
• What are their latency service-level objectives (SLOs)?
• Which predicates, joins, and orderings recur?
• How rapidly do tables grow?
• What is the read-to-write ratio?
• Are workloads seasonal or bursty?
• What consistency and freshness are required?

2. Storage Structures and Data Locality

Pages, rows, and locality

A page is a unit commonly transferred between storage and memory. If a query needs many rows located on the same pages, it benefits from spatial locality. If required rows are scattered, more page reads may be needed. A row that exceeds page capacity may be stored using overflow or out-of-row mechanisms, depending on the DBMS.

Row-oriented storage stores complete records together. It is efficient when transactions retrieve or modify many columns of a small number of rows. Column-oriented storage stores values from the same column together. It is often efficient for analytical scans that read a few columns from many rows, especially when compression is effective.

Heaps and ordered organizations

A heap supports flexible insertion but does not guarantee physical order. An ordered or clustered organization stores rows according to a key or clustering structure. Clustering can improve range access, such as retrieving all transactions for one customer or month, but it can make random inserts expensive if new keys create page splits.

Physical order is not the same as SQL result order. Unless an ORDER BY clause is specified, SQL does not guarantee row ordering.

Buffer management and locality

The buffer pool caches frequently used pages in memory. Repeated access to a small working set can be very fast, whereas a query that repeatedly evicts and reloads pages may be slow. Indexes reduce the amount of data examined, but an index that causes thousands of random lookups may be slower than a sequential table scan. Consequently, an index is valuable only in relation to selectivity, row width, access pattern, and available memory.

3. Indexes as Access Paths

An index is an auxiliary structure mapping key values to rows or row locations. B-tree indexes are general-purpose structures supporting equality, range, and ordered access. Hash indexes can be effective for equality searches where supported, but they generally do not support ordered range scans. Bitmap indexes can be useful for low-cardinality analytical columns in some systems, but they may be unsuitable for high-concurrency updates.

Selectivity and usefulness

Selectivity describes how narrowly a predicate identifies rows. A predicate returning 1% of a table is generally more selective than one returning 60%. However, selectivity alone does not determine whether an index will be used. The optimizer also considers row width, clustering, statistics, cache state, and the cost of random access.

A rough special-case estimate for an index lookup is:

This is not a cost formula for all DBMSs. It assumes a simple index lookup and ignores caching, parallelism, compression, and concurrent activity.

Primary, unique, and secondary indexes

A primary key constraint identifies each row and is often backed by a unique index. A unique index also enforces uniqueness for another candidate key. A secondary, or nonclustered, index is an additional access path. Every additional index consumes storage and usually increases the work required for inserts, deletes, and updates to indexed columns.

Composite indexes and the leftmost principle

A composite index contains multiple columns, for example:

CREATE INDEX ix_order_customer_date
ON Orders (CustomerID, OrderDate);

This index is naturally useful for predicates beginning with CustomerID, and often for searches on both CustomerID and OrderDate. It is less useful for a predicate on OrderDate alone because the leading portion of the index is not constrained. This is commonly called the leftmost-prefix principle, although exact optimizer behavior varies by DBMS.

Column order should reflect common predicates, join conditions, ordering requirements, and selectivity. The most selective column is not automatically the best first column; workload and access patterns govern the decision.

Covering indexes

An index is covering when it contains all columns needed by a query, either as key columns or included payload columns where the DBMS supports them. A covering index can avoid lookups into the base table:

CREATE INDEX ix_sales_product_date
ON Sales (ProductID, SaleDate)
INCLUDE (Quantity, NetAmount);

Covering indexes can substantially reduce I/O for frequent queries, but they enlarge the index and increase write maintenance. They should be justified by measured workload benefit.

Sargability

A predicate is sargable when the DBMS can use an index access method efficiently. Applying a function to an indexed column may prevent direct use of the index:

-- Often less sargable
WHERE YEAR(OrderDate) = 2025

-- Range formulation
WHERE OrderDate >= '2025-01-01'
  AND OrderDate <  '2026-01-01'

The range formulation also avoids ambiguity about time components. Correct syntax and date literals depend on the DBMS and data type, so production code should use typed parameters rather than locale-dependent strings.

Other common causes of poor sargability include implicit data-type conversion, leading wildcards such as LIKE '%phone', arithmetic on columns, and mismatched collations. Functional or expression indexes may be appropriate when the transformed expression is a stable and frequent search key.

4. Partitioning and Data Lifecycle Design

Partitioning divides a logical table into physically distinct portions while preserving a unified logical interface. It can improve manageability, enable partition pruning, and support parallel processing. It does not automatically make every query faster.

Main partitioning strategies

Range partitioning by date is common in fact tables. A query with a compatible date predicate may read only relevant partitions. This is partition pruning. If a query applies a nontransparent function or lacks a partition-key restriction, the DBMS may inspect many or all partitions.

Partitioning can simplify data retention. Old partitions may be archived or detached more efficiently than row-by-row deletion. Nevertheless, foreign keys, global indexes, statistics, and cross-partition queries require DBMS-specific consideration.

Partition count should be governed by management and query requirements, not by a desire to create the largest possible number. Excessive partitions increase planning overhead and administrative burden.

5. Query Execution Plans

An execution plan is the DBMS’s selected strategy for producing a result. It is commonly represented as a tree or directed graph. Operators may include scans, seeks, joins, filters, sorts, aggregates, exchanges, and spool or temporary-storage operations.

Common access and join operators

• Table or sequential scan: reads many or all pages. Often appropriate for large fractions of a table.
• Index seek or range scan: navigates an index to selected keys.
• Index scan: reads a substantial part of an index, possibly because it is narrower than the table.
• Nested-loop join: repeatedly probes the inner input; effective when the outer input is small and the inner side is indexed.
• Hash join: builds a hash structure on one input and probes it with another; useful for large equality joins.
• Merge join: combines sorted inputs; effective when inputs are already ordered or cheaply sorted.
• Sort: orders rows, potentially spilling to temporary storage if memory is insufficient.
• Aggregate: computes grouped or scalar results; may use hashing or sorting.

The optimizer estimates cardinality at each operator. A cardinality estimate is the predicted number of rows produced. The actual row count is observed during execution. Large differences between estimated and actual values often indicate stale statistics, correlated columns, skew, parameter sensitivity, or a predicate the optimizer cannot model well.

Reading a plan systematically

A practical reading sequence is:

1. Confirm the correct query, parameters, database, and environment.
2. Identify the highest-cost or longest-running operators.
3. Compare estimated and actual rows.
4. Look for large scans, repeated lookups, spills, excessive memory grants, and expensive sorts.
5. Check join order and join algorithms.
6. Inspect warnings such as implicit conversions or missing statistics.
7. Test a change and compare actual execution metrics.

Estimated cost percentages are useful clues but are not universal measurements of elapsed time. Actual duration, logical reads, CPU, memory, waits, and rows processed provide stronger evidence.

6. Statistics and Cardinality Estimation

Statistics summarize data distributions so that the optimizer can estimate predicate selectivity and join cardinality. They may include row counts, distinct-value estimates, density information, and histograms. A histogram approximates the distribution of values in a column or expression.

If a column has N rows and a predicate matches approximately n rows, the observed selectivity is:

Here s is dimensionless, n is the number of qualifying rows, and N is the total row count. This is a descriptive relationship, not necessarily the optimizer’s estimate. The formula applies when both counts refer to the same population and predicate.

For two predicates A and B, a simplistic independence assumption estimates:

This assumption can be badly wrong when columns are correlated. For example, Country and PostalCode are not independent. Modern optimizers may use extended statistics, multi-column statistics, or adaptive techniques, but their capabilities differ.

Statistics should be refreshed after substantial data change, especially after bulk loads, deletes, or changes in data distribution. Automatic statistics maintenance is valuable but does not eliminate the need for monitoring. Sampling means statistics are estimates; increasing sampling or creating targeted multi-column statistics may improve estimation at additional maintenance cost.

7. Query Optimization Techniques

Retrieve only what is required

Avoid SELECT * in production interfaces. Explicit projection reduces network traffic, memory use, and index-covering requirements. It also protects applications from unintended schema changes.

Filter early, but understand optimizer behavior

A clear predicate allows the optimizer to push filters toward data sources. Early filtering reduces intermediate rows, but manually rewriting a query is not always necessary because optimizers perform predicate pushdown themselves. The correct approach is to write semantically clear, sargable SQL and verify the plan.

Join deliberately

Join columns should have compatible data types and appropriate indexes where selective nested-loop access is expected. Avoid accidental Cartesian products. Use foreign-key relationships and constraints to communicate data integrity; in some systems, trusted constraints also assist optimization.

Avoid unnecessary sorting and repeated work

Sorting can be expensive, particularly when it spills to disk. An index whose order matches a frequent ORDER BY may remove a sort, but only if the complete ordering and predicate conditions are compatible. Repeated calculations may be moved into persisted computed columns, summary tables, or materialized views when justified.

Use parameters correctly

Parameterized queries improve security and plan reuse. However, a plan optimal for one parameter value may be poor for another when data is skewed. This parameter-sensitivity problem may require recompilation, specialized plans, query hints, or workload-specific redesign, depending on the DBMS. Hints should be used cautiously because they can become wrong as data and software change.

Measure logical work

A useful operational metric is average throughput:

If elapsed time is measured in seconds, throughput is requests per second. Latency and throughput are different: increasing concurrency may increase throughput while worsening individual response time. Performance objectives must therefore specify both where appropriate.

8. Denormalization, Materialization, and Pre-Aggregation

Normalization reduces redundancy and update anomalies. Denormalization intentionally introduces redundancy or stores derived data to improve read performance. Examples include copying a customer segment into a fact table, storing a summary table by month, or materializing a frequently used join.

Denormalization is justified when:

• a measured query is important and repeatedly expensive;
• the additional storage and maintenance cost is acceptable;
• freshness requirements are explicit;
• consistency controls are defined;
• the design is simpler or more reliable than repeatedly performing the expensive operation.

A materialized view stores query results and refreshes them periodically or incrementally. It can greatly improve reporting latency but introduces refresh cost, staleness, dependency management, and extra storage. A summary table must have a clear grain, such as one row per product per day. Ambiguous grain is a major source of double counting.

Denormalization should not be used to conceal missing indexes, poor predicates, or an unexamined execution plan. It is a design decision, not a universal optimization.

9. Worked Example: Diagnosing a Slow Sales Query

Consider a sales fact table containing 800 million rows partitioned by SaleDate. A report asks for product totals during January 2025:

SELECT ProductID, SUM(NetAmount) AS Revenue
FROM Sales
WHERE SaleDate >= :start_date
  AND SaleDate <  :end_date
GROUP BY ProductID;

The query is logically sound. An initial plan scans every partition, sorts a large intermediate result, and takes 95 seconds. Investigation finds three issues:

1. The report passes dates as strings, causing an implicit conversion on SaleDate.
2. The January partition statistics are six months old.
3. The table is row-oriented, although the workload is dominated by large analytical scans of a few columns.

The team applies typed parameters, refreshes statistics, and tests a column-oriented storage structure or columnar index supported by the DBMS. The revised plan prunes to the January partition and performs a parallel hash aggregation using compressed column data. The query now takes 8 seconds.

This result illustrates several principles. Partitioning helps only when the predicate permits pruning. Statistics affect plan selection and cardinality estimates. Storage format should match workload. The improvement did not come from a single “magic” index; it came from aligning data types, statistics, partitioning, and storage with the access pattern.

The team also verifies that the report’s required freshness is compatible with columnar maintenance and that write performance has not violated the transactional workload’s SLO.

10. Illustrative Case: Retail Analytics Platform

Background. A fictional national retailer operates an order-processing database and a separate analytical warehouse. Analysts report that weekly customer-value dashboards frequently exceed a 30-second SLO. The largest fact table contains five years of order lines and grows by 4 million rows per day.

Problem. The dashboard joins order lines, products, stores, and customers; filters by a rolling date range; and calculates revenue, margin, and customer segments. The initial design uses normalized tables, several single-column indexes, and ad hoc SQL generated by a reporting tool.

Analysis. Query monitoring shows that the largest cost is scanning historical order lines. Actual row counts differ substantially from estimates for some product categories. Several predicates wrap date and status columns in functions. A few dashboard queries request all columns, producing wide intermediate results. The single-column indexes do not support the common combination of date and store filters.

Approach. The data team:

• partitions the fact table by month and verifies partition pruning;
• creates statistics reflecting frequently correlated filter columns;
• replaces function-wrapped date predicates with half-open typed ranges;
• introduces a composite access path for high-value store/date searches;
• creates a daily summary table at the documented grain of store, product category, and date;
• uses column-oriented storage for warehouse scans;
• limits dashboard projections to required columns;
• establishes query regression tests and captures actual plans for critical reports.

Outcome. Median dashboard latency falls below the SLO, while the daily summary supports the most common time-grain queries. Some exploratory queries remain slower, which is accepted because they scan broader data and are not subject to the dashboard SLO. The team documents summary refresh delay, validates totals against the normalized source, and monitors storage and refresh cost.

Learning points. Performance objectives must be workload-specific. Partitioning, statistics, query formulation, storage format, and pre-aggregation work together. A summary table is safe only when its grain, refresh process, and reconciliation controls are explicit. Finally, optimization is iterative: every change requires measurement and regression monitoring.

11. Governance, Security, and Operational Considerations

Performance engineering must preserve correctness and responsible data use. Indexes and materialized results may duplicate sensitive data, increasing the number of locations requiring access control, encryption, retention, and deletion. A denormalized customer attribute may become stale or expose information to users who could not access the source table directly.

Parameterized SQL is essential to reduce injection risk. Query logs, execution plans, and monitoring data may contain customer identifiers or sensitive predicates; they require appropriate masking, retention, and access controls. Least privilege should apply to both base tables and derived structures.

Operational safeguards include:

• testing changes with representative data volumes;
• checking plans after database-engine upgrades;
• measuring write overhead from new indexes;
• defining rollback procedures;
• monitoring storage growth, fragmentation where relevant, statistics freshness, lock waits, and replication lag;
• documenting ownership and refresh schedules for materialized data;
• using change control for production physical design.

Cloud systems add cost considerations. Scanning more data may increase usage charges, while overprovisioning compute may conceal inefficient SQL. Cost, latency, reliability, and energy consumption should be evaluated together.

12. Contemporary Developments and Limitations

Modern DBMSs increasingly use automatic tuning, adaptive joins, memory-grant feedback, learned cardinality models, and workload-aware indexing recommendations. These tools can identify regressions and propose changes, but they depend on representative telemetry and correct constraints. Recommendations should be tested rather than accepted blindly.

AI-assisted SQL generation can accelerate development, but generated queries may contain inefficient joins, unsafe dynamic SQL, incorrect grain, or inappropriate assumptions about indexes and partitions. Human review, security testing, execution-plan inspection, and data validation remain necessary.

No optimization technique eliminates fundamental resource limits. An index cannot make a query requiring every row selective. Partitioning cannot compensate for an unsuitable data model. Caching can reduce repeated work but introduces invalidation and freshness problems. Parallelism can reduce elapsed time while increasing CPU contention. Performance is therefore a systems property rather than a property of SQL text alone.

Best-Practice Workflow

1. Define the business SLO, workload, freshness, and correctness requirements.
2. Capture representative queries, parameters, data volumes, and concurrency.
3. Inspect actual execution plans and runtime metrics.
4. Check cardinality estimates, statistics, sargability, joins, and data types.
5. Consider indexes, partitioning, storage format, and materialization as alternatives.
6. Test one controlled change at a time where possible.
7. Measure latency, CPU, I/O, memory, throughput, and write impact.
8. Validate results, security, and transactional correctness.
9. Deploy with monitoring and a rollback plan.
10. Reassess as data distribution, workloads, and DBMS versions change.

Chapter Summary

Physical design determines how a logical database is stored and accessed. Pages, locality, buffering, row- and column-oriented storage, and clustered organization influence I/O and CPU behavior. Indexes provide access paths, but their value depends on selectivity, column order, sargability, covering ability, workload, and write cost.

Partitioning supports lifecycle management and can reduce work through partition pruning, but it does not automatically improve every query. Execution plans reveal scans, seeks, joins, sorts, aggregations, spills, and parallel operations. Comparing estimated with actual rows is central to diagnosing cardinality-estimation problems. Statistics describe distributions and must be maintained as data changes.

Effective optimization uses clear, parameterized, sargable SQL; appropriate join and projection choices; carefully justified indexes; and measured use of partitioning, columnar storage, materialized views, and pre-aggregation. Denormalization can improve read performance but creates consistency and maintenance obligations. Governance requires attention to security, privacy, cost, correctness, operational change control, and ongoing regression monitoring. The central professional principle is evidence-based optimization: measure the workload, change the physical design deliberately, and verify the result.

Key Terms

Physical design: The storage and access-path design used to implement a logical database.

Page: A unit of storage commonly transferred between disk or persistent storage and memory.

Buffer pool: Memory used by a DBMS to cache data and index pages.

Heap: A table organization without a guaranteed physical ordering of rows.

Clustered organization: Storage arrangement that keeps rows ordered or grouped according to a key.

Index: An auxiliary structure that supports faster access to rows or values.

Composite index: An index containing two or more columns.

Covering index: An index containing all data required by a query.

Selectivity: The fraction or proportion of rows satisfying a predicate.

Sargability: The property of a predicate that permits efficient use of an access method.

Partitioning: Dividing one logical table into physically managed portions.

Partition pruning: Reading only partitions that can contain qualifying rows.

Execution plan: The operator strategy selected by a DBMS to execute SQL.

Cardinality estimate: The optimizer’s predicted number of rows produced by an operation.

Statistics: Data summaries used by an optimizer to estimate selectivity and cardinality.

Materialized view: Stored results of a query that are refreshed over time.

Denormalization: Intentional redundancy or precomputation introduced for performance or usability.

Parameter sensitivity: A situation in which one execution plan is not optimal for all parameter values.

Service-level objective: A measurable performance or reliability target.

Query regression: A deterioration in query performance after a change.

Review Questions

Short Answer Questions

1. Why might a table scan be faster than an index lookup for a query returning a large fraction of a table?
2. What is the leftmost-prefix principle for composite indexes?
3. How does partition pruning reduce query work?
4. Why are actual-versus-estimated row counts useful when reading an execution plan?
5. State two risks introduced by denormalization.

Long Answer Questions

1. Explain how workload characteristics should influence the choice between row-oriented and column-oriented storage.
2. Discuss the benefits and costs of composite and covering indexes in a high-write transactional system.
3. Explain how stale or inadequate statistics can lead to a poor execution plan.
4. Compare range, list, hash, and composite partitioning, including an appropriate use case for each.
5. Develop a complete evidence-based workflow for investigating a slow SQL query.

Analytical / Application Questions

1. A query filters a 500-million-row table by month but applies a function to the date column. Propose a rewritten predicate and explain what additional physical-design evidence you would inspect.
2. A dashboard query is fast for small customers but slow for a national customer whose rows dominate the table. Analyze the likely causes and recommend plan or design investigations.
3. A reporting team proposes adding ten indexes to solve slow queries. Evaluate the proposal and design a testing framework that considers read latency, write overhead, storage, statistics, concurrency, and rollback.

Multiple Choice Questions with Answers

Question 1

Which condition most directly enables partition pruning?

A. A predicate restricts the partitioning column using a compatible range

B. The query uses SELECT *

C. The table has many single-column indexes

D. The query omits its WHERE clause

Correct Answer: A. A predicate restricts the partitioning column using a compatible range

Question 2

Which index is generally most suitable for a query filtering by CustomerID and then by OrderDate when both predicates are common?

A. An index on an unrelated descriptive column

B. A composite index beginning with CustomerID and including OrderDate

C. An index containing only a generated row number

D. A bitmap index on every table column

Correct Answer: B. A composite index beginning with CustomerID and including OrderDate

Question 3

What is a common consequence of applying a function directly to an indexed column in a predicate?

A. It guarantees a covering index scan

B. It may prevent efficient direct use of the index

C. It automatically refreshes statistics

D. It forces partition pruning

Correct Answer: B. It may prevent efficient direct use of the index

Question 4

What does a large difference between estimated and actual row counts usually indicate?

A. The SQL result is necessarily incorrect

B. The network is disconnected

C. The optimizer’s cardinality assumptions may be inaccurate

D. The table has no primary key

Correct Answer: C. The optimizer’s cardinality assumptions may be inaccurate

Question 5

Which join algorithm is often effective when the outer input is small and the inner input has a suitable index?

A. Nested-loop join

B. Cartesian join

C. Full table materialization

D. Unordered merge without matching keys

Correct Answer: A. Nested-loop join

Question 6

If a predicate matches 20,000 rows in a table containing 1,000,000 rows, what is its observed selectivity?

A. 0.002

B. 0.02

C. 0.2

D. 20

Correct Answer: B. 0.02

Question 7

What is a principal advantage of a covering index?

A. It eliminates all write maintenance

B. It guarantees the optimizer will use it

C. It may avoid lookups into the base table for the query

D. It removes the need for data validation

Correct Answer: C. It may avoid lookups into the base table for the query

Question 8

Which statement best describes denormalization?

A. It always improves every query

B. It intentionally introduces redundancy or derived data for a justified performance benefit

C. It is identical to encryption

D. It prevents all update anomalies

Correct Answer: B. It intentionally introduces redundancy or derived data for a justified performance benefit

Question 9

Why should query optimization compare actual runtime metrics rather than rely only on estimated cost percentages?

A. Estimated costs are universal measurements of elapsed seconds

B. Actual metrics reveal observed latency, reads, CPU, waits, and rows processed

C. Execution plans never contain estimates

D. Runtime metrics cannot be collected in production

Correct Answer: B. Actual metrics reveal observed latency, reads, CPU, waits, and rows processed

Question 10

Which practice best protects a materialized summary table from producing misleading analytics?

A. Leave its grain undocumented

B. Refresh it only when users complain

C. Define its grain, freshness, reconciliation, and ownership explicitly

D. Allow unrestricted direct updates by dashboard users

Correct Answer: C. Define its grain, freshness, reconciliation, and ownership explicitly