SQL Server 数据库文件管理

       SQL Server 数据库文件管理

杜飞

       2014年,注定要和数据库打交道,陆续接到的任务都是和数据库管理相关的,某民航系统的数据库优化、某地产企业的数据库高可用、某手机连锁企业的数据库读写分离,某快捷连接企业的数据库迁移,一下子感脚2014怎么了?于是,准备就数据库的管理写几篇文章,今天算是开篇吧。

       关于数据库文件的管理问题,我经常说,常在江湖混,哪有不挨棍,用的时间长了,基本上都有遇到一些数据库文件管理上的问题,比如说:

1. SQL Server数据文件空间满

2. 日志文件暴涨

3. 文件不能收缩

4. 如何实现文件的自动增长和自动收缩

      这篇文章就围绕这些问题展开,当然要想熟练的对数据库空间进行管理,需要先了解一下相关的理论知识。首先,我们先来看一下数据文件的空间管理。

数据文件空间管理

       每个 SQL Server 数据库至少具有两个操作系统文件:一个数据文件和一个日志文件。  数据文件包含数据和对象,例如表、索引、存储过程和视图。 日志文件包含恢复数据库中的所有事务所需的信息。 为了便于分配和管理,可以将数据文件集合起来,放到文件组中。在SQL Server中数据存储的基本单位称为页。每页是8KB,SQL Server读取或者是写入数据的最小单位也是页,那么1MB就有128页。行不能跨页(页的单个行最大数量是8,060字节8kb 1024*8),不包括Text/Image类型的页数据,对于可变长类型的列,如果行超过8060,则从最大长度的列开始,将一个或多个可变长度列移动到ROW_OVERFLOW_DATA分配单元中的页,在原始页上维护一个24字节的指针,如果行的总大小小于8060,就再移回来.执行查询等操作时将延长处理时间,因为这些记录将同步处理,而不是异步。

如下图所示:

SQL Server 数据库文件管理

      但还有另一个概念,大家要知道,叫做区,区是指8个物理上连续的页的集合,如果这8个物理上连续的页属于同一个表,则这种区称为统一区,如果这8个页分别属于至少两个不同的表。则这种区称为混合区。

SQL Server 数据库文件管理

     虽然每个页有8KB,但并不是说这8KB都用来存放具体数据,每页的开头有一个96字节的页头,用来存储有关页的系统信息,例如:页码、页类型、页的可用空间以及拥有该页的对象ID(也就是这个页是哪个对象在用)。不同类型的数据,存放在不同类型的页里面。如下图所示,就显示了数据文件中各种页类型以及它们里面存放的内容:

SQL Server 数据库文件管理

       在这里面,我们用到较多的页是Data和Text/Image类型,而在一个数据文件的开头则分布很多管理页面如:GM、SGAM、PFS。SQL Server通过这些页面知道这个数据文件中的哪些页面已经使用,哪些页面还没有使用等。

      当一张表或一个索引需要更多的空间时,SQL Server需要找到能够用来分配的空间。如果该表或索引整体仍然少于8个页面,SQL Server必须找到能够用来分配的混合类型区构成的空间。如果表或索引有8个页面或更大,SQL Server必须找到一个*的统一类型的区。那么SQL Server就需要知道区已经分配出去,哪些区可以使用,这就要用到全局分配映射页面和共享全局分配映射页面,简称为:GAM/SGAM。

     GAM记录了哪些区已经被分配并用作何种用途。一个GAM页面在它所覆盖空间里针对每一个区都有一个数据位。如果为1,则为空闲区,可以用来分配,如果为0则该区已经被使用。GAM大约能标识64000个区,也就是4G的空间。如果超过4G,则再启用一个GAM页来标识下一个4G空间。

       SGAM记录了哪些区当前是混合区,并且至少有一个未使用的页面。它也能标识64000个区,大约4G空间。如果bit位标识为1,则说明它使用的是混合区并且至少有一个页可用来分配 。如果为0,则说明是统一区,或者是混合区,但已经没有空闲页面。

       那么SQL Server可以很方便地查找需要的页面,如需要一个新的完全没有使用的区,那么可以使用任何一个在GAM页面中对应的比特位值为1的区。如果需要找到一个有着可用空间,如一个或多个*页面的混合类型的区,那么它可以寻找一个对应的GAM中的值为0、SGAM中的值为1的区。如果不存在有可用空间的混合类型的区,SQL Server会使用GAM页面来寻找一个全新的区并将其分配为混合类型的区,然后使用该区中的一页。如果根本没有*区,那么这个文件已经满了。  

       SQL Server能够迅速地锁定一个文件中的GAM页面,因为它总是位于任何数据库文件的第三页上(页码为2)。SGAM页面是在第四页上(页码为3)。下一个GAM页面出现在第一个GAM页面(页码为2)以后的每511 230个页面中,并且下一个SGAM页面出现在第一个SGAM页面(页码为3)以后的每511 230个页面中。每一个数据库文件的页码为0的页面是文件头页面,并且每个文件仅有一页。页码0是头文件页,页码1是页面*空间页(Page Free Space,PFS)。并且每一个数据库的前八个页面是固定不变的。

