系列内容来自Woodytu的博客 SQL Server 存储(1/8):理解数据页结构 - Woodytu - 博客园,一共八篇,记录下来学习
SQL Server用8KB的数据页来存储数据行,页也是磁盘 I/O 操作的基本单位。数据页由:页头(标头),数据区(数据行和可用空间)及行偏移量 3个部分组成。
在讨论在SQL Server数据页内部结构具体是什么样之前,我们来创建一个表并插入记录。
CREATE TABLE Customers
(
FirstName CHAR(50) NOT NULL,
LastName CHAR(50) NOT NULL,
Address CHAR(100) NOT NULL,
ZipCode CHAR(5) NOT NULL,
Rating INT NOT NULL,
ModifiedDate DATETIME NOT NULL,
);
INSERT INTO dbo.Customers
(
FirstName,
LastName,
Address,
ZipCode,
Rating,
ModifiedDate
)
VALUES
(
'Woody' , -- FirstName - char(50)
'Tu' , -- LastName - char(50)
'ZUOQIAO YOUXI TOWN LINHAI CITY' , -- Address - char(50)
'0000' , -- ZipCode - char(5)
1 , -- Rating - int
'2015-05-07 10:09:51' -- ModifiedDate - datetime
);
现在我们要找出SQL Server给这个表分配的页有哪些,需要用到非文档的命令DBCC IND,命令详情参考 DBCC命令
DBCC IND('InternalStorageFormat','Customers',-1)
可以看到有2条记录,一个页面类型为10(PageType)一个为1。页面类型为10是IAM页,为1是数据页,可以看到数据页ID是79。
得到了数据页的页号为79,现在来看看79号数据页里存放的数据,这就要用到DBCC PAGE命令,命令详情参考 DBCC命令
DBCC TRACEON(3604)
DBCC PAGE(InternalStorageFormat,1,79,3)
GO
SQL Server会给我们包含4个部分的输出:
1)第1部分是BUFFER,里面是一些内存分配信息,对此我们没多少兴趣。
2)第2部分是固定96 bytes大小的页头(page header),输出类似:
页头相关字段的含义:
- Page @0x08F84000 同BUFFER中的bpage地址
- m_pageId = (1:79) 数据页号
- m_headerVersion = 1 头文件版本号,一直为1
- m_type = 1 页面类型,1为数据页面
- m_typeFlagBits = 0x4 数据页和索引页为4,其他页为0
- m_level = 0 该页在索引页(B树)中的级数
- m_flagBits = 0x8000 页面标志
- m_objId (AllocUnitId.idObj) = 46 同Metadata: ObjectId
- m_indexId (AllocUnitId.idInd) = 256 同Metadata: IndexId
- Metadata: AllocUnitId = 72057594040942592 存储单元的ID,sys.allocation_units.allocation_unit_id
- Metadata: PartitionId = 72057594039304192 数据页所在的分区号,sys.partitions.partition_id
- Metadata: IndexId = 0 页面的索引号,sys.objects.object_id&sys.indexes.index_id
- Metadata: ObjectId = 277576027 该页面所属的对象的id,sys.objects.object_id
- m_prevPage = (0:0) 该数据页的前一页面;主要用在数据页、索引页和IAM页
- m_nextPage = (0:0) 该数据页的后一页面;主要用在数据页、索引页和IAM页
- pminlen = 221 定长数据所占的字节数
- m_slotCnt = 2 页面中的数据的行数
- m_freeCnt = 7644 页面中剩余的空间
- m_freeData = 544 从第一个字节到最后一个字节的空间字节数
- m_reservedCnt = 0 活动事务释放的字节数
- m_lsn = (255:8406:2) 日志记录号
- m_xactReserved = 0 最新加入到m_reservedCnt领域的字节数
- m_xdesId = (0:0) 添加到m_reservedCnt的最近的事务id
- m_ghostRecCnt = 0 幻影数据的行数
- m_tornBits = 0 页的校验位或者被由数据库页面保护形式决定分页保护位取代
3)页面相关分配情况:
- GAM (1:2) = ALLOCATED 在GAM页上的分配情况
- SGAM (1:3) = ALLOCATED 在SGAM页上的分配情况
- PFS (1:1) = 0x61 MIXED_EXT ALLOCATED 50_PCT_FULL 在PFS页上的分配情况,该页为50%满,
- DIFF (1:6) = CHANGED
- ML (1:7) = NOT MIN_LOGGED
接下来就是用于存放实际数据的槽(slot),每条记录存放一个槽(slot)里。0号槽在页里拥有第1条数据,1号槽拥有第2条数据,以此类推。通过下面的图片,你可以看到我们记录大小是224 bytes,217 bytes(50+50+100+5+4+8) 的定长和7 bytes 的系统行开销。
4)页的最后一部分是行偏移数组表,可以用参数为1的DBCC PAGE命令查看
DBCC TRACEON(3604)
DBCC PAGE(InternalStorageFormat,1,79,1)
GO
SQL Server在输出信息的底部,给我们如下返回:
这个行偏移表,应该从下往上读。这里我们插入了2条记录,所以表里有2个槽条目。第1条记录指向第96 bytes,刚好在页头后。在页里的行偏移表里,每条记录需要2 bytes的大小来存储。这个行偏移表可以帮助我们管理页面的记录,例如在堆表上建立的非聚集索引,每个非聚集索引行里都包含一个物理指针[文件号:页号:槽号](file:page:solt)映射回堆表里的行记录。因此,在读取页时,可以找到堆表里的对应行,再通过行偏移表里槽号里的偏移量,就可以在页里读取到对应的行记录。如果我们要修改页中间的记录,并不一定需要重组整个页,只要修改偏移表里偏移量即可。
在页头我们看到当前页面还有7644 bytes可以用,我们一起来验证下:(8 * 1024) - 96 - (217 * 2)-(7 * 2)-(2 * 2)=7644 bytes
8 * 1024 = 页的总大小,8K
96 = 页头固定大小 96 bytes
217 * 2 = 每条记录的总长 * 记录数
7 * 2 = 每条记录的系统行开销 * 记录数
2 * 2 = 行偏移表里每槽占用字节数 * 记录数
页是 8KB 的大小,即 8192 bytes,固定 96 bytes的大小给页头使用,接下来是具体的数据(以槽的方式存储),数据记录的最大长度是 8060 bytes(包括 7 bytes的系统行开销),因此一条记录中你拥有的最大字节数是 8053 bytes。因此下面的表创建语句会失败。
CREATE TABLE Maxsize(
id CHAR(8000) NOT NULL,
id1 CHAR(54) NOT NULL
);
剩下的 36 bytes (8192-96-8060)保留给槽数组(Slot array)或者任何转发行返回指针(forwarding row back pointer)每条10 bytes。这意味一个页不一定就能保存18(36/2)条记录。槽数组根据你的记录数从下往上增长。如果记录长度小,页里就可以存储更多的记录,偏移表也会自下而上占用更多的空间。