分析SQL Server数据mdf文件

数据库是存储在系统中的已定义空间，用于对用户对象进行分组。此空间可以拆分为多个按组组织的文件。

在SQL Server中，每个实例的数据库限制为32,767个，每个数据库可以存储超过20亿个对象，物理大小可达524,272太字节！

在这篇文章中，我将更深入地解释SQL Server数据文件的物理结构。遗憾的是，我无法编写关于此主题的所有假设，行为和详细信息，但您将阅读文档结构的良好概述，以及更好地理解索引内部，文件碎片，事务日志内部等等的良好起点...

在我们开始之前，让我们记住一些重要的概念。

数据库文件

您可以将数据库文件视为普通操作系统文件。数据库必须至少有两个文件：一个用于数据，一个用于t-logs。

数据文件分为两种类型：主数据文件和辅助数据文件。每个数据库只能有一个主数据文件，它会跟踪数据库中的所有其他文件。按照惯例，主数据文件具有.mdf扩展名，辅助文件具有.ndf。

对于日志文件，扩展名为.ldf。每个数据库必须至少有一个。

页面结构

在Microsoft中，SQL Server中基本的数据存储单元是页面。分配给数据库中的数据文件（.mdf或.ndf）的磁盘空间在逻辑上被划分为从0到n连续编号的页面。磁盘I / O操作在页面级别执行。也就是说，SQL Server读取或写入整个数据页。

SQL Server中的所有页面都具有相同的结构。在页面的顶部有一个96字节的标题，它包括PageID，页面所属的结构类型，页面中的记录数以及指向上一页和下一页的指针。因此，我们可以存储8096个字节的记录。但是，数据记录的最大长度为8060字节，如页面底部（最新的36个字节），位于包含行偏移信息（每行2个字节）的插槽数组中。此数组可以根据记录的大小从底部到顶部增长，因此可以在页面中容纳更多记录，并且偏移表将占用更多空间。这些插槽按索引键定义的顺序存储。对于堆，没有特别的订单。

空间分配

SQL Server中的空间分配在称为“范围”的部分中进行管理，这些部分基本上是一组八个逻辑上连续的页面。所以，很多64 KB（8 * 8 KB /页）。

扩展区的使用使分配系统更有效。这些单位可分为两种类型：

• 统一范围：由单个对象拥有的页面组成的范围单位
• 混合范围：由最多共享八个对象的页面组成的范围单位。

从混合范围开始为新表或索引分配页面。当对象增长到8页时，所有将来的分配都将使用统一的范围。

有关扩展区的所有信息都在GAM，SGAM和IAM页面中进行跟踪，也称为“分配位图”。

文件结构

基本上，SQL Server数据文件具有以下基本结构，我们将分析：

• 第0页：标题
• 第1页：第一个PFS
• 第2页：第一个GAM
• 第3页：第一届SGAM
• 第4页：未使用
• 第5页：未使用
• 第6页：第一个DCM
• 第7页：第一个BCM

文件头

所有文件都在页码0中有一个标题。此标头存储有关该特定文件的元数据，并且checkdb无法恢复。如果发生损坏，您必须恢复整个文件。

您可以使用以下DBCC命令浏览某些数据库的标头：

