一次SQL调优聊一聊 SQLSERVER 数据页

一次,sql,sqlserver,数据 · 浏览次数 : 2395

小编点评

**总结** **减少请求在数据页上的流转** * 通过使用 SQL Server 的 Query Optimization器，优化查询计划。 * 通过使用缓存技术，减少查询结果在数据页上的流转。 * 通过使用索引技术，提高数据页的命中率。 **减少逻辑读** * 通过使用 SQL Server 的 Query Optimization器，优化查询计划。 * 通过使用索引技术，提高数据页的命中率。 * 通过使用缓存技术，减少查询结果在数据页上的流转。 **提升 sql 的查询性能** * 通过使用 SQL Server 的 Query Optimization器，优化查询计划。 * 通过使用索引技术，提高数据页的命中率。 * 通过使用缓存技术，减少查询结果在数据页上的流转。 * 通过使用 SQL Server 的 Query Execution Plan cache，减少查询执行计划中的流转。 **其他** * SQL Server 的 Query Execution Plan cache，可以减少查询执行计划中的流转。 * SQL Server 的 Query Optimization器，可以优化查询计划。 * SQL Server 的索引技术，可以提高数据页的命中率。 * SQL Server 的缓存技术，可以减少查询结果在数据页上的流转。

正文

一：背景

1.讲故事

最近给一位朋友做 SQL 慢语句 优化，花了些时间调优，遗憾的是 SQLSERVER 非源码公开，玩起来不是那么顺利，不过从这次经历中我觉得明年的一个重大任务就是好好研究一下它，争取在 SQLSERVER 性能优化上做一些成绩，哈哈！个人觉得要想深入研究 SQLSERVER，得从它的存储引擎说起，说到存储引擎又得从核心的 数据页 说起，毕竟 mdf 就是由 数据页 拼出来的，当然理解的不对大家可以指出来。

二：理解数据页

1. 什么是数据页

一般来说，对大块资源或者数据进行高效管理都会按照一定粒度来划分的，比如说 Windows 对内存的管理就是按照 内存页 (4k) 来进行划分，言外之意就是 SQLSERVER 对 mdf 的管理也是按照 数据页（8k) 来划分的，画个图大概就是这样的。

那如何来验证这个结论呢？刚才也说了数据都在数据页上，我们弄点数据然后在指定的数据页上提取出来就好了，这里用的是 SQLServer 2019 。


CREATE DATABASE MyTestDB
GO
USE MyTestDB;
GO
IF OBJECT_ID('person') IS NOT NULL
    DROP TABLE person;
CREATE TABLE person
(
    id INT IDENTITY,
    name CHAR(5)
);
GO
INSERT INTO dbo.person( name ) VALUES ('john');
INSERT INTO dbo.person( name ) VALUES ('mary');

2. 寻找数据所在的数据页

刚才图中也说了 mdf 是由无数个 数据页 拼出来的，那如何找到 person 表所在的数据页呢？其实微软提供了一个 dbcc ind 命令可以帮我们洞察出来，同时记得开始 3604 标记，让输出显示在控制台上，而不是默认的错误日志中，这个命令具体怎么用，大家可以查看官方文档。


DBCC TRACEON(3604)
DBCC IND(MyTestDB,person, -1)

从输出看有两条记录，第一个是 PagePID=41 是 IAM 数据页，而PagePID=280 就是我们 person 表记录所在的数据页编号，也就是说 person 表的记录在 mdf 文件偏移为 0n280 * 0n8192 的位置，用 WinDbg 算一下就是 0x00230000 。


0:090> ? 0n280 * 0n8192
Evaluate expression: 2293760 = 00000000`00230000

那是不是呢？可以用 WinHex 验证一下，为了不出现进程占用，先把 MyTestDB 下线了，最后记得再上线即可。


ALTER DATABASE MyTestDB SET OFFLINE

ALTER DATABASE MyTestDB SET ONLINE

从 WinHex 上看，果然是在偏移为 0x00230000 这个数据页上。

3. 如何从内存中看到数据页

刚才我们看到的数据页只是物理硬盘上的，但数据页和数据页之间的逻辑关系肯定是在内存中用一定的数据结构来承载的，接下来看下这个 数据页 映射到 SQLSERVER 进程内存的哪里呢？微软提供了 DBCC PAGE 命令可以查看指定数据页的详细信息。


DBCC TRACEON(3604)
DBCC PAGE(MyTestDB,1,280,2)

输出结果如下：


DBCC 执行完毕。如果 DBCC 输出了错误信息，请与系统管理员联系。

PAGE: (1:280)


BUFFER:


BUF @0x000002B41104F480

bpage = 0x000002B3F0632000          bPmmpage = 0x0000000000000000       bsort_r_nextbP = 0x000002B41104F3D0
bsort_r_prevbP = 0x0000000000000000 bhash = 0x0000000000000000          bpageno = (1:280)
bpart = 1                           ckptGen = 0x0000000000000000        bDirtyRefCount = 0
bstat = 0x9                         breferences = 0                     berrcode = 0
bUse1 = 12454                       bstat2 = 0x0                        blog = 0x15ab215a
bsampleCount = 0                    bIoCount = 0                        resPoolId = 0
bcputicks = 0                       bReadMicroSec = 182                 bDirtyContext = 0x0000000000000000
bDbPageBroker = 0x0000000000000000  bdbid = 10                          bpru = 0x000002B3FA708040

PAGE HEADER:


Page @0x000002B3F0632000

m_pageId = (1:280)                  m_headerVersion = 1                 m_type = 1
m_typeFlagBits = 0x0                m_level = 0                         m_flagBits = 0x8200
m_objId (AllocUnitId.idObj) = 179   m_indexId (AllocUnitId.idInd) = 256 
Metadata: AllocUnitId = 72057594049658880                                
Metadata: PartitionId = 72057594043170816                                Metadata: IndexId = 0
Metadata: ObjectId = 581577110      m_prevPage = (0:0)                  m_nextPage = (0:0)
pminlen = 13                        m_slotCnt = 2                       m_freeCnt = 8060
m_freeData = 128                    m_reservedCnt = 0                   m_lsn = (37:584:3)
m_xactReserved = 0                  m_xdesId = (0:0)                    m_ghostRecCnt = 0
m_tornBits = -116446693             DB Frag ID = 1                      

Allocation Status

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

DATA:


Memory Dump @0x000000F840DF8000

000000F840DF8000:   01010000 00820001 00000000 00000d00 00000000  ....................
000000F840DF8014:   00000200 b3000000 7c1f8000 18010000 01000000  ........|...........
000000F840DF8028:   25000000 48020000 03000000 00000000 00000000  %...H...............
000000F840DF803C:   1b2a0ff9 00000000 00000000 00000000 00000000  .*..................
000000F840DF8050:   00000000 00000000 00000000 00000000 10000d00  ....................
000000F840DF8064:   01000000 6a6f686e 20020000 10000d00 02000000  ....john ...........
000000F840DF8078:   6d617279 20020000 00002121 21212121 21212121  mary .....!!!!!!!!!!
000000F840DF808C:   21212121 21212121 21212121 21212121 21212121  !!!!!!!!!!!!!!!!!!!!
000000F840DF80A0:   21212121 21212121 21212121 21212121 21212121  !!!!!!!!!!!!!!!!!!!!
...
000000F840DF9FF4:   21212121 21212121 70006000                    !!!!!!!!p.`.

OFFSET TABLE:

Row - Offset                        
1 (0x1) - 112 (0x70)                
0 (0x0) - 96 (0x60)                 


DBCC 执行完毕。如果 DBCC 输出了错误信息，请与系统管理员联系。

Completion time: 2022-12-30T17:48:20.5466040+08:00

从上面的 Memory Dump 区节中可以看到，数据在进程内存的 000000F840DF8064 ~ 000000F840DF8078 范围内，这里要吐槽的是内存地址按照 大端布局 的，看起来很不习惯，可以用 windbg 用 小端布局 来显示。


0:116> dp 000000F840DF8064
000000f8`40df8064  6e686f6a`00000001 000d0010`00000220
000000f8`40df8074  7972616d`00000002 21210000`00000220
000000f8`40df8084  21212121`21212121 21212121`21212121
000000f8`40df8094  21212121`21212121 21212121`21212121
000000f8`40df80a4  21212121`21212121 21212121`21212121
000000f8`40df80b4  21212121`21212121 21212121`21212121
000000f8`40df80c4  21212121`21212121 21212121`21212121
000000f8`40df80d4  21212121`21212121 21212121`21212121

4. sql 请求源码研究

喜欢玩 windbg 的朋友肯定想对 sqlserver 进行汇编级洞察，比如研究下 sql 请求在 sqlserver 里面的执行流是什么样的？其实很简单，我们可以这样处理，使用 ba 对 john 的内存地址下一个硬件断点，即 ba r4 000000f840df8064+0x4 ，然后在 SSMS 上执行一条 SELECT * FROM person 语句，因为要提取 john 自然就会命中。


0:104> ba r4 000000f840df8064+0x4 
0:104> g
Breakpoint 0 hit
sqlmin!BTreeMgr::GetHPageIdWithKey+0x4a:
00007ff8`d4ea121a 48894c2478      mov     qword ptr [rsp+78h],rcx ss:000000f8`45278028=0000024800000025
0:102> k
 # Child-SP          RetAddr               Call Site
