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 of B-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 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
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.
Reference: Pinal Dave (https://blog.sqlauthority.com)
