SQL Server Index Internals: B-Tree, Page Splits, Fill Factor and Fragmentation (2026)

B-Tree Structure: Root, Intermediate, and Leaf Pages

SQL Server stores every index — both clustered and non-clustered — as a Balanced Tree (B-tree). The tree has three tiers of 8 KB pages: the root page at the top, zero or more levels of intermediate (non-leaf) pages in the middle, and the leaf level at the bottom.

Conceptually, picture a three-level structure like this:

                  [ Root Page ]
                  /            \
        [Intermediate]    [Intermediate]
        /      \               /      \
   [Leaf]  [Leaf]        [Leaf]   [Leaf]

Each non-leaf page contains index key values paired with page pointers — the page IDs of the child pages whose key range starts at that value. The root page is the entry point for every single-row lookup. Intermediate pages are navigated top-down. The leaf level is where actual data lives.

For a clustered index, the leaf pages are the data pages — there is no separate copy of the row. The entire row is stored in key order at the leaf level. For a non-clustered index, the leaf pages contain the index key columns plus a row locator: either the clustered index key (on a table with a clustered index) or the physical row identifier (RID) on a heap.

The leaf pages are also doubly linked — each page holds a pointer to the previous and next leaf page in key order. This enables efficient range scans without returning to the root after each leaf page.

Leaf Page Anatomy: The 8 KB Page

Every storage allocation in SQL Server is an 8 KB (8,192 byte) page. Of those bytes, 96 are reserved for the page header (page ID, page type, log sequence number, free-space pointer, etc.), leaving 8,060 bytes for row data and the slot array.

The layout of a single page looks like this:

┌─────────────────────────────────────────────────┐
│  Page Header (96 bytes)                         │
│  page_id, page_type, lsn, free_space_offset ... │
├─────────────────────────────────────────────────┤
│  Row 1                                          │
│  Row 2                                          │
│  Row 3   ...rows grow down from the top...      │
│                                                 │
│  (free space)                                   │
│                                                 │
│  ...slot array grows up from the bottom...      │
│  Slot 3 offset  │  Slot 2 offset  │  Slot 1 ... │
└─────────────────────────────────────────────────┘

The slot array is a two-byte-per-slot array at the bottom of the page. Each slot stores the byte offset of one row. Rows are not required to sit in key order inside the page — the slot array imposes the logical sort order. When SQL Server needs the Nth row in key order, it looks up slot N, reads its offset, and jumps directly to that row. This means a DELETE followed by an INSERT does not require physically shifting rows — just updating the slot array and writing the new row into free space.

For a clustered index leaf page, each row is the complete row including all non-key columns. For a non-clustered index leaf page, each row contains: included columns + key columns + the row locator (clustered key or RID).

Navigating the B-Tree: 3–4 I/Os for Millions of Rows

Consider finding a single row in a table with 50 million rows. SQL Server starts at the root page. It binary-searches the key entries on the root page to find the child pointer for the relevant key range, reads that intermediate page, binary-searches again, and follows the pointer to the leaf page. The leaf page contains the actual row data.

A three-level B-tree therefore resolves any single-row lookup in exactly 3 logical reads: root + one intermediate + one leaf. In practice, the root and upper intermediate pages for large indexes are almost always already in the buffer pool (because they are accessed on every query), so most lookups cost only 1–2 physical I/Os.

A four-level tree handles tables with hundreds of millions of rows and still costs only 4 logical reads per lookup. This logarithmic growth rate — O(log N) — is why indexes are so powerful compared to table scans, which are O(N).

-- Verify the tree depth of an index
SELECT
    i.name                        AS index_name,
    s.index_depth,
    s.page_count,
    s.record_count,
    s.avg_fragmentation_in_percent
FROM sys.indexes i
CROSS APPLY sys.dm_db_index_physical_stats(
    DB_ID(), i.object_id, i.index_id, NULL, 'LIMITED') s
WHERE OBJECT_NAME(i.object_id) = 'YourTable'
ORDER BY i.index_id;

The index_depth column shows the number of levels. For a 10-million-row table with a 4-byte integer key you would typically see index_depth = 3. For a 36-byte GUID key on the same table you might see index_depth = 4 because each non-leaf page holds fewer pointers.

