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 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
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 (http://blog.SQLAuthority.com)

About these ads

13 thoughts on “SQL SERVER – Index Levels, Page Count, Record Count and DMV - sys.dm_db_index_physical_stats

  1. Pingback: SQL SERVER – Index Levels and Delete Operations – Page Level Observation Journey to SQL Authority with Pinal Dave

  2. Pingback: SQL SERVER – Disabled Index and Index Levels and B-Tree Journey to SQL Authority with Pinal Dave

  3. Do you know how to delete pages in sql related to a dnn site? Where do i look to in sql to find these pages? I have about 150 pages i need to delete and manually is very time consuming. I honestly can’t believe dnn does not have the feature of deleting multiple pages at the same time. I do not have a backup to restore to. If you know how, please PM my email. Thank you very much for your help and all your helpful posts.

  4. Pingback: SQL SERVER – Difference between Create Index – Drop Index – Rebuild Index – Quiz – Puzzle – 21 of 31 « SQL Server Journey with SQL Authority

  5. i want to make mlm database.
    that table is this.
    create table mlm(
    MemberID int,
    RootID int ,
    Position varchar(2),
    Name varchar(10),
    )
    insert into mlm values(1,0,’Left’,’A’)
    insert into mlm values(2,1,’Centre’,’B’)
    insert into mlm values(3,1,’Right’,’C’)
    insert into mlm values(4,1,’Left’,’D)
    insert into mlm values(5,2,’Centre’,’E’)
    insert into mlm values(6,2,’Right’,’F’)
    /*i wan to count total left id of 1 Member ID that is 4 */
    2 is 1,
    .
    but how to count in sql server this system

  6. Pingback: SQL SERVER – Identify Most Resource Intensive Queries – SQL in Sixty Seconds #029 – Video « SQL Server Journey with SQL Authority

  7. hi sir
    i want to manage the level of user for MLM
    plz give me solution of how to manage level

    ex-
    Memberid SponsorId
    100 NULL
    101 100
    102 100
    103 100
    104 101
    105 101
    106 101
    107 102
    108 102
    109 102
    110 103
    111 103
    112 103
    113 104


    from 104 to 113 has 0 level and 101,102 and 103 has been completed his 1st level and 100 has been completed his 2 level

    how to i manage the level of user dynamically after user registration

    plz help me sir

    • database design —–

      Memberid ———-SponsorId

      100 —————– NULL
      101 —————– 100
      102 —————– 100
      103 —————– 100
      104 —————– 101
      105 —————– 101
      106 —————– 101
      107 —————– 102
      108 —————– 102
      109 —————– 102
      110 —————– 103
      111 —————– 103
      112 —————– 103
      113 —————– 104

  8. Pingback: SQL SERVER – Weekly Series – Memory Lane – #036 | Journey to SQL Authority with Pinal Dave

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s