00 000000f8`45277fb0 00007ff8`d4ea0b59     sqlmin!BTreeMgr::GetHPageIdWithKey+0x4a
01 000000f8`45278450 00007ff8`d4ea08b7     sqlmin!IndexPageManager::GetPageWithKey+0x119
02 000000f8`45278d20 00007ff8`d4eb22d1     sqlmin!GetRowForKeyValue+0x203
03 000000f8`45279880 00007ff8`d4eb2a39     sqlmin!IndexDataSetSession::GetRowByKeyValue+0x141
04 000000f8`45279a70 00007ff8`d4eb279b     sqlmin!IndexDataSetSession::FetchRowByKeyValueInternal+0x230
05 000000f8`45279ed0 00007ff8`d4eb2883     sqlmin!RowsetNewSS::FetchRowByKeyValueInternal+0x437
06 000000f8`4527a000 00007ff8`d4eaadab     sqlmin!RowsetNewSS::FetchRowByKeyValue+0x96
07 000000f8`4527a050 00007ff8`d4f93d60     sqlmin!CMEDScan::StartSearch+0x4f8
08 000000f8`4527a170 00007ff8`d4f93f3a     sqlmin!CMEDCatYukonObject::GetTemporalCurrentTableId+0x10e
09 000000f8`4527a380 00007ff8`d801f0d1     sqlmin!CMEDProxyRelation::GetTemporalCurrentTableId+0x7a
0a 000000f8`4527a3c0 00007ff8`d801dfb8     sqllang!CAlgTableMetadata::FPartialBind+0xb58
0b 000000f8`4527a580 00007ff8`d80394b3     sqllang!CAlgTableMetadata::Bind+0x317
0c 000000f8`4527a620 00007ff8`d800415d     sqllang!CRelOp_Get::BindTree+0x78f
0d 000000f8`4527a890 00007ff8`d80418a1     sqllang!COptExpr::BindTree+0x85
0e 000000f8`4527a8c0 00007ff8`d800415d     sqllang!CRelOp_FromList::BindTree+0x31
0f 000000f8`4527a920 00007ff8`d802c2a3     sqllang!COptExpr::BindTree+0x85
10 000000f8`4527a950 00007ff8`d800415d     sqllang!CRelOp_QuerySpec::BindTree+0x2e8
11 000000f8`4527aa60 00007ff8`d80042dd     sqllang!COptExpr::BindTree+0x85
12 000000f8`4527aa90 00007ff8`d800415d     sqllang!CRelOp_SelectQuery::BindTree+0x489
13 000000f8`4527ab80 00007ff8`d8003f23     sqllang!COptExpr::BindTree+0x85
14 000000f8`4527abb0 00007ff8`d8004e47     sqllang!CRelOp_Query::FAlgebrizeQuery+0x4bd
15 000000f8`4527ae30 00007ff8`d7ff5576     sqllang!CProchdr::FNormQuery+0x8f
16 000000f8`4527ae70 00007ff8`d7ff4a79     sqllang!CProchdr::FNormalizeStep+0x5bd
17 000000f8`4527b4b0 00007ff8`d7ff5124     sqllang!CSQLSource::FCompile+0xea5
18 000000f8`4527e1b0 00007ff8`d7e659c3     sqllang!CSQLSource::FCompWrapper+0xcb
19 000000f8`4527e280 00007ff8`d7e6387a     sqllang!CSQLSource::Transform+0x721
1a 000000f8`4527e3e0 00007ff8`d7e6e67b     sqllang!CSQLSource::Execute+0x4fa
1b 000000f8`4527e8c0 00007ff8`d7e6d815     sqllang!process_request+0xca6
1c 000000f8`4527efc0 00007ff8`d7e6d5ef     sqllang!process_commands_internal+0x4b7
1d 000000f8`4527f0f0 00007ff8`d4096523     sqllang!process_messages+0x1d6
1e 000000f8`4527f2d0 00007ff8`d4096e6d     sqldk!SOS_Task::Param::Execute+0x232
1f 000000f8`4527f8d0 00007ff8`d4096c75     sqldk!SOS_Scheduler::RunTask+0xa5
20 000000f8`4527f940 00007ff8`d40bb160     sqldk!SOS_Scheduler::ProcessTasks+0x39d
21 000000f8`4527fa60 00007ff8`d40baa5b     sqldk!SchedulerManager::WorkerEntryPoint+0x2a1
22 000000f8`4527fb30 00007ff8`d40bafa4     sqldk!SystemThreadDispatcher::ProcessWorker+0x3ed
23 000000f8`4527fe30 00007ff8`f6d86fd4     sqldk!SchedulerManager::ThreadEntryPoint+0x3b5
24 000000f8`4527ff20 00007ff8`f865cec1     KERNEL32!BaseThreadInitThunk+0x14
25 000000f8`4527ff50 00000000`00000000     ntdll!RtlUserThreadStart+0x21

从线程栈上看，有 SQLSERVER 核心的 Scheduler ，Task 以及命令分析器，查询优化器，查询执行器等各种核心元素，后续再慢慢研究吧。

三：总结

深入的理解数据，索引在数据页上的布局，可以从根本上帮助我们理解如何减少请求在数据页上的流转，减少逻辑读，从而提升 sql 的查询性能。