articles

Articles of SQLschool.gr Team

DBCC EXTENTINFO – Another undocumented/documented useful dbcc command

Antonios Chatzipavlis

Tuesday 17 July 2012

Internals & ArchitectureManage / Monitor / Tune

Αρκετές φορές έχω αναφέρει κατά την διάρκεια των μαθημάτων και των παρουσιάσεων που κάνω για την αρχιτεκτονική μιας βάσης, ότι τα data files χωρίζονται σε σελίδες των 8K, κάθε 8 τέτοιες σελίδες αποτελούν ένα extent και ότι υπάρχουν δύο είδη extent τα uniform και τα mixed.

Uniform είναι αυτά που οι 8 σελίδες τους ανήκουν σε ένα object πχ στον πίνακα των πελατών, ενώ mixed είναι αυτά που οι 8 σελίδες τους ανήκουν σε διαφορετικά.

Επανειλημμένα έχω αναφέρει ότι σε κάθε πίνακα οι πρώτες 8 σελίδες του ανήκουν σε mixed extend και από την 9 και μετά πάνε σε uniform extent.

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

Τι κάνει η DBCC EXTENTINFO;

Για όλους αυτούς του λόγους είναι χρήσιμο να γνωρίζω την ύπαρξη της DBCC EXTENTINFO. Η συγκεκριμένη ανήκει στην κατηγορία των undocumented dbcc commands, αλλά είναι γνωστή σε όσους ασχολούνται συστηματικά με τον SQL Server.

Η εκτέλεση της επιστρέφει ένα dataset του οποίου η κάθε γραμμή αντιπροσωπεύει ένα extent στην βάση μου εάν αυτό είναι uniform και πολλές (μέχρι 8) αν είναι mixed.

Τι επιστρέφει η DBCC EXTENTINFO;

Αναλυτικά οι κολώνες που επιστρέφονται στον dataset της εκτέλεσης της DBCC EXTENTINFO είναι:

Column Name	Περιγραφή - Ερμηνεία
file_id	Ο αριθμός του data file της βάσης που η συγκεκριμένη σελίδα ανήκει
page_id	Ο αριθμός της σελίδας που ξεκινάει το extent αν είναι uniform ή ο αριθμός της σελίδας στο mixed
*pg_alloc*	Ο αριθμός των σελίδων στο συγκεκριμένο extent που έχουν δευσμευτεί για το συγκεκριμένο object.
ext_size	Ο αριθμός των σελίδων του extent.
object_id	Το object id του πίνακα στον οποίο ανήκει η σελίδα / extent.
index_id	Το index id του index /heap στο πίνακα.
partition_number	Ο αριθμός του partition για τον index/heap.
partition_id	Το partition id για τον index/heap
iam_chain_type	O τύπος της IAM αλυσίδας για την οποία χρησιμοποιείται το extent αυτό (in-row, LOB, overflow).
pfs_bytes	Το byte array που δείχνει τον πόσο του ελεύθερου χώρου, αν υπάρχουν ghost records, αν είναι ΙΑΜ, αν είναι μέρος ένος mixed extent και λοιπά

Που είναι σε ποιο extent είμαι;

Παρατηρώντας τις κολώνες που επιστρέφονται εύλογα κάποιος θα αναρωτηθεί που είναι ο αριθμός του extent για το οποίο αναφέρετε η συγκεκριμένη γραμμή;

Όντως δεν υπάρχει ξεχωριστή κολώνα αλλά μπορείς να βρεις τον αριθμό του extent αν διαιρέσεις τον αριθμό της σελίδα με το 8. Το ακέραιο μέρος του αποτελέσματος της διαίρεσης είναι το ο αριθμός του extent.

Παραδείγματα

Ας δούμε όμως ένα παράδειγμα όσο το δυνατόν πιο απλό ώστε να γίνει εύκολα κατανοητό το όλο θέμα. Για αυτό το λόγο θα φτιάξω μια νέα βάση στην οποίο θα δημιουργήσω ένα πίνακα Τ ο οποίος εσκεμμένα θα έχει ένα μέγεθος εγγραφής τέτοιο που να καταλαμβάνει μια ολόκληρη σελίδα. Έτσι κάθε εγγραφή που θα βάζω θα είναι και μια ξεχωριστή σελίδα. Η επιλογή μου αυτή είναι για να απλουστεύσουμε τους υπολογισμούς. Επίσης για να προλάβω όσους θα πουν ότι δεν έχω primary key ή indexes θα επαναλάβω ότι σκοπός μου αρχικά είναι να καταλάβουμε τι ακριβώς κάνει και δείχνει η DBCC EXTENTINFO.

Δημιουργία παραδείγματος

Με το παρακάτω script δημιουργώ όσα παραπάνω ανέφερα

IF ( DB_ID('dbDemo') > 0 )
    DROP DATABASE dbdemo;
GO

CREATE DATABASE dbDemo;
GO

