PFS stands for Page
Free Space. Every database file is split
(logically) into PFS intervals with the first PFS as Page 1. A PFS
interval is 8088 pages, or about 64MB. A PFS has one byte for each page in the
PFS interval (not including itself).
The bits in each byte has some meaning as
illustrated below
- bits 0-2: how much free space is on the page
- 000 is empty
- 001 is 1 to 50% full
- 010 is 51 to 80% full
- 011 is 81 to 95% full
- 100 is 96 to 100% full
- bit 3 : 1 if page has ghost records else 0
- bit 4 : 1 if an IAM page else 0
- bit 5 : 1 if mixed extent, 0 if uniform
- bit 6 : 1 if page allocated, 0 if unallocated
Without wasting
let’s see this through an example. Using the database creation script mentioned
above in Data File section, create a database called Page. Fire the DBCC PAGE on
the PFS page as show below. I have not copied the complete output of the command
as it would be too long.
DBCC TRACEON
(3604);
DBCC PAGE (Page, 1,
1, 3);
PFS: Page Alloc Status @0x000000000E0CA000
(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 IAM Page Mixed Ext
(1:9) - = ALLOCATED
100_PCT_FULL Mixed Ext
(1:10) - = ALLOCATED
0_PCT_FULL Mixed Ext
(1:11) - = ALLOCATED
0_PCT_FULL IAM Page Mixed Ext
(1:12) - = ALLOCATED
100_PCT_FULL IAM Page Mixed Ext
(1:13) - (1:16) = ALLOCATED
0_PCT_FULL Mixed Ext
(1:17) - = ALLOCATED
0_PCT_FULL IAM Page Mixed Ext
(1:18) - (1:19) = ALLOCATED
0_PCT_FULL Mixed Ext
(1:20) - = ALLOCATED
0_PCT_FULL IAM Page Mixed Ext
(1:21) - (1:25) = ALLOCATED
0_PCT_FULL Mixed Ext
(1:26) - = ALLOCATED
0_PCT_FULL IAM Page Mixed Ext
(1:27) - = ALLOCATED
0_PCT_FULL Mixed Ext
(1:28) - = ALLOCATED
0_PCT_FULL IAM Page Mixed Ext
(1:29) - = ALLOCATED
0_PCT_FULL Mixed Ext
(1:30) - = ALLOCATED
0_PCT_FULL IAM Page Mixed Ext
(1:31) - = ALLOCATED
0_PCT_FULL Mixed Ext
(1:32) - = ALLOCATED
50_PCT_FULL Mixed Ext
(1:33) - = ALLOCATED
0_PCT_FULL Mixed Ext
(1:34) - = ALLOCATED
0_PCT_FULL IAM Page Mixed Ext
(1:35) - = ALLOCATED
0_PCT_FULL Mixed Ext
(1:36) - = ALLOCATED
0_PCT_FULL IAM Page Mixed Ext
(1:37) - = ALLOCATED
0_PCT_FULL Mixed Ext
(1:38) - = ALLOCATED
0_PCT_FULL IAM Page Mixed Ext
(1:39) - = ALLOCATED
0_PCT_FULL Mixed Ext
(1:40) - (1:41) = ALLOCATED
0_PCT_FULL
(1:42) - = NOT ALLOCATED
0_PCT_FULL IAM Page Mixed Ext
(1:43) - = NOT ALLOCATED
0_PCT_FULL Mixed Ext
(1:44) - = NOT ALLOCATED
0_PCT_FULL IAM Page Mixed Ext
(1:45) - (1:46) = NOT ALLOCATED
0_PCT_FULL Mixed Ext
(1:47) - = NOT ALLOCATED
0_PCT_FULL IAM Page Mixed Ext
(1:48) - (1:53) = ALLOCATED
0_PCT_FULL Mixed Ext
(1:54) - = ALLOCATED
0_PCT_FULL IAM Page Mixed Ext
(1:55) - = ALLOCATED
0_PCT_FULL Mixed Ext
(1:56) - (1:63) = ALLOCATED
0_PCT_FULL
(1:64) - = ALLOCATED
0_PCT_FULL Mixed Ext
(1:65) - = ALLOCATED
0_PCT_FULL IAM Page Mixed Ext
(1:66) - (1:71) = ALLOCATED
0_PCT_FULL Mixed Ext
(1:72) - = ALLOCATED
0_PCT_FULL IAM Page Mixed Ext
(1:73) - = ALLOCATED
0_PCT_FULL Mixed Ext
(1:74) - = ALLOCATED
0_PCT_FULL IAM Page Mixed Ext
(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) - = NOT ALLOCATED
0_PCT_FULL Mixed Ext
(1:80)
- = NOT ALLOCATED 0_PCT_FULL IAM Page Mixed
Ext
(1:81) - = ALLOCATED
0_PCT_FULL Mixed Ext
(1:82) - = ALLOCATED
0_PCT_FULL IAM Page Mixed Ext
(1:83) - = ALLOCATED
0_PCT_FULL Mixed Ext
(1:84) - = ALLOCATED
0_PCT_FULL IAM Page Mixed Ext
(1:85) - = ALLOCATED
0_PCT_FULL Mixed Ext
(1:86) - = ALLOCATED
0_PCT_FULL IAM Page Mixed Ext
(1:87) - = ALLOCATED
0_PCT_FULL Mixed Ext
(1:88) - = ALLOCATED
0_PCT_FULL IAM Page Mixed Ext
(1:89)
- = NOT ALLOCATED 0_PCT_FULL Mixed
Ext
(1:90) - = NOT ALLOCATED
0_PCT_FULL IAM Page Mixed Ext
(1:91) - = ALLOCATED
0_PCT_FULL Mixed Ext
(1:92) - = ALLOCATED
0_PCT_FULL IAM Page Mixed Ext
(1:93) - = NOT ALLOCATED
0_PCT_FULL Mixed Ext
(1:94) - = NOT ALLOCATED
0_PCT_FULL IAM Page Mixed Ext
(1:95) - = ALLOCATED
0_PCT_FULL Has Ghost 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) - = NOT ALLOCATED
0_PCT_FULL Mixed Ext
(1:110) - = NOT ALLOCATED
0_PCT_FULL IAM Page Mixed Ext
(1:111) - (1:113) = ALLOCATED
0_PCT_FULL Mixed Ext
(1:114) - (1:115) = NOT ALLOCATED
0_PCT_FULL Mixed Ext
(1:116) - = ALLOCATED
0_PCT_FULL Mixed Ext
(1:117) - = ALLOCATED
0_PCT_FULL IAM Page Mixed Ext
(1:118) - = NOT ALLOCATED
0_PCT_FULL Mixed Ext
(1:119) - = NOT ALLOCATED
0_PCT_FULL IAM Page Mixed Ext
(1:120) - = NOT ALLOCATED
0_PCT_FULL Mixed Ext
(1:121) - = NOT ALLOCATED
0_PCT_FULL IAM Page Mixed Ext
(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) - = NOT ALLOCATED
0_PCT_FULL Has Ghost Mixed Ext
(1:127) - = NOT ALLOCATED
0_PCT_FULL IAM Page Mixed Ext
(1:128) - = ALLOCATED
0_PCT_FULL Mixed Ext
(1:129) - = ALLOCATED
0_PCT_FULL IAM Page Mixed Ext
(1:130) - = ALLOCATED
0_PCT_FULL Mixed Ext
(1:131) - = ALLOCATED
0_PCT_FULL IAM Page Mixed Ext
(1:132) - = ALLOCATED
0_PCT_FULL Mixed Ext
(1:133) - = ALLOCATED
0_PCT_FULL IAM Page Mixed Ext
(1:134) - = ALLOCATED
0_PCT_FULL Mixed Ext
(1:135) - = ALLOCATED
0_PCT_FULL IAM Page Mixed Ext
(1:136) - = ALLOCATED
0_PCT_FULL Mixed Ext
(1:137) - = ALLOCATED
0_PCT_FULL IAM Page Mixed Ext
(1:138) - = ALLOCATED
0_PCT_FULL Mixed Ext
(1:139) - = ALLOCATED
0_PCT_FULL IAM Page Mixed Ext
(1:140) - = ALLOCATED
0_PCT_FULL Mixed Ext
(1:141) - = ALLOCATED
0_PCT_FULL IAM Page Mixed Ext
(1:142) - (1:144) = ALLOCATED
0_PCT_FULL Mixed Ext
(1:145) - = ALLOCATED
0_PCT_FULL IAM Page Mixed Ext
(1:146) - = ALLOCATED
0_PCT_FULL Mixed Ext
(1:147) - = ALLOCATED
0_PCT_FULL IAM Page Mixed Ext
(1:148) - = NOT ALLOCATED
50_PCT_FULL Mixed Ext
(1:149) - = ALLOCATED
0_PCT_FULL IAM Page Mixed Ext
(1:150) - = ALLOCATED
0_PCT_FULL Mixed Ext
(1:151) - = NOT ALLOCATED
100_PCT_FULL Mixed Ext
(1:152) - = ALLOCATED
0_PCT_FULL Mixed Ext
(1:153) - = ALLOCATED
0_PCT_FULL IAM Page Mixed Ext
(1:154) - = ALLOCATED
0_PCT_FULL Mixed Ext
(1:155) - = ALLOCATED
0_PCT_FULL IAM Page Mixed Ext
(1:156) - (1:158) = ALLOCATED
0_PCT_FULL Mixed Ext
(1:159) - = ALLOCATED
100_PCT_FULL Mixed Ext
(1:160) - = ALLOCATED
50_PCT_FULL Mixed Ext
(1:161) - = ALLOCATED
0_PCT_FULL Mixed Ext
(1:162) - = ALLOCATED
0_PCT_FULL IAM Page Mixed Ext
(1:163) - = ALLOCATED
0_PCT_FULL Mixed Ext
(1:164) - = ALLOCATED
0_PCT_FULL IAM Page Mixed Ext
(1:165) - = ALLOCATED
0_PCT_FULL Mixed Ext
(1:166) - = ALLOCATED
0_PCT_FULL IAM Page Mixed Ext
(1:167) - = ALLOCATED
0_PCT_FULL Mixed Ext
(1:168) - = ALLOCATED
0_PCT_FULL IAM Page Mixed Ext
(1:169) - = ALLOCATED
0_PCT_FULL Mixed Ext
(1:170) - = ALLOCATED
0_PCT_FULL IAM Page Mixed Ext
(1:171) - = ALLOCATED
0_PCT_FULL Mixed Ext
(1:172) - = ALLOCATED
0_PCT_FULL IAM Page Mixed Ext
(1:173) - (1:175) = NOT ALLOCATED
0_PCT_FULL
(1:176) - (1:181) = ALLOCATED
0_PCT_FULL
(1:182) -
(1:383) = NOT ALLOCATED 0_PCT_FULL
I will fire the DBCC
PAGE command again on the PFS page; however this time I will change the option
of output format from 3 to 1. Below is the output of executing the command and
again I have not copied the complete content.
DBCC PAGE (Page, 1,
1, 1); -- Executing this gives the output below
Memory Dump @0x000000000F49A060
0000000000000000: 00009c1f 44444444 00004444
70646070 †..œ.DDDD..DDpd`p
0000000000000010: 74606060 60706060 70606060
60607060 †t````p``p`````p`
0000000000000020: 70607060 61607060 70607060
40403020 †p`p`a`p`p`p`@@0
0000000000000030: 30202030 60606060 60607060
40404040 †0 0``````p`@@@@
0000000000000040: 40404040 60706060 60606060
70607060 †@@@@`p``````p`p`
0000000000000050: 70606020 30607060 70607060 70203060 †p`` 0`p`p`p`p 0`
0000000000000060: 70203068 70607060 70607060
70607060 †p 0hp`p`p`p`p`p`
0000000000000070: 70203060 60602020 60702030
20306070 †p 0``` `p 0 0`p
0000000000000080: 60702830 60706070 60706070
60706070 †`p(0`p`p`p`p`p`p
0000000000000090: 60706060 60706070 21706024
60706070 †`p```p`p!p`$`p`p
00000000000000A0: 60606064 61607060 70607060
70607060 †```da`p`p`p`p`p`
00000000000000B0:
70000000 40404040 40400000 00000000 †p...@@@@@@......
Now I will create a table called Test on this
Page database using the script below and populate 1 record in this table.
CREATE TABLE Test (col1 int identity (1,1), col2
varchar(500));
GO
INSERT INTO Test VALUES (REPLICATE('a',500));
Check which pages
have been allocated to this table Test using the DBCC IND command.
DBCC IND (Page,
Test, 1);
Page 80 is the data
page and 89 is the IAM page. In order to check what these pages were before
creation of table Test, we will go to the output of the DBCC PAGE command that
we fired previously. As highlighted in green above, page 80 was an IAM page from
mixed extent and page 89 was a non IAM page. Also looking at the memory dump of
PFS page, the highlighted bytes in pink are the bytes for page 80 and 89. Let’s
expand those bytes
Page 80 has byte as
(30) = 0011 – 0000 (in binary each digit) = 00110000
As per the
explanation above for each bit
Bit 0-2 = 000 = page
is free
Bit 3 = 0 = no ghost
records
Bit 4 = 1 = it is an
IAM page
Bit 5 = 1 = it is
from a mixed extent
Bit 6 = 0 = page 80
is not allocated yet
Page 89 has byte as
(20) = 0010 – 0000 (in binary each digit) = 00100000
As per the
explanation above for each bit
Bit 0-2 = 000 = page
is free
Bit 3 = 0 = no ghost
records
Bit 4 = 0 = it is
not an IAM page
Bit 5 = 1 = it is
from a mixed extent
Bit 6 = 0 = page 89
is not allocated yet
We haven’t fired the
DBCC PAGE command after creation of the table and data insertion. Let’s do that
and see the output
DBCC PAGE (Page,
Test, 1, 3);
PFS: Page Alloc Status @0x000000000EF4A000
(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 IAM Page Mixed Ext
(1:9) - = ALLOCATED
100_PCT_FULL Mixed Ext
(1:10) - = ALLOCATED
0_PCT_FULL Mixed Ext
(1:11) - = ALLOCATED
0_PCT_FULL IAM Page Mixed Ext
(1:12) - = ALLOCATED
100_PCT_FULL IAM Page Mixed Ext
(1:13) - (1:16) = ALLOCATED
0_PCT_FULL Mixed Ext
(1:17) - = ALLOCATED
0_PCT_FULL IAM Page Mixed Ext
(1:18) - = ALLOCATED
0_PCT_FULL Has Ghost Mixed Ext
(1:19) - = ALLOCATED
0_PCT_FULL Mixed Ext
(1:20) - = ALLOCATED
0_PCT_FULL IAM Page Mixed Ext
(1:21) - (1:25) = ALLOCATED
0_PCT_FULL Mixed Ext
(1:26) - = ALLOCATED
0_PCT_FULL IAM Page Mixed Ext
(1:27) - = ALLOCATED
0_PCT_FULL Mixed Ext
(1:28) - = ALLOCATED
0_PCT_FULL IAM Page Mixed Ext
(1:29) - = ALLOCATED
0_PCT_FULL Mixed Ext
(1:30) - = ALLOCATED
0_PCT_FULL IAM Page Mixed Ext
(1:31) - = ALLOCATED
0_PCT_FULL Mixed Ext
(1:32) - = ALLOCATED
50_PCT_FULL Mixed Ext
(1:33) - = ALLOCATED
0_PCT_FULL Mixed Ext
(1:34) - = ALLOCATED
0_PCT_FULL IAM Page Mixed Ext
(1:35) - = ALLOCATED
0_PCT_FULL Mixed Ext
(1:36) - = ALLOCATED
0_PCT_FULL IAM Page Mixed Ext
(1:37) - = ALLOCATED
0_PCT_FULL Mixed Ext
(1:38) - = ALLOCATED
0_PCT_FULL IAM Page Mixed Ext
(1:39) - = ALLOCATED
0_PCT_FULL Mixed Ext
(1:40) - (1:41) = ALLOCATED
0_PCT_FULL
(1:42) - = NOT ALLOCATED
0_PCT_FULL IAM Page Mixed Ext
(1:43) - = NOT ALLOCATED
0_PCT_FULL Mixed Ext
(1:44) - = NOT ALLOCATED
0_PCT_FULL IAM Page Mixed Ext
(1:45) - (1:46) = NOT ALLOCATED
0_PCT_FULL Mixed Ext
(1:47) - = NOT ALLOCATED
0_PCT_FULL IAM Page Mixed Ext
(1:48) - (1:53) = ALLOCATED
0_PCT_FULL Mixed Ext
(1:54) - = ALLOCATED
0_PCT_FULL IAM Page Mixed Ext
(1:55) - = ALLOCATED
0_PCT_FULL Has Ghost Mixed Ext
(1:56) - (1:63) = ALLOCATED
0_PCT_FULL
(1:64) - = ALLOCATED
0_PCT_FULL Mixed Ext
(1:65) - = ALLOCATED
0_PCT_FULL IAM Page Mixed Ext
(1:66) - (1:71) = ALLOCATED
0_PCT_FULL Mixed Ext
(1:72) - = ALLOCATED
0_PCT_FULL IAM Page Mixed Ext
(1:73) - = ALLOCATED
0_PCT_FULL Mixed Ext
(1:74) - = ALLOCATED
0_PCT_FULL IAM Page Mixed Ext
(1:75) - = ALLOCATED
0_PCT_FULL Mixed Ext
(1:76) - = ALLOCATED
0_PCT_FULL IAM Page Mixed Ext
(1:77) - (1:79) = ALLOCATED
0_PCT_FULL Mixed Ext
(1:80)
- = ALLOCATED 50_PCT_FULL Mixed
Ext
(1:81) - = ALLOCATED
0_PCT_FULL Mixed Ext
(1:82) - = ALLOCATED
0_PCT_FULL IAM Page Mixed Ext
(1:83) - = ALLOCATED
0_PCT_FULL Mixed Ext
(1:84) - = ALLOCATED
0_PCT_FULL IAM Page Mixed Ext
(1:85) - = ALLOCATED
0_PCT_FULL Mixed Ext
(1:86) - = ALLOCATED
0_PCT_FULL IAM Page Mixed Ext
(1:87) - = ALLOCATED
0_PCT_FULL Mixed Ext
(1:88) -
(1:89) = ALLOCATED 0_PCT_FULL IAM Page Mixed
Ext
(1:90) - = NOT ALLOCATED
0_PCT_FULL IAM Page Mixed Ext
(1:91) - = ALLOCATED
0_PCT_FULL Mixed Ext
(1:92) - = ALLOCATED
0_PCT_FULL IAM Page Mixed Ext
(1:93) - = NOT ALLOCATED
0_PCT_FULL Mixed Ext
(1:94) - = NOT ALLOCATED
0_PCT_FULL IAM Page Mixed Ext
(1:95) - = ALLOCATED
0_PCT_FULL Has Ghost 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) - = NOT ALLOCATED
0_PCT_FULL Mixed Ext
(1:110) - = NOT ALLOCATED
0_PCT_FULL IAM Page Mixed Ext
(1:111) - (1:113) = ALLOCATED
0_PCT_FULL Mixed Ext
(1:114) - (1:115) = NOT ALLOCATED
0_PCT_FULL Mixed Ext
(1:116) - = ALLOCATED
0_PCT_FULL Has Ghost Mixed Ext
(1:117) - = ALLOCATED
0_PCT_FULL IAM Page Mixed Ext
(1:118) - = NOT ALLOCATED
0_PCT_FULL Mixed Ext
(1:119) - = NOT ALLOCATED
0_PCT_FULL IAM Page Mixed Ext
(1:120) - = NOT ALLOCATED
0_PCT_FULL Mixed Ext
(1:121) - = NOT ALLOCATED
0_PCT_FULL IAM Page Mixed Ext
(1:122) - = ALLOCATED
0_PCT_FULL Has Ghost 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) - = NOT ALLOCATED
0_PCT_FULL Has Ghost Mixed Ext
(1:127) - = NOT ALLOCATED
0_PCT_FULL IAM Page Mixed Ext
(1:128) - = ALLOCATED
0_PCT_FULL Mixed Ext
(1:129) - = ALLOCATED
0_PCT_FULL IAM Page Mixed Ext
(1:130) - = ALLOCATED
0_PCT_FULL Has Ghost Mixed Ext
(1:131) - = ALLOCATED
0_PCT_FULL IAM Page Mixed Ext
(1:132) - = ALLOCATED
0_PCT_FULL Mixed Ext
(1:133) - = ALLOCATED
0_PCT_FULL IAM Page Mixed Ext
(1:134) - = ALLOCATED
0_PCT_FULL Mixed Ext
(1:135) - = ALLOCATED
0_PCT_FULL IAM Page Mixed Ext
(1:136) - = ALLOCATED
0_PCT_FULL Mixed Ext
(1:137) - = ALLOCATED
0_PCT_FULL IAM Page Mixed Ext
(1:138) - = ALLOCATED
0_PCT_FULL Mixed Ext
(1:139) - = ALLOCATED
0_PCT_FULL IAM Page Mixed Ext
(1:140) - = ALLOCATED
0_PCT_FULL Mixed Ext
(1:141) - = ALLOCATED
0_PCT_FULL IAM Page Mixed Ext
(1:142) - (1:144) = ALLOCATED
0_PCT_FULL Mixed Ext
(1:145) - = ALLOCATED
0_PCT_FULL IAM Page Mixed Ext
(1:146) - = ALLOCATED
0_PCT_FULL Mixed Ext
(1:147) - = ALLOCATED
0_PCT_FULL IAM Page Mixed Ext
(1:148) - = NOT ALLOCATED
50_PCT_FULL Mixed Ext
(1:149) - = ALLOCATED
0_PCT_FULL IAM Page Mixed Ext
(1:150) - = ALLOCATED
0_PCT_FULL Mixed Ext
(1:151) - = NOT ALLOCATED
100_PCT_FULL Mixed Ext
(1:152) - = ALLOCATED
0_PCT_FULL Mixed Ext
(1:153) - = ALLOCATED
0_PCT_FULL IAM Page Mixed Ext
(1:154) - = ALLOCATED
0_PCT_FULL Has Ghost Mixed Ext
(1:155) - = ALLOCATED
0_PCT_FULL IAM Page Mixed Ext
(1:156) - (1:158) = ALLOCATED
0_PCT_FULL Mixed Ext
(1:159) - = ALLOCATED
100_PCT_FULL Mixed Ext
(1:160) - = ALLOCATED
50_PCT_FULL Mixed Ext
(1:161) - = ALLOCATED
0_PCT_FULL Mixed Ext
(1:162) - = ALLOCATED
0_PCT_FULL IAM Page Mixed Ext
(1:163) - = ALLOCATED
0_PCT_FULL Mixed Ext
(1:164) - = ALLOCATED
0_PCT_FULL IAM Page Mixed Ext
(1:165) - = ALLOCATED
0_PCT_FULL Mixed Ext
(1:166) - = ALLOCATED
0_PCT_FULL IAM Page Mixed Ext
(1:167) - = ALLOCATED
0_PCT_FULL Has Ghost Mixed Ext
(1:168) - = ALLOCATED
0_PCT_FULL IAM Page Mixed Ext
(1:169) - = ALLOCATED
0_PCT_FULL Mixed Ext
(1:170) - = ALLOCATED
0_PCT_FULL IAM Page Mixed Ext
(1:171) - = ALLOCATED
0_PCT_FULL Mixed Ext
(1:172) - = ALLOCATED
0_PCT_FULL IAM Page Mixed Ext
(1:173) - (1:175) = NOT ALLOCATED
0_PCT_FULL
(1:176) - (1:181) = ALLOCATED
0_PCT_FULL
(1:182) - (1:383) = NOT ALLOCATED
0_PCT_FULL
You can see the change in the allocation
information in PFS. Page 80 no is no more IAM page and page 89 on the other hand
is an IAM page. Let’s see the byte information for these pages in PFS using
command as below
DBCC PAGE (Page, Test, 1, 1);
Memory Dump @0x000000000F92C060
0000000000000000: 00009c1f 44444444 00004444
70646070 †..œ.DDDD..DDpd`p
0000000000000010: 74606060 60706860 70606060
60607060 †t````ph`p`````p`
0000000000000020: 70607060 61607060 70607060
40403020 †p`p`a`p`p`p`@@0
0000000000000030: 30202030 60606060 60607068
40404040 †0 0``````ph@@@@
0000000000000040: 40404040 60706060 60606060
70607060 †@@@@`p``````p`p`
0000000000000050: 70606060 61607060 70607060 70703060 †p```a`p`p`p`pp0`
0000000000000060: 70203068 70607060 70607060
70607060 †p 0hp`p`p`p`p`p`
0000000000000070: 70203060 60602020 68702030
20306870 †p 0``` hp 0 0hp
0000000000000080: 60702830 60706870 60706070
60706070 †`p(0`php`p`p`p`p
0000000000000090: 60706060 60706070 21706024
60706870 †`p```p`p!p`$`php
00000000000000A0: 60606064 61607060 70607068
70607060 †```da`p`p`php`p`
00000000000000B0: 70000000 40404040 40400000
00000000 †p...@@@@@@......
Page 80 has byte as
(61) = 0110 – 0001 (in binary each digit) = 01100001
As per the
explanation above for each bit
Bit 0-2 = 001 = page
is 0 – 50% full
Bit 3 = 0 = no ghost
records
Bit 4 = 0 = it is
not an IAM page
Bit 5 = 1 = it is
from a mixed extent
Bit 6 = 1 = page 80
is allocated now
Page 89 has byte as
(70) = 0111 – 0000 (in binary each digit) = 01110000
As per the
explanation above for each bit
Bit 0-2 = 000 = page
is free
Bit 3 = 0 = no ghost
records
Bit 4 = 1 = it an
IAM page
Bit 5 = 1 = it is
from a mixed extent
Bit 6 = 1 = page 89 is allocated
Couple of important
points to note here
- Free space (bits 0-2) is only tracked for pages storing LOB values (i.e. text/image in SQL Server 2000, plus varchar(max)/varbinary(max)/XML and row-overflow data in SQL Server 2005) and heap data pages. This is because these are the only pages that store unordered data and so insertions can occur anywhere there's space. For indexes, there's an explicit ordering so there's no choice in the insertion point.
- When table Test had a clustered index on it, deletion of record still left the page with that record with the m_ghostRecCnt = 1, however performing the same deletion on Test when it was heap, cleaned the data page immediately with the m_ghostRecCnt = 0.