SQL Server 数据库文件管理

       下面,我们可以通过DBCC Page命令查看某一个数据库的页面信息,此命令的语法是:

       dbcc page(数据库名称|数据库ID,文件编号,页面编号,输出选项)

输出选项:0:默认值,输出缓冲区的标题和页面标题;1:输出缓冲区的标题、页面标题(分别输出每一行),以及行偏移量表;2:输出缓冲区的标题、页面标题(整体输出页面),以及行偏移量表。3:输出标题的同时,显示列值。

       但需要此命令之前,必须启用DBCC TRACEON(3604)。

SQL Server 数据库文件管理

一个完整的页面包含四个部分BUFFER、PAGE HEADER、DATA、OFFSET TABLE,分别表示缓存、页面的头部信息、数据和偏移表。

BUFFER:缓存部分,用于标识页在内存中的位置。

PAGE HEADER:页面头部信息,包括一些重要属性,如:m_pageid为页编号;m_headerversion为页头格式的版本;m_type为页的类型,如:1,表明是数据页,2为索引页,8为GAM页,9为SGAM页等;m_typeflagebits:类型标识位,基本上用不到;m_level为在B树结构中的层级,最底层的层级为0;m_flagbits为页的属性,如0x200表明页有checksum检查;m_prevpage和m_nextpage,在B树结构的同一层级数据页之间,互相通过m_prevpage和m_nextpage连接起来;m_slotcnt表明当前页中有多少条记录;m_freecnt表明当前页中还剩余多少 空间,以字节为单位。m_lsn表明当前页中的所有记录中,最后一个改变相对应的日志记录号。

DATA部分一般分为若干插槽号(Slot),如果是数据页或索引页的话,可以理解为一行记录,SQLServer通过文件号+页面号+插槽号用来唯一标识表中的每一条记录。但在GAM页中我们可以把Slot 0理解为GAM页的保留页,共计94个字节。

页的末尾是行偏移表,对于页中的每一行,每个行偏移表都包含一个条目。每个条目记录对应行的第一个字节与页首的距离。行偏移表中的条目的顺序与页中行的顺序相反。如下图所示:

SQL Server 数据库文件管理SQL Server 数据库文件管理

     我们可以看到第一个数据行,也就是上图中的最后一个记录,偏移量是96,这是因为正常好前面的是96个字节的头部,接下来正好是第一条记录,其他记录的分析,依次类推。

SQL Server 数据库文件管理,布布扣,bubuko.com

SQL Server 数据库文件管理

上一篇:Oracle角色、权限的一些常用视图


下一篇:Photoshop调出暗调古典冷褐色水边的美女图片