sqlschool.gr logo

articles

Articles of SQLschool.gr Team

How Clustered Index keeps key order in a page

Antonios Chatzipavlis
Saturday 11 April 2020

Αρκετός καιρός έχει περάσει από το τελευταίο μου άρθρο σχετικά με τα SQL Server Internals και νομίζω ότι είναι καλή η στιγμή μιας και είμαστε όλοι στα σπίτια μας εξαιτίας του COVID-19.

Με την ευκαιρία αυτή ελπίζω να είστε εσείς και οι οικογένειες σας καλά. Αυτό που χρειάζεται είναι να κάνουμε υπομονή και να ακολουθούμε τις οδηγίες. Έχουμε κάνει ένα pause και θα βγούμε από αυτό ελπίζω σύντομα αλλά αυτό που μετράει είναι να είμαστε υγιείς.

Ας έρθουμε όμως στο σκοπό του άρθρου αυτού και να εξηγήσουμε πως φυσικά αποθηκεύονται τα δεδομένα σε ένα data page στη περίπτωση που έχω ένα clustered index στο table.

Όπως όλοι γνωρίζεται όταν έχω clustered index στο table τα δεδομένα τοποθετούνται στην σειρά με βάση το field/key που έχει χρησιμοποιηθεί για τον clustered index. Αυτό δεν είναι λάθος, καλώς το αναφέρουμε, αλλά εσωτερικά στο data page τα πράγματα είναι λίγο διαφορετικά, χωρίς όμως αυτό να αλλάζει κάτι στην συμπεριφορά του clustered index, ούτε να προσδίδει κάποιο αρνητικό στοιχείο. Το αντίθετο θα έλεγα, έχει γίνει για την καλύτερη απόδοση του.

Page structure and page offset

Πριν ξεκινήσω όμως την αναφορά μου σε αυτά θα πρέπει να είμαι σίγουρος ότι όλοι γνωρίζουμε ότι κάθε data file σε μια SQL Server database δομείτε εσωτερικά σε pages των 8ΚΒ. Σε αυτά αποθηκεύονται τα δεδομένα του SQL Server. Ένα page έχει τον page header που είναι 96 bytes και το offset array το οποίο βρίσκεται στο τέλος του page, είναι 36 bytes και παρέχει τους δείκτες (pointers) στο byte location της αρχής της κάθε γραμμής (record) που είναι στην συγκεκριμένη σελίδα.

Αυτό είναι αποθηκευμένο αντίστροφα πάνω στην σελίδα δηλαδή το offset του πρώτου record είναι στο τέλος του συγκεκριμένου array το δεύτερο είναι προτελευταίο κ.ο.κ.

Το υπόλοιπο τμήμα (8060 bytes) είναι ο χώρος στον οποίο αποθηκεύονται τα records. Αυτός είναι και ο λόγος που λέμε ότι το max record length δεν μπορεί να είναι πάνω από 8060 bytes καθώς ένα record δεν μπορεί να είναι σπασμένο σε δύο pages.

 
Page structure and page offset

image

image

Example

Για να κατανοήσουμε το τι γίνεται θα χρησιμοποιήσω ένα παράδειγμα στο οποίο δημιουργώ μια απλή database και ένα table που έχει record length συνολικά 111 bytes. Αυτό σημαίνει ότι χωράνε στο κάθε data page περίπου 70 rows

create database democistore;
go

use democistore;
go


create table T 
( 
    rid int primary key clustered,
    col char(100)
)


Αρχικά ας βάλουμε το πρώτο row στο table.

insert into T (rid,col) values (1,'row-1')


Μπορούμε να δούμε σε ποιο data page έχει τοποθετηθεί αυτό το row, είτε με τη γνωστή αλλά όχι επίσημα documented DBCC IND, είτε με το επίσης γνωστό αλλά όχι επίσημα documented DMF sys.dm_db_database_page_allocations.

Using the DBCC IND

