SQLSERVER 阻塞之 PFS 页到底是什么？

一背景

1. 讲故事

在 SQLSERVER 的众多阻塞场景中有不小的一部分是由于 PFS 页上的 闩锁 等待造成的毕竟写页操作一定是要串行化的在面对 闩锁(PAGELATCH_X) 等待问题上一定要搞明白 PFS 页到底是什么 这篇就来好好聊一聊。

二PFS 详解

1. 什么是 PFS 页

我们知道数据库是由海量的 数据页 组成表记录会写入到 数据页 上那海量的数据页如何管理呢 SQLSERVER 想到了一个办法从海量的数据页中按一定规则择取一些作为 管理页 使用比如

• GAM 跟踪区分配情况
• SGAM 跟踪共享区分配情况
• PFS 跟踪数据页的空间使用情况

这里我简述一下吧GAM数据页中一个 bit 跟踪一个 64k 的空间(一个区)所以一个 8k 的GAM页 可以跟踪大约 (8 * 8192) * 64k = 4G 的空间而 PFS 数据页用一个 byte 跟踪一个 8k 的数据页理论上可以管理 8192 * 8k = 64M 的数据。

接下来的问题是这 1byte 是如何标记一个数据页的使用情况呢 简单的画个图吧。

从图中看这一个跟踪 byte 差不多都给塞满了有了这些基础之后接下来用一个案例来演示一下。

2. 案例演示

创建一个 MyTestDB 数据库新建一个 post 表参考sql如下

CREATE DATABASE MyTestDB
GO
USE MyTestDB
GO
CREATE TABLE post (id INT IDENTITY, content CHAR(1000))

创建好之后来观察下 MyTestDB 的 mdf 文件中的 PFS 数据页可以使用 DBCC PAGE 命令但这个命令需要获取 fileid, pageid 这两个参数那如何提取呢

• 如何提取 fileid

可以查询 sys.database_files 系统表提取。

SELECT file_id,name,physical_name FROM sys.database_files;

• 如何提取 pageid

刚才说过了这种管理页都是有规律的比如 PFS 页是 64M 一个在 file_id=1 的文件中第一个区的第1号数据页就是 PFS 页即 MyTestDB.mdf 文件偏移 8192byte 的位置这里稍微补充一下mdf 文件的第一个区中的 8 个 page 都是有特殊用途的画个简图如下

接下来观察下这个 PFS 数据页它的 type=11后续我们还会对它不断的观察。

DBCC TRACEON(3604)
DBCC PAGE(MyTestDB,1,1,3)

------ output -----
Page @0x000001DB52882000

m_pageId = (1:1)                    m_headerVersion = 1                 m_type = 11
m_typeFlagBits = 0x0                m_level = 0                         m_flagBits = 0x0
m_objId (AllocUnitId.idObj) = 99    m_indexId (AllocUnitId.idInd) = 0   Metadata: AllocUnitId = 6488064
Metadata: PartitionId = 0           Metadata: IndexId = 0               Metadata: ObjectId = 99
m_prevPage = (0:0)                  m_nextPage = (0:0)                  pminlen = 0
m_slotCnt = 1                       m_freeCnt = 2                       m_freeData = 8188
m_reservedCnt = 0                   m_lsn = (37:192:1)                  m_xactReserved = 0
m_xdesId = (0:0)                    m_ghostRecCnt = 0                   m_tornBits = 651443756
DB Frag ID = 1     

好了继续测试吧插入一条数据观察 PFS 页对应的位数是否有变化 比如空间使用率参考sql如下

INSERT INTO post(content) VALUES ('aaaaa')
DBCC TRACEON(3604)
DBCC IND(MyTestDB,post,-1)

从图中可以看到插入的 aaaaa 记录写到了 240 号数据页跟踪这个数据页的byte 理论上是在 PFS 页偏移 0n240 byte 的位置那具体是哪一个位置的 byte 跟踪的呢我们用 windbg 观察下 PFS 数据页的内存页地址即可首先用 DBCC PAGE(MyTestDB,1,1,2) 找到 Page 页在内存的首地址。

Memory Dump @0x000000C8177F8000

