go backarticles

Articles of SQLschool.gr Team

How Clustered Index keeps key order in a page

Antonios Chatzipavlis

Αρκετός καιρός έχει περάσει από το τελευταίο μου άρθρο σχετικά με τα 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 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
PASS chapter logo

The Official PASS Local Group for Greece

About us Contact us Terms of Use Privacy Sing in Register
sql school greece logo
© 2010-2020 All rights reserved

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