SqlServer_存储结构
浏览数：30 /
时间：2015年06月12日
本次的内容有：
1.文件组概念总结。
2.区的概念总结。
2.0 没有索引的表依然分配索引空间实验验证。
2.1 SqlServer分区做法的实验验证。
3.数据页的概念总结。
3.1 深入查看emp表存储的页中page header的信息。
SqlServer存储结构。
如下图：
SqlServer的逻辑存储结构，分为三个部分。
1.file_group(文件组):
·分为primary文件组和用户文件组。
·primary文件组主要存储数据库系统信息。
·用户文件组主要存储用户信息。
·primary文件组中的数据文件可分为：主数据文件（.mdf）和辅助数据文件（.ndf）
·主数据文件（.mdf）除了存储系统和用户数据，还存储了所有辅助数据文件和重做日志文件的属性信息。
·sqlserver-->主数据文件（.mdf）-->辅助数据文件（.ndf）
2.extent（区）:
·区是SqlServer分配存储单位。由8个连续固定大小的数据页组成。（64K）
·SqlServer中的区有两种类型：mixed extent（混合区）和uniform extent（统一区）。
·SqlServer不会对空表分配区，在添加数据时，才真正给表分配区。
·混合区可由多个表和索引使用，统一区只能由一个表或索引使用。
·即使某个表没有创建索引，SqlServer依然会给这个表分配一个页，存储IAM(Index Allocation Map)结构。（实验验证）
·在对表或索引分配区时，会先在混合区上分配8个数据页，之后的区分配则在统一区上分配。
·上则先分混合区，后续统一区的做法原因是：避免一次性分配一个区給一个过小的表。
当某个表已经被分配了8个数据页并且需要更多的存储空间时，会在统一区上给它续分。
2.0没有索引的表依然分配索引空间实验验证。
一.创建一张测试表。
1> create table test(a int,b varchar(10))
2> go
二.用系统存储过程sp_spaceused统计test表的空间使用情况。发现时没有分配区的。
1> exec sp_spaceused ‘test‘, true
2> go
name rows reserved data index_size
------------------------- ----------- ------------------ ------------------ ------------
test 0 0 KB 0 KB 0 KB
三.添加数据。
1> insert into test values(1,‘extent‘)
2> go
(1 行受影响)
四.再次查看test表的统计信息。
1> exec sp_spaceused ‘test‘, true
2> go
name rows reserved data index_size
------------------------- ----------- ------------------ ------------------ -----------
test 1 16 KB 8 KB 8 KB
--可以看到，没有索引，也给表分配了一个页。
2.1SqlServer分区做法的实验验证。
一.在test库里测试。
1> use test
2> go
已将数据库上下文更改为 ‘test‘。
二.创建一张测试表。
1> create table test1(a int,b varchar(10))
2> go
三.往test表中添加数据，达到8K之后。
14> dbcc extentinfo(test,test1)
15> go
file_id page_id pg_alloc ext_size object_id index_id partition_number
----------- ----------- ----------- ----------- ----------- ----------- ----------------
1 90 1 1 2105058535 0 1
1 94 1 1 2105058535 0 1
1 109 1 1 2105058535 0 1
1 110 1 1 2105058535 0 1
1 114 1 1 2105058535 0 1
1 115 1 1 2105058535 0 1
1 118 1 1 2105058535 0 1
1 119 1 1 2105058535 0 1
1 184 8 8 2105058535 0 1
1 192 6 8 2105058535 0 1
--可以看到，ext_size(区的大小，以页表示。)在第八个数据页（64k）分配之后，继续分配时大小是8个数据页。
3.data_page（数据页）:
·date_page大小固为8K，不能修改。
·每个数据页都有96字节的页头，用于存储页码、页类型、页的可用空间以及拥有该页的对象ID（也就是这个页是哪个对象在用）。
3.1实验查看emp表存储的页中page header的信息。
第一步：先得到emp表存储的文件号+页面序号。（page_id=file_id：page_num）
用以下命令：
1> select first,root,indid from sysindexes WHERE ID=OBJECT_ID(‘emp‘) AND INDID IN (0,1)
2> go
firstrootindid
0x5900000001000x5900000001001
(1 行受影响)
上面命令选项解释：
--first, --第一个page ID
--root, --最后一个page ID
--indid –-(0表示沒有聚集索引，1表示有聚集索引)--（其实就是B树索引）
继续--
只看frist列。
frist值返回如下: 0x590000000100 为十六进制。
现在人为转换为file_id和page_num.
第一步: 去掉0x标示符，把这些值分成一个字节的值；
59 00 00 00 01 00
第二步：将这些字节反转过来。
00 01 | 00 00 00 59
前两个字节代表File ID，后四个字节代表page number.--十六转十进制
于是得到EMP表存储在file_id=1,page_num=89
第二步使用DBCC命令显示出页面块头信息。
执行DBCC PAGE(yzh,1,89,0)
DBCC命令的解释---
dbcc page ( {‘dbname‘ | dbid}, filenum, pagenum [, printopt={0|1|2|3} ])
The printopt parameter has the following meanings:
·0 - print just the page header
·1 - page header plus per-row hex dumps and a dump of the page slot array
·2 - page header plus whole page hex dump
·3 - page header plus detailed per-row interpretation
dbname | dbid :代表数据库名|数据库ID
filenum :文件序号。
pagenum :页面序号。
如：DBCC PAGE(yzh,1,89,0) --查询显示数据库yzh，文件1号，页面89号，单单是页面头部信息。
在SSMS界面执行DBCC PAGE(yzh,1,89,0)得到以下内容。
------------------------------------------------------------------------------------------
PAGE: (1:89)
BUFFER:
--缓存中的页面信息。
BUF @0x0000000082FA6E40
bpage = 0x0000000082126000 bhash = 0x0000000000000000 bpageno = (1:89)
bdbid = 5 breferences = 0 bcputicks = 0
bsampleCount = 0 bUse1 = 22258 bstat = 0xc00009
blog = 0x32159 bnext = 0x0000000000000000
PAGE HEADER:
Page @0x0000000082126000
m_pageId = (1:89) m_headerVersion = 1 m_type = 1
m_typeFlagBits = 0x4 m_level = 0 m_flagBits = 0x200
m_objId (AllocUnitId.idObj) = 31 m_indexId (AllocUnitId.idInd) = 256
Metadata: AllocUnitId = 72057594039959552
Metadata: PartitionId = 72057594038910976 Metadata: IndexId = 1
Metadata: ObjectId = 5575058 m_prevPage = (0:0) m_nextPage = (0:0)
pminlen = 32 m_slotCnt = 14 m_freeCnt = 7328m_freeData = 881 m_reservedCnt = 0 m_lsn = (20:439:16)
m_xactReserved = 0 m_xdesId = (0:601) m_ghostRecCnt = 0
m_tornBits = -338526208
PAGE HEADER页面头部信息，96字节
m_pageId-当前页面号
m_headerVersion-版本号，始终为1
m_type-页面类型，1为数据类型
m_typeFlagBits-数据页索引页为4，其他页为0.
m_level = 0 -B树索引的级别。
m_flagBits-页面标识
m_objId (AllocUnitId.idObj)-对应的对象id
m_indexId (AllocUnitId.idInd) = 256 -索引ID，0 代表堆, 1 代表聚集索引, 2-250 代表非聚集索引 大于250就是text或image字段
Metadata: ObjectId = 5575058 -该页面所属的对象的id，-可通过sysobjects查询验证。
m_prevPage = (0:0)-该数据页的前一页面
m_nextPage = (0:0)-该数据页的后一页面
pminlen = 32-一行数据的最小值
m_slotCnt = 14 -页面中数据的行数
m_freeCnt = 7328-页面中剩余的空间，还剩下7328空间。-（注释1）
m_freeData = 881-空余空间偏移量，已用空间容量。
m_lsn = (20:439:16) 日志记录号。
Allocation Status
GAM (1:2) = ALLOCATED SGAM (1:3) = NOT ALLOCATED
PFS (1:1) = 0x60 MIXED_EXT ALLOCATED 0_PCT_FULL DIFF (1:6) = CHANGED
ML (1:7) = NOT MIN_LOGGED
DBCC 执行完毕。如果 DBCC 输出了错误信息，请与系统管理员联系。
---------------------------------------------------------------------------------------------------
注释1：一个page大小是8192子句，emp表记录有14行，这里理应不会剩下这么多的空闲空间。
于是我通过下面命令，查询emp表使用了多少个page.
1> select o.name,sum(reserved) as reserved from sysindexes i,sysobjects o
2> where i.id=o.id and o.name=‘emp‘
3> group by o.name
4> go
name reserved
------------------------- -----------
EMP 2
(1 行受影响)
不出所料，emp表使用了两个page，我只查询了其中一个。
郑重声明：本站内容如果来自互联网及其他传播媒体，其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享，并不代表本站赞同其观点和对其真实性负责，也不构成任何其他建议。