IF (OBJECT_ID('T') > 0 )
    DROP TABLE T;
GO

CREATE TABLE T
(
        ID INT IDENTITY(1,1)
    ,   RecordData CHAR(8000) DEFAULT (REPLICATE('a',8000))
);
GO

Παράδειγμα 1ο

Ας έρθουμε με το παρακάτω script να βάλουμε την πρώτη εγγραφή στον πίνακα Τ και να εκτελέσουμε και την DBCC EXTENTINFO και να εξηγήσουμε το αποτέλεσμα της

INSERT INTO T DEFAULT VALUES;
GO
DBCC EXTENTINFO (0,T,-1);
GO

Το αποτέλεσμα της εκτέλεσης της DBCC ENTENTINFO είναι το παρακάτω

Τι μας λέει αυτή η γραμμή;

Μας λέει με απλά λόγια ότι στο πρώτο data αρχείο της βάση μας (file_id=1) για το object_id 788197858 που είναι ο πίνακας Τ με index_id = 0 που σημαίνει ότι είμαι σε heap έχει δεσμευτεί η σελίδα 24279 στο extent 3034 (το οποίο όπως έχω ήδη πει βγαίνει από την διαίρεση του αριθμού της σελίδας με το 8). Καταλαβαίνω ότι αυτή είναι σε mixed extend καθώς η pg_alloc κολώνα έχει την τιμή 1 και η ext_size έχει την ίδια τιμή.

Ας προχωρήσουμε όμως και να βάλουμε ακόμα μια γραμμή στο πίνακα μας και να δούμε τα αποτελέσματα της DBCC EXTENTINFO

INSERT INTO T DEFAULT VALUES;
GO
DBCC EXTENTINFO (0,T,-1);
GO

Ερμηνεύοντας την δεύτερη γραμμή έχουμε ότι στο πρώτο data αρχείο της βάση μας (file_id=1) για το object_id 788197858 που είναι ο πίνακας Τ με index_id = 0 που σημαίνει ότι είμαι σε heap έχει δεσμευτεί η σελίδα 24305 στο extent 3038 (το οποίο όπως έχω ήδη πει βγαίνει από την διαίρεση του αριθμού της σελίδας με το 8). Καταλαβαίνω ότι αυτή είναι σε mixed extend καθώς η pg_alloc κολώνα έχει την τιμή 1 και η ext_size έχει την ίδια τιμή.

Μέχρι τώρα έχω προσθέσει δύο εγγραφές που είναι δύο σελίδες σε δύο διαφορετικά extents, άρα μου μένουν μέχρι να συμπληρώσω τις πρώτες 8 σελίδες στον συγκεκριμένο πίνακα ακόμα 6 σελίδες.

Ας έρθουμε να προσθέσουμε ακόμα 4 εγγραφές και ας δούμε τι μας βγάζει τώρα η DBCC EXTENTINFO

INSERT INTO T DEFAULT VALUES;
GO 4
DBCC EXTENTINFO (0,T,-1);
GO

Για να ερμηνεύσουμε το αποτέλεσμα. Παρατηρήστε σε αυτό ότι οι 4 εγγραφές που πριν λίγο μπήκαν μέσω του ίδιου batch έχουν καταλάβει τις σελίδες 24306, 24307, 24308, 24309 που είναι όλες στο ίδιο extent (3038) αλλά αυτό δεν είναι uniform.

Ας έρθουμε να προσθέσουμε ακόμα 2 εγγραφές και ας δούμε τι μας βγάζει τώρα η DBCC EXTENTINFO

INSERT INTO T DEFAULT VALUES;
GO 2
DBCC EXTENTINFO (0,T,-1);
GO

Ερμηνεύοντας το αποτέλεσμα θα δούμε ότι με την εισαγωγή των δύο νέων εγγραφών έχουν δεσμευτεί ακόμα δύο σελίδες οι 24310, 24311 οι οποίες και αυτές ανήκουν στον ίδιο extent (3038) αλλά αυτό δεν είναι uniform.

Μέχρι τώρα έχουμε περάσει 8 εγγραφές που όπως έχω εξηγήσει παραπάνω αντιστοιχούν σε μια σελίδα και στο extent 3034 έχω μια σελίδα την 24279 που ήταν η πρώτη εγγραφή ενώ οι υπόλοιπες 7 εγγραφές είναι στο extent 3038

Ας έρθουμε να προσθέσουμε ακόμα 1 εγγραφή και ας δούμε τι μας βγάζει τώρα η DBCC EXTENTINFO

INSERT INTO T DEFAULT VALUES;
GO
DBCC EXTENTINFO (0,T,-1);
GO

Βλέποντας στο αποτέλεσμα της DBCC EXTENTINFO την γραμμή 9 θα παρατηρήσω ότι το ext_size είναι πλέον 8 και έχει το pg_alloc =1. Αυτό σημαίνει ότι πλέον είμαι σε uniform extent και στο οποίο έχει καταλειφθεί μία σελίδα.
Χωρίς να περιμένω περισσότερα θα βάλω ακόμα μια εγγραφή

