go backarticles

Articles of SQLschool.gr Team

sp_indexdetails – Get index details and internals

Antonios Chatzipavlis

Overview

Από τα θέματα που είναι σημαντικά και φυσικά έχουν απασχολήσει και θα συνεχίσουν να πράττουν αυτό είναι οι indexes και τα statistics αυτών.

Αρκετός είναι επίσης και ο χρόνος που δαπανάμε για κάνουμε monitoring και optimizing αυτούς χρησιμοποιώντας τα εργαλεία όπως τα DMVs/DMFs. Αυτό σημαίνει ότι θα πρέπει να εκτελεστούν αυτά μεμονωμένα και ίσως κάποιοι δεν γνωρίζουν την ύπαρξη τους.

Εδώ και αρκετό καιρό ήθελα να φτιάξω κάτι κυρίως για προσωπική χρήση που να μου δίνει την εικόνα που ήθελα με σκοπό να την χρησιμοποιώ στα μαθήματα και τις παρουσιάσεις που κάνω.

Πρέπει να ομολογήσω ότι αρκετά πράγματα που έχω στο μυαλό μου δεν τα έχω υλοποιήσει στην πρώτη έκδοση αυτής. Επίσης να τονίσω ξανά ότι έναυσμα για να ξεκινήσω την υλοποίηση της ήταν να καλύψω τις ανάγκες μου για την παρουσίαση των index internals κατά την διάρκεια των μαθημάτων που κάνω.

Η βασική ανάγκη που ήθελα να καλύψω ήταν να δείχνω την b-tree αρχιτεκτονική των indexes αποτυπώνοντας τις σελίδες που χρησιμοποιούνται σαν leaf level, non leaf level, για αυτούς που θέλουν να βουτήξουν στα index internals.

Φυσικά και μπορεί να χρησιμοποιηθεί και για άλλους σκοπούς αλλά πρέπει να επισημάνω ότι σε μεγάλους πίνακες η εκτέλεση της παίρνει αρκετό χρόνο. Τέλος να επισημάνω ότι η SP αυτή μπορεί υποστηρίζει SQL Server 2014 SP2 και πάνω.

The sp_indexdetails

Parameters

Οι παράμετροι που λαμβάνει είναι δύο:

  • @table_name (sysname): Είναι το όνομα του πίνακα στον οποίο θέλουμε να δούμε τον index. Καλό είναι να είναι της μορφής schema.table.
  • @table_idxid (int): Είναι το index id του index το οποίο μπορούμε να πάρουμε από το sys.indexes.

Results

Τα αποτελέσματα που βγάζει είναι αρκετά και για να γίνουν εύκολα κατανοητά έχω προσθέσει να εμφανίζεται ένα λεκτικό που να υποδηλώνει το αντίστοιχο section

Section 1 – Index basic information

Σε αυτό βλέπουμε βασικές πληροφορίες όπως σε ποια βάση είμαστε το όνομα του πίνακα αλλά και τα index properties.

Section 2 – Index columns and included columns

Σε αυτό βλέπουμε ποιο ή ποια είναι το/τα πεδία που αποτελούν τον index και αν έχει included columns ποια είναι αυτά.

Section 3 – Index fragmentation info

Σε αυτό βλέπουμε το fragmentation ανά partition και index level.

Section 4 – Index physical stats

Σε αυτό βλέπουμε τα δεδομένα που επιστρέφονται από την sys.dm_db_index_physical_stats με σκοπό να καταλάβουμε σε πρώτη φάση τα index internals.

Section 5 – Index Statistics

Σε αυτό βλέπουμε τα statistics που υπάρχουν για τον συγκεκριμένο index. Για την συλλογή αυτών έχω χρησιμοποιήσει την γνωστή DBCC SHOW_STATISTICS. Ο λόγος που χρησιμοποίησα τα νέα DMV είναι γιατί αυτά είναι στις δύο τελευταίες εκδόσεις και ήθελα να έχω την δυνατότητα να κάνω χρήση της SP αυτή και σε παλαιότερες εκδόσεις.