Page Splits: What Triggers Them

A page split occurs when SQL Server needs to insert a new row onto an index page that is already full. Because B-tree pages must maintain key order, SQL Server cannot simply write the row onto the next available page — it must insert the row into the correct position in the ordered sequence.

When the target page has no free space, SQL Server performs a page split:

1. Allocates a new 8 KB page from the database file.
2. Moves approximately half the rows from the full page to the new page.
3. Updates the doubly-linked leaf-level pointers to thread the new page into the chain.
4. Updates the parent (intermediate) page to add a pointer to the new page.
5. Writes the new row to whichever of the two pages it belongs in.

The split type depends on the insert pattern:

• 50/50 split — random key inserts (e.g., GUID or unordered natural keys). The full page ends up approximately 50% full and the new page approximately 50% full. Results in high logical fragmentation.
• 90/10 split — sequential key inserts (e.g., IDENTITY, monotonically increasing datetime). SQL Server detects the pattern and moves only about 10% of the rows to the new page, keeping the old page nearly full. Much less fragmentation, but still requires allocating a new page.

-- Compare: random GUID vs sequential GUID
-- Random GUID (causes 50/50 splits):
CREATE TABLE dbo.RandomGuid (
    id   UNIQUEIDENTIFIER DEFAULT NEWID() NOT NULL PRIMARY KEY,
    val  INT NOT NULL
);

-- Sequential GUID (causes 90/10 splits, much less fragmentation):
CREATE TABLE dbo.SeqGuid (
    id   UNIQUEIDENTIFIER DEFAULT NEWSEQUENTIALID() NOT NULL PRIMARY KEY,
    val  INT NOT NULL
);

Page Split Impact: Fragmentation, Log, and PAGELATCH Waits

Every page split has compounding costs beyond the immediate I/O overhead:

Transaction log amplification

A single split can generate dozens of log records: the allocation of the new page, the before-and-after images of both pages, the update to the parent intermediate page, and possibly a cascading split if the parent is also full. Under heavy random insert workloads, page splits can account for 30–50% of total log generation.

Logical fragmentation

After a 50/50 split, the original page and the new page are linked in logical order but are physically adjacent in the file only if the new page happened to be allocated from the next contiguous extent. In practice, the new page is usually allocated from a mixed-extent or a non-contiguous location, so the logical and physical ordering diverge. Read-ahead (sequential I/O optimization) breaks down when pages are out of physical order, which degrades range scan performance — particularly on spinning disk.

PAGELATCH_EX waits

During a split, SQL Server holds an exclusive page latch on both the original page and the new page for the duration of the operation. High-throughput insert workloads targeting the same hot page — a common pattern with IDENTITY keys on a single-file database — serialize on PAGELATCH_EX waits. The fix is usually to partition the table, add multiple data files (for tempdb-style workloads), or use a fill factor that reduces split frequency.

TempDB activity

Certain split operations require sort work in TempDB — particularly during online index rebuilds or when version store entries are required for snapshot isolation. A sustained page-split storm can cause unexpected TempDB version store growth.

Demonstrating Page Splits with Random GUIDs

The following script creates a test table with a random GUID primary key, inserts 50,000 rows to force page splits, then measures the resulting fragmentation using sys.dm_db_index_physical_stats.

-- Step 1: Create a table with a random GUID PK (worst case for splits)
USE tempdb;
GO

DROP TABLE IF EXISTS dbo.SplitDemo;
CREATE TABLE dbo.SplitDemo (
    id        UNIQUEIDENTIFIER DEFAULT NEWID() NOT NULL PRIMARY KEY,
    payload   CHAR(200)        NOT NULL DEFAULT REPLICATE('X', 200)
);
GO

-- Step 2: Capture baseline fragmentation (should be 0%)
SELECT
    index_id,
    avg_fragmentation_in_percent,
    page_count,
    avg_page_space_used_in_percent
FROM sys.dm_db_index_physical_stats(
    DB_ID('tempdb'),
    OBJECT_ID('tempdb..SplitDemo'),
    NULL, NULL, 'SAMPLED');
GO

