【MSSQL】来谈谈 SQL Server 内部具体储存资料的方式

前言

今天稍早的时候与同事聊天时提及到了 mdf、ndf 以及 ldf 之间的关係
让我想起之前遇到 ldf 档案大小爆炸级别的成长时
深感对这方面的知识量不足
因此特地借阅了 Pro SQL Server Internals 来进行阅读
想了想后 乾脆把这些笔记贴出来
也方便我未来时方便进行查阅
以下正文内容皆是我阅读时所记录下来的
如果有任何错误也麻烦在留言区告诉我一声
我会立即进行修改的

---

以下节录至 Pro SQL Server Internals Chapter 1

---

每个资料库中都含有一个或多个 Transaction Log File 以及 一个或多个 Data Files
而 Transaction Log File 主要的功用便是记录下所有Session下所有资料修改的交易纪录
以便在遇到问题时进行 Transaction Rollback 或 Redo

每个资料库只会有一个 Primary Data File 预设副档名为 .mdf
而资料库也可以拥有 Secondary Data File 预设副档名为 .ndf

所有资料库档案都将被 group 到 Filegroup 中以便资料库管理
根据经验法则（Rule of thumb）习惯上设定4 Data File 与 16 logical CPU 运作, 而在此之后则保持档案数量与逻辑CPU的比率为1:8

在 File 成长的部分 MSSQL 分成 AUTOGROW_SINGLE_FILE 及 AUTOGROW_ALL_FILES
AUTOGROW_SINGLE_FILE 将只针对 Filegroup 底下的单独 File 进行增长
AUTOGROW_ALL_FILES 则针对 Filegroup 底下的所有 File 进行增长

SQL Server 预设会对Transaction Log File 进行校正归零的行为
而透过 Instant File Initialization 的启用或关闭则可加速资料库的创建或修复

---

SQL Server 主要有三种方式去进行资料存取

row-based storage
透过 data row 将所有Column的资料结合在一起进行存放column-based storage
与前者相反这项技术的储存单位则为 ColumnIn-memory storage
这部分将在 VIII 进行详细的解说

SQL Server 提供了多元的资料型态
而这些资料型态分为
1. 固定长度
2. 可变长度
在固定长度下，储存空间为固定大小就算里头的值为 NULL 也是同样 （int: 4 Bytes, nchar(10): 20 Bytes）
而可变长度下在通常状况下 则为原先大小加上 2 额外的 Bytes
例如 nvarchar(4000) 去存放一个 5 字元的资料 则会使用 12 Bytes 去储存
而通常情况下则使用 2 Bytes 去存放 NULL

上面这边为 SQL Server Data Row 的架构图

TagA 及 TagB 主要用来表达 Row Type, Logical deleted ... 等相关资讯FSize 与 FData 两个分别代表固定长度资料的 Size 及 DataNCol 的部分则是表示该 Data Row 内有几的 ColumnNullbits 用来表示“以每个栏位为单位，纪录是NULL或不是NULL“剩余的部分则是拿来纪录 offsets 或 资料的 version 纪录

透过 DBCC IND 可取得 Page 分配空间的相关详细资讯
在固定长度Column的Table上 会有最大限制Bytes (8,060)
而在动态长度Column的Table上则会透过 Row-overflow data 的方式去存取

在超过的资料中会透过图中的方式去纪录
并且对应位置的Page 从一开始就会是资料内容

---

LOB Storage

在 Text 、 nText 或 image这些资料型态中，SQL Server 会透过特殊的Page (LOB Data Pages) 去进行存放

■ Note

You can control this behavior to a degree by using the “text in row” table option.
For example,

exec sp_table_option dbo.MyTable, 'text in row', 200

forces SQL Server to store LOB data less than or equal to 200 bytes in-row.
LOB data greater than 200 bytes would be stored in LOB pages.

如果有需要透过in-row page 的方式去存放text 的资料的话
可透过上述原文提及到的方式限制在多少长度以下透过 in-row 存放，超过则透过 LOB Data Page 存放

---

Select * IO Problem

在捞取资料时，要注意不要透过 * 来进行捞取
会造成效率上问题 尤其资料是在具有 LOB 或 Row-overflow 的 Page时

---

Extents and Allocation Map Pages

每8个Page组成一个 extents
而Extents 主要分成两种
mixed extents
uniform extents
在物件新创立时会将前 8 Page建立成混合区
而后接续的Page则会透过统一区储存

SQL Server主要使用 Allocation Map 来进行储存
而 Allocation Map 主要分为

Global Allocation Map
用来记录 extents 及 page 的使用状况
储存方式为 bitmap，零代表目前此extents已被使用，反之存放一则是目前空间是空闲的。
而 Global Allocation Map 主要可存放 64,000 extents 换句话说也就是 4 GB
每个资料库档案皆拥有一个佔据 4 GB 的 Global Allocation MapShared Global Allocation Map
主要用来追蹤混合区 extents
储存方式同样为 bitmap
如果代表的extents 为混合区并且是空闲的状态则会储存为一
同样的 SGAM 可存放 64,000 extents 也同样是 4 GB

在 SQL Server 2016 则提供 MIXED_PAGE_ALLOCATION 选项
通常 tempdb 会disable 而其余的 user database 预设会是 enabled

