Section 12.3: Performance Tuning & Operations

12.3 Performance Tuning & Operations

Database performance tuning and capacity planning are core operational disciplines that ensure systems meet transactional SLAs and handle database growth efficiently. Tuning involves optimizing physical database structures, query code, and hardware resources to maximize throughput and minimize query response times.

Database Indexing Strategies

An index is a physical structure that improves the speed of data retrieval operations on a database table. Without an index, the database engine must perform a full table scan, reading every row from disk.

• Clustered Index: A clustered index determines the physical storage order of data rows in a table. Because physical rows can only be stored in a single sorted order, a table can have only one clustered index (usually created automatically on the primary key). The leaf nodes of a clustered index contain the actual data pages.
• Non-Clustered Index: A non-clustered index is a separate index structure that contains index key values and pointers back to the physical data rows (row identifiers in a heap table, or the clustered index key in a clustered table). A table can have multiple non-clustered indexes.
• Composite Index: An index created on two or more columns. The order of columns in a composite index is critical. The database engine can only use a composite index if the query's WHERE clause filters on the leading (leftmost) column of the index. This is known as the left-to-right prefix matching rule.

As databases modify data, index fragmentation occurs. When data is inserted or updated, index pages split, creating physical gaps and logical disorder. This degrades read performance. DBAs monitor fragmentation levels to decide on maintenance actions:

• Index Reorganization: Physically reorders the leaf-level pages in place. Reorganizing is an online operation (the table remains accessible) and uses minimal system resources. It is recommended for moderate fragmentation (typically 5% to 30%).
• Index Rebuild: Drops and recreates the index from scratch. This removes all fragmentation and updates statistics. Rebuilding is a resource-intensive process that can lock the table (offline rebuild) unless the enterprise database edition supports online rebuilds. It is recommended for severe fragmentation (typically greater than 30%).

Database Partitioning

As database tables scale to millions of rows, query performance degrades even with indexing. Database partitioning splits a large database table or index into smaller physical segments, while maintaining a single logical table interface for applications.

• Horizontal Partitioning: Divides table rows across separate partitions based on a partition key. For example, a transaction table might be partitioned by year. The database engine uses "partition pruning" to scan only the partitions containing the queried dates, ignoring the rest. A distributed form of horizontal partitioning is sharding, where partitions are stored across separate physical database servers (shared-nothing architecture) to scale out write throughput.
• Vertical Partitioning: Divides table columns into separate physical tables. This is highly effective when a table has wide, infrequently accessed columns (such as text blobs or binary files) alongside narrow, frequently accessed columns. By separating them, the database fits more rows into a single memory buffer page, reducing I/O overhead.

Common partitioning methods include:

• Range Partitioning: Assigns rows to partitions based on a range of values (e.g., date ranges).
• List Partitioning: Assigns rows based on a predefined list of values (e.g., customer country codes).
• Hash Partitioning: Applies a mathematical hash function to a partition key to distribute rows evenly across a fixed number of partitions, preventing data skew.

Query Optimization and Execution Plans

The database query optimizer is a software component that determines the most efficient way to execute a SQL statement. It evaluates multiple access paths and generates an execution plan representing the physical operations required to retrieve the data.

DBAs examine execution plans to identify bottlenecks:

• Table Scan vs. Index Seek: A table scan reads every database page on disk, which is highly inefficient for large tables. An index seek traverses the index B-tree to retrieve specific matching rows directly.
• Join Operations:
  • Nested Loops Join: For each row in the outer table, the engine searches the inner table. This is fast for small datasets.
  • Hash Join: The optimizer creates a hash table in memory from the smaller table and probes it with the larger table. This is used for large, unsorted datasets.
  • Merge Join: The optimizer merges two pre-sorted datasets. This is highly efficient but requires the inputs to be sorted on the join key.

To generate accurate execution plans, the optimizer relies on database statistics. Statistics capture data distribution metadata (such as histograms) for columns and indexes. If statistics become outdated due to heavy data modifications, the optimizer may choose inefficient execution plans (e.g., choosing a table scan over an index seek). Regular updates of database statistics are a key operational task.

Storage Capacity Planning

Database performance is heavily constrained by disk storage subsystems. Storage capacity planning involves tracking data growth and ensuring storage performance meets business needs.

• Data Growth Forecasting: DBAs track database file sizes and project future requirements based on growth trends and archiving policies.
• IOPS Constraints: Storage performance is measured in Input/Output Operations Per Second (IOPS). Operational databases require high IOPS to handle concurrent read and write transactions.
• Storage Tiering: To balance cost and performance, organizations place data on different storage tiers based on access frequency:
  • Tier 1 (Hot Storage): Frequently accessed transactional data and active indexes stored on high-speed Solid-State Drives (SSDs).
  • Tier 2 (Warm Storage): Less frequently accessed data stored on cost-effective magnetic Hard Disk Drives (HDDs).
  • Tier 3 (Cold Storage): Archived data stored on low-cost tape libraries or cloud object storage.