-- Step 3: Insert 50,000 rows with random GUIDs
INSERT INTO dbo.SplitDemo (id, payload)
SELECT NEWID(), REPLICATE('X', 200)
FROM (SELECT TOP 50000 ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) rn
      FROM sys.all_columns a CROSS JOIN sys.all_columns b) x;
GO

-- Step 4: Re-check fragmentation (expect 80-99% for random GUIDs)
SELECT
    index_id,
    avg_fragmentation_in_percent,
    page_count,
    avg_page_space_used_in_percent,
    fragment_count,
    avg_fragment_size_in_pages
FROM sys.dm_db_index_physical_stats(
    DB_ID('tempdb'),
    OBJECT_ID('tempdb..SplitDemo'),
    NULL, NULL, 'SAMPLED');
GO

On a typical run, avg_fragmentation_in_percent reaches 95–99% and avg_page_space_used_in_percent drops to around 50%, confirming the 50/50 split pattern. Running the same test with NEWSEQUENTIALID() produces fragmentation under 1% with page density above 95%.

External Fragmentation: Out-of-Order Pages

External fragmentation is measured by avg_fragmentation_in_percent in sys.dm_db_index_physical_stats. It represents the percentage of leaf pages whose logical ordering does not match their physical ordering on disk — i.e., the slot-array linked list says "next page is page 412" but page 412 is physically located before page 390 in the file.

External fragmentation matters primarily on spinning disk (HDD) because HDDs pay a seek-time penalty when the read-ahead mechanism has to skip around the disk platters to follow a fragmented logical sequence. On a modern SSD, random reads are nearly as fast as sequential reads, so external fragmentation has little impact on scan performance — though it still inflates page count (due to half-full pages from 50/50 splits), which increases I/O volume.

-- Query fragmentation for all indexes in a database
-- Run in the context of the target database
SELECT
    OBJECT_SCHEMA_NAME(i.object_id)    AS schema_name,
    OBJECT_NAME(i.object_id)           AS table_name,
    i.name                             AS index_name,
    s.index_type_desc,
    s.avg_fragmentation_in_percent,
    s.page_count,
    s.record_count,
    s.avg_page_space_used_in_percent,
    s.fragment_count
FROM sys.indexes i
CROSS APPLY sys.dm_db_index_physical_stats(
    DB_ID(), i.object_id, i.index_id, NULL, 'LIMITED') s
WHERE s.page_count > 100   -- ignore tiny indexes
  AND s.index_type_desc NOT LIKE '%HEAP%'
ORDER BY s.avg_fragmentation_in_percent DESC;

Internal Fragmentation: Wasted Space Within Pages

Internal fragmentation is the flip side of external fragmentation. It is measured by avg_page_space_used_in_percent — a low value means each page is mostly empty. After a wave of 50/50 page splits, you might have 10,000 pages that are each only 50% full, where 5,000 fully-packed pages would suffice. This doubles the I/O cost of every range scan, because the storage engine has to read twice as many physical pages to retrieve the same amount of data.

Internal fragmentation is also caused by excessive fill factor. Setting fill factor to 50% intentionally leaves half of every page empty after a rebuild. If the table has no inserts (a read-only reporting table), that 50% empty space is permanently wasted — you are storing the data in twice as many pages as necessary and doubling your scan I/O cost.

The ideal is to balance internal and external fragmentation: enough free space to absorb inserts without splits, but not so much that page density drops significantly and inflates I/O.

-- Find indexes with high internal fragmentation (low page density)
SELECT
    OBJECT_NAME(i.object_id)           AS table_name,
    i.name                             AS index_name,
    s.avg_page_space_used_in_percent   AS page_density_pct,
    s.page_count,
    s.avg_fragmentation_in_percent     AS logical_frag_pct
FROM sys.indexes i
CROSS APPLY sys.dm_db_index_physical_stats(
    DB_ID(), i.object_id, i.index_id, NULL, 'SAMPLED') s
WHERE s.page_count > 100
  AND s.avg_page_space_used_in_percent < 60   -- wasted space threshold
ORDER BY s.avg_page_space_used_in_percent;

Fill Factor: What It Is and How to Choose

