Heap 的 Row Forward 與 Clustered Index 的 Page Split

觀察動態長度資料修改時,若該 page 無法提供足夠空間放變動後資料,會造成的影響。

若資料表為 Heap,則 SQL Server 會在該紀錄原址放一個 Forward 指標,並將新資料放在新增的 Page 內,這在修改上效率較佳。但查詢大量資料時效率較差,因為需來回逐紀錄讀取 Page

若資料表為 Clustered,則 SQL Server 會做 Page Split,這在大量修改超過長度的紀錄時效率較差。但查詢大量資料時效率較佳

Heap

建立 Heap:

use tempdb
drop table Heap
create table Heap(c1 int identity,c2 varchar(1000))
go
set nocount on
insert Heap values(‘a’)
go 1000

begin tran
    update Heap set c2=REPLICATE(‘a’,500) where c1<10

觀察 Performance Counter

\SQL Server:Access Methods\Forwarded Records/sec  : 9.007

\SQL Server:Access Methods\Page Splits/sec : 2.002


    exec sp_lock @@spid

image

    dbcc traceon(3604)
    dbcc page(tempdb,1,455,3)
commit

DBCC 的執行已經完成。如果 DBCC 印出錯誤訊息,請連絡您的系統管理員。

PAGE: (1:455)

BUFFER:

BUF @0x0000000097FF6A00

bpage = 0x0000000097E70000           bhash = 0x0000000000000000           bpageno = (1:455)
bdbid = 2                            breferences = 0                      bcputicks = 0
bsampleCount = 0                     bUse1 = 26877                        bstat = 0xc0000b
blog = 0xcbcbbbbb                    bnext = 0x0000000000000000          

PAGE HEADER:

Page @0x0000000097E70000

m_pageId = (1:455)                   m_headerVersion = 1                  m_type = 1
m_typeFlagBits = 0x4                 m_level = 0                          m_flagBits = 0x8
m_objId (AllocUnitId.idObj) = 628154 m_indexId (AllocUnitId.idInd) = 28160
Metadata: AllocUnitId = 7926335385338773504                              
Metadata: PartitionId = 7782220191937069056                               Metadata: IndexId = 0
Metadata: ObjectId = 1930489956      m_prevPage = (0:0)                   m_nextPage = (0:0)
pminlen = 8                          m_slotCnt = 428                      m_freeCnt = 455
m_freeData = 7025                    m_reservedCnt = 63                   m_lsn = (151:433:215)
m_xactReserved = 63                  m_xdesId = (0:21857)                 m_ghostRecCnt = 0
m_tornBits = 0                      

Allocation Status

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

Slot 0 Offset 0x1b20 Length 9

Record Type = FORWARDING_STUB        Record Attributes =                  Record Size = 9

Memory Dump @0x000000001694BB20

0000000000000000:   04e30100 00010090 00†††††††††††††††††………       
Forwarding to  =  file 1 page 483 slot 144                               

Slot 1 Offset 0x1b29 Length 9

 

select avg_fragmentation_in_percent,fragment_count,avg_fragment_size_in_pages,page_count,avg_page_space_used_in_percent,forwarded_record_count
from sys.dm_db_index_physical_stats(DB_ID(),NULL,NULL,NULL,’DETAILED’)
WHERE object_id=object_id(‘Heap’)

結果

avg_fragmentation_in_percent fragment_count       avg_fragment_size_in_pages page_count           avg_page_space_used_in_percent forwarded_record_count
—————————- ——————– ————————– ——————– —————————— ———————-
50                           2                    1.5                        3                    93.451939708426                9

查詢資料時會浪費 I/O,往返讀取 Forward 紀錄

set statistics io on
select * from Heap where c1 < 10

資料表 ‘Heap’。掃描計數 1,邏輯讀取 12,實體讀取 0,讀取前讀取 0,LOB 邏輯讀取 0,LOB 實體讀取 0,LOB 讀取前讀取 0。

