ToB企服应用市场:ToB评测及商务社交产业平台

标题: 一次SQL调优 聊一聊 SQLSERVER 数据页 [打印本页]
作者: 惊雷无声    时间: 2022-12-30 21:21
标题: 一次SQL调优 聊一聊 SQLSERVER 数据页
一：背景

1.讲故事

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

1. 什么是数据页

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

那如何来验证这个结论呢？刚才也说了数据都在数据页上，我们弄点数据然后在指定的数据页上提取出来就好了，这里用的是 SQLServer 2019 。
  1. CREATE DATABASE MyTestDB
  2. GO
  3. USE MyTestDB;
  4. GO
  5. IF OBJECT_ID('person') IS NOT NULL
  6.     DROP TABLE person;
  7. CREATE TABLE person
  8. (
  9.     id INT IDENTITY,
  10.     name CHAR(5)
  11. );
  12. GO
  13. INSERT INTO dbo.person( name ) VALUES ('john');
  14. INSERT INTO dbo.person( name ) VALUES ('mary');
复制代码
2. 寻找数据所在的数据页

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

从输出看有两条记录，第一个是 PagePID=41 是 IAM 数据页，而PagePID=280 就是我们 person 表记录所在的数据页编号，也就是说 person 表的记录在 mdf 文件偏移为 0n280 * 0n8192 的位置，用 WinDbg 算一下就是 0x00230000 。
  1. 0:090> ? 0n280 * 0n8192
  2. Evaluate expression: 2293760 = 00000000`00230000
复制代码
那是不是呢？可以用 WinHex 验证一下，为了不出现进程占用，先把 MyTestDB 下线了，最后记得再上线即可。
  1. ALTER DATABASE MyTestDB SET OFFLINE
  3. ALTER DATABASE MyTestDB SET ONLINE
复制代码

从 WinHex 上看，果然是在偏移为 0x00230000 这个数据页上。
3. 如何从内存中看到数据页

刚才我们看到的数据页只是物理硬盘上的，但数据页和数据页之间的逻辑关系肯定是在内存中用一定的数据结构来承载的，接下来看下这个 数据页 映射到 SQLSERVER 进程内存的哪里呢？微软提供了 DBCC PAGE 命令可以查看指定 数据页 的详细信息。
  1. DBCC TRACEON(3604)
  2. DBCC PAGE(MyTestDB,1,280,2)
复制代码
输出结果如下：
  1. DBCC 执行完毕。如果 DBCC 输出了错误信息，请与系统管理员联系。
  3. PAGE: (1:280)
  6. BUFFER:
  9. BUF @0x000002B41104F480
  11. bpage = 0x000002B3F0632000          bPmmpage = 0x0000000000000000       bsort_r_nextbP = 0x000002B41104F3D0
  12. bsort_r_prevbP = 0x0000000000000000 bhash = 0x0000000000000000          bpageno = (1:280)
  13. bpart = 1                           ckptGen = 0x0000000000000000        bDirtyRefCount = 0
  14. bstat = 0x9                         breferences = 0                     berrcode = 0
  15. bUse1 = 12454                       bstat2 = 0x0                        blog = 0x15ab215a
  16. bsampleCount = 0                    bIoCount = 0                        resPoolId = 0
  17. bcputicks = 0                       bReadMicroSec = 182                 bDirtyContext = 0x0000000000000000
  18. bDbPageBroker = 0x0000000000000000  bdbid = 10                          bpru = 0x000002B3FA708040
  20. PAGE HEADER:
  23. Page @0x000002B3F0632000
  25. m_pageId = (1:280)                  m_headerVersion = 1                 m_type = 1
  26. m_typeFlagBits = 0x0                m_level = 0                         m_flagBits = 0x8200
  27. m_objId (AllocUnitId.idObj) = 179   m_indexId (AllocUnitId.idInd) = 256
  28. Metadata: AllocUnitId = 72057594049658880                                
  29. Metadata: PartitionId = 72057594043170816                                Metadata: IndexId = 0
  30. Metadata: ObjectId = 581577110      m_prevPage = (0:0)                  m_nextPage = (0:0)
  31. pminlen = 13                        m_slotCnt = 2                       m_freeCnt = 8060
  32. m_freeData = 128                    m_reservedCnt = 0                   m_lsn = (37:584:3)
  33. m_xactReserved = 0                  m_xdesId = (0:0)                    m_ghostRecCnt = 0
  34. m_tornBits = -116446693             DB Frag ID = 1                     
  36. Allocation Status
  38. GAM (1:2) = ALLOCATED               SGAM (1:3) = NOT ALLOCATED          PFS (1:1) = 0x41 ALLOCATED  50_PCT_FULL
  39. DIFF (1:6) = CHANGED                ML (1:7) = NOT MIN_LOGGED           
  41. DATA:
  44. Memory Dump @0x000000F840DF8000
  46. 000000F840DF8000:   01010000 00820001 00000000 00000d00 00000000  ....................
  47. 000000F840DF8014:   00000200 b3000000 7c1f8000 18010000 01000000  ........|...........
  48. 000000F840DF8028:   25000000 48020000 03000000 00000000 00000000  %...H...............
  49. 000000F840DF803C:   1b2a0ff9 00000000 00000000 00000000 00000000  .*..................
  50. 000000F840DF8050:   00000000 00000000 00000000 00000000 10000d00  ....................
  51. 000000F840DF8064:   01000000 6a6f686e 20020000 10000d00 02000000  ....john ...........
  52. 000000F840DF8078:   6d617279 20020000 00002121 21212121 21212121  mary .....!!!!!!!!!!
  53. 000000F840DF808C:   21212121 21212121 21212121 21212121 21212121  !!!!!!!!!!!!!!!!!!!!
  54. 000000F840DF80A0:   21212121 21212121 21212121 21212121 21212121  !!!!!!!!!!!!!!!!!!!!
  55. ...
  56. 000000F840DF9FF4:   21212121 21212121 70006000                    !!!!!!!!p.`.
  58. OFFSET TABLE:
  60. Row - Offset                        
  61. 1 (0x1) - 112 (0x70)               
  62. 0 (0x0) - 96 (0x60)                 
  65. DBCC 执行完毕。如果 DBCC 输出了错误信息，请与系统管理员联系。
  67. Completion time: 2022-12-30T17:48:20.5466040+08:00
