articles

Articles of SQLschool.gr Team

How SQL Server find free pages - The importance of Page Free Page(s) (PFS)

Antonios Chatzipavlis

Friday 30 July 2021

Overview

Συνεχίζουμε την εξερεύνηση μας στα SQL Server internals και στο άρθρο αυτό θα αναλύσουμε την Page Free Space (PFS), την χρήση της και την σημασία της σε κάθε data file που υπάρχει στην εκάστοτε database.

Σε κάθε data file που έχουμε σε μία database υπάρχουν μία (by default) ή περισσότερες (ανάλογα το μέγεθος του) page free pages.

The Page Free Space (PFS) page

Η τιμή του page type (m_type field στο page header) για τις PFS είναι 11.

Η πρώτη PFS είναι πάντα η σελίδα 1 που υπάρχει σε κάθε data file (στην ουσία είναι η δεύτερη καθώς είναι 0 base και η 0 είναι η file header).

SQL Script-1

create database bin;
go

use bin
go

DBCC TRACEON(3604);
DBCC PAGE(0,1,1,3);

Σε αντίθεση με τις GAM & SGAM που είναι bitmap η κάθε PFS είναι byte-map. Σε αυτή, κάθε page στο data file της database συνδέεται με ένα byte από τα διαθέσιμα bytes της PFS (θυμίζω ότι κάθε σελίδα είναι 8ΚΒ και έχει 96 bytes page header). Από τα εναπομείναντα bytes τα 8088 είναι αυτά που αποτελούν το byte-map. Αυτό σημαίνει ότι κάθε PFS χαρτογραφεί τις επόμενες από αυτή 8088 pages και κάθε 8088 pages υπάρχει μια νέα PFS. Αυτό σημαίνει ότι εκτός από την page 1 που είναι η πρώτη PFS η επόμενη είναι η 8088 και επόμενες είναι οι 16176, 24264, 32352, 40440 κ.ο.κ ανάλογα με το μέγεθος του data file. Αν κάποιος θέλει να δει πόσες PFS έχει το κάθε data file της database του μπορεί να το κάνει εκτελώντας το παρακάτω query.

SQL Query-2

use bin
go
select DB_NAME() as database_name,name,physical_name,size,(size/8088) + 1 as pfsnum 
from sys.database_files
where type = 0

Το αποτέλεσμα όπως φαίνεται θα μας επιστρέψει μία PFS καθώς έχω δημιουργήσει την database με τα default sizes που είναι 8ΜΒ.

database_name	name	physical_name	size	pfsnum
bin	bin	D:\MSSQL15.MSSQLSERVER\MSSQL\DATA\bin.mdf	1024	1

Αν μεγαλώσω το αρχείο στα 64ΜΒ θα δω ότι έχω 2

SQL Script

use master;
go
alter database bin
modify file (name='bin', size = 64MB);
go

use bin
go
select DB_NAME() as database_name,name,physical_name,size, (size/8088) + 1 as pfsnum 
from sys.database_files
where type = 0;
go

database_name	name	physical_name	size	pfsnum
bin	bin	D:\MSSQL15.MSSQLSERVER\MSSQL\DATA\bin.mdf	8192	2

Αν μεγαλώσω το αρχείο στα 128ΜΒ θα δω ότι έχω 3

database_name	name	physical_name	size	pfsnum
bin	bin	D:\MSSQL15.MSSQLSERVER\MSSQL\DATA\bin.mdf	16384	3

Αν μεγαλώσω το αρχείο στα 1GB θα δω ότι έχω 17 κ.ο.κ

database_name	name	physical_name	size	pfsnum
bin	bin	D:\MSSQL15.MSSQLSERVER\MSSQL\DATA\bin.mdf	131072	17

PFS Internals

Όπως ανέφερα και παραπάνω κάθε σελίδα αντιπροσωπεύεται από ένα byte στην PFS. Αυτό όμως περιέχει σημαντικές πληροφορίες για αυτή και αυτές είναι οι εξής

Bit 7: δεν χρησιμοποιείται.
Bit 6: δείχνει αν το page είναι allocated
Bit 5: δείχνει αν το page είναι μέλος ενός mixed extent
Bit 4: δείχνει αν το page αυτό είναι IAM page
Bit 3: δείχνει αν το page αυτό περιέχει ghost records
Bits 0-2: δείχνουν το πόσο γεμάτο είναι το page με την εξής λογική