SQL Server 2008 後才提供重整 Heap 的能力

alter table Heap rebuild

select avg_fragmentation_in_percent,fragment_count,avg_fragment_size_in_pages,page_count,avg_page_space_used_in_percent,forwarded_record_count
from sys.dm_db_index_physical_stats(DB_ID(),NULL,NULL,NULL,’DETAILED’)
WHERE object_id=object_id(‘Heap’)

avg_fragmentation_in_percent fragment_count       avg_fragment_size_in_pages page_count           avg_page_space_used_in_percent forwarded_record_count
—————————- ——————– ————————– ——————– —————————— ———————-
50                           2                    2                          4                    69.4495922905856               0

重整後 I/O 減少了

select * from Heap where c1 < 10

資料表 ‘Heap’。掃描計數 1,邏輯讀取 4,實體讀取 0,讀取前讀取 0,LOB 邏輯讀取 0,LOB 實體讀取 0,LOB 讀取前讀取 0。

 

Clustered Index

若是採用 Clustered Index,則會造成 Page Split

建立 Clustered Index:

drop table tblClustered
create table tblClustered(c1 int identity primary key,c2 varchar(1000))
go


set nocount on
insert tblClustered values(‘a’)
go 1000

begin tran
    update tblClustered set c2=REPLICATE(‘a’,500) where c1<10

觀察 Performance Counter

\SQL Server:Access Methods\Forwarded Records/sec : 0

\SQL Server:Access Methods\Page Splits/sec : 7.999

exec sp_lock @@spid

image

dbcc traceon(3604)
dbcc page(tempdb,1,448,3)
commit

DBCC 的執行已經完成。如果 DBCC 印出錯誤訊息,請連絡您的系統管理員。

PAGE: (1:448)

BUFFER:

BUF @0x0000000099FF2A40

bpage = 0x0000000099DC6000           bhash = 0x0000000000000000           bpageno = (1:448)
bdbid = 2                            breferences = 0                      bcputicks = 0
bsampleCount = 0                     bUse1 = 27917                        bstat = 0xc0000b
blog = 0xbcbcbbbb                    bnext = 0x0000000000000000          

PAGE HEADER:

Page @0x0000000099DC6000

m_pageId = (1:448)                   m_headerVersion = 1                  m_type = 1
m_typeFlagBits = 0x4                 m_level = 0                          m_flagBits = 0x8000
m_objId (AllocUnitId.idObj) = 647529 m_indexId (AllocUnitId.idInd) = 3328
Metadata: AllocUnitId = 936748764929523712                               
Metadata: PartitionId = 216172818896715776                                Metadata: IndexId = 1
Metadata: ObjectId = 39007220        m_prevPage = (1:240)                 m_nextPage = (1:247)
pminlen = 8                          m_slotCnt = 217                      m_freeCnt = 3192
m_freeData = 4582                    m_reservedCnt = 0                    m_lsn = (159:376:27)
m_xactReserved = 0                   m_xdesId = (0:0)                     m_ghostRecCnt = 0
m_tornBits = 0                      

Allocation Status

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

Slot 0 Offset 0x60 Length 515

Record Type = PRIMARY_RECORD         Record Attributes =  NULL_BITMAP VARIABLE_COLUMNS
Record Size = 515                   
Memory Dump @0x000000001011A060

