Indexing in Depth

33.1 B+Tree Structure

[Root Page]
         /     |     \
  [Intermediate pages]
   /  |  \     ...
[Leaf pages] ↔ [Leaf] ↔ [Leaf]   (doubly linked)

33.2 Clustered Index

Defines the physical (logical) order of the data. One per table.

Ideal key: narrow, unique, static, ever-increasing (e.g., INT IDENTITY).

CREATE CLUSTERED INDEX CIX_Orders_OrderDate ON dbo.Orders(OrderDate);

33.3 Nonclustered Index

A separate structure. The leaf level holds key columns plus a row locator (the clustering key or a RID).

Up to 999 per table. Key limits: 32 columns and 1,700 bytes.

CREATE NONCLUSTERED INDEX IX_Orders_CustomerID ON dbo.Orders(CustomerID)
INCLUDE (OrderDate, Status)              -- covering columns stored at the leaf only
WITH (FILLFACTOR = 90, ONLINE = ON, DATA_COMPRESSION = PAGE, SORT_IN_TEMPDB = ON);

33.4 Index Types

Multiple key columns. Column order matters (leftmost prefix).

Includes every column a query needs (no lookups) Has a WHERE predicate: CREATE INDEX IX ON dbo.Orders(Status) WHERE Status = 'Pending' Column-oriented storage (Module 38) XML, Spatial, Full-text, JSON, Vector Key sort direction

RESUMABLE = ON for index build/rebuild (2017+/2019+)

33.5 Key Lookups and Covering

A Key Lookup (or RID Lookup) happens when a nonclustered index doesn't cover the query. It's expensive at large row counts.

Fix it by adding INCLUDE columns or adjusting the keys.

33.6 Index Design Guidelines

Index WHERE, JOIN, ORDER BY, and GROUP BY columns.

Equality columns first, then inequality columns, then INCLUDE the rest.

Selectivity matters, but the query pattern matters more.

Don't over-index. Every index slows INSERT, UPDATE, and DELETE and consumes space and memory.

Index foreign key columns.

Remove unused and duplicate indexes.

33.7 SARGability (Search ARGument-able)

-- ❌ Non-SARGable (index scan)
WHERE YEAR(OrderDate) = 2025
WHERE LEFT(LastName, 1) = 'S'
WHERE UnitPrice * 1.1 > 100
WHERE ISNULL(Status, '') = 'Pending'
WHERE CustomerCode = 12345      -- if CustomerCode is VARCHAR → implicit conversion

-- ✅ SARGable (index seek)
WHERE OrderDate >= '2025-01-01' AND OrderDate < '2026-01-01'
WHERE LastName LIKE 'S%'
WHERE UnitPrice > 100 / 1.1
WHERE Status = 'Pending'
WHERE CustomerCode = '12345'

33.8 Fragmentation and Maintenance

Internal fragmentation: empty space within pages. External (logical) fragmentation: out-of-order pages.

Caused by page splits (random inserts and updates that grow rows).

Traditional guidance: 5–30% → REORGANIZE, >30% → REBUILD. On modern SSD storage, statistics updates often matter more than defragmentation. Use Ola Hallengren's maintenance solution.

SELECT OBJECT_NAME(ips.object_id) AS TableName, i.name, ips.index_type_desc,
       ips.avg_fragmentation_in_percent, ips.page_count
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'LIMITED') ips
JOIN sys.indexes i ON i.object_id = ips.object_id AND i.index_id = ips.index_id
WHERE ips.page_count > 1000 ORDER BY ips.avg_fragmentation_in_percent DESC;

ALTER INDEX IX_Orders_CustomerID ON dbo.Orders REORGANIZE;              -- online, light
ALTER INDEX IX_Orders_CustomerID ON dbo.Orders REBUILD WITH (ONLINE = ON);  -- also updates stats
ALTER INDEX ALL ON dbo.Orders REBUILD;
ALTER INDEX IX_Orders_CustomerID ON dbo.Orders DISABLE;

33.9 Index DMVs

-- Index usage
SELECT OBJECT_NAME(s.object_id) tbl, i.name, s.user_seeks, s.user_scans, s.user_lookups, s.user_updates
FROM sys.dm_db_index_usage_stats s JOIN sys.indexes i ON i.object_id = s.object_id AND i.index_id = s.index_id
WHERE s.database_id = DB_ID();

-- Missing index suggestions (evaluate critically!)
SELECT d.statement, d.equality_columns, d.inequality_columns, d.included_columns,
       s.user_seeks, s.avg_user_impact
FROM sys.dm_db_missing_index_details d
JOIN sys.dm_db_missing_index_groups g ON g.index_handle = d.index_handle
JOIN sys.dm_db_missing_index_group_stats s ON s.group_handle = g.index_group_handle
ORDER BY s.user_seeks * s.avg_user_impact DESC;

-- Operational stats (locking, latching, page splits)
SELECT * FROM sys.dm_db_index_operational_stats(DB_ID(), NULL, NULL, NULL);

33.10 Data Compression

Row compression: variable-length storage of fixed types.

Page compression: row compression plus prefix and dictionary compression.

Columnstore and columnstore archive compression.

COMPRESS() / DECOMPRESS() (GZIP) for individual values.

EXEC sp_estimate_data_compression_savings 'dbo', 'Orders', NULL, NULL, 'PAGE';
ALTER TABLE dbo.Orders REBUILD WITH (DATA_COMPRESSION = PAGE);