Overview

Η γνώση των internals στο SQL Server όπως πολλές φορές έχω αναφέρει είναι σημαντική γιατί έτσι μπορείς να σχεδιάσεις καλύτερα το database schema, να γράψεις καλύτερα queries ή διαδικασίες και φυσικά να μπορείς να κάνεις καλύτερο troubleshooting.

Σε αυτό το άρθρο θα κατέβουμε για τα καλά το πάτο του βαρελιού καθώς θα κατανοήσουμε το πως ο SQL Server αντιμετωπίζει αυτά που του ζητάμε.

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

Με αυτά σαν επεξήγηση ας προχωρήσουμε στο θέμα το του άρθρου αυτού.

Είναι γνωστό ότι τα δεδομένα που βάζουμε στα tables αλλά και στους indexes αποθηκεύονται σε 8KB pages και το ερώτημα είναι να βρούμε αυτές που περιέχουν αυτά.

Οι λόγοι που μπορεί να σε οδηγήσουν να αναζητήσεις αυτή την πληροφορία είναι αρχικά μαθησιακοί (για όσους θέλουν να μάθουν σε βάθος την αρχιτεκτονική του SQL Server) αλλά και πρακτικοί καθώς σε δύσκολες περιπτώσεις τόσο corruption αλλά και επίλυσης θεμάτων απόδοσης θέλεις να έχει μια εικόνα που περιέχει λεπτομέρεια τέτοια που να σε βοηθήσουν να λύσεις αυτά.

The undocumented DMF sys.dm_db_databases_page_allocation

Για να μπορέσω να πάρω αυτή την εικόνα υπάρχει ένα undocumented DMF το sys.dm_db_databases_page_allocation.

Αυτό το DMF δέχεται πέντε παραμέτρους:

• @DatabaseId: Είναι υποχρεωτική παράμετρος και δίνουμε το database ID της database στην οποία θέλουμε να γίνει η εκτέλεση. Μπορούμε να πάρουμε το id αυτό με την χρήση της DB_ID() function
• @TableId: Δεν είναι υποχρεωτική παράμετρος και στην περίπτωση που περάσουμε το NULL σαν τιμή θα επιστρέψει όλους τους πίνακες που έχουμε στην database και αυτό πραγματικά είναι αργό αλλά και δεν έχει να μας προσφέρει κάτι σαν πληροφορία καθώς όπως είπα πάμε στοχευμένα. Το Table Id για το πίνακα που μας ενδιαφέρει μπορούμε να το πάρουμε με την χρήση της OBJECT_ID() function.
• @IndexId: Δεν είναι υποχρεωτική παράμετρος και στην περίπτωση που περάσουμε το NULL σαν τιμή θα επιστρέψει πληροφορία για όλους τους indexes. Αν θέλουμε για συγκεκριμένο index μπορούμε να πάρουμε το Index Id από το sys.indexes catalog view.
• @PartitionId: Δεν είναι υποχρεωτική παράμετρος και στην περίπτωση του NULL σαν input value επιστρέφονται όλα τα partitions.
• @Mode: Είναι υποχρεωτική παράμετρος και παίρνει δύο τιμές ‘LIMITED’ / ‘DETAILED’. Η πρώτη επιστρέφει λιγότερες πληροφορίες η δεύτερη περισσότερες αλλά χρειάζονται περισσότεροι πόροι για την εκτέλεση της.

ATTENTION
Σε αυτό το σημείο θα πρέπει να επισημάνω ότι η χρήση της συγκεκριμένης DMF έχει αρκετά σημαντικό κόστος σε πόρους αλλά και σε locks και IO και μεγάλες databases (>1TB) παίρνει πολυυυυυυ χρόνο.

Learning by example

Αρχικά ας δημιουργήσουμε μια database και σε αυτή ένα table X, αρχικά σαν heap.

create database bindb;
go

use bindb;
go

create table X (col1 int, col2 datetime2(7), col3 char(100));
go