INSERT INTO T DEFAULT VALUES;
GO
DBCC EXTENTINFO (0,T,-1);
GO

Βλέποντας στο αποτέλεσμα της DBCC EXTENTINFO θα δω ότι δεν έχει μπει νέα γραμμή αλλά στην γραμμή 9 θα παρατηρήσω ότι το pg_alloc=2. Αυτό σημαίνει ότι στο extent αυτό είναι πλέον κατειλημμένες 2 σελίδες. Το extent αυτό είναι το 3040 και βγαίνει με την γνωστή διαίρεση.

Αυτή την φορά θα βάλω 10 εγγραφές και ας δούμε το αποτέλεσμα

INSERT INTO T DEFAULT VALUES;
GO 10
DBCC EXTENTINFO (0,T,-1);
GO

Παρατηρώντας το αποτέλεσμα βλέπω ότι πλέον στον extent 3040 έχουν καταληφτεί όλες οι σελίδες πλέον και αυτό το καταλαβαίνω βλέποντας το pg_alloc=8.

Επίσης έχει μπει ακόμα μια γραμμή στο αποτέλεσμα μου η 10η η οποία τι μου λέει;

Μου λέει ότι το extent που ξεκινάει από την σελίδα 24328 και είναι το 3041 είναι uniform (ext_size=8) ανήκει στον πίνακα Τ (αυτό το βλέπω από το object_id) και έχει καταλυμένες 4 σελίδες.

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

Παράδειγμα 2ο

Θα φτιάξω ακόμα ένα πίνακα της ίδιας φιλοσοφίας όπως ο πρώτος και θα βάλω με ένα batch 20 εγγραφές και θα δω τι έχει γίνει στο αποτέλεσμα της DBCC EXTENTINFO.

IF (OBJECT_ID('T1') > 0 )
    DROP TABLE T1;
GO

CREATE TABLE T1
(
        ID INT IDENTITY(1,1)
    ,    RecordData CHAR(8000) DEFAULT (REPLICATE('a',8000))
);
GO

INSERT INTO T1 DEFAULT VALUES;
GO 20

DBCC EXTENTINFO (0,T1,-1);
GO

Παρατηρώντας τα αποτελέσματα της DBCC EXTENTINFO θα δω ότι οι πρώτες 8 σελίδες είναι mixed extents και συγκεκριμένα οι πρώτες 7 είναι στο 3039 extent , η 8 σελίδα στο 3042, ενώ οι επόμενες 8 σελίδες είναι στο 3043 extent το οποίο είναι uniform και οι επόμενες 4 στο 3044 το οποίο και αυτό είναι uniform.

Πώς κάνω defragmentation;

Βλέπω ότι οι πρώτες 8 είναι διασκορπισμένες σε περισσότερα από 1 extents και το ερώτημα μου είναι αν θα μπορούσα κάπως να τα μαζέψω αυτά σε ένα ακόμα και αν αυτό είναι mixed.

H λύση είναι απλή και γνωστή από το πώς κάνουμε defragmentation στους indexes. Αυτό θα κάνω και εδώ και ας έχω heap με το παρακάτω script. Προσέξτε το ALTER TABLE ιδιαίτερα

ALTER TABLE T1
REBUILD;
GO
DBCC EXTENTINFO (0,T1,-1);
GO

Βλέπω πλέον ότι και οι 8 πρώτες σελίδες είναι πλέον και αυτές σε uniform extent έτσι έχω πλέον 3 uniform extents απλά αυτό που ίσως σας ξενίσει είναι ότι στο πρώτο extent στην γραμμή 1 έχω 9 σελίδες στην pg_alloc κολώνα. Η παραπάνω κολώνα είναι ΙΑΜ σελίδα καθώς όταν ένα table ή index ξεκινάει με uniform extent η ΙΑΜ συμπεριλαμβάνεται στον υπολογισμό του πρώτου extent.

Χρησιμότητα της DBCC EXTENTINFO

Με όλα τα παραπάνω είδαμε πως μπορούμε χρησιμοποιώντας την DBCC EXTENTINFO να καταλάβουμε πως είναι τοποθετημένες οι σελίδες μας σε ένα πίνακα ή index και έτσι θα μας βοηθήσει να καταλάβουμε καλύτερα το fragmentation σε Indexes και στο πίνακα μας όταν ψάχνουμε για αυτό σε αυτούς.

Για όλα όσα όμως αφορούν τους Index στον SQL Server 2012 θα σας συνιστούσα να αγοράσετε και να διαβάσετε τον νέο βιβλίο του Jason Strate με τίτλο Expert Performance Indexing for SQL Server 2012 έκδοσης της Apress. Παρόλα αυτά όμως και εγώ θα σας ενημερώνω με τα post μου σε αυτό το θέμα

/*antonch*/

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.