Clustered Indexes, Nonclustered Indexes, Row Locators, and Page Splits

Learning outcomes

ServiceHub now needs both fast point lookups and chronological operational scans. That requires a precise model of rowstore indexes. SQL Server documentation often says “B-tree”; rowstore indexes are implemented as a B+ tree: nonleaf levels guide navigation and the leaf level contains either the clustered data rows or nonclustered index rows. This lesson connects that structure to row locators, splits, density, fragmentation, and the separate problem of last-page latch contention.

1. The clustered leaf is the table

In a rowstore clustered index, the leaf level contains the full data rows ordered by the clustering key. A nonclustered index has its own ordered leaf rows containing its keys, included columns, and a row locator. If the base object is clustered, the row locator is the clustering key; if the base is a heap, the locator is a RID. That is why a wide clustering key can silently widen every nonclustered index.

USE ServiceHubLab;GOIF SCHEMA_ID(N'lab08') IS NULL EXEC(N'CREATE SCHEMA lab08 AUTHORIZATION dbo;');DROP TABLE IF EXISTS lab08.WorkOrderIndexProbe;CREATE TABLE lab08.WorkOrderIndexProbe(  work_order_id bigint NOT NULL,  opened_at datetime2(3) NOT NULL,  status varchar(20) NOT NULL,  customer_id int NOT NULL,  summary varchar(500) NOT NULL,  CONSTRAINT PK_WorkOrderIndexProbe    PRIMARY KEY CLUSTERED(work_order_id));CREATE INDEX IX_WorkOrderIndexProbe_StatusOpenedON lab08.WorkOrderIndexProbe(status,opened_at)INCLUDE(customer_id);GO

2. Populate enough rows to make the tree visible

Very small indexes can be a single page, so a B+ tree lesson needs enough rows to create multiple pages. The data generator is deterministic and local; it is not a benchmark.

;WITH n AS( SELECT TOP (20000) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS n FROM sys.all_objects a CROSS JOIN sys.all_objects b)INSERT lab08.WorkOrderIndexProbe(work_order_id,opened_at,status,customer_id,summary)SELECT n,       DATEADD(minute,n,'2026-01-01T00:00:00'),       CASE WHEN n%7=0 THEN 'ESCALATED' ELSE 'OPEN' END,       1+n%1000,       REPLICATE('s',120)FROM n;GOSELECT i.name,ps.index_level,ps.index_depth,ps.page_count,       ps.avg_page_space_used_in_percent,       ps.avg_fragmentation_in_percentFROM sys.indexes AS iCROSS APPLY sys.dm_db_index_physical_stats (DB_ID(),OBJECT_ID(N'lab08.WorkOrderIndexProbe'),i.index_id,NULL,'DETAILED') AS psWHERE i.object_id=OBJECT_ID(N'lab08.WorkOrderIndexProbe')ORDER BY i.index_id,ps.index_level DESC;GO

Expect leaf rows at index_level=0. Higher levels appear as the tree grows. The exact page count, depth, density, and fragmentation are local observations.

3. Page splits, page density, and fragmentation are related but not identical

A page split occurs when SQL Server needs room in a B+ tree page and must allocate/reorganize pages. Mid-tree splits can create logical fragmentation and extra log/I/O work. Page density asks how full pages are; logical fragmentation asks how out-of-order leaf pages are relative to logical key order. A low fill factor intentionally lowers density, which can reduce some middle-page splits but increases the number of pages that must be cached and read.

The operational DMV exposes leaf_allocation_count; for an index, Microsoft documents a leaf page allocation as corresponding to a page split. Counters are cumulative and reset-sensitive, so take a before/after delta for a controlled lab.

SELECT leaf_allocation_count,nonleaf_allocation_count,       leaf_page_merge_count,range_scan_count,singleton_lookup_countFROM sys.dm_db_index_operational_stats (DB_ID(),OBJECT_ID(N'lab08.WorkOrderIndexProbe'),1,NULL);GO-- Create a separate random-GUID clustered structure for split observation.DROP TABLE IF EXISTS lab08.RandomKeyProbe;CREATE TABLE lab08.RandomKeyProbe(  id uniqueidentifier NOT NULL DEFAULT NEWID(),  payload char(300) NOT NULL DEFAULT REPLICATE('x',300),  CONSTRAINT PK_RandomKeyProbe PRIMARY KEY CLUSTERED(id));INSERT lab08.RandomKeyProbe DEFAULT VALUES;GO 2000SELECT leaf_allocation_count,nonleaf_allocation_countFROM sys.dm_db_index_operational_stats (DB_ID(),OBJECT_ID(N'lab08.RandomKeyProbe'),1,NULL);SELECT page_count,avg_page_space_used_in_percent,avg_fragmentation_in_percentFROM sys.dm_db_index_physical_stats (DB_ID(),OBJECT_ID(N'lab08.RandomKeyProbe'),1,NULL,'DETAILED')WHERE index_level=0;GO

GO 2000 is a client-utility repeat count supported by tools such as SSMS/sqlcmd, not T-SQL sent to the engine. If your client does not support repeat counts, use a set-based row generator instead.

4. Do not use fill factor to solve a different problem

Reducing fill factor reserves space on leaf pages when an index is built/rebuilt. Microsoft warns that unnecessarily low fill factor increases storage, memory, and I/O. It is most relevant when inserts are distributed into already-existing key ranges. Sequential inserts usually target the right edge; leaving empty space throughout earlier pages does not directly solve a hot last page.

Last-page contention is a latch-concurrency problem commonly visible as PAGELATCH_EX waits on a sequential-key index under high insert concurrency. SQL Server 2019+ offers OPTIMIZE_FOR_SEQUENTIAL_KEY as one option for eligible workloads. That is separate from logical fragmentation and must be diagnosed from waits and concurrency evidence.

SELECT name,fill_factor,optimize_for_sequential_key,is_disabledFROM sys.indexesWHERE object_id=OBJECT_ID(N'lab08.WorkOrderIndexProbe');GO-- Example only after proving sequential-key latch contention:-- ALTER INDEX PK_WorkOrderIndexProbe-- ON lab08.WorkOrderIndexProbe-- SET (OPTIMIZE_FOR_SEQUENTIAL_KEY = ON);GO

5. Production judgment and cleanup

Use physical stats to describe current structure and operational stats to describe cumulative activity. Correlate both with query plans, waits, I/O, and business latency. Avoid rebuild churn on tiny objects. Treat OPTIMIZE_FOR_SEQUENTIAL_KEY as a targeted concurrency feature, not a default checkbox. Rebuilds and fill-factor changes can be logged, CPU/I/O intensive, and edition/version-sensitive when online/resumable options are involved.

DROP TABLE IF EXISTS lab08.RandomKeyProbe;DROP TABLE IF EXISTS lab08.WorkOrderIndexProbe;GO

Authoritative references

• Index architecture and design guide — B+ trees and row locators
• sys.dm_db_index_operational_stats — split and access counters
• Specify fill factor — page-split and density tradeoffs
• CREATE INDEX — OPTIMIZE_FOR_SEQUENTIAL_KEY
• SQL Server 2025 build versions — servicing baseline