仓酷云

标题: SQL SERVER存储单位页与区详解 [打印本页]

作者: 柔情似水 时间: 2015-1-16 14:09
标题: SQL SERVER存储单位页与区详解
MySQL这个名字是怎么来的已经不清楚了。基本指南和大量的库和工具带有前缀“my”已经有10年以上，而且不管怎样，MySQLAB创始人之一的的女儿也叫My。SQLServer中数据存储的基础单元是页。为数据库中的数据文件（.mdf或.ndf）分派的磁盘空间能够从逻辑上分别成页（从0到n一连编号）。磁盘I/O操纵在页级实行
区是八个物理上一连的页的汇合，用来无效地办理页。一切页都存储在区中。

甚么是页?
观点：页的巨细为8KB,每页的开首是96字节的标头，用于存储有关页的体系信息。此信息包含页码、页范例、页的可用空间和具有该页的工具的分派单位ID。

范例：
1)Data:当textinrow设置为ON时，包括除text、ntext、image、nvarchar(max)、varchar(max)、varbinary(max)和xml数据以外的一切数据的数据行。
2)Index：索引条目
3)Text/Image：年夜型工具数据范例：text、ntext、image、nvarchar(max)、varchar(max)、varbinary(max)和xml数据。
数据行凌驾8KB时为可变长度数据范例列：varchar、nvarchar、varbinary和sql_variant
4)GAM，SGAM：有关区是不是分派的信息
5)PageFreeSpace（PFS）：有关页分派和页的可用空间的信息
6)IndexAllocationMap(IAM):有关每一个分派单位中表或索引所利用的区的信息
7)BulkChangedMap(BCM):有关每一个分派单位中自最初一条BACKUPLOG语句以后的年夜容量操纵所修正的区的信息
8)DifferentialChangedMap(DCM):有关每一个分派单位中自最初一条BACKUPDATABASE语句以后变动的区的信息。
ps；日记文件不包括页，而是包括一系列日记纪录。

页布局：
在数据页上，数据行紧接着标头按按次安排。页的开端是行偏移表，关于页中的每行，每一个行偏移表都包括一个条目。

年夜型行撑持：
页的单个行中的最年夜数据量和开支是8,060字节(8KB)。可是，这不包含用Text/Image页范例存储的数据。包括varchar、nvarchar、varbinary或sql_variant列的表不受此限定的束缚。
当表中的一切流动列和可变列的行的总巨细凌驾限定的8,060字节时，SQLServer将从最年夜长度的列入手下手静态将一个或多个可变长度列挪动到ROW_OVERFLOW_DATA分派单位中的页。
将列挪动到ROW_OVERFLOW_DATA分派单位中的页后，将在IN_ROW_DATA分派单位中的原始页上保护24字节的指针。假如后续操纵减小了行的巨细，SQLServer会静态将列移回到原始数据页.

行溢出的事项：
a.凌驾8,060字节的行巨细限定大概会影响功能，由于SQLServer仍坚持每页8KB的限定。
下面提到了,当超越8060的时分静态挪动行的做法。以是我们要在计划表的时分思索溢出的行的比列和查询这些溢出行的频次.
假如大概必要常常查询行溢出数据中的很多行，请思索对表格举行标准化处置，以使某些列挪动到另外一个表中。然后能够在异步JOIN操纵中实行查询。

b.其他数据范例列（一样平常数据列，Text/Image页范例存储的数据不包括)必需在8,060字节的行限定以内.

c.会萃索引的索引键不克不及包括在ROW_OVERFLOW_DATA分派单位中具有现无数据的varchar列。
这里举个例子：
CREATEtable#ksss1
(
id1varchar(820),
id2varchar(800),
id3varchar(800),
id4varchar(800),
id5varchar(800),
id6varchar(800),
id7varchar(800),
id8varchar(800),
id9varchar(800),
id10varchar(800),
id11varchar(800),
id12int
)

--拔出测试纪录
DECLARE@SVARCHAR(820),@s1varchar(800)
SELECT@S=ISNULL(@S,)+LTRIM(A.NUMBER+B.NUMBER),@s1=ISNULL(@S,)+LTRIM(A.NUMBER+B.NUMBER)
FROM
MASTER..SPT_VALUESA,MASTER..SPT_VALUESB
WHEREA.TYPE=PANDB.TYPE=P
exec(insert#ksss1select+@s+,+@s1+,+@s1+,+@s1+,+@s1+
,+@s1+,+@s1+,+@s1+,+@s1+,+@s1
+,+@s1+,1)

--创建索引
createclusteredindexOS_ID1_DI2ON#Ksss1(ID1,id12)

这里的表的最年夜行总和长度凌驾了8060字节,我拔出的这笔记录凌驾了8060字节,当我拔出的时分产生页面溢出,id1列溢出到ROW_OVERFLOW_DATA分派单位内.在IN_ROW_DATA分派单位中留下了个指针,接着我为表创建会萃索引:
createclusteredindexOS_ID1_DI2ON#Ksss1(ID1)
这个时分再拔出下面那笔记录,也能够拔出乐成,这里产生的历程实际上是如许的:再创立会萃索引后,数据产生“页面搬场”-此时最为键列的ID1列已不在ROW_OVERFLOW_DATA分派单位内,它从头回到了IN_ROW_DATA分派单位中,换成了其他非键列溢出.如许今后每次拔出纪录ID1不会再溢出.

ps：能够包含包括行溢出数据的列，作为非会萃索引的键列或非键列

d.关于利用希罕列的表，纪录巨细限定为8,018字节。

e.若要取得有关大概包括行溢出数据的表或索引的信息，请利用sys.dm_db_index_physical_stats静态办理函数。

甚么是区?
观点：区是办理空间的基础单元。一个区是八个物理上一连的页（即64KB）。这意味着SQLServer数据库中每MB有16个区。

分类：SQLServer不会将一切辨别配给包括大批数据的表
1):一致区，由单个工具一切。区中的一切8页只能由所属工具利用。
2):夹杂区，最多可由八个工具共享。区中八页的每页可由分歧的工具一切。
分派页的历程：一般从夹杂区向新表或索引分派页。

当表或索引增加到8页时，将酿成利用一致区举行后续分派。
假如对现有表创立索引，而且该表包括的行足以在索引中天生8页，则对该索引的一切分派都利用一致区举行。

辨别配：
SQLServer利用两品种型的分派映照表来纪录区的分派：

全局分派映照表(GAM)：纪录已分派的区。每一个GAM包括64,000个区，相称于近4GB的数据。GAM用一个位来暗示所涵盖区间内的每一个区的形态。假如位为1，则区可用；假如位为0，则区已分派。

共享全局分派映照表(SGAM)：纪录以后用作夹杂区且最少有一个未利用的页的区。每一个SGAM包括64,000个区，相称于近4GB的数据。SGAM用一个位来暗示所涵盖区间内的每一个区的形态。假如位为1，则区正用作夹杂区且有可用页。假如位为0，则区未用作夹杂区，大概固然用作夹杂区但其一切页均在利用中。

举行区办理算法复杂点情形以下：
a.分派一致区:数据库引擎将在GAM中搜刮为1的位，并将其设置为0。
b.查找具有可用页的夹杂区:将在SGAM中搜刮为1的位.
c.分派夹杂区:在GAM中搜刮为1的位，将其设置为0，然后将SGAM中对应的位设置为1。
d.开释区，数据库引擎确保将GAM位设置为1，将SGAM位设置为0。

跟踪可用空间:
页可用空间(PFS)页纪录每页的分派形态，是不是已分派单个页和每页的可用空间量。PFS对每页都有一个字节，纪录该页是不是已分派。假如已分派，则纪录该页是为空、已满1%到50%、已满51%到80%、已满81%到95%仍是已满96%到100%。
将辨别配给工具后，数据库引擎将利用PFS页来纪录区中的哪些页已分派或哪些页可用。数据库引擎必需分派新页时，将利用此信息。

办理工具利用的空间:
索引分派映照(IAM)页将映照分派单位利用的数据库文件中4GB部分中的区.

分派单位分三品种型：
IN_ROW_DATA：用于存储堆分区或索引分区,最少含有一个;
LOB_DATA：用于存储年夜型工具(LOB)数据范例，比方xml、varbinary(max)和varchar(max)。
ROW_OVERFLOW_DATA：用于存储凌驾8,060字节行巨细限定的varchar、nvarchar、varbinary或sql_variant列中存储的可变长度数据。

布局：IAM页含有一个标头,指明IAM页所映照的区局限的肇端区.IAM页中另有一个年夜位图，个中每一个位代表一个区。
假如某个位是0，它所代表的区将不会分派给具有该IAM页的分派单位。假如这个位是1，它所代表的区将被分派给具有该IAM页的分派单位。

IAM页的散布：
每一个分派单位在有区的每一个文件中最少有一个IAM页。假如分派给分派单位的文件中的区的局限凌驾了一个IAM页可以纪录的局限，一个文件中也大概会有多个IAM页。
由于假如分派单位包括来自多个文件的区，大概凌驾一个文件的4GB局限，那末一个IAM链中将链接多个IAM页。

拔出行历程：
在以后页中拔出新行，而以后页中没有可用空间时，它将利用IAM和PFS页查找要将该行分派到的页.
数据库引擎利用IAM页查找分派给分派单位的区。关于每一个区，数据库引擎将搜刮PFS页，以检察是不是有可用的页。

跟踪已修正的区：
SQLServer利用DCM，BCM跟踪被年夜容量复制操纵修正的区，和自前次完全备份后修正的区。这些数据布局极年夜地加速了差别备份的速率。当数据库利用年夜容量日记恢复形式时，这些数据布局也能够加速将年夜容量复制操纵纪录至日记的速率。

差别变动映照表(DCM)：跟踪自前次实行BACKUPDATABASE语句后变动过的区。
差别备份只读取DCM页即可以断定已修正的区。如许年夜年夜削减了差别备份必需扫描的页数

年夜容量变动映照表(BCM)：跟踪自前次实行BACKUPLOG语句后，被年夜容量日记纪录操纵修正的区/
只要在数据库利用年夜容量日记纪录恢复形式时，才会与BCM页有关.
在此恢复形式中，当实行BACKUPLOG时，备份历程将扫描BCM查找已修正的区。然后，将那些区包含在日记备份中。

在数据文件中：
文件头=》PFS=》GAM=》SGAM=》BCM=》DCM
第0页第1页第2页第3页第4页第5页
第一个PFS页以后是一个巨细约莫为8,000页的PFS页。
在第2页的第一个GAM页以后另有另外一个GAM页（包括64,000个区），在第3页的第一个SGAM页以后也有另外一个SGAM页（包括64,000个区）
DCM页和BCM页的距离与GAM和SGAM页的距离不异，都是64,000个区。对于insert和delete，event中包含了插入/删除的记录的所有字段的值(太爽了。。)

作者: 简单生活 时间: 2015-1-18 12:11
对于微软系列的工具除了一遍遍尝试还真没有太好的办法

作者: 精灵巫婆 时间: 2015-1-26 15:44
原理很简单，对要求长时间计算某一时间点的报表生成和防用户操作错误很有帮助。但是比起Oracle10g的闪回技术还是细粒度不够。可惜！

作者: 再见西城 时间: 2015-2-4 20:38
呵呵，这就是偶想说的

作者: 再现理想 时间: 2015-2-10 09:19
这一点很好的加强了profiler的功能。但是提到profiler提醒大家注意一点。windows2003要安装sp1补丁才能启动profiler。否则点击没有反应。

作者: 分手快乐 时间: 2015-3-10 16:14
原来的计算字段其实和虚拟字段很像。只是管理方面好了而已，性能方面提高不多。但是SQL2005提供了计算字段的持久化，这就提高了查询的性能，但是会加重insert和update的负担。OLTP慎用。OLAP可以大规模使用。

作者: 爱飞 时间: 2015-3-17 09:08
在select语句中可以使用groupby子句将行划分成较小的组，然后，使用聚组函数返回每一个组的汇总信息，另外，可以使用having子句限制返回的结果集。

作者: 金色的骷髅 时间: 2015-3-24 05:02
SQLServer的异构移植功能个人感觉最好了。（如果对比过SQLServer的链接服务器和Oracle的透明网关的朋友会发现SQLServer的sp_addlinkedserver(openquery)异构数据库系列比Oracle真是强太多了。）

欢迎光临仓酷云 (http://ckuyun.com/)