0000000000000000:   30000800 08000000 02000001 00030261 †0…………..a
0000000000000010:   61616161 61616161 61616161 61616161 †aaaaaaaaaaaaaaaa
0000000000000020:   61616161 61616161 61616161 61616161 †aaaaaaaaaaaaaaaa
0000000000000030:   61616161 61616161 61616161 61616161 †aaaaaaaaaaaaaaaa
0000000000000040:   61616161 61616161 61616161 61616161 †aaaaaaaaaaaaaaaa
0000000000000050:   61616161 61616161 61616161 61616161 †aaaaaaaaaaaaaaaa
0000000000000060:   61616161 61616161 61616161 61616161 †aaaaaaaaaaaaaaaa
0000000000000070:   61616161 61616161 61616161 61616161 †aaaaaaaaaaaaaaaa
0000000000000080:   61616161 61616161 61616161 61616161 †aaaaaaaaaaaaaaaa
0000000000000090:   61616161 61616161 61616161 61616161 †aaaaaaaaaaaaaaaa
00000000000000A0:   61616161 61616161 61616161 61616161 †aaaaaaaaaaaaaaaa
00000000000000B0:   61616161 61616161 61616161 61616161 †aaaaaaaaaaaaaaaa
00000000000000C0:   61616161 61616161 61616161 61616161 †aaaaaaaaaaaaaaaa
00000000000000D0:   61616161 61616161 61616161 61616161 †aaaaaaaaaaaaaaaa
00000000000000E0:   61616161 61616161 61616161 61616161 †aaaaaaaaaaaaaaaa
00000000000000F0:   61616161 61616161 61616161 61616161 †aaaaaaaaaaaaaaaa
0000000000000100:   61616161 61616161 61616161 61616161 †aaaaaaaaaaaaaaaa
0000000000000110:   61616161 61616161 61616161 61616161 †aaaaaaaaaaaaaaaa
0000000000000120:   61616161 61616161 61616161 61616161 †aaaaaaaaaaaaaaaa
0000000000000130:   61616161 61616161 61616161 61616161 †aaaaaaaaaaaaaaaa
0000000000000140:   61616161 61616161 61616161 61616161 †aaaaaaaaaaaaaaaa
0000000000000150:   61616161 61616161 61616161 61616161 †aaaaaaaaaaaaaaaa
0000000000000160:   61616161 61616161 61616161 61616161 †aaaaaaaaaaaaaaaa
0000000000000170:   61616161 61616161 61616161 61616161 †aaaaaaaaaaaaaaaa
0000000000000180:   61616161 61616161 61616161 61616161 †aaaaaaaaaaaaaaaa
0000000000000190:   61616161 61616161 61616161 61616161 †aaaaaaaaaaaaaaaa
00000000000001A0:   61616161 61616161 61616161 61616161 †aaaaaaaaaaaaaaaa
00000000000001B0:   61616161 61616161 61616161 61616161 †aaaaaaaaaaaaaaaa
00000000000001C0:   61616161 61616161 61616161 61616161 †aaaaaaaaaaaaaaaa
00000000000001D0:   61616161 61616161 61616161 61616161 †aaaaaaaaaaaaaaaa
00000000000001E0:   61616161 61616161 61616161 61616161 †aaaaaaaaaaaaaaaa
00000000000001F0:   61616161 61616161 61616161 61616161 †aaaaaaaaaaaaaaaa
0000000000000200:   616161†††††††††††††††††††††††††††††††aaa             

Slot 0 Column 1 Offset 0x4 Length 4 Length (physical) 4

select avg_fragmentation_in_percent,fragment_count,avg_fragment_size_in_pages,page_count,avg_page_space_used_in_percent,forwarded_record_count
from sys.dm_db_index_physical_stats(DB_ID(),NULL,NULL,NULL,’DETAILED’)
WHERE object_id=object_id(‘tblClustered’)

結果

avg_fragmentation_in_percent fragment_count       avg_fragment_size_in_pages page_count           avg_page_space_used_in_percent forwarded_record_count
—————————- ——————– ————————– ——————– —————————— ———————-
60                           4                    1.25                       5                    55.5497899678774              NULL
0                            1                    1                          1                    0.778354336545589             NULL

查詢資料時直接 Scan

set statistics io on
select * from Heap where c1 < 10

資料表 ‘tblClustered’。掃描計數 1,邏輯讀取 3,實體讀取 0,讀取前讀取 0,LOB 邏輯讀取 0,LOB 實體讀取 0,LOB 讀取前讀取 0。

發表迴響

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

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 位部落客按了讚: