数据库空间管理 学习笔记

SQL Server 中数据存储的基本单位是页（Page）。磁盘I/O 操作在页级执行。

SQL Server 读取或写入数据的最小单位就是以8 KB 为单位的页。

每页的开头是一个96 B 的页头，用于存储有关页的系统信息。包括页码、页类型、页的可用空间，以及拥有该页

的对象的分配单元ID。不同类型的数据，存储在不同类型的页面里。

在正常数据页上，数据行紧接着页的标头按顺序放置。页的末尾是行偏移量表，对于页中的每一行，每个行偏移表都包含一个条目。

每个条目记录对应行的第一个字节与页首的距离。行偏移表中的条目的顺序与页中行的顺序相反。

------------------此处插入宋大神的注解------------------------

除了页头占用的空间和行偏移矩阵占用的空间，中间剩下的空间就是给数据行使用的。一个数据行中还存在其他的信息用于表示该行数据，具体的结构是这样的：状态位A 1字节状态位B 1字节定长数据类型的长度 2字节定长数据的内容 具体定长数据字节列数 2字节NULL位图 列数/8个字节变长列的个数 2字节变长列的偏移矩阵 变长列个数*2个字节变长列的数据 具体变长数据字节 这些最少占据1+1+2+2+1=7

-------------------------------------------------------------------

输出指定的页面，可以使用：DBCC PAGE(<db_id>, <file_id>, <page_id>, <format_id>)

Db_id 可以从sp_helpdb 的结果中得到。

File_id 可以从sp_helpfile 的结果中得到。

Format_id 是你指定的输出格式。

在运行DBCC PAGE 之前，还需要打开跟踪标志（trace flag）3604

在 SQL Server 中，行不能跨页，属于同一行的所有字段的数据都要放在同一个页面里。页的最大数据量是8 060 B（8 KB）。

所以一般数据类型字段所组成的一行，最长加起来不能超过8 KB。

但是，这一限制不包括Text/Image数据类型字段的数据。

这些类型字段的数据会被单独存放在LOB（LargeObject）页面里。

SQL Server 2005 开始的版本中，提供了varchar(max)、nvarchar(max)、varbinary(max)这样的数据类型。

如果包含这一类数据类型字段的行，总长不超过8 KB，数据还是会一起存放在普通数据页面里。

如果总长超过了8 KB，SQL Server 就会把这些字段的数据分开，单独存放在一种叫Row-Overflow（行溢出）的页面里。

区（Extent）是8 个物理上连续的页的集合，用来有效地管理页。所有页都存储在区中。

• 统一区，由单个对象所有。区中的所有8 页只能由一个对象使用。

• 混合区，最多可由8 个对象共享。区中8 页的每页可由不同的对象所有。但是一页总是只能属于一个对象。

通常从混合区向新表或索引分配页。当表或索引增长到8 页时，将变成使用统一区进行后续分配。

DBCC SHOWFILESTATS

这个命令能直接从GAM 和SGAM 这样的系统分配页面上面读取区分配信息，直接算出数据库文件里有多少区已被分配。

sp_spaceused （+ updateusage才会准确）

DBCC SHOWCONTIG（ 慎用，SQL Server 2005 之后可以使用sys.dm_db_index_physical_stats）

sys.dm_db_partition_stats

可以从页面这个级别来分析检查数据库空间大小。

日志文件不是按页/区来组织的。

数据库引擎在内部将每一物理日志文件分成多个虚拟日志单元。虚拟日志单元没有固定大小，

且一个物理日志文件所包含的虚拟日志单元数不固定。

日志文件每自动增长一次，会至少增加一个虚拟日志单元。

如果一个日志文件经历了多次小的自动增长，里面的虚拟日志单元数目会比正常的日志文件多很多。

这种情况会影响到日志文件管理的效率，甚至造成数据库启动要花很长时间。

事务日志是一种回绕的文件。当创建数据库时，逻辑日志文件从物理日志文件的始端开始。

新日志记录被添加到逻辑日志的末端，然后向物理日志的末端扩张。

当逻辑日志的末端到达物理日志文件的末端时，新的日志记录将回绕到物理日志文件的始端，继续向后写。

DBCC SQLPERF(LOGSPACE)可以查看日志文件的使用情况。

Tempdb 系统数据库是一个全局资源，可供连接到SQL Server 实例的所有用户使用，在SQL Server2005 中，用于保存下列各项：

用户对象

• 用户定义的表和索引。

• 系统表和索引。

• 全局临时表和索引。

• 局部临时表和索引。

• table 变量。

• 表值函数中返回的表。

内部对象

• 用于游标或假脱机操作以及临时大型对象（LOB）存储的工作表。

• 用于哈希联接或哈希聚合操作的工作文件。

• 用于创建或重新生成索引等操作（如果指定了SORT_IN_TEMPDB）的中间排序结果，

或者某些GROUP BY、ORDER BY 或UNION 查询的中间排序结果。

每个内部对象至少使用9 页：一个IAM 页，一个8 页的区。

版本存储区

• 由使用快照隔离级别或已提交读隔离级别（基于行版本控制）的数据库中的数据修改事务生成的行版本。

• 由数据修改事务为实现联机索引操作、多个活动的结果集（MARS）以及AFTER 触发器等功能而生成的行版本。

Tempdb 空间使用的一大特点，是只有一部分对象，例如用户创建的临时表、table 变量等，

可以用sys.allocation_units 和sys.partitions 这样的管理视图来管理。

tempdb 的空间使用是不能用sp_spaceused 来跟踪的。（不准确）

sys.dm_db_file_space_usage这张视图能反映tempdb 在几个大类里的空间使用分布。

是用户对象（user_object_reserved_page_count），还是系统对象（internal_object_reserved_page_count）

，还是版本存储区（version_store_reserved_page_count）。