001: To page είναι μεταξύ 1% and 50% γεμάτο.
010: To page είναι μεταξύ 51% and 80% γεμάτο.
011: To page είναι μεταξύ 81% and 95% γεμάτο.
100: To page είναι μεταξύ 96% and 100% γεμάτο.

Viewing PFS Data

Αν θέλουμε να δούμε τις πληροφορίες αυτές μπορούμε να τις δούμε με την DBCC PAGE (στο παράδειγμα δείχνω την πρώτη PFS)

SQL Script

use bin;
go
DBCC TRACEON(3604);
DBCC PAGE(0,1,1,3);

PFS Data

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

PAGE: (1:1)


BUFFER:


BUF @0x00000152AE79BF40

bpage = 0x000001528BBDA000          bPmmpage = 0x0000000000000000       bsort_r_nextbP = 0x0000000000000000
bsort_r_prevbP = 0x0000000000000000 bhash = 0x0000000000000000          bpageno = (1:1)
bpart = 2                           ckptGen = 0x000001528D8FFDF0        bDirtyRefCount = 0
bstat = 0x9                         breferences = 0                     berrcode = 0
bUse1 = 24626                       bstat2 = 0x8                        blog = 0x15a7a
bsampleCount = 0                    bIoCount = 0                        resPoolId = 0
bcputicks = 0                       bReadMicroSec = 525                 bDirtyContext = 0x0000000000000000
bDbPageBroker = 0x0000000000000000  bdbid = 35                          bpru = 0x0000015297BB8040

PAGE HEADER:


Page @0x000001528BBDA000

m_pageId = (1:1)                    m_headerVersion = 1                 m_type = 11
m_typeFlagBits = 0x0                m_level = 0                         m_flagBits = 0x200
m_objId (AllocUnitId.idObj) = 99    m_indexId (AllocUnitId.idInd) = 0   Metadata: AllocUnitId = 6488064
Metadata: PartitionId = 0           Metadata: IndexId = 0               Metadata: ObjectId = 99
m_prevPage = (0:0)                  m_nextPage = (0:0)                  pminlen = 0
m_slotCnt = 1                       m_freeCnt = 2                       m_freeData = 8188
m_reservedCnt = 0                   m_lsn = (40:184:12)                 m_xactReserved = 0
m_xdesId = (0:0)                    m_ghostRecCnt = 0                   m_tornBits = -1450600436
DB Frag ID = 1                      

Allocation Status

GAM (1:2) = ALLOCATED               SGAM (1:3) = NOT ALLOCATED          PFS (1:1) = 0x44 ALLOCATED 100_PCT_FULL
DIFF (1:6) = CHANGED                ML (1:7) = NOT MIN_LOGGED           

PFS: Page Alloc Status  @0x0000003B4C878000

(1:0)        - (1:3)        =     ALLOCATED 100_PCT_FULL                                      
(1:4)        - (1:5)        = NOT ALLOCATED   0_PCT_FULL                                      
(1:6)        - (1:7)        =     ALLOCATED 100_PCT_FULL                                      
(1:8)        -              =     ALLOCATED   0_PCT_FULL                     Mixed Ext        
(1:9)        -              =     ALLOCATED 100_PCT_FULL                     Mixed Ext        
(1:10)       -              =     ALLOCATED   0_PCT_FULL           IAM Page  Mixed Ext        
(1:11)       -              =     ALLOCATED   0_PCT_FULL                     Mixed Ext        
(1:12)       -              =     ALLOCATED 100_PCT_FULL           IAM Page  Mixed Ext        
(1:13)       -              =     ALLOCATED   0_PCT_FULL           IAM Page  Mixed Ext        
(1:14)       -              =     ALLOCATED   0_PCT_FULL                     Mixed Ext        
(1:15)       -              =     ALLOCATED   0_PCT_FULL           IAM Page  Mixed Ext        
(1:16)       - (1:17)       =     ALLOCATED   0_PCT_FULL                     Mixed Ext        
(1:18)       -              =     ALLOCATED   0_PCT_FULL           IAM Page  Mixed Ext        
(1:19)       - (1:20)       =     ALLOCATED   0_PCT_FULL                     Mixed Ext        
(1:21)       - (1:22)       =     ALLOCATED   0_PCT_FULL           IAM Page  Mixed Ext        
(1:23)       -              =     ALLOCATED   0_PCT_FULL                     Mixed Ext        
(1:24)       - (1:31)       =     ALLOCATED   0_PCT_FULL                                      
(1:32)       -              =     ALLOCATED  50_PCT_FULL                     Mixed Ext        
(1:33)       -              =     ALLOCATED   0_PCT_FULL           IAM Page  Mixed Ext        
(1:34)       -              =     ALLOCATED   0_PCT_FULL                     Mixed Ext        
(1:35)       -              =     ALLOCATED   0_PCT_FULL           IAM Page  Mixed Ext        
(1:36)       - (1:38)       =     ALLOCATED   0_PCT_FULL                     Mixed Ext        
(1:39)       -              =     ALLOCATED   0_PCT_FULL           IAM Page  Mixed Ext        
(1:40)       - (1:44)       =     ALLOCATED   0_PCT_FULL                     Mixed Ext        
(1:45)       -              =     ALLOCATED   0_PCT_FULL           IAM Page  Mixed Ext        
(1:46)       - (1:48)       =     ALLOCATED   0_PCT_FULL                     Mixed Ext        
(1:49)       -              =     ALLOCATED   0_PCT_FULL           IAM Page  Mixed Ext        
(1:50)       - (1:54)       =     ALLOCATED   0_PCT_FULL                     Mixed Ext        
(1:55)       -              =     ALLOCATED   0_PCT_FULL           IAM Page  Mixed Ext        
(1:56)       - (1:59)       =     ALLOCATED   0_PCT_FULL                                      
(1:60)       -              = NOT ALLOCATED   0_PCT_FULL Has Ghost                            
(1:61)       -              =     ALLOCATED   0_PCT_FULL                                      
(1:62)       -              = NOT ALLOCATED   0_PCT_FULL Has Ghost                            
(1:63)       - (1:68)       =     ALLOCATED   0_PCT_FULL                                      
(1:69)       -              = NOT ALLOCATED   0_PCT_FULL Has Ghost                            
(1:70)       - (1:71)       =     ALLOCATED   0_PCT_FULL                                      
(1:72)       - (1:75)       =     ALLOCATED   0_PCT_FULL                     Mixed Ext        
(1:76)       -              =     ALLOCATED   0_PCT_FULL           IAM Page  Mixed Ext        
(1:77)       - (1:78)       =     ALLOCATED   0_PCT_FULL                     Mixed Ext        
(1:79)       -              =     ALLOCATED 100_PCT_FULL                     Mixed Ext        
(1:80)       -              =     ALLOCATED  80_PCT_FULL                     Mixed Ext        
(1:81)       -              =     ALLOCATED   0_PCT_FULL           IAM Page  Mixed Ext        
(1:82)       -              =     ALLOCATED   0_PCT_FULL                     Mixed Ext        
(1:83)       - (1:84)       =     ALLOCATED   0_PCT_FULL           IAM Page  Mixed Ext        
(1:85)       - (1:87)       =     ALLOCATED   0_PCT_FULL                     Mixed Ext        
(1:88)       -              =     ALLOCATED   0_PCT_FULL           IAM Page  Mixed Ext        
(1:89)       - (1:91)       =     ALLOCATED   0_PCT_FULL                     Mixed Ext        
(1:92)       -              =     ALLOCATED   0_PCT_FULL           IAM Page  Mixed Ext        
(1:93)       - (1:95)       =     ALLOCATED   0_PCT_FULL                     Mixed Ext        
(1:96)       -              =     ALLOCATED   0_PCT_FULL           IAM Page  Mixed Ext        
(1:97)       -              =     ALLOCATED   0_PCT_FULL                     Mixed Ext        
(1:98)       -              =     ALLOCATED   0_PCT_FULL           IAM Page  Mixed Ext        
(1:99)       -              =     ALLOCATED   0_PCT_FULL                     Mixed Ext        
(1:100)      -              =     ALLOCATED   0_PCT_FULL           IAM Page  Mixed Ext        
(1:101)      -              =     ALLOCATED   0_PCT_FULL                     Mixed Ext        
(1:102)      -              =     ALLOCATED   0_PCT_FULL           IAM Page  Mixed Ext        
(1:103)      -              =     ALLOCATED   0_PCT_FULL                     Mixed Ext        
(1:104)      -              =     ALLOCATED   0_PCT_FULL           IAM Page  Mixed Ext        
(1:105)      -              =     ALLOCATED   0_PCT_FULL                     Mixed Ext        
(1:106)      -              =     ALLOCATED   0_PCT_FULL           IAM Page  Mixed Ext        
(1:107)      -              =     ALLOCATED   0_PCT_FULL                     Mixed Ext        
(1:108)      -              =     ALLOCATED   0_PCT_FULL           IAM Page  Mixed Ext        
(1:109)      - (1:113)      =     ALLOCATED   0_PCT_FULL                     Mixed Ext        
(1:114)      -              =     ALLOCATED   0_PCT_FULL           IAM Page  Mixed Ext        
(1:115)      - (1:116)      =     ALLOCATED   0_PCT_FULL                     Mixed Ext        
(1:117)      -              =     ALLOCATED   0_PCT_FULL           IAM Page  Mixed Ext        
(1:118)      - (1:122)      =     ALLOCATED   0_PCT_FULL                     Mixed Ext        
(1:123)      -              =     ALLOCATED   0_PCT_FULL           IAM Page  Mixed Ext        
(1:124)      -              =     ALLOCATED   0_PCT_FULL                     Mixed Ext        
(1:125)      -              =     ALLOCATED   0_PCT_FULL           IAM Page  Mixed Ext        
(1:126)      - (1:128)      =     ALLOCATED   0_PCT_FULL                     Mixed Ext        
(1:129)      - (1:131)      =     ALLOCATED   0_PCT_FULL           IAM Page  Mixed Ext        
(1:132)      - (1:133)      =     ALLOCATED   0_PCT_FULL                     Mixed Ext        
(1:134)      -              =     ALLOCATED   0_PCT_FULL           IAM Page  Mixed Ext        
(1:135)      - (1:136)      =     ALLOCATED   0_PCT_FULL                     Mixed Ext        
(1:137)      -              =     ALLOCATED   0_PCT_FULL           IAM Page  Mixed Ext        
(1:138)      - (1:140)      =     ALLOCATED   0_PCT_FULL                     Mixed Ext        
(1:141)      -              =     ALLOCATED   0_PCT_FULL           IAM Page  Mixed Ext        
(1:142)      - (1:151)      =     ALLOCATED   0_PCT_FULL                     Mixed Ext        
(1:152)      -              =     ALLOCATED   0_PCT_FULL           IAM Page  Mixed Ext        
(1:153)      -              =     ALLOCATED   0_PCT_FULL                     Mixed Ext        
(1:154)      -              =     ALLOCATED   0_PCT_FULL           IAM Page  Mixed Ext        
(1:155)      - (1:156)      =     ALLOCATED   0_PCT_FULL                     Mixed Ext        
(1:157)      -              =     ALLOCATED   0_PCT_FULL           IAM Page  Mixed Ext        
(1:158)      - (1:161)      =     ALLOCATED   0_PCT_FULL                     Mixed Ext        
(1:162)      -              = NOT ALLOCATED   0_PCT_FULL                     Mixed Ext        
(1:163)      - (1:165)      =     ALLOCATED   0_PCT_FULL                     Mixed Ext        
(1:166)      -              = NOT ALLOCATED   0_PCT_FULL                     Mixed Ext        
(1:167)      -              =     ALLOCATED   0_PCT_FULL                     Mixed Ext        
(1:168)      - (1:174)      =     ALLOCATED   0_PCT_FULL                                      
(1:175)      -              = NOT ALLOCATED   0_PCT_FULL                                      
(1:176)      - (1:183)      =     ALLOCATED   0_PCT_FULL                                      
(1:184)      - (1:187)      =     ALLOCATED   0_PCT_FULL                     Mixed Ext        
(1:188)      - (1:191)      = NOT ALLOCATED   0_PCT_FULL                     Mixed Ext        
(1:192)      -              =     ALLOCATED   0_PCT_FULL                                      
(1:193)      - (1:199)      = NOT ALLOCATED   0_PCT_FULL                                      
(1:200)      - (1:201)      =     ALLOCATED   0_PCT_FULL                     Mixed Ext        
(1:202)      - (1:203)      = NOT ALLOCATED   0_PCT_FULL                     Mixed Ext        
(1:204)      -              =     ALLOCATED   0_PCT_FULL                     Mixed Ext        
(1:205)      - (1:206)      = NOT ALLOCATED   0_PCT_FULL                     Mixed Ext        
(1:207)      -              =     ALLOCATED   0_PCT_FULL                     Mixed Ext        
(1:208)      - (1:213)      = NOT ALLOCATED   0_PCT_FULL                     Mixed Ext        
(1:214)      - (1:215)      =     ALLOCATED   0_PCT_FULL                     Mixed Ext        
(1:216)      - (1:223)      =     ALLOCATED   0_PCT_FULL                                      
(1:224)      - (1:227)      =     ALLOCATED   0_PCT_FULL                     Mixed Ext        
(1:228)      -              =     ALLOCATED   0_PCT_FULL           IAM Page  Mixed Ext        
(1:229)      -              =     ALLOCATED   0_PCT_FULL                     Mixed Ext        
(1:230)      -              =     ALLOCATED   0_PCT_FULL           IAM Page  Mixed Ext        
(1:231)      -              =     ALLOCATED   0_PCT_FULL                     Mixed Ext        
(1:232)      - (1:236)      =     ALLOCATED   0_PCT_FULL                                      
(1:237)      - (1:239)      = NOT ALLOCATED   0_PCT_FULL                                      
(1:240)      -              =     ALLOCATED   0_PCT_FULL                                      
(1:241)      - (1:247)      = NOT ALLOCATED   0_PCT_FULL                                      
(1:248)      - (1:256)      =     ALLOCATED   0_PCT_FULL                                      
(1:257)      - (1:263)      = NOT ALLOCATED   0_PCT_FULL                                      
(1:264)      -              =     ALLOCATED   0_PCT_FULL                                      
(1:265)      - (1:271)      = NOT ALLOCATED   0_PCT_FULL                                      
(1:272)      - (1:279)      =     ALLOCATED   0_PCT_FULL                                      
(1:280)      - (1:295)      = NOT ALLOCATED   0_PCT_FULL                                      
(1:296)      - (1:303)      =     ALLOCATED   0_PCT_FULL                                      
(1:304)      - (1:309)      =     ALLOCATED   0_PCT_FULL                     Mixed Ext        
(1:310)      -              =     ALLOCATED   0_PCT_FULL           IAM Page  Mixed Ext        
(1:311)      -              =     ALLOCATED   0_PCT_FULL                     Mixed Ext        
(1:312)      -              =     ALLOCATED   0_PCT_FULL           IAM Page  Mixed Ext        
(1:313)      -              =     ALLOCATED   0_PCT_FULL                     Mixed Ext        
(1:314)      -              =     ALLOCATED   0_PCT_FULL           IAM Page  Mixed Ext        
(1:315)      -              =     ALLOCATED   0_PCT_FULL                     Mixed Ext        
(1:316)      -              =     ALLOCATED   0_PCT_FULL           IAM Page  Mixed Ext        
(1:317)      -              =     ALLOCATED   0_PCT_FULL                     Mixed Ext        
(1:318)      -              =     ALLOCATED   0_PCT_FULL           IAM Page  Mixed Ext        
(1:319)      -              =     ALLOCATED   0_PCT_FULL                     Mixed Ext        
(1:320)      - (1:322)      =     ALLOCATED   0_PCT_FULL           IAM Page  Mixed Ext        
(1:323)      - (1:324)      =     ALLOCATED 100_PCT_FULL                     Mixed Ext        
(1:325)      -              =     ALLOCATED  80_PCT_FULL                     Mixed Ext        
(1:326)      - (1:327)      = NOT ALLOCATED   0_PCT_FULL                                      
(1:328)      - (1:347)      =     ALLOCATED   0_PCT_FULL                                      
(1:348)      - (1:359)      = NOT ALLOCATED   0_PCT_FULL                                      
(1:360)      -              =     ALLOCATED   0_PCT_FULL                                      
(1:361)      - (1:391)      = NOT ALLOCATED   0_PCT_FULL                                      
(1:392)      - (1:411)      =     ALLOCATED   0_PCT_FULL                                      
(1:412)      - (1:423)      = NOT ALLOCATED   0_PCT_FULL                                      
(1:424)      -              =     ALLOCATED   0_PCT_FULL                                      
(1:425)      - (1:8087)     = NOT ALLOCATED   0_PCT_FULL                                       

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Summary

Για να βρει ο SQL Server την επόμενη ελεύθερη σελίδα την οποία θα χρησιμοποιήσει για τοποθετήσει τα δεδομένα διαβάζει τις PFS pages. Φυσικά δεν χρησιμοποιείται μόνο για αυτό το σκοπό αλλά και για άλλους σκοπούς όπως για τις διαδικασίες καθαρισμού των ghost records κ.α

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.