第一个 GAM 会出现在 data file 的第三页，第一个 SGAM 则会出现在 data file 的第四页
接下来两者皆会每隔511,230 pages 出现一次

在追蹤不同类型（In-Row、Row Overflow、LOB）的 page 时，会透过 index allocation map (IAM) 来进行
每个 Table 或 Index 都拥有自己的 IAM
而这些 IAM page 会组成一个 linked list 而被称为 IAM chains

■ Tip

You can reduce the size of the data row by creating tables in a manner in which
variable-length columns, which usually store null values, are defined as the last
ones in the CREATE TABLE statement. This is the only case in which the order of
columns in the CREATE TABLE statement matters.

可以在创建资料表的最后加上一个可变动长度的Column用来存放NULL
这样比起在前面创建可以更节省空间
这是唯一一个创建资料表顺序上会影响的部分

■Tip.

1 Page( 8 KB ), 1 extents = 8 Page = 16 KB
extents 分成 统一区 跟 混合区

---

Data Modify

当资料要进行修改时，
SQL Server 会先从 Data File 将所需的 Page 读进 Buffer Pool，
并且同步写入该交易的 Log 纪录
在这之后才会将资料进行修改
此时修改所需的还原纪录便会被记录在 Transaction Log 中

最后 SQL Server 会将 Buffer Pool 中标记为 dirty 的 Data Page 重新以非同步的方式写回 Data File 中并且纪录一个特殊的 Transaction Log
而这部分是透过 Check Point 这个程序

而在插入步棸中也是相同
会先将所需的 Extents 或 Page 读取进入 Buffer Pool
而后会进行 Transaction Log 纪录，并以非同步的方式写入 Data File
而在删除中，则会透过 Ghosted 的方式进行处理而非直接进行物理删除
因此可以快速的删除及恢复原先状态及资料

而 SQL Server 有提供一种叫 lazy writer
这个 Writer 会将资料写入 Data File 并从记忆体将其移除

First, when SQL Server processes DML queries (SELECT, INSERT, UPDATE, DELETE, and
MERGE), it never works with the data without first loading the data pages into the
buffer pool. Second, when you modify the data, SQL Server synchronously writes log
records to the transaction log. The modified data pages are saved to the data files
asynchronously in the background.

上述两点重点

在执行 DML 时，都会先将资料读取至 Buffer Pool 进行处理修改资料时会以同步方式交易资料至 log 纪录，并以非同步的方式写回 Data File

---

Much Ado about Data Row Size

当一个 Query 的 IO 操作越多，
则越多的 Data Page 需要被写入 Buffer
也因此速度就会越慢

而当 Data Row 的 Size 太大时，
则代表在搜寻时要经过多个 Pages
因此速度会比较慢
除此之外也会花费较多的 Buffer 记忆体空间去存取

这边举了一个例子来让我们知道储存型别大小所带来的影响
由于 LargeRows 宣告固定长度字串因此相较于 SmallRows 的动态长度字串空间花的比较多
也因此在搜寻时，LargeRows 读写速度远小于 SmallRows
因此这边作者提出一些实用建议

用 Bit 资料型别来代表 Boolean 资料在时间型别选用适当的Type
smalldatetime (four bytes of storage space)
datetime2(0) (six bytes of storage space)
datetime (eight bytes of storage space)使用 decimal 或 real 资料型别来取代 float
同样的在金钱上可透过 money 或 small money 来取代 float避免用固定长度的字串，除非确认其资料最大最小长度经常一致

虽然在目前比较只少 18 bytes
但在资料笔数一增长时便会带来明显的差异
并且由于单笔资料 Size 增加
会使得网路频宽、记忆体使用量、备份档案大小
而若没考虑到这些则会在云服务盛行的现代为公司带来不少的成本浪费
而在资料库设计中
资料表若原先没考虑到后续变更后果
则会为后续造成更大的成本
Ex . smallint to int

---

Table Alteration

SQL Server 有三种 Table Alteration 情境

只改 Metadata
Ex. 新增、移除栏位资讯，更改非空值栏位变成空值栏位（Nullable）改变 Metadata 并检查 data row 是否符合更改后条件
Ex. 更改 int 栏位变成 smallint 则需要检查是否符合数值区间更改 Metadata 前需要先更改所有的 data row
Ex. char to int, char to varchar
透过 Table Alteration 更改栏位空间大小并不会因此而降低
如果是 int -> smallint 这个case 则 SQL Server 会纪录 type 并确认数值区间
但与更改前的空间相同
如果是 smallint -> int 则会增加空间去纪录额外需要的资讯
从课本中可知道空间被浪费了
如果要降低空间浪费则需要透过重建 heap table 或新增clustered index
而在进行修改时，SQL Server 会透过 schema modification (SCH-M) lock 来避免其他 Session 去存取该 Table 的资讯

---

Summary

data file 可以透过 filegroup 去划分
在这边建议建立多个 data file 去储存资讯，并且将各项需划分的资料透过 filegroup 分割开来，来隔离保护资料

以上内容为 Pro SQL Server 阅读后做下来的笔记
有兴趣的读者务必参阅

Pro SQL Server Internals