前文
本篇會跟大家對於SQL-Server資料表深入淺出的介紹.
關於Page
資料會存在Page中
而一個Page大小為 8K/Page => 8092(8060 bytes)
每個Page除了存取資料還會存放一些
MetaData，我們可以先當作是每個Page大小是8K
Heap資料表
如果一張資料表沒有Clustered Index就會為Heap資料表，這意味著Heap資料表的資料不會有排序一直把資料新增進資料表中，Heap資料表Insert資料快
適合使用在Log資料表、Event資料表、稽核資料表....一直新增資料但比較少查詢表
IAM(index allocation map)
當Heap資料表要搜尋資料SQL-Server透過IAM(index allocation map)去尋要掃描Page範圍，因為IAM會以範圍存在於檔案中的順序來表示它們，這代表循序的堆積掃描都將依檔案順序進行。
使用IAM分頁設定掃描順序也表示堆積中的資料列通常不會依插入順序傳回
IAM Page在讀取資料的示意圖
可以看到讀取Page中資料順序和新增資料順序不一樣.
forwarding pointer
假如在Heap資料表更新欄位資料，就可能會造成forwarding pointer
製造forwarding pointer是因為原本Page塞不下更新後資料就會先把資料搬到另一個新建立Page上並在原本Page建立一個類似指標東西指向它.
這個指標會存在原本的Page大小是16 byte
forwarding pointer(Demo)
一個Page大小是8k
我們建立一個ForwardingPointers資料表，並且新增3筆資料進去
其中有一筆資料
replicate('2',7800)佔據78xxBytes.
create table dbo.ForwardingPointers
(
ID int not null,
Val varchar(8000) null
);
insert into dbo.ForwardingPointers(ID,Val)
values(1,null),(2,replicate('2',7800)),(3,null);
新增完後我們利用DMV查詢目前ForwardingPointers使用的Page數量可以看到只使用一頁
因為目前資料大小可以放在同一個Page
select page_count, avg_record_size_in_bytes, avg_page_space_used_in_percent
,forwarded_record_count
from sys.dm_db_index_physical_stats(db_id(),object_id(N'dbo.ForwardingPointers'),0
,null,'DETAILED');
我們將dbo.ForwardingPointers另外兩個
Val IS NULL更新成replicate('2',7800)
UPDATE dbo.ForwardingPointers
SET Val = replicate('2',7800)
WHERE Val IS NULL
再查詢一次dbo.ForwardingPointers使用Page，能發現已經使用了3個Page(因為已經觸發forwarding pointer)
已經把這次更新的資料搬到新Page上，因為更新後的資料大小已經超過目前Page可以負擔的大小
RID Lookup
在資料庫索引深入淺出(一)有說，資料表沒有Clustered Index且使用Index所有查詢欄位不包含在Converting Index中就會透過RID Lookup查找確切Page上的Row(藉由Row-Id)
此資料表是
Heap資料表在NonClustered Index中會存放Heap RID
記得在
DBCC IND取得的PID是要找PageType = 2
DBCC traceon (3604);
DBCC IND ([Your DataBase],T1,-1)
DBCC PAGE([Your DataBase],1,[Your PID],3)
透過DBCC可以看到查找資料表Page資料可以顯示如下結果集.
如果是Heap資料表會有一個欄位是Heap RID(Key)欄位.
HEAP RID:0x40110F0001002900
大小 8 bytes
- FID（2 bytes）
- PID（4 bytes）
- SLOT（2 bytes）
可藉由下面的Script來拆解Heap RID(Key)資料
--轉換RID為 FID:PID:slot格式
declare @Heaprid binary(8)
set @Heaprid = 0x40110F0001002900
select [FID:PID:Slot]=
CONVERT (VARCHAR(5),
CONVERT(INT, SUBSTRING(@Heaprid, 6, 1)
+ SUBSTRING(@Heaprid, 5, 1)))
+ ':'
+ CONVERT(VARCHAR(10),
CONVERT(INT, SUBSTRING(@Heaprid, 4, 1)
+ SUBSTRING(@Heaprid, 3, 1)
+ SUBSTRING(@Heaprid, 2, 1)
+ SUBSTRING(@Heaprid, 1, 1)))
+ ':'
+ CONVERT(VARCHAR(5),
CONVERT(INT, SUBSTRING(@Heaprid, 8, 1)
+ SUBSTRING(@Heaprid, 7, 1)))
透過上面Script我們可以得到1:987456:41
我們在透過
DBCC PAGE(AdventureWorks2012_Data,1,987456,3)
就可以查找到我們要的資料在PID = 987456這個Page中.
dbcc page 語法
下面語法透過dbcc page可以了解資料表存取資訊
/* 建立測試資料表 */
drop table if exists dbo.T;
create table dbo.T
(
Name nvarchar(10) not null,
EmpID int not null,
CouID int not null,
Locate nchar(2) not null,
Dist nchar(1) not null,
BDate datetime not null,
Address nvarchar(100) null,
CheckID int not null
);
go
/* 寫入測試資料 */
insert into dbo.T
(Name, EmpID, CouID, Locate, Dist, BDate, Address, CheckID)
values
(N'Daniel',1,950,N'TW',N'M','2020-01-01 00:00:00',N'Taipei City',9);
go
/* 先取得Page的位置 */
dbcc ind ('DEMO','dbo.T', -1);
/* 16776 - 不會一樣, 依照產出的資料配置dbcc page */
/* 查看Page內容 */
dbcc traceon (3604);
dbcc page ('DEMO', 1, 16776, 3)
dbcc traceoff (3604);
PageType – the page type. Some common ones are:
- 1 – data page
- 2 – index page
- 3 and 4 – text pages
- 8 – GAM page
- 9 – SGAM page
- 10 – IAM page
- 11 – PFS page
dbcc traceon (3604);
DBCC IND ( { ‘dbname’ | dbid }, { ‘objname’ | objid },{ nonclustered indid | 1 | 0 | -1 | -2 } [, partition_number] )
- 第一個參數是數據庫名或數據庫ID。
- 第二個參數是數據庫中的對象名或對象ID，對象可以是表或者索引視圖。
- 第三個參數是一個非聚集索引ID或者 1, 0, 1, or 2. 值的含義：
- 0: 只顯示對象的in-row data頁和 in-row IAM 頁。
- 1: 顯示對象的全部頁, 包含IAM 頁, in-row數據頁, LOB 數據頁row-overflow 數據頁 . 如果請求的對象含有聚集所以則索引頁也包括。
- -1: 顯示全部IAM頁,數據頁, 索引頁 也包括 LOB 和row-overflow 數據頁。
- -2: 顯示全部IAM頁。
如果本文對您幫助很大，可街口支付斗內鼓勵石頭^^
