Database performance bottlenecks are rarely caused by CPU or memory limits; over 90% of slow database operations stem from improper index architectures and suboptimal query plans.

1. Clustered vs. Non-Clustered B-Trees

Understanding how the database engine arranges data on disk is the foundation of high-throughput database engineering:

  • Clustered Index: Dictates the physical sorting order of rows on 8KB data pages. A table can possess exactly one clustered index (typically on the Primary Key).
  • Non-Clustered Index: Creates a separate B-tree structure where leaf nodes contain index keys and row pointers (either the clustered key or a physical RID) pointing back to the base table.

2. Eliminating Expensive Key Lookups with Covering Indexes

When a non-clustered index satisfies a WHERE clause but lacks columns requested in the SELECT list, SQL Server performs a Key Lookup for every matching row. By adding non-key columns using the INCLUDE clause, you create a "Covering Index" that answers queries directly from index leaf pages with zero base table IO.

3. Reading Execution Plans

Look out for Table Scans, Clustered Index Scans, and high-cost Hash Match Joins. Aim for selective Index Seeks with low IO reads.