Αν εκτελέσουμε την DMF συνδυαστικά με το sys.indexes catalog view όπως στο παρακάτω query δεν θα έχουμε κάποιο αποτέλεσμα καθώς δεν έχουμε βάλει κάποιο row και είναι γνωστό ότι η δημιουργία ενός table δεν καταλαμβάνει χώρο εκτός από τα απαραίτητα entries που γίνονται στα metadata system objects. Στο σημείο αυτό θα ήθελα να σας προτρέψω να εκτελέσετε και μεμονωμένα το DMF για να δείτε το τι επιστρέφει και το οποίο από τα ονόματα των στηλών είναι εύκολο κανείς να καταλάβει αυτά.

select 
         db_name(p.database_id)          as [database_name]
    ,    object_name(p.object_id)        as table_name
    ,    i.[name]                        as index_name
    ,    is_allocated
    ,    allocated_page_file_id          as [file_id]
    ,    allocated_page_page_id          as [page_id]
    ,    page_type_desc
    ,    page_level
    ,    previous_page_page_id           as previous_page_id
    ,    next_page_page_id               as next_page_id
from 
    sys.dm_db_database_page_allocations (
                            db_id()
                        ,   object_id('x')
                        ,   null
                        ,   null
                        ,   'detailed'
                                        ) as p
left outer join sys.indexes i  on i.object_id = p.object_id and i.index_id = p.index_id
order by  table_name , index_name, page_level desc, is_allocated desc, previous_page_page_id

Αν τώρα στο table προσθέσω ένα row και το εκτελέσω ξανά θα δω αποτέλεσμα το οποίο είναι το παρακάτω

insert into X values (1,SYSDATETIME(),'a') 

Αν εξετάσουμε το αποτέλεσμα βλέπουμε ότι η σελίδα 126 έχει δεσμευτεί σαν Index Allocation Map (IAM) και η πρώτη σελίδα που περιέχει δεδομένα είναι η 352.
Επίσης επειδή δεν έχω κάποιο clustered index βλέπω στο index_name column την τιμή null.

Create Clustered Index

create clustered index cidx on x(col1)

Create Non-Clustered Index

Αν τώρα φτιάξουμε και ένα nonclustered index στο col2 column έχουμε τα εξής αποτελέσματα στα οποία παρατηρούμε ότι η 126 είναι η IAM του index και έχει μόνο μία σελίδα την 352 που είναι στο leaf level αυτού.

create  index idx on x(col2)

Adding more rows

Αν βάλουμε 20000 rows στο table αυτό θα δούμε το εξής το παρακάτω αποτέλεσμα από την εκτέλεση του query.
Από το αποτέλεσμα αυτό μπορούμε να καταλάβουμε πολλά που αφορούν την αρχιτεκτονική του SQL Server και ιδιαίτερα για την δομή των indexes.
Για παράδειγμα αν πάρουμε το clustered index (cidx) θα δούμε ότι η σελίδα 127 είναι η IAM όπου στο page_type_desc επιστρέφει DATA_PAGE είναι τα actual data αλλά και το leaf level του clustered index και αυτό το καταλαβαίνουμε από το page_level που είναι μηδέν (και θα δείτε ότι έχουμε πολλά) και τα previous/next page id δείχνουν στην σειρά των pages/data. Επίσης το page_level δείχνει το επόμενο index level που μπορεί αν είναι κάποιο intermediate ή το root index level. Το root index level μπορούμε να το καταλάβουμε από το μεγαλύτερη τιμή που θα δούμε στο page_level αλλά μπορούμε εύκολα να το εντοπίσουμε όταν το page_type_desc είναι INDEX_PAGE και τα previous/next page id είναι NULL

Summary

Είδαμε πως μπορούμε να πάρουμε internals info για το πως λειτουργεί ο SQL Server. Κρατάμε την DMF που δείξαμε παρόλο που είναι undocumented καθώς θα την χρησιμοποιήσουμε σε επόμενα άρθρα.

//Antonios Chatzipavlis