Fill factor is a value between 1 and 100 that tells SQL Server how full to pack each non-leaf page (and, depending on version, leaf pages) when it builds or rebuilds an index. A fill factor of 80 means SQL Server leaves 20% of each page empty after a rebuild, providing a buffer for future inserts without triggering immediate page splits.

Fill factor is only applied at rebuild time. It is not maintained dynamically — pages fill up naturally as rows are inserted, and fragmentation accumulates again until the next rebuild. Think of it as the "reset" setting, not a live governor.

Fill Factor Recommendations by Workload Pattern

Setting Fill Factor: Index-Level vs Server Default

You can control fill factor at three scopes: server-wide default, per-index at creation time, and per-index at rebuild time.

-- 1. Check and set the server-wide fill factor default
EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
EXEC sp_configure 'fill factor';
-- Change the server default to 80:
EXEC sp_configure 'fill factor', 80;
RECONFIGURE;
GO

-- 2. Set fill factor when creating an index
CREATE NONCLUSTERED INDEX IX_Orders_CustomerDate
ON dbo.Orders (customer_id, order_date)
INCLUDE (order_total, status)
WITH (FILLFACTOR = 80, ONLINE = ON);
GO

-- 3. Rebuild a specific index with a new fill factor
ALTER INDEX IX_Orders_CustomerDate
ON dbo.Orders
REBUILD WITH (FILLFACTOR = 75, ONLINE = ON, SORT_IN_TEMPDB = ON);
GO

-- 4. Rebuild all indexes on a table with a unified fill factor
ALTER INDEX ALL ON dbo.Orders
REBUILD WITH (FILLFACTOR = 80, ONLINE = ON);
GO

-- 5. View the currently stored fill factor for each index
SELECT
    OBJECT_NAME(i.object_id) AS table_name,
    i.name                   AS index_name,
    i.fill_factor,
    i.type_desc
FROM sys.indexes i
WHERE OBJECT_NAME(i.object_id) = 'Orders'
  AND i.fill_factor > 0
ORDER BY i.index_id;

Rebuild vs Reorganize: Decision Script

SQL Server provides two index maintenance operations with very different characteristics:

ALTER INDEX REORGANIZE

• Defragments the leaf level in place by physically rearranging pages to match logical order.
• Always online — holds only short-duration page locks, never blocks reads or writes for more than milliseconds.
• Generates minimal transaction log (no row moves, just page re-ordering).
• Does not apply fill factor — pages are compacted but not padded.
• Does not update statistics.
• Suitable for fragmentation in the 5–30% range.

ALTER INDEX REBUILD

• Drops and recreates the entire index from scratch.
• Offline by default (table lock); online with ONLINE = ON on Enterprise Edition.
• Applies the stored fill factor, resetting page density to the configured value.
• Updates statistics to 100% sample — equivalent to running UPDATE STATISTICS ... WITH FULLSCAN.
• Generates significant transaction log (full before/after images of every page).
• Suitable for fragmentation above 30% or when fill factor needs to be reset.

-- Industry-standard decision script using sys.dm_db_index_physical_stats
-- Run this as a basis for a maintenance stored procedure

DECLARE @SchemaName  SYSNAME;
DECLARE @TableName   SYSNAME;
DECLARE @IndexName   SYSNAME;
DECLARE @Frag        FLOAT;
DECLARE @PageCount   BIGINT;
DECLARE @SQL         NVARCHAR(2000);

DECLARE cur CURSOR FAST_FORWARD FOR
    SELECT
        OBJECT_SCHEMA_NAME(i.object_id),
        OBJECT_NAME(i.object_id),
        i.name,
        s.avg_fragmentation_in_percent,
        s.page_count
    FROM sys.indexes i
    CROSS APPLY sys.dm_db_index_physical_stats(
        DB_ID(), i.object_id, i.index_id, NULL, 'LIMITED') s
    WHERE s.page_count > 100           -- skip trivially small indexes
      AND i.type > 0                   -- skip heaps (type = 0)
      AND i.is_disabled = 0;

OPEN cur;
FETCH NEXT FROM cur INTO @SchemaName, @TableName, @IndexName,
                          @Frag, @PageCount;

