SQL Server 数据库文件管理

本文涉及的产品
RDS SQL Server Serverless,2-4RCU 50GB 3个月
推荐场景:
云数据库 RDS SQL Server,独享型 2核4GB
日志服务 SLS,月写入数据量 50GB 1个月
简介:

       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,就再移回来.执行查询等操作时将延长处理时间,因为这些记录将同步处理,而不是异步。

如下图所示:

image

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

image

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

image

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

image

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

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

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

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

image

一个完整的页面包含四个部分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个字节。

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

imageimage

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




 本文转自 dufei 51CTO博客,原文链接:http://blog.51cto.com/dufei/1377630,如需转载请自行联系原作者


相关实践学习
SQL Server on Linux入门教程
SQL Server数据库一直只提供Windows下的版本。2016年微软宣布推出可运行在Linux系统下的SQL Server数据库,该版本目前还是早期预览版本。本课程主要介绍SQLServer On Linux的基本知识。 相关的阿里云产品:云数据库RDS SQL Server版 RDS SQL Server不仅拥有高可用架构和任意时间点的数据恢复功能,强力支撑各种企业应用,同时也包含了微软的License费用,减少额外支出。 了解产品详情: https://www.aliyun.com/product/rds/sqlserver
相关文章
|
2天前
|
SQL Oracle 关系型数据库
|
1天前
|
SQL Oracle 关系型数据库
MySQL、SQL Server和Oracle数据库安装部署教程
数据库的安装部署教程因不同的数据库管理系统(DBMS)而异,以下将以MySQL、SQL Server和Oracle为例,分别概述其安装部署的基本步骤。请注意,由于软件版本和操作系统的不同,具体步骤可能会有所变化。
14 3
|
6天前
|
SQL 监控 安全
数据库安全:SQL注入防御实践
【7月更文挑战第11天】SQL注入攻击作为一种常见的网络攻击手段,对数据库的安全性和业务稳定构成了严重威胁。为了有效防御SQL注入攻击,开发者和数据库管理员应采取一系列实践措施,包括输入验证与过滤、使用参数化查询、限制数据库用户权限、使用Web应用程序防火墙、定期更新和打补丁、实施实时监控和审计以及使用HTTPS协议等。通过这些措施的实施,可以显著提升数据库的安全性,降低遭受SQL注入攻击的风险。同时,开发者和数据库管理员应持续关注新的安全威胁和防御技术,不断提升自身的安全防护能力。
|
7天前
|
SQL 存储 安全
数据库数据恢复—SQL Server数据库出现逻辑错误的数据恢复案例
SQL Server数据库数据恢复环境: 某品牌服务器存储中有两组raid5磁盘阵列。操作系统层面跑着SQL Server数据库,SQL Server数据库存放在D盘分区中。 SQL Server数据库故障: 存放SQL Server数据库的D盘分区容量不足,管理员在E盘中生成了一个.ndf的文件并且将数据库路径指向E盘继续使用。数据库继续运行一段时间后出现故障并报错,连接失效,SqlServer数据库无法附加查询。管理员多次尝试恢复数据库数据但是没有成功。
|
6天前
|
SQL Java 关系型数据库
Java面试题:描述JDBC的工作原理,包括连接数据库、执行SQL语句等步骤。
Java面试题:描述JDBC的工作原理,包括连接数据库、执行SQL语句等步骤。
16 0
|
6天前
|
SQL 监控 Java
Java面试题:简述数据库性能优化的常见手段,如索引优化、SQL语句优化等。
Java面试题:简述数据库性能优化的常见手段,如索引优化、SQL语句优化等。
15 0
|
10天前
|
SQL 存储 数据库
SQL Server性能优化策略与实践
在使用Microsoft SQL Server(简称MSSQL)进行数据库管理时,性能优化是确保系统高效运行、提升用户体验的关键环节
|
11天前
|
数据库 数据安全/隐私保护
Failed to load resource: the server responded with a status of 404 ()出错的原因是,因为自己调试的时候,设置了与宝塔不一样的数据库
Failed to load resource: the server responded with a status of 404 ()出错的原因是,因为自己调试的时候,设置了与宝塔不一样的数据库
|
29天前
|
SQL IDE Java
Java连接SQL Server数据库的详细操作流程
Java连接SQL Server数据库的详细操作流程