In a recent query tuning project, one of the developers asked me if there was a way to determine how many pages are used by an index and if I could demonstrate the different levels of a B-Tree. In this article, we will explore the B-Tree structure of a clustered index and how to use Dynamic Management Views to gather information about index levels, rows, and pages.

The B-Tree Structure

The B-Tree structure is a hierarchical data structure used by SQL Server to organize and access data efficiently. In this structure, the leaf level is considered Level 0, and there can be multiple levels of intermediate nodes. Let’s take a look at a diagram of a clustered index B-Tree:

As you can see from the diagram, the leaf level is at the bottom, and there can be several intermediate nodes above it. Each level contains a different number of pages and rows.

Gathering Information about Index Levels

To gather information about the levels of an index, as well as the number of rows and pages at each level, we can use Dynamic Management Views. Let’s run the following commands to create a table with data and a clustered index:

USE tempdb
GO

-- Create Table FragTable
CREATE TABLE FragTable (
    ID CHAR(800),
    FirstName CHAR(2000),
    LastName CHAR(3000),
    City CHAR(2253)
)
GO

-- Create Clustered Index
CREATE CLUSTERED INDEX [IX_FragTable_ID] ON FragTable ([ID] ASC) ON [PRIMARY]
GO

-- Insert one Million Records
INSERT INTO FragTable (ID, FirstName, LastName, City)
SELECT TOP 100 ROW_NUMBER() OVER (ORDER BY a.name) RowID,
    'Bob',
    CASE WHEN ROW_NUMBER() OVER (ORDER BY a.name) % 2 = 1 THEN 'Smith' ELSE 'Brown' END,
    CASE WHEN ROW_NUMBER() OVER (ORDER BY a.name) % 10 = 1 THEN 'New York'
         WHEN ROW_NUMBER() OVER (ORDER BY a.name) % 10 = 5 THEN 'San Marino'
         WHEN ROW_NUMBER() OVER (ORDER BY a.name) % 10 = 3 THEN 'Los Angeles'
         ELSE 'Houston' END
FROM sys.all_objects a
CROSS JOIN sys.all_objects b
GO

-- Check the percentages
SELECT avg_page_space_used_in_percent,
    avg_fragmentation_in_percent,
    index_level,
    record_count,
    page_count,
    fragment_count,
    avg_record_size_in_bytes
FROM sys.dm_db_index_physical_stats(DB_ID('TempDb'), OBJECT_ID('FragTable'), NULL, NULL, 'DETAILED')
GO

-- Clean up
DROP TABLE FragTable

After running the above commands, we can examine the result set. From the result set, we can see that there are multiple levels of the index. In our example, we have 4 levels of the index, and each level has a different number of pages and rows.

In a future article, we will analyze the result set in more depth and explore how to interpret the information gathered from the Dynamic Management Views.