WHILE @@FETCH_STATUS = 0
BEGIN
    IF @Frag >= 30
    BEGIN
        SET @SQL = N'ALTER INDEX ' + QUOTENAME(@IndexName)
                 + N' ON ' + QUOTENAME(@SchemaName) + N'.' + QUOTENAME(@TableName)
                 + N' REBUILD WITH (ONLINE = ON, SORT_IN_TEMPDB = ON);';
        PRINT 'REBUILD: ' + @SQL;
        -- EXEC sp_executesql @SQL;  -- uncomment to execute
    END
    ELSE IF @Frag >= 5
    BEGIN
        SET @SQL = N'ALTER INDEX ' + QUOTENAME(@IndexName)
                 + N' ON ' + QUOTENAME(@SchemaName) + N'.' + QUOTENAME(@TableName)
                 + N' REORGANIZE;';
        PRINT 'REORGANIZE: ' + @SQL;
        -- EXEC sp_executesql @SQL;  -- uncomment to execute
    END
    ELSE
        PRINT 'OK (< 5%): ' + @SchemaName + '.' + @TableName
              + ' -> ' + @IndexName;

    FETCH NEXT FROM cur INTO @SchemaName, @TableName, @IndexName,
                              @Frag, @PageCount;
END;

CLOSE cur;
DEALLOCATE cur;

Online vs Offline Rebuild: Locking Behaviour

By default, ALTER INDEX REBUILD acquires a Schema Modification (Sch-M) lock on the table for the entire duration of the rebuild. This blocks all reads and writes — effectively taking the table offline. For a 50 GB table, an offline rebuild might run for 20–40 minutes, which is unacceptable during business hours.

ONLINE = ON changes the locking strategy dramatically:

1. SQL Server acquires a short Sch-S (Schema Stability) lock at the start to read the table definition.
2. The rebuild builds the new index structure alongside the existing one, allowing reads and writes to continue against the old structure.
3. Changes made to the table during the rebuild are tracked in a delta mapping table.
4. At completion, SQL Server acquires a very brief Sch-M lock (typically milliseconds) to apply the delta and swap the old index with the new one.

-- Online rebuild with MAXDOP and wait-at-low-priority options
ALTER INDEX IX_Orders_CustomerDate
ON dbo.Orders
REBUILD WITH (
    ONLINE = ON (
        WAIT_AT_LOW_PRIORITY (
            MAX_DURATION = 5 MINUTES,
            ABORT_AFTER_WAIT = SELF
        )
    ),
    MAXDOP      = 4,
    FILLFACTOR  = 80,
    SORT_IN_TEMPDB = ON
);

The WAIT_AT_LOW_PRIORITY option (SQL Server 2014+) tells the engine: "wait up to 5 minutes for the final Sch-M lock at low priority; if you cannot get it, abort the rebuild rather than blocking high-priority queries." This prevents maintenance jobs from disrupting busy OLTP workloads.

Ola Hallengren Index Maintenance: The Industry Standard

Writing custom maintenance scripts from scratch is unnecessary. Ola Hallengren's SQL Server Maintenance Solution is the industry-standard open-source toolkit used by the majority of production SQL Server installations worldwide. The IndexOptimize stored procedure handles all index and statistics maintenance with sophisticated logic built in.

What IndexOptimize does

• Queries sys.dm_db_index_physical_stats with configurable mode (LIMITED / SAMPLED / DETAILED).
• Applies configurable thresholds for reorganize vs rebuild (default: reorganize 5–30%, rebuild above 30%).
• Respects ONLINE and SORT_IN_TEMPDB options, with edition detection (falls back to offline if not Enterprise).
• Updates statistics separately on indexes not rebuilt (customizable).
• Logs all operations to a CommandLog table for auditing and trend analysis.
• Supports include/exclude filters by database, schema, table, and index name — pattern wildcards supported.
• Handles partitioned tables correctly, maintaining each partition independently.

-- Basic IndexOptimize call: maintain all indexes in the current database
EXEC dbo.IndexOptimize
    @Databases          = 'USER_DATABASES',
    @FragmentationLow   = NULL,               -- no action for low frag
    @FragmentationMedium= 'INDEX_REORGANIZE', -- reorganize 5–30%
    @FragmentationHigh  = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
    @FragmentationLevel1= 5,
    @FragmentationLevel2= 30,
    @MinNumberOfPages   = 100,
    @SortInTempdb       = 'Y',
    @MaxDOP             = 4,
    @UpdateStatistics   = 'ALL',
    @OnlyModifiedStatistics = 'Y',
    @LogToTable         = 'Y';