000000C8177F8000:   010b0000 00000000 00000000 00000000 00000000  ....................
000000C8177F8014:   00000100 63000000 0200fc1f 01000000 01000000  ....c...............
000000C8177F8028:   25000000 e0000000 1c000000 00000000 00000000  %...................
000000C8177F803C:   2c3ed426 01000000 00000000 00000000 00000000  ,>.&................
000000C8177F8050:   00000000 00000000 00000000 00000000 00009c1f  ....................
000000C8177F8064:   44444444 00004444 60647060 74706070 60607060  DDDD..DD`dp`tp`p``p`
000000C8177F8078:   60707060 40404040 40404040 61706070 60606070  `pp`@@@@@@@@ap`p```p
000000C8177F808C:   60646060 60706060 60706060 60606070 40404040  `d```p```p`````p@@@@

接下来用 WinDbg 附加 SqlServer由于前 96byte 是数据页头所以理论位置应该是 000000C8177F8000+0x60 + 0n240 的位置截图如下

哈哈终于给找到了那 0x41 是什么意思呢可以用 windbg 的 .formats 命令观察一下。

0:118> .formats 41
Evaluate expression:
  Hex:     00000000`00000041
  Decimal: 65
  Decimal (unsigned) : 65
  Octal:   0000000000000000000101
  Binary:  00000000 00000000 00000000 00000000 00000000 00000000 00000000 01000001

对应 byte 中位解读可以知道当前 240 号数据页的空间使用率为 1 ~50%同时 数据页已被分配。

这里我们顺带观察下 PageID=80 号这个 IAM 页看看 PFS 中如何表示它的在内存中计算位置应该是 000000C8177F8000+0x60 + 0n80输出如下

0:118> dp 000000C8177F8000+0x60 + 0n80 L2
000000c8`177f80b0  70607070`60606070 60302070`60606070
0:118> .formats 70
Evaluate expression:
  Hex:     00000000`00000070
  Decimal: 112
  Decimal (unsigned) : 112
  Octal:   0000000000000000000160
  Binary:  00000000 00000000 00000000 00000000 00000000 00000000 00000000 01110000

再对应 byte 位解读可以知道当前的 80号数据页 的四点信息

• 当前数据页已分配
• 当前数据页在混合区
• 当前数据页为 IAM 页
• 当前为特殊管理页

接下来再插入 4条 数据观察下 空间使用率 是否有变化 ?

INSERT INTO post(content) VALUES ('bbbbb')
INSERT INTO post(content) VALUES ('ccccc')
INSERT INTO post(content) VALUES ('ddddd')
INSERT INTO post(content) VALUES ('eeeee')

用 DBCC PAGE 观察内存页首地址之后再次用 WinDBG 附加观察输出如下

0:126> .formats 42
Evaluate expression:
  Hex:     00000000`00000042
  Decimal: 66
  Decimal (unsigned) : 66
  Octal:   0000000000000000000102
  Binary:  00000000 00000000 00000000 00000000 00000000 00000000 00000000 01000010

从输出可以看到已经由原来的 41 变成了 42其中的 010 刚好表示当前数据页为 51~80% 满作为校验的话可以直接观察 240号 页头上的 PFS (1:1) = 0x42 ALLOCATED 80_PCT_FULL 描述。

DBCC TRACEON(3604)
DBCC IND(MyTestDB,post,-1)
DBCC PAGE(MyTestDB,1,240,2)

-----  output -----

PAGE: (1:240)
...

PAGE HEADER:

Page @0x000001DB58F92000
...           
Allocation Status

GAM (1:2) = ALLOCATED               SGAM (1:3) = NOT ALLOCATED          PFS (1:1) = 0x42 ALLOCATED  80_PCT_FULL
DIFF (1:6) = CHANGED                ML (1:7) = NOT MIN_LOGGED           
...

三总结

总的来说 PFS页 瓶颈主要来自于用户创建的 临时表 和 表变量常常在高并发的场景下由于高频的创建和删除临时表以及对临时表的插入SQLSERVER 不得不高频的修改 PFS页 造成这一块的瓶颈如果大家在阻塞中看到大量的PageLatch_x 等待资源记得一定要将 Tempdb 中的 ndf 划分为多个来分摊写入压力。