Indexes are one of the core building blocks of SQL Server performance. A well-designed index can dramatically reduce the number of pages a query reads. A speculative or unnecessarily wide index can increase write cost, storage use and maintenance pressure. This guide connects the internal architecture to practical design decisions.
Author’s note: This is the reviewed and expanded English edition of my original Index Mimarisi article, first published on August 16, 2025. The laboratory screenshots below are from that original work.
Why does an index matter?
A book index gives us a direct path to a subject instead of forcing us to read every page. A rowstore index provides a similar ordered access structure. The optimizer can use it to locate qualifying rows without scanning the entire table when that access path is selective and economical.
SELECT *
FROM dbo.Employees;[company_article_image src=”articles/index-architecture/01-small-table-query.png” alt=”Northwind Employees query and SQL Server statistics for the small table” caption=”The initial Employees table contains nine rows and completes quickly.”]
The actual execution plan uses a Clustered Index Scan because every row is requested. A scan on a tiny table is often the correct choice; seeing a scan operator does not automatically mean there is a performance problem.
[company_article_image src=”articles/index-architecture/02-clustered-index-scan.png” alt=”Clustered Index Scan execution plan for Employees” caption=”The optimizer selects a Clustered Index Scan for the small data set.”]
What changes when the table grows?
After the test table was expanded to 610,010 rows, a selective FirstName predicate produced 17,054 logical reads and a parallel scan in this laboratory. These figures belong to this data distribution and environment; the important practice is to compare logical reads, CPU, elapsed time and the actual execution plan together.
[company_article_image src=”articles/index-architecture/03-employees-row-count.png” alt=”Employees table expanded to 610010 rows” caption=”The Employees table was enlarged for the index test.”]
[company_article_image src=”articles/index-architecture/04-filtered-query-statistics.png” alt=”Query statistics before the nonclustered index” caption=”I/O and timing statistics before adding the targeted index.”]
[company_article_image src=”articles/index-architecture/05-parallel-scan-plan.png” alt=”Parallel Clustered Index Scan plan” caption=”The filtered query uses a parallel scan before the new index.”]
CREATE NONCLUSTERED INDEX IX_Employees_FirstName
ON dbo.Employees (FirstName)
WITH (ONLINE = ON);After the nonclustered index was added, the example completed with 21 logical reads and an Index Seek. ONLINE support depends on SQL Server version, edition and index characteristics, and it does not eliminate every lock or resource cost.
[company_article_image src=”articles/index-architecture/06-indexed-query-statistics.png” alt=”Reduced logical reads after the nonclustered index” caption=”The test query completes with 21 logical reads after indexing.”]
[company_article_image src=”articles/index-architecture/07-index-seek-plan.png” alt=”Execution plan using Nonclustered Index Seek” caption=”The optimizer can now use an Index Seek.”]
Pages and extents
SQL Server logically divides data files into 8 KiB pages. Data-file I/O is performed at page level. Eight physically contiguous pages form a 64 KiB extent. Transaction log files use a separate log-record architecture rather than data pages.
[company_article_image src=”articles/index-architecture/08-page-architecture.png” alt=”SQL Server data page architecture” caption=”The main regions of a SQL Server data page.”]
[company_article_image src=”articles/index-architecture/09-page-layout.png” alt=”SQL Server page and row layout” caption=”Rows and the slot array inside a page.”]
The B+ tree
Disk-based rowstore indexes are organized as B+ trees. The root is the entry point, intermediate levels direct the search, and the leaf level contains clustered data pages or nonclustered keys, included columns and row locators.
[company_article_image src=”articles/index-architecture/10-btree-structure.png” alt=”SQL Server B+ tree root intermediate and leaf levels” caption=”Root, intermediate and leaf levels in a rowstore index.”]
A table without a clustered index is a heap; a heap can still have nonclustered indexes. A table with a clustered index stores its rows at the clustered B+ tree leaf level, and it can have only one clustered index.
Core index types
- Clustered: the leaf level contains the table data pages.
- Nonclustered: a separate access structure with a row locator.
- Unique: enforces uniqueness of the key.
- Filtered: indexes a well-defined subset of rows.
- Composite: uses more than one key column, where key order matters.
- Columnstore: stores data by column and is designed for analytical workloads.
Covering a query
A nonclustered index covers a query when all required columns can be returned from that index. Included columns may remove an expensive Key Lookup, but wide include lists increase storage and DML cost.
[company_article_image src=”articles/index-architecture/11-key-lookup-plan.png” alt=”Execution plan containing a Key Lookup” caption=”A Key Lookup is required when selected columns are absent from the index.”]
CREATE NONCLUSTERED INDEX IX_Employees_FirstName
ON dbo.Employees (FirstName)
INCLUDE (LastName, Title)
WITH (ONLINE = ON, DROP_EXISTING = ON);[company_article_image src=”articles/index-architecture/12-covering-index-plan.png” alt=”Index Seek plan without a Key Lookup” caption=”The covering index removes the lookup in this example.”]
Operational options
FILLFACTOR controls leaf-page fullness at create or rebuild time; it does not preserve free space permanently. PAD_INDEX extends the fill-factor behavior to non-leaf levels. SORT_IN_TEMPDB moves temporary sort work to tempdb, while MAXDOP limits parallelism for the index operation.
DROP_EXISTING can rebuild an existing index under a new definition. ROW and PAGE compression may reduce storage and buffer-pool usage at a CPU cost. Every option should be tested against the actual workload and supported product configuration.
Decision checklist
- Measure the query with representative data and parameters.
- Review actual plan, logical reads, CPU and elapsed time together.
- Check whether an existing index can satisfy the requirement.
- Account for INSERT, UPDATE, DELETE, storage and maintenance cost.
- Test safely, deploy with a rollback plan and measure again.
Good index design does not mean adding an index for every query. It means creating a measurable balance between read benefit and the write and maintenance cost of the whole workload.