Section 6 – Index structure details

To section αυτό ήταν και η αιτία δημιουργίας της SP αυτής. Σε αυτό μπορούμε να δούμε την αρχιτεκτονική του index. Οι πληροφορίες που δίνει είναι:

  • partition_number: ο αριθμός του partition.
  • page_id: : ο αριθμός της σελίδας που έχει χρησιμοποιηθεί.
  • page_type: : ο τύπος της σελίδας.
  • index_level: : το level στο οποίο ανήκει η συγκεκριμένη σελίδα. Οι τιμές που επιστρέφονται σε αυτό είναι:
    • ROOT και είναι το root level του index,
    • ΝΟΝ-LEAF-Lx κάποιο από τα non leaf levels του index το οποίο δείχνω αυτό στο τέλος πχ NON-LEAF-L1, NON-LEAF-L2 κ.λ.π. Πρέπει να επισημάνω ότι το μικρότερο νούμερο είναι και το χαμηλότερο non leaf level,
    • LEAF είναι σελίδα που ανήκει στο leaf level του index
  • total_rows: δείχνει το συνολικό αριθμό των rows/values στο index_level.
  • page_rows: δείχνει το αριθμό των rows/values στην σελίδα.
  • page_free_bytes: δείχνει τον ελεύθερο χώρο της σελίδα σε bytes.
  • previous_page_id: δείχνει την προηγούμενη στην σειρά σελίδα ώστε να μπορώ να καταλάβω αν οι σελίδες είναι σε πραγματική σειρά ή έχω jumps που υποδηλώνουν fragmentation. Αν η τιμή είναι null τότε καταλαβαίνω ότι είναι η πρώτη σελίδα στο συγκεκριμένο index level.
  • next_page_id: δείχνει την επόμενη στην σειρά σελίδα και ισχύουν ότι ανέφερα στο previous page id. Εδώ το null δείχνει την τελευταία σελίδα.
  • extent_number: δείχνει σε ποιο extent ανήκει η συγκεκριμένη σελίδα.

Code of sp_indexdetails


if not exists (select * from sys.schemas where name ='sqlschoolgr')
begin
    exec('create schema sqlschoolgr');
end
go

create or alter proc sqlschoolgr.sp_indexdetails (@table_name sysname, @table_idxid int)
/*
    AUTHOR: ANTONIOS CHATZIPAVLIS - SQLSCHOOL.GR

    RETURNS DETAILED INFO ABOUT AN INDEX.
    
    PARAMETERS

    @table_name  : the table name 
    @table_idxid : the index id 

    Target SQL Server Versions : SQL Server 2014 SP2 and above
*/
as
begin
    set nocount on;

    -- variables declaration 

    declare    @table_objid    int = object_id(@table_name);
    declare    @idx_name        sysname;
    declare    @idx_columns    nvarchar(4000) ='';
    declare    @idx_includes    nvarchar(4000) ='';
    declare @dbname            sysname = db_name();                

    -- get index name
       
    select    @idx_name=name 
    from        sys.indexes 
    where    object_id = @table_objid and index_id = @table_idxid;
       
    -- basic info
    -- display db name and table name

    select 'Index basic information' as Section;

    select    db_name()        as [database_name]
        ,    @table_name        as table_name;

    -- display index info
       
    select    *
    from    sys.indexes 
    where    object_id = @table_objid and index_id = @table_idxid;

    -- show index col(s) and included cols

    select 'Index columns and included columns' as Section;
    select    @idx_columns += COL_NAME(object_id,column_id)+','
    from    sys.index_columns
    where    object_id = @table_objid  and index_id = @table_idxid and key_ordinal > 0
    order by key_ordinal;
                     
    select    @idx_includes += COL_NAME(object_id,column_id)+','
    from    sys.index_columns
    where    object_id = @table_objid and index_id = @table_idxid and is_included_column = 1
    order by key_ordinal;

    select    left(@idx_columns,len(@idx_columns)-1) as index_columns
    ,        case
            when len(@idx_includes) > 0 then left(@idx_includes,len(@idx_includes)-1) 
            else null 
            end as index_included_columns

    -- index physical stats
    -- using index physical statisitcs

    select 'Index fragmentation info' as Section;

    select    object_id, index_id, index_level, partition_number, record_count, index_type_desc,
            max(index_level) over (partition by object_id,partition_number,index_id) as max_level,
            avg_fragmentation_in_percent AS 'external_Fragmentation_(%)',
            100.0 - avg_page_space_used_in_percent AS 'internal_fragmentation_(%)',
            page_count
    into    #idx_total
    from 
            sys.dm_db_index_physical_stats( db_id(), @table_objid, @table_idxid, null,'detailed') ;
       
    -- show fragemntation by partition
       
    select    partition_number, index_level,[external_Fragmentation_(%)],[internal_fragmentation_(%)],record_count,page_count
    from    #idx_total
    
    -- display index physical stats
    select 'Index physical stats' as Section;
    select    *
    from    sys.dm_db_index_physical_stats( db_id(), @table_objid, @table_idxid, null,'detailed') ;

    -- display index statistics

    select 'Index Statistics' as Section;

    DBCC SHOW_STATISTICS(@table_name, @idx_name) WITH STAT_HEADER, NO_INFOMSGS ;
    DBCC SHOW_STATISTICS(@table_name, @idx_name) WITH DENSITY_VECTOR, NO_INFOMSGS;
    DBCC SHOW_STATISTICS(@table_name, @idx_name) WITH HISTOGRAM, NO_INFOMSGS;
    DBCC SHOW_STATISTICS(@table_name, @idx_name) WITH STATS_STREAM, NO_INFOMSGS;
       
    select    *
    from    sys.dm_db_incremental_stats_properties(@table_objid,(select stats_id from sys.dm_db_stats_properties(@table_objid,@table_idxid)));

    -- display index stucture by page

    select 'Index structure in details' as Section;

    if ( SERVERPROPERTY('ProductMajorVersion') >= '15' ) -- SQL 2019+
    begin
        select 
            partition_number        as partition_number
        ,   allocated_page_page_id  as [page_id]
        ,   p.page_type_desc        as page_type
        ,   case 
                when p.page_level = 0 then 'LEAF'
                when p.page_level = s.max_level then 'ROOT'
                else 'NON-LEAF-L'+cast(s.index_level as varchar(10))
            end                     as index_level
        ,   s.record_count          as total_rows
        ,   d.slot_count            as page_rows
        ,   d.free_bytes            as page_free_bytes
        ,   p.previous_page_page_id as previous_page_id
        ,   p.next_page_page_id        as next_page_id
        ,   p.extent_page_id /8     as extent_number
        from 
                sys.dm_db_database_page_allocations ( db_id(), @table_objid, @table_idxid, null, 'detailed') as p
        cross apply
                sys.dm_db_page_info ( db_id(), p.allocated_page_file_id, p.allocated_page_page_id, 'detailed' ) as d
        left outer join 
                sys.indexes i  
                    on i.object_id = p.object_id and i.index_id = p.index_id
        inner join 
                #idx_total as s 
                    on s.index_id=p.index_id and s.index_level=p.page_level and s.object_id = p.object_id and s.partition_number = p.partition_id

        where  p.page_type in (1,2) 
        order by  partition_number,s.index_level desc, allocated_page_page_id;

    end

    else
    
    begin

        create table #dbccindresults
        (
            PageFID            bigint 
        ,    PagePID            bigint 
        ,   IAMFID            varchar (max) 
        ,    IAMPID            varchar (max) 
        ,    ObjectID        varchar (max) 
        ,    IndexID            bigint 
        ,    PartitionNumber varchar (max) 
        ,    PartitionID     bigint
        ,    iam_chain_type  varchar (max) 
        ,    PageType        bigint 
        ,    IndexLevel        bigint 
        ,   NextPageFID        bigint  
        ,   NextPagePID     bigint 
        ,    PrevPageFID        bigint
        ,    PrevPagePID        bigint
        )


        declare @x1 varchar(100) = QUOTENAME (@dbname,'''')
        declare @x2 varchar(100) = QUOTENAME (@table_name,'''')
        insert into #dbccindresults
        exec ('dbcc ind ('+@x1+','+ @x2   +','+@table_idxid+')');

        select 
            partition_number        as partition_number
        ,   allocated_page_page_id  as [page_id]
        ,   p.page_type_desc        as page_type
        ,   case 
                when p.page_level = 0 then 'LEAF'
                when p.page_level = s.max_level then 'ROOT'
                else 'NON-LEAF-L'+cast(s.index_level as varchar(10))
            end                     as index_level
        ,   s.record_count          as total_rows
        ,   p.previous_page_page_id as previous_page_id
        ,   p.next_page_page_id        as next_page_id
        ,   p.extent_page_id /8     as extent_number
        from 
                sys.dm_db_database_page_allocations ( db_id(), @table_objid, @table_idxid, null, 'detailed') as p
        inner join 
                #dbccindresults as d on  p.allocated_page_file_id = d.PageFID and p.allocated_page_page_id = d.PagePID
        left outer join 
                sys.indexes i  
                    on i.object_id = p.object_id and i.index_id = p.index_id
        inner join 
                #idx_total as s 
                    on s.index_id=p.index_id and s.index_level=p.page_level and s.object_id = p.object_id and s.partition_number = p.partition_id

        where  p.page_type in (1,2) 
        order by  partition_number,s.index_level desc, allocated_page_page_id;

    end

end
go

Summary

Όπως είπα και παραπάνω η συγκεκριμένη SP δημιουργήθηκε για εκπαιδευτικούς σκοπούς και απαιτεί χρόνο για την εκτέλεση της σε μεγάλους indexes. Φυσικά και μπορεί να χρησιμοποιηθεί και διαφορετικά αλλά θεωρώ ότι πρέπει να εμπλουτιστεί περισσότερο για να αποτελέσει ένα καλό εργαλείο.


Antonios Chatzipavlis

Antonios Chatzipavlis

Antonios is a Data Solutions Consultant and Trainer. He has been working in IT since 1988. In his career, he has worked as senior developer, IT Manager, Solutions Architect and IT Consultant. Since 1995 he has been devoted on new technologies and software development tools, mainly by Microsoft, either by training company staff and colleagues or assisting them in design, development and implementation as a consultant or chief developer. He has focused in Databases and Data Science since 1995. He specialized in Microsoft SQL Server since version 6.0 in areas like SQL Server Internals, Database Design and Development, Business Intelligence and in 2010 he has started working with Azure Data Platform, NoSQL databases, Big Data Technologies and Machine Learning. He is an active member of many IT communities in Greece, answering colleagues' questions and writing articles in his web site. He is the owner of SQLschool.gr which is a community portal with a lot of information about Microsoft SQL Server. He has been a Microsoft Certified Trainer (MCT) since 2000. Microsoft honored him as MVP on Data Platform due to his activities in SQL Server since 2010. He holds a large number of Microsoft Certifications and Microsoft SQL Server Certifications since version 6.5.



Leave your comment

COMMENT

FULL NAME

EMAIL ADDRESS

We use Gravatar

WEB SITE



captcha


 

Newsletter

If you want to receive updates from us subscribe below with your email.
Follow us in
sql school greece logo
© 2010-2021 All rights reserved

This site uses cookies for operational and analytics purposes only. By continuing to browse this site, you agree to their use.