设为首页 收藏本站

运维网

查看: 122|回复: 0

[经验分享] SQL Server 数据库文件管理

[复制链接]

尚未签到

发表于 2018-10-11 13:55:43 | 显示全部楼层 |阅读模式
  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,就再移回来.执行查询等操作时将延长处理时间,因为这些记录将同步处理,而不是异步。
  如下图所示:
DSC0000.png

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

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

  在这里面,我们用到较多的页是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)。并且每一个数据库的前八个页面是固定不变的。
DSC0003.png

  下面,我们可以通过DBCC Page命令查看某一个数据库的页面信息,此命令的语法是:
  dbcc page(数据库名称|数据库ID,文件编号,页面编号,输出选项)
  输出选项:0:默认值,输出缓冲区的标题和页面标题;1:输出缓冲区的标题、页面标题(分别输出每一行),以及行偏移量表;2:输出缓冲区的标题、页面标题(整体输出页面),以及行偏移量表。3:输出标题的同时,显示列值。
  但需要此命令之前,必须启用DBCC TRACEON(3604)。
DSC0004.png

  一个完整的页面包含四个部分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个字节。
  页的末尾是行偏移表,对于页中的每一行,每个行偏移表都包含一个条目。每个条目记录对应行的第一个字节与页首的距离。行偏移表中的条目的顺序与页中行的顺序相反。如下图所示:
DSC0005.png DSC0006.png

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


运维网声明 1、欢迎大家加入本站运维交流群:群①:263444886群②:197202523群③:485755530群④:201730672群⑤:202807635运维网交流群⑥:281548029
2、本站所有主题由该帖子作者发表,该帖子作者与运维网享有帖子相关版权
3、其他单位或个人使用、转载或引用本文时必须注明原文的出处
4、如本帖侵犯到任何版权问题,请立即告知本站,本站将及时予与删除并致以最深的歉意
5、运维网 - 服务您的运维操作管理专家!
6、联系人Email:admin@yunvn.com 网址:www.iyunv.com

点击关注更多内容
您需要登录后才可以回帖 登录 | 立即注册  

本版积分规则  允许回帖邮件提醒楼主

扫码加入运维网微信交流群X

扫码加入运维网微信交流群

扫描二维码加入运维网微信交流群,最新一手资源尽在官方微信交流群!快快加入我们吧...

扫描微信二维码查看详情

客服 E-mail:kefu@yunvn.com

本站由青云提供云计算服务

运维网--中国最专业的运维工程师交流社区

京ICP备14039699号-1 Copyright © 2012-2018

使用手机软件扫描微信二维码

关注我们可获取更多热点资讯

Good good study day day up !


提醒:禁止发布任何违反国家法律、法规的言论与图片等内容;本站内容均来自个人观点与网络等信息,非本站认同之观点.


独家合作伙伴: 青云cloud

快速回复 返回顶部 返回列表