复制代码
从上面的 Memory Dump 区节中可以看到，数据在进程内存的 000000F840DF8064 ~ 000000F840DF8078 范围内，这里要吐槽的是内存地址按照 大端布局 的，看起来很不习惯，可以用 windbg 用 小端布局 来显示。
  1. 0:116> dp 000000F840DF8064
  2. 000000f8`40df8064  6e686f6a`00000001 000d0010`00000220
  3. 000000f8`40df8074  7972616d`00000002 21210000`00000220
  4. 000000f8`40df8084  21212121`21212121 21212121`21212121
  5. 000000f8`40df8094  21212121`21212121 21212121`21212121
  6. 000000f8`40df80a4  21212121`21212121 21212121`21212121
  7. 000000f8`40df80b4  21212121`21212121 21212121`21212121
  8. 000000f8`40df80c4  21212121`21212121 21212121`21212121
  9. 000000f8`40df80d4  21212121`21212121 21212121`21212121
复制代码
4. sql 请求源码研究

喜欢玩 windbg 的朋友肯定想对 sqlserver 进行汇编级洞察，比如研究下 sql 请求在 sqlserver 里面的执行流是什么样的？ 其实很简单，我们可以这样处理，使用 ba 对 john 的内存地址下一个硬件断点，即 ba r4 000000f840df8064+0x4 ，然后在 SSMS 上执行一条 SELECT * FROM person  语句，因为要提取 john 自然就会命中。
  1. 0:104> ba r4 000000f840df8064+0x4
  2. 0:104> g
  3. Breakpoint 0 hit
  4. sqlmin!BTreeMgr::GetHPageIdWithKey+0x4a:
  5. 00007ff8`d4ea121a 48894c2478      mov     qword ptr [rsp+78h],rcx ss:000000f8`45278028=0000024800000025
  6. 0:102> k
  7. # Child-SP          RetAddr               Call Site
  8. 00 000000f8`45277fb0 00007ff8`d4ea0b59     sqlmin!BTreeMgr::GetHPageIdWithKey+0x4a
  9. 01 000000f8`45278450 00007ff8`d4ea08b7     sqlmin!IndexPageManager::GetPageWithKey+0x119
  10. 02 000000f8`45278d20 00007ff8`d4eb22d1     sqlmin!GetRowForKeyValue+0x203
  11. 03 000000f8`45279880 00007ff8`d4eb2a39     sqlmin!IndexDataSetSession::GetRowByKeyValue+0x141
  12. 04 000000f8`45279a70 00007ff8`d4eb279b     sqlmin!IndexDataSetSession::FetchRowByKeyValueInternal+0x230
  13. 05 000000f8`45279ed0 00007ff8`d4eb2883     sqlmin!RowsetNewSS::FetchRowByKeyValueInternal+0x437
  14. 06 000000f8`4527a000 00007ff8`d4eaadab     sqlmin!RowsetNewSS::FetchRowByKeyValue+0x96
  15. 07 000000f8`4527a050 00007ff8`d4f93d60     sqlmin!CMEDScan::StartSearch+0x4f8
  16. 08 000000f8`4527a170 00007ff8`d4f93f3a     sqlmin!CMEDCatYukonObject::GetTemporalCurrentTableId+0x10e
  17. 09 000000f8`4527a380 00007ff8`d801f0d1     sqlmin!CMEDProxyRelation::GetTemporalCurrentTableId+0x7a
  18. 0a 000000f8`4527a3c0 00007ff8`d801dfb8     sqllang!CAlgTableMetadata::FPartialBind+0xb58
  19. 0b 000000f8`4527a580 00007ff8`d80394b3     sqllang!CAlgTableMetadata::Bind+0x317
  20. 0c 000000f8`4527a620 00007ff8`d800415d     sqllang!CRelOp_Get::BindTree+0x78f
  21. 0d 000000f8`4527a890 00007ff8`d80418a1     sqllang!COptExpr::BindTree+0x85
  22. 0e 000000f8`4527a8c0 00007ff8`d800415d     sqllang!CRelOp_FromList::BindTree+0x31
  23. 0f 000000f8`4527a920 00007ff8`d802c2a3     sqllang!COptExpr::BindTree+0x85
  24. 10 000000f8`4527a950 00007ff8`d800415d     sqllang!CRelOp_QuerySpec::BindTree+0x2e8
  25. 11 000000f8`4527aa60 00007ff8`d80042dd     sqllang!COptExpr::BindTree+0x85
  26. 12 000000f8`4527aa90 00007ff8`d800415d     sqllang!CRelOp_SelectQuery::BindTree+0x489
  27. 13 000000f8`4527ab80 00007ff8`d8003f23     sqllang!COptExpr::BindTree+0x85
  28. 14 000000f8`4527abb0 00007ff8`d8004e47     sqllang!CRelOp_Query::FAlgebrizeQuery+0x4bd
  29. 15 000000f8`4527ae30 00007ff8`d7ff5576     sqllang!CProchdr::FNormQuery+0x8f
  30. 16 000000f8`4527ae70 00007ff8`d7ff4a79     sqllang!CProchdr::FNormalizeStep+0x5bd
  31. 17 000000f8`4527b4b0 00007ff8`d7ff5124     sqllang!CSQLSource::FCompile+0xea5
  32. 18 000000f8`4527e1b0 00007ff8`d7e659c3     sqllang!CSQLSource::FCompWrapper+0xcb
  33. 19 000000f8`4527e280 00007ff8`d7e6387a     sqllang!CSQLSource::Transform+0x721
  34. 1a 000000f8`4527e3e0 00007ff8`d7e6e67b     sqllang!CSQLSource::Execute+0x4fa
  35. 1b 000000f8`4527e8c0 00007ff8`d7e6d815     sqllang!process_request+0xca6
  36. 1c 000000f8`4527efc0 00007ff8`d7e6d5ef     sqllang!process_commands_internal+0x4b7
  37. 1d 000000f8`4527f0f0 00007ff8`d4096523     sqllang!process_messages+0x1d6
  38. 1e 000000f8`4527f2d0 00007ff8`d4096e6d     sqldk!SOS_Task::Param::Execute+0x232
  39. 1f 000000f8`4527f8d0 00007ff8`d4096c75     sqldk!SOS_Scheduler::RunTask+0xa5
  40. 20 000000f8`4527f940 00007ff8`d40bb160     sqldk!SOS_Scheduler::ProcessTasks+0x39d
  41. 21 000000f8`4527fa60 00007ff8`d40baa5b     sqldk!SchedulerManager::WorkerEntryPoint+0x2a1
  42. 22 000000f8`4527fb30 00007ff8`d40bafa4     sqldk!SystemThreadDispatcher::ProcessWorker+0x3ed
  43. 23 000000f8`4527fe30 00007ff8`f6d86fd4     sqldk!SchedulerManager::ThreadEntryPoint+0x3b5
  44. 24 000000f8`4527ff20 00007ff8`f865cec1     KERNEL32!BaseThreadInitThunk+0x14
  45. 25 000000f8`4527ff50 00000000`00000000     ntdll!RtlUserThreadStart+0x21
复制代码
从线程栈上看，有 SQLSERVER 核心的 Scheduler ，Task 以及 命令分析器，查询优化器，查询执行器 等各种核心元素，后续再慢慢研究吧。
三： 总结

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

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！



欢迎光临 ToB企服应用市场:ToB评测及商务社交产业平台 (https://dis.qidao123.com/) Powered by Discuz! X3.4