Όπως φαίνεται και στην παρακάτω εικόνα το page που έχει δεσμευτεί και περιέχει το συγκεκριμένο row είναι η 264 καθώς η σελίδα 89 είναι η Index Allocation Map (IAM) όπως διακρίνουμε τόσο από το IAMID αλλά όσο και από το PageType field. (δείτε αυτό το παλαιότερο άρθρο μου.


Using DBCC IND
image

Using the sys.dm_db_database_page_allocations DMF

Όπως φαίνεται και στην παρακάτω εικόνα πάλι είναι η 264 καθώς δεν είναι η ΙΑΜ.

Θα πρέπει να επισημάνω καθώς πιθανώς να αναρωτηθείτε γιατί βλέπουμε και άλλες σελίδες (265 έως 271).

Η απάντηση είναι απλή. Tο παράδειγμα είναι σε SQL Server 2019 και από τον 2017 πλέον γίνεται uniform extent allocation και όχι mixed όπως γίνονταν προγενέστερα (μπορείτε να δείτε για αυτά στο άρθρο μου αυτό.)


Using the sys.dm_db_database_page_allocations DMF
image

Using the DBCC PAGE

Επειδή όμως θέλουμε να δούμε τα πραγματικά περιεχόμενα του page, θα χρησιμοποιήσω πάλι ένα όχι επίσημα documented αλλά γνωστό DBCC το DBCC PAGE.

Απλά για να δούμε τα αποτελέσματα στο SSMS πρέπει να χρησιμοποιήσουμε το trace flag 3604

Στην παρακάτω εικόνα έχω κάνει scroll και βλέπουμε μόνο το τμήμα που αποθηκεύονται τα rows


DBCC PAGE result with row-1
image



Ας βάλουμε τώρα το επόμενο record και ας δούμε το αποτέλεσμα της DBCC PAGE

insert into T (rid,col) values (2,'row-2')



DBCC PAGE result with row-1 and row-2
image



Αν κάνουμε scroll μέχρι το τέλος θα δούμε την πληροφορία για το page offset.

Όπου το πρώτο row ξεκινάει από το 96 (εκεί δηλαδή που τελειώνει το page header) και το δεύτερο από το τέλος του πρώτου 96+111 = 207.

Να θυμίσω ότι το record length είναι 111 bytes.


DBCC PAGE Page Offset with row-1 and row-2
image



Ας βάλουμε το επόμενο rows αλλά αυτό δεν θα είναι το 3 αλλά το 4. Αυτο σημαίνει ότι ο clustered index θα είναι ταξινομημένος πλέον ως εξής 1,2,4.

insert into T (rid,col) values (4,'row-4')

DBCC PAGE result with row-1, row-2, row-4
image



Αν κάνουμε scroll μέχρι το τέλος θα δούμε την πληροφορία για το page offset, όπου και αυτό ξεκινάει από το τέλος του προηγούμενου.


DBCC PAGE Page Offset with row-1, row-2, row-4
image



Τώρα ας βάλουμε το 3 και ας δούμε τι έχουμε

insert into T (rid,col) values (3,'row-3')

DBCC PAGE result with row-1, row-2, row-4, row-3
image



Αν κάνουμε scroll μέχρι το τέλος θα δούμε την πληροφορία για το page offset.


DBCC PAGE Page Offset with row-1, row-2, row-4, row-3
image



Result

Βλέπουμε ξεκάθαρα ότι το row-3 ξεκινάει από το τέλος του προηγούμενου ΑΛΛΑ το page offset είναι ταξινομημένο με βάση το field/key που έχω στον clustered index.

Αυτό σημαίνει ότι έχω σαν αποτέλεσμα αυτό που ανάφερα και παραπάνω για την συμπεριφορά των clustered indexes αλλά εσωτερικά υλοποιείται με το sorting στο page offset.



//Antonios Chatzipavlis


Antonios Chatzipavlis

Antonios Chatzipavlis

Antonios Chatzipavlis is a highly experienced Data Solutions Consultant and Trainer. He has been working in the IT industry since 1988, holding various roles such as senior developer, IT Manager, Data & AI Solutions Architect and Consultant.

Since 1995, Antonios has focused on modern technologies and software development tools, primarily by Microsoft. He has specialized in Data & AI since 2000, with expertise in Microsoft Data Platform (SQL Server, Azure SQL Databases, Azure Synapse Analytics, Microsoft Fabric, Power BI, AI) and Databricks.

Antonios is also a Microsoft Certified Trainer (MCT) for over 25 years, has been recognized as a Microsoft Most Valuable Professional (MVP) in Data Platform since 2010 and he is in the Data Expert 40 Powerlist 2024 by Boussias. He is the co-founder and visionary behind XLYTiCA, a company dedicated to Data & AI solutions.

Episode

Task Flows in Microsoft Fabric

image

More Episodes...

Tip

Get Certified: Become a Fabric Data Engineer

More Tips...

Become a member

If you want to receive updates from us become a member to our community.

Connect

Explore

Learn

sqlschool.gr © 2010-2025 All rights reserved

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