Scheduling with SQL Agent

The recommended schedule for most OLTP systems is:

• Nightly (off-peak hours): full IndexOptimize run with LIMITED mode fragmentation check.
• Weekly (weekend): SAMPLED or DETAILED mode for more accurate fragmentation measurement.
• After bulk loads: run targeted maintenance on the affected tables immediately using DETAILED mode.

Monitoring Fragmentation Over Time

A one-off fragmentation check tells you the current state. A trend over time tells you the rate of fragmentation growth — which determines whether your maintenance schedule is adequate and which indexes are generating the most churn.

The following script creates a lightweight fragmentation history table and a scheduled collection procedure. Run it as a daily SQL Agent job to build a 90-day trend.

-- Step 1: Create history table (run once)
CREATE TABLE dbo.IndexFragmentationHistory (
    captured_at                  DATETIME2        NOT NULL DEFAULT SYSDATETIME(),
    database_name                SYSNAME          NOT NULL,
    schema_name                  SYSNAME          NOT NULL,
    table_name                   SYSNAME          NOT NULL,
    index_name                   SYSNAME          NOT NULL,
    index_type_desc              NVARCHAR(60)     NOT NULL,
    avg_fragmentation_pct        FLOAT            NOT NULL,
    avg_page_space_used_pct      FLOAT            NOT NULL,
    page_count                   BIGINT           NOT NULL,
    record_count                 BIGINT           NOT NULL,
    fragment_count               BIGINT           NOT NULL,
    index_depth                  TINYINT          NOT NULL
);
GO

-- Step 2: Collection stored procedure
CREATE OR ALTER PROCEDURE dbo.CollectIndexFragmentation
AS
BEGIN
    SET NOCOUNT ON;

    INSERT INTO dbo.IndexFragmentationHistory (
        database_name, schema_name, table_name, index_name,
        index_type_desc, avg_fragmentation_pct, avg_page_space_used_pct,
        page_count, record_count, fragment_count, index_depth
    )
    SELECT
        DB_NAME()                          AS database_name,
        OBJECT_SCHEMA_NAME(i.object_id)   AS schema_name,
        OBJECT_NAME(i.object_id)          AS table_name,
        ISNULL(i.name, 'HEAP')            AS index_name,
        s.index_type_desc,
        s.avg_fragmentation_in_percent,
        s.avg_page_space_used_in_percent,
        s.page_count,
        s.record_count,
        s.fragment_count,
        s.index_depth
    FROM sys.indexes i
    CROSS APPLY sys.dm_db_index_physical_stats(
        DB_ID(), i.object_id, i.index_id, NULL, 'LIMITED') s
    WHERE s.page_count > 100
      AND i.type IN (1, 2);   -- clustered and non-clustered only

    -- Purge rows older than 90 days
    DELETE FROM dbo.IndexFragmentationHistory
    WHERE captured_at < DATEADD(DAY, -90, SYSDATETIME());
END;
GO

-- Step 3: Query to see fragmentation trend for a specific index
SELECT
    CAST(captured_at AS DATE)       AS capture_date,
    avg_fragmentation_pct,
    avg_page_space_used_pct,
    page_count
FROM dbo.IndexFragmentationHistory
WHERE table_name = 'Orders'
  AND index_name = 'IX_Orders_CustomerDate'
ORDER BY captured_at;

A healthy trend shows fragmentation building slowly from near 0% after a rebuild to perhaps 15–25% by the next maintenance window. If you see fragmentation hitting 80%+ within 24 hours of a rebuild, the write pattern is generating extreme page splits — investigate fill factor, key design (random vs sequential), and whether a table partition or key change would be more effective than more frequent rebuilds.

Pair this fragmentation data with query performance trends from your monitoring tool (or from execution plan analysis) to confirm that fragmentation growth correlates with scan performance degradation. On SSD-based systems you may find that fragmentation has little measurable impact on query duration, in which case you can safely extend your rebuild intervals and reduce maintenance overhead.

Read Next