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. Φυσικά και μπορεί να χρησιμοποιηθεί και διαφορετικά αλλά θεωρώ ότι πρέπει να εμπλουτιστεί περισσότερο για να αποτελέσει ένα καλό εργαλείο.