讀取 heap 的 FirstIAM

use tempdb

create table t(id int identity,c1 char(894))
–就算指定 100% 的 fillfactor 和 pad_index,在資料表擺放資料或建置索引時
–每個資料頁依然會保留空間
create index idx on t(id,c1) with fillfactor=100, PAD_INDEX
set nocount on
go
insert t values(‘hello’)
go 4000

 

select convert(char(30), name) ‘name’,
id,
indid,
convert(varchar(2), (convert(int, substring(first, 6, 1)) * power(2, 8)) +
(convert(int, substring(first, 5, 1)))) + ‘:’ +
convert(varchar(11),
(convert(int, substring(first, 4, 1)) * power(2, 24)) +
(convert(int, substring(first, 3, 1)) * power(2, 16)) +
(convert(int, substring(first, 2, 1)) * power(2, 8)) +
(convert(int, substring(first, 1, 1)))) ‘first’,
first,
convert(varchar(2), (convert(int, substring(root, 6, 1)) * power(2, 8)) +
(convert(int, substring(root, 5, 1)))) + ‘:’ +
convert(varchar(11),
(convert(int, substring(root, 4, 1)) * power(2, 24)) +
(convert(int, substring(root, 3, 1)) * power(2, 16)) +
(convert(int, substring(root, 2, 1)) * power(2, 8)) +
(convert(int, substring(root, 1, 1)))) ‘root’,
root,
convert(varchar(2), (convert(int, substring(firstIAM, 6, 1)) * power(2, 8))
+ (convert(int, substring(firstIAM, 5, 1)))) + ‘:’ +
convert(varchar(11),
(convert(int, substring(firstIAM, 4, 1)) * power(2, 24)) +
(convert(int, substring(firstIAM, 3, 1)) * power(2, 16)) +
(convert(int, substring(firstIAM, 2, 1)) * power(2, 8)) +
(convert(int, substring(firstIAM, 1, 1)))) ‘firstIAM’,
firstIAM
from sys.sysindexes
where id=OBJECT_ID(‘t’)

image

dbcc traceon(3604)
dbcc page(‘tempdb’,1,342,3)

執行結果

PAGE: (1:342)

BUFFER:

BUF @0x0000000089FC5A40

bpage = 0x0000000089646000           bhash = 0x0000000000000000           bpageno = (1:342)
bdbid = 2                            breferences = 0                      bcputicks = 0
bsampleCount = 0                     bUse1 = 11231                        bstat = 0xc0000b
blog = 0xcbbbb89b                    bnext = 0x0000000000000000          

PAGE HEADER:

Page @0x0000000089646000

m_pageId = (1:342)                   m_headerVersion = 1                  m_type = 10
m_typeFlagBits = 0x0                 m_level = 0                          m_flagBits = 0x0
m_objId (AllocUnitId.idObj) = 580029 m_indexId (AllocUnitId.idInd) = 8448
Metadata: AllocUnitId = 2377900641264402432                              
Metadata: PartitionId = 8502796127483068416                               Metadata: IndexId = 0
Metadata: ObjectId = 1694017166      m_prevPage = (0:0)                   m_nextPage = (0:0)
pminlen = 90                         m_slotCnt = 2                        m_freeCnt = 6
m_freeData = 8182                    m_reservedCnt = 0                    m_lsn = (420:255:130)
m_xactReserved = 0                   m_xdesId = (0:0)                     m_ghostRecCnt = 0
m_tornBits = 0                      

Allocation Status

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

IAM: Header @0x0000000018CFA064 Slot 0, Offset 96

sequenceNumber = 0                   status = 0x0                         objectId = 0
indexId = 0                          page_count = 0                       start_pg = (1:0)

IAM: Single Page Allocations @0x0000000018CFA08E

Slot 0 = (1:330)                     Slot 1 = (1:359)                     Slot 2 = (1:364)
Slot 3 = (1:372)                     Slot 4 = (1:377)                     Slot 5 = (1:379)
Slot 6 = (1:382)                     Slot 7 = (1:387)                    

IAM: Extent Alloc Status Slot 1 @0x0000000018CFA0C2

(1:0)        – (1:448)      = NOT ALLOCATED                              
(1:456)      -              =     ALLOCATED                              
(1:464)      – (1:472)      = NOT ALLOCATED                              
(1:480)      -              =     ALLOCATED                              
(1:488)      -              = NOT ALLOCATED                              
(1:496)      -              =     ALLOCATED                              
(1:504)      – (1:552)      = NOT ALLOCATED                              
(1:560)      -              =     ALLOCATED                              
(1:568)      – (1:584)      = NOT ALLOCATED                              
(1:592)      -              =     ALLOCATED                              
(1:600)      -              = NOT ALLOCATED                              
(1:608)      -              =     ALLOCATED                              
(1:616)      – (1:632)      = NOT ALLOCATED                              
(1:640)      -              =     ALLOCATED                              
(1:648)      – (1:688)      = NOT ALLOCATED                              
(1:696)      -              =     ALLOCATED                              
(1:704)      – (1:752)      = NOT ALLOCATED                              
(1:760)      -              =     ALLOCATED                              
(1:768)      -              = NOT ALLOCATED                              
(1:776)      -              =     ALLOCATED                               
               
                         

發表迴響

在下方填入你的資料或按右方圖示以社群網站登入:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / 變更 )

Twitter picture

You are commenting using your Twitter account. Log Out / 變更 )

Facebook照片

You are commenting using your Facebook account. Log Out / 變更 )

Google+ photo

You are commenting using your Google+ account. Log Out / 變更 )

連結到 %s

%d 位部落客按了讚: