SQL SERVER – Index Levels, Page Count, Record Count and DMV – sys.dm_db_index_physical_stats

In the recent Query Tuning project, one of the developers who were helping me out in the project asked me if there is any way that he could know how many pages are used by any Index,  and if there is any way I could demonstrate the different levels ofB-Tree.

The following is the diagram on Clustered Index that I have quickly drawn using MS Word for the said developer.

Clustered Index B-Tree

Let us quickly see the diagram of B-Tree and how the levels are set up. The leaf level is always considered as Level 0. There can be many levels of the intermediate nodes. In the example above, I have listed only one intermediate node for demonstration purposes.

We can use Dynamic Management Views to figure out how many different levels are there for any Index, as well as how many rows are stored at each level and the number of pages used for all Index.

Let us run the following commands and generate a table with data. We will create a Clustered Index so we can have B-Tree structure.

USE tempdb
GO-- Create Table FragTableCREATE TABLE FragTable (ID CHAR(800),FirstName CHAR(2000),LastName CHAR(3000),City CHAR(2253))GO-- Create Clustered IndexCREATE CLUSTERED INDEX [IX_FragTable_ID] ON FragTable([ID] ASC) ON [PRIMARY]
GO-- Insert one Million RecordsINSERT 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 aCROSS JOIN sys.all_objects b
GO-- Check the percentagesSELECT avg_page_space_used_in_percent,avg_fragmentation_in_percent,index_level,record_count,page_count,fragment_count,avg_record_size_in_bytesFROMsys.dm_db_index_physical_stats(DB_ID('TempDb'),OBJECT_ID('FragTable'),NULL,NULL,'DETAILED')GO-- Clean upDROP TABLE FragTable
GO

Now let us check the result-set of the table.

From the resultset above, we can see that there are multiple levels of the Index. In our example, we have 4 levels of Index, and each level has different numbers of  pages and rows. In one of the future articles I will post, we will analyze the result in a deeper sense.