DBCC fileheader [（{'dbname'| dbid} [，fileid]）

您将在RedoStartLSN，BindingId，SectorSize，Status和Growth记录中找到有用的信息。

页面自由空间

PFS识别分配状态并确定可用空间量。这些页面包含每页1个字节，覆盖文件中的8,088页。

基本的PFS结构是：

• 位1：指示页面是否已分配。
• 位2：指示页面是否来自混合范围。
• 位3：表示此页面是IAM页面。
• 位4：表示此页面包含虚影记录
• 位5到7：组合的三位值，表示页面丰满度如下：
  • 0：页面为空
  • 1：页面已满1-50％
  • 2：页面已满51-80％
  • 3：页面满81-95％
  • 4：页面已满96-100％

每个文件的第一页是PFS，在第一个PFS之后每8,000页分配另一个PFS。

将扩展区分配给对象后，数据库引擎使用PFS页面记录扩展区中的哪些页面已分配或空闲。仅当SQL Server找不到具有足够空间来容纳数据的页面时，它才会分配新的扩展区。

GAM和SGAM

SQL Server使用两种特殊类型的页面来记录已分配的范围以及范围可用的使用类型（混合或统一）：

全局分配映射（GAM）页面：跟踪任何类型的扩展区的分配。GAM对于所涵盖的区间中的每个范围都有一点。位0表示相应的范围正在使用中，而在另一方面，该位是1个轨道空闲范围。GAM可以覆盖大约64,000个范围，或几乎4 GB的数据。因此，每4 GB文件大小有一个GAM页面。

共享全局分配映射（SGAM）页面：这与GAM相同，但它仅指混合扩展区。SGAM在其覆盖的时间间隔内的每个范围都有一点。如果该位为1，则使用的范围是混合范围并且具有空闲页面，并且0位表示非混合范围或其页面全部在使用中的混合范围。

差异变化图（DCM）

这是第7页（第6页）。它跟踪自上次完整数据库备份以来文件中的哪些扩展区已被修改。

与GAM和SGAM一样，BCM页面在其文件的覆盖扇区中的每个范围都有1位。第一个BCM页面位于每个数据文件的第七页，此后每511,230页。

批量更改地图（BCM）

也称为最小记录映射（ML Map），它是第8页（第7页），在文件中的范围用于最小或批量记录操作时使用。

BCM页面上的每个位表示一个范围，如果该位为1，则自上次事务日志备份以来，此范围已由最小日志记录的批量操作更改。第一个BCM页面位于每个数据文件的第八页，此后每511,230页。每次发生日志备份时，BCM页面上的所有位都将重置为0。

引导页面

文件1中的第9页可能是数据库中最重要的页面 - 引导页面。有关整个数据库的基本元数据存储在此页面中。如果此页面已损坏，则checkdb无法修复此页面，并且需要还原文件编号1才能恢复数据库。

要获取有关数据库引导页面的信息，可以执行DBCC PAGE指向文件1中的第9页或DBCC dbinfo [（'dbname'）]，始终激活跟踪标志3604（DBCC TRACEON（3604））。

索引分配图 - IAM页面

IAM页面的功能是跟踪属于表或索引的范围。IAM是一个位图，它将范围引用到对象。这种页面覆盖4 GB范围，属于GAM间隔，并且不位于文件中的已知位置。

对于每个4 GB范围的数据，分区和分配单元类型，需要IAM页面进行跟踪。

顺便说一下，分配单元是一组基于页面类型管理数据的页面，可以有三种类型：

• IN_ROW_DATA：包含除大对象（LOB）数据之外的所有数据的数据或索引行。
• LOB_DATA：存储在以下一种或多种数据类型中的大对象数据：text，ntext，image，xml，varchar（max），nvarchar（max），varbinary（max）或CLR用户定义类型。
• ROW_OVERFLOW_DATA：存储在varchar，nvarchar，varbinary或sql_variant列中的可变长度数据，超过了8,060字节行大小限制。

例如，具有所有三种类型的数据（行内，行和行溢出）的四个分区上的表具有至少12个IAM页面。

上述页面有四种可能的组合有效：

IAM链

知道单个IAM页面覆盖文件中的4-GB范围（512.000页）并且属于单个GAM间隔，如果分配单元包含来自多个文件的范围或多于一个4-GB范围，则会发生什么？文件？将在IAM链中链接多个IAM页面，这是IAM页面的无序序列。

基本上，IAM页面具有包含关于其属于哪个GAM间隔的信息的头部，序列号和链接信息。

演示

对于这些演示，我将使用AdventureWorks2008R2数据库进行更简单的再现。您可以在http://msftdbprodsamples.codeplex.com/上下载此示例数据库和其他数据库。

演示1：页面剖析和PFS，GAM，SGAM，DCM和BCM演示

那么让我们开始演示分配位图。%% lockres %%和%% physloc %%机制将返回行的物理位置。所以我将使用以下查询返回数据库日志表的前5行，显示每行的物理位置。

SELECT TOP （5 ） a 。%% physloc %% 作为 地址，
a 。%% lockres %% 作为 LockHashValue ，
a 。* 
来自 dbo 。databaselog AS 一

我得到了以下几行，我将探索第二行。

要探索第二行内部，我将使用数据库控制台命令（DBCC）PAGE。请注意，1：150：1表示FILE：PAGE：SLOT。所以这是命令，使用第150页：

DBCC traceon （3604 ） - 我们需要在执行DBCC PAGE之前激活此标志

DBCC 页 （adventureworks2008r2 ， 1 ， 150 ， 2 ）

DBCC traceoff （3604 ）

执行返回了一个很长的结果，但是现在让我们专注于Header部分中的Allocation Status：

分配状态

GAM（1：2）=分配的SGAM（1：3）= 
未分配的PFS（1：1）= 0x63 MIXED_EXT已分配95_PCT_FULL DIFF（1：6）=已更改
ML（1：7）=未MIN_LOGGED

基本上我们可以获得有关此页面的所有分配信息以及跟踪此页面的PFS，GAM，SGAM，DCM和BCM。

在页面标题之后，我们有数据。这是一个示例：

lot 0，Offset 0x60，Length 2732，DumpStyle BYTE 
Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP VARIABLE_COLUMNS 
Record Size = 2732

内存转储@ 0x0000000009ABA060

0000000000000000：30001000 01000000 5d85e400 23a00000†0 ......。]ä。＃.. 
0000000000000010：08000006 0027003f 00450055 00db03ac†... ..'。？。EUÛ.¬0000000000000020 
：0a640062 006f0043 00520045 00410054†.dboCREAT 
0000000000000030：0045005f 00540041 0042004c 00450064† .E ._。TABLEd 
0000000000000040：0062006f 00450072 0072006f 0072004c†.boErrorL 
0000000000000050：006f0067 00430052 00450041 00540045†.ogCREATE 
0000000000000060：00200054 00410042 004c0045 0020005b†。.TABLE。[ 
0000000000000070：00640062 006f005d 002e005b 00450072†.dbo] ... [.Er 
0000000000000080：0072006f 0072004c 006f0067 005d0028†.rorLog]。（
0000000000000090：000d000a 00200020 00200020 005b0045†... ... ... [。E
00000000000000A0：00720072 006f0072 004c006f 00670049†.rrorLogI

并得出结论，行偏移。这样，您可以看到页面解剖的所有三个部分，如本文前面所述：

OFFSET表：

行 - 偏移量

5（0x5） - 7017（0x1b69）
4（0x4） - 6774（0x1a76）
3（0x3） - 5713（0x1651）
2（0x2） - 4560（0x11d0）
1（0x1） - 2828（0xb0c）
0（0x0） - 96（0x60）

演示2：期待IAM

现在我将执行一个查询，它将返回表DatabaseLog的所有分配单元。 为此，我将关联sys.partitions和sys.system_internals_allocation_units DMV，对于每个分配单元，我们可以获取页面信息，这将引导我们进入IAM信息/链。

我正在使用由Kimberley Tripp完成的页面转换算法（http://sqlskills.com/blogs/paul/post/Inside-The-Storage-Engine-sp_AllocationMetadata-putting-undocumented-system-catalog-views-to-work .aspx）。

所以，这里的查询：
SELECT OBJECT_NAME（p 。OBJECT_ID ） AS '表名' ，
我。将 AS 命名为'indexName' ，
p 。partition_number ，
au 。type_desc ，
CONVERT （VARCHAR（6 ）， CONVERT （INT， 子串 （AU 。first_page ， 6 ， 1 ） + 
子串 （
AU 。first_page ， 5 ， 1 ）））
+ '：' 
+ CONVERT （VARCHAR（20 ）， CONVERT （INT， 子串 （AU 。first_page ， 4 ， 1 ） + 
子串 （AU 。first_page ， 3 ， 1 ） + 子串 （AU 。first_page ， 2 ， 1 ） +
子 （
AU 。first_page ， 1 ， 1 ））） AS 'FIRSTPAGE' ，
CONVERT （VARCHAR（6 ）， CONVERT （INT， 子串 （AU 。root_page ， 6 ， 1 ） + 
子 （
AU 。root_page ， 5 ， 1 ）））
+ '：' 
+ CONVERT （VARCHAR（20 ）， CONVERT （INT， 子串 （AU 。root_page ， 4 ， 1 ） + 
子串
（AU 。root_page ， 3 ， 1 ） + 子串 （AU 。root_page ， 2 ， 1 ） + 子串
（AU 。root_page ，
1 ， 1 ））） AS 'rootPage' ，
CONVERT （VARCHAR（6 ）， CONVERT （INT， 子串 （AU 。first_iam_page ， 6 ， 1 ） + 
子串 （AU 。first_iam_page ， 5 ， 1 ）））
+ '：' 
+ CONVERT （VARCHAR（20 ）， CONVERT （INT， Substring （au。first_iam_page ， 4 ， 1 ）
+ 
子串 （AU 。first_iam_page ， 3 ， 1 ） + 子串 （AU 。first_iam_page ， 2 ， 1 ）
+ 
子串 （AU 。first_iam_page ， 1 ， 1 ））） AS 'firstIamPage' 
FROM SYS 。索引 AS i 
JOIN sys 。分区 AS p 
ON i 。object_id = p 。object_id 
和 我。index_id = p 。index_id 
JOIN sys 。system_internals_allocation_units AS au 
ON p 。hobt_id = au 。container_id 
WHERE p 。object_id = Object_id（'databaselog' ）
ORDER BY tablename ;

这是结果（请注意，在执行DBCC IND时，您可以在更加分散的布局中使用相同的信息）：

如果我们在选项3的根页面中执行DBCC PAGE，我们可以获得属于分配单元的所有IAM链。在这种情况下，我选择属于聚簇索引PK_DatabaseLog_DatabaseLogID的IN_ROW_DATA。

DBCC TRACEON （3604 ）
DBCC 页 （adventureworks2008r2 ， 1 ， 1270 ， 3 ）
DBCC TRACEOFF （3604 ）

在输出中，检查ChildPageID，可以按正确的顺序观察链所拥有的所有页面：

现在，您可以使用DBCC PAGE浏览属于此分配单元的所有数据。在此IAM演示中，可以验证所引用表的所有分配单元，在本例中为4，并检查其中一个分配单元的IAM链。

演示3：我们现在知道桌子，但是......其他对象在哪里？

存储过程，视图，功能在哪里？显然，存储到相同的文件结构中！但是......我们怎么做到这一点？

为了证明这一点，我们需要使用专用管理员连接（DAC）进行连接才能访问sys.sysobjvalues。在这种情况下，我将sys.sysobjvalues与sysobjects表交叉，只显示类型为“P”的对象（存储过程）。这是查询：

SELECT v 。%% physloc %% 作为 地址，
CONVERT （VARCHAR（6 ）， CONVERT （INT， 子串 （v 。%% physloc %% ， 6 ， 1 ） + 
子串 （
v 。%% physloc %% ， 5 ， 1 ）））
+ '：' 
+ CONVERT （VARCHAR（20 ）， CONVERT （INT， 子串 （v 。%% physloc %% ， 4 ， 1 ） + 
子串 （v 。%% physloc %% ， 3 ， 1 ） + 子串 （v 。%% physloc %% ， 2 ， 1 ） + 
子串 （v 
。%% physloc %% ， 1 ， 1 ））） AS 'rootPage'，
o 。命名 对象名，
铸造（v 。imageval AS VARCHAR（最大值）） 代码
FROM SYS 。对象 o 
INNER JOIN 系统。sysobjvalues v 
ON o 。object_id = v 。objid 
WHERE o 。type = 'P'

这是输出：

沃利亚！现在您知道对象存储的确切页面，您可以浏览它。如果过程是加密的，也可以解密代码。我建议阅读这篇文章：http：//williamsorellana.org/page/2/

让我们探索对象“uspPrintError”，它存储在第257页：

DBCC TRACEON （3604 ）
DBCC 页 （adventureworks2008r2 ， 1 ， 257 ， 3 ）
DBCC TRACEOFF （3604 ）

所以，在那个巨大输出的中间，我们可以在插槽13上找到对象（我只会输出一部分）：

插槽13偏移量0x1a6b长度765 
记录类型= PRIMARY_RECORD记录属性= NULL_BITMAP VARIABLE_COLUMNS 
记录大小= 765

内存转储@ 0x0000000009ABBA6B

0000000000000000：30001100 0102797f 4d010000 00000000†0 ...... ..yM ...... 
0000000000000010：000600c0 02002000 fd023801 02000000†... ........ ... ... 
0000000000000020：0d0a0d0a 2d2d2075 73705072 696e7445†... .- uspPrintE 
0000000000000030：72726f72 20707269 6e747320 6572726f†rror prints erro 
0000000000000040：7220696e 666f726d 6174696f 6e206162†r information ab 
0000000000000050 ：6f757420 74686520 6572726f 72207468†出错误第
0000000000000060：61742063 61757365 64200d0a 2d2d2065†在引起..- E 
0000000000000070：78656375 74696f6e 20746f20 6a756d70†xecution跳
0000000000000080：20746f20 74686520 43415443 4820626c†到CATCH BL 
0000000000000090：6f636b20 6f662061 20545259 2e2e2e43 †尝试... C
00000000000000A0：41544348 20636f6e 73747275 63742e20†ATCH构造。
00000000000000B0：0d0a2d2d 2053686f 756c6420 62652065†..-应通过电子邮件
00000000000000C0：78656375 74656420 20776974 66726f6d†从机智xecuted 
00000000000000D0：68696e20 74686520 73636f70 65206f66†欣的范围
00000000000000E0：20612043 41544348 20626c6f 636b206f†CATCH块ø 
00000000000000F0：74686572 77697365 200d0a2d 2d206974†therwise ..- it 
0000000000000100：2077696c 6c207265 7475726e 20776974†将返回
0000000000000110：686f7574 20707269 6e74696e 6720616e†hout打印
0000000000000120：79206572 726f7220 696e666f 726d6174†y错误信息
0000000000000130：696f6e2e 0d0a4352 45415445 2050524f†ion ... CREATE PRO 
0000000000000140：43454455 5245205b 64626f5d 2e5b7573†CEDURE [dbo]。[us 
0000000000000150：70507269 6e744572 726f725d 200d0a41†pPrintError] ..A

那么现在，如果我们......

DROP PROCEDURE uspprinterror

...并快速阅读页面内容标题：

DBCC TRACEON （3604 ）
DBCC 页 （adventureworks2008r2 ， 1 ， 257 ， 3 ）
DBCC TRACEOFF （3604 ）

我们将得到以下输出：

PAGE HEADER：

第@ 0x0000000082582000页

m_pageId =（1：257）m_headerVersion = 1 m_type = 1 
m_typeFlagBits = 0x4 m_level = 0 m_flagBits = 0x0 
m_objId（AllocUnitId.idObj）= 60 m_indexId（AllocUnitId.idInd）= 1元数据：AllocUnitId = 281474980642816 
元数据：PartitionId = 281474980642816元数据： IndexId = 1 
元数据：ObjectId = 60 m_prevPage =（1：23236）m_nextPage =（1：22959）
pminlen = 17 m_slotCnt = 35 m_freeCnt = 2889 
m_freeData = 7528 m_reservedCnt = 0 m_lsn =（554：35134：9）
m_xactReserved = 0 m_xdesId =（0：1586506）m_ghostRecCnt = 1

我们发现有一个Ghost记录（m_ghostRecCnt = 1），我们在第13个插槽中有以下信息：

插槽13偏移量0x1a6b长度765

记录类型= GHOST_DATA_RECORD记录属性= NULL_BITMAP VARIABLE_COLUMNS 
记录大小= 765

内存转储@ 0x0000000009ABBA6B

0000000000000000：3c001100 0102797f 4d010000 00000000†<... ..％...... 
0000000000000010：000600c0 02002000 fd023801 02000000†... ........ ... ... 
0000000000000020：0d0a0d0a 2d2d2075 73705072 696e7445†... .- uspPrintE 
0000000000000030：72726f72 20707269 6e747320 6572726f†rror prints erro 
0000000000000040：7220696e 666f726d 6174696f 6e206162†r information ab 
0000000000000050 ：6f757420 74686520 6572726f 72207468†出错误第
0000000000000060：61742063 61757365 64200d0a 2d2d2065†在引起..- E 
0000000000000070：78656375 74696f6e 20746f20 6a756d70†xecution跳
0000000000000080：20746f20 74686520 43415443 4820626c†到CATCH BL 
0000000000000090：6f636b20 6f662061 20545259 2e2e2e43 †尝试... C
00000000000000A0：41544348 20636f6e 73747275 63742e20†ATCH构造。
00000000000000B0：0d0a2d2d 2053686f 756c6420 62652065†..-应为e 
00000000000000C0：78656375 74656420 66726f6d 20776974†xffcuted from wit 
00000000000000D0：68696e20 74686520 73636f70 65206f66†hin范围
00000000000000E0：20612043 41544348 20626c6f 636b206f†a CATCH块o

让我们等一下再检查一下......

DBCC traceon （3604 ）

DBCC 页 （adventureworks2008r2 ， 1 ， 257 ， 3 ）

DBCC traceoff （3604 ）

输出：

PAGE HEADER：

第@ 0x0000000082582000页

m_pageId =（1：257）m_headerVersion = 1 m_type = 1 
m_typeFlagBits = 0x4 m_level = 0 m_flagBits = 0x0 
m_objId（AllocUnitId.idObj）= 60 m_indexId（AllocUnitId.idInd）= 1元数据：AllocUnitId = 281474980642816

元数据：PartitionId = 281474980642816元数据：IndexId = 1

元数据：ObjectId = 60 m_prevPage =（1：23236）m_nextPage =（1：22959）

pminlen = 17 m_slotCnt = 34 m_freeCnt = 3656 
m_freeData = 7528 m_reservedCnt = 0 m_lsn =（554：35140：3）
m_xactReserved = 0 m_xdesId =（0：1586506）m_ghostRecCnt = 0 
m_tornBits = -1445838435

而对于Slot 13：

插槽13偏移0x330长度40 
记录类型= PRIMARY_RECORD记录属性= NULL_BITMAP VARIABLE_COLUMNS 
记录大小= 40

内存转储@ 0x0000000009ABA330

0000000000000000：30001100 01aaa153 4e000000 00000000†0 ....a¡N...... 
0000000000000010：00060000 02002000 28003801 02000000†......（。8 ... 
0000000000000020：2828302e 30302929††††††††††††††††††††††††

记录m_ghostRecCnt现在为0，我们再也找不到过程代码了。此外，objid记录指向另一个项目。

无论如何，如果你在选项2中运行DBCC PAGE，你仍然可以找到一些对象轨道！

基本上，页面现在是“免费的”，但字节保持不变，直到将某些内容写入该空间。所以，幸运的是，你可以在紧急情况下恢复对象。真棒！:)