我在字节当主管：百次面试结果，总结一个刷掉99%求职者的问题！

我一个在大厂当主管的朋友&＃xff0c;跟我说&＃xff1a;

“现在招性能测试太难了&＃xff0c;当然不是说没人干&＃xff0c;一开招聘信息就能收到一大把简历&＃xff0c;其中不乏学历亮眼、背景出色、简历里各种高并发、大流量的项目经验的人才。

问题在于&＃xff0c;当你提出讲一个项目中遇到的性能问题&＃xff0c;以及如何分析定位时&＃xff0c;却发现绝大多数根本没有遇到过性能问题。

甚至面试了几个高级性能测试工程师&＃xff0c;还是发现一旦涉及到性能分析调优&＃xff0c;就开始左顾右盼、答非所问。”

“问题的结症在于&＃xff0c;大部分面试者参与的性能测试项目就不多&＃xff0c;企业又希望招有经验的测试&＃xff0c;这本身就存在矛盾&＃xff01;”

我说&＃xff0c;你说的情况谁不知道&＃xff1f;你不如讲点实际的&＃xff0c;讲点经验&＃xff0c;性能调优你说怎么弄&＃xff1f;

朋友思考了一会说道&＃xff1a;“那不如正好聊聊我手上的这个项目&＃xff0c;聊聊SQLSERVER数据页&＃xff01;”

“什么是数据页 ”

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

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

CREATEDATABASE MyTestDBGOUSE MyTestDB;GOIF OBJECT_ID(&＃39;person&＃39;) IS NOT NULLDROPTABLE person;CREATETABLE person(idINTIDENTITY,nameCHAR(5));GOINSERT INTO dbo.person( name ) VALUES (&＃39;john&＃39;);INSERTINTO dbo.person( name ) VALUES (&＃39;mary&＃39;);

“寻找数据所在的数据页”

一般来说&＃xff0c;对大块资源或者数据进行高效管理都会按照一定粒度来划分刚才图中也说了 mdf 是由无数个 数据页 拼出来的&＃xff0c;那如何找到 person 表所在的数据页呢&＃xff1f;其实微软提供了一个 dbcc ind 命令可以帮我们洞察出来&＃xff0c;同时记得开始3604标记&＃xff0c;让输出显示在控制台上&＃xff0c;而不是默认的错误日志中&＃xff0c;这个命令具体怎么用&＃xff0c;大家可以查看官方文档。

DBCCTRACEON(3604)DBCCIND(MyTestDB,person, -1)

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

0:090> ? 0n280 * 0n8192Evaluateexpression: 2293760 &＃61; 00000000&＃96;00230000

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

ALTERDATABASE MyTestDB SETOFFLINEALTERDATABASE MyTestDB SETONLINE

从WinHex 上看&＃xff0c;果然是在偏移为 0x00230000 这个数据页上。

“如何从内存中看到数据页”

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

DBCCTRACEON(3604)DBCCPAGE(MyTestDB,1,280,2)

输出结果如下&＃xff1a;

DBCC执行完毕。如果 DBCC 输出了错误信息&＃xff0c;请与系统管理员联系。PAGE: (1:280)BUFFER:BUF&＃64;0x000002B41104F480bpage &＃61; 0x000002B3F0632000 bPmmpage &＃61; 0x0000000000000000 bsort_r_nextbP &＃61; 0x000002B41104F3D0bsort_r_prevbP &＃61; 0x0000000000000000 bhash &＃61; 0x0000000000000000 bpageno &＃61; (1:280)bpart &＃61; 1 ckptGen &＃61; 0x0000000000000000 bDirtyRefCount &＃61; 0bstat &＃61; 0x9 breferences &＃61; 0 berrcode &＃61; 0bUse1 &＃61; 12454 bstat2 &＃61; 0x0 blog &＃61; 0x15ab215absampleCount &＃61; 0 bIoCount &＃61; 0 resPoolId &＃61; 0bcputicks &＃61; 0 bReadMicroSec &＃61; 182 bDirtyContext &＃61; 0x0000000000000000bDbPageBroker &＃61; 0x0000000000000000 bdbid &＃61; 10 bpru &＃61; 0x000002B3FA708040PAGE HEADER:Page&＃64;0x000002B3F0632000m_pageId &＃61; (1:280) m_headerVersion &＃61; 1 m_type &＃61; 1m_typeFlagBits &＃61; 0x0 m_level &＃61; 0 m_flagBits &＃61; 0x8200m_objId (AllocUnitId.idObj) &＃61; 179 m_indexId (AllocUnitId.idInd) &＃61; 256 Metadata: AllocUnitId &＃61; 72057594049658880 Metadata: PartitionId &＃61; 72057594043170816 Metadata: IndexId &＃61; 0Metadata: ObjectId &＃61; 581577110 m_prevPage &＃61; (0:0) m_nextPage &＃61; (0:0)pminlen &＃61; 13 m_slotCnt &＃61; 2 m_freeCnt &＃61; 8060m_freeData &＃61; 128 m_reservedCnt &＃61; 0 m_lsn &＃61; (37:584:3)m_xactReserved &＃61; 0 m_xdesId &＃61; (0:0) m_ghostRecCnt &＃61; 0m_tornBits &＃61; -116446693 DB Frag ID &＃61; 1 Allocation StatusGAM(1:2) &＃61; ALLOCATED SGAM (1:3) &＃61; NOT ALLOCATED PFS (1:1) &＃61; 0x41 ALLOCATED 50_PCT_FULLDIFF(1:6) &＃61; CHANGED ML (1:7) &＃61; NOT MIN_LOGGED DATA:MemoryDump &＃64;0x000000F840DF8000000000F840DF8000: 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.&＃96;.OFFSETTABLE:Row- Offset 1 (0x1) - 112 (0x70) 0 (0x0) - 96 (0x60) DBCC执行完毕。如果 DBCC 输出了错误信息&＃xff0c;请与系统管理员联系。Completiontime: 2022-12-30T17:48:20.5466040&＃43;08:00

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

0:116> dp 000000F840DF8064000000f8&＃96;40df8064 6e686f6a&＃96;00000001000d0010&＃96;00000220000000f8&＃96;40df8074 7972616d&＃96;00000002 21210000&＃96;00000220000000f8&＃96;40df8084 21212121&＃96;2121212121212121&＃96;21212121000000f8&＃96;40df8094 21212121&＃96;21212121 21212121&＃96;21212121000000f8&＃96;40df80a4 21212121&＃96;2121212121212121&＃96;21212121000000f8&＃96;40df80b4 21212121&＃96;21212121 21212121&＃96;21212121000000f8&＃96;40df80c4 21212121&＃96;2121212121212121&＃96;21212121000000f8&＃96;40df80d4 21212121&＃96;21212121 21212121&＃96;21212121

“sql 请求源码研究”

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

0:104> ba r4 000000f840df8064&＃43;0x40:104> gBreakpoint 0 hitsqlmin!BTreeMgr::GetHPageIdWithKey&＃43;0x4a:00007ff8&＃96;d4ea121a 48894c2478 mov qword ptr [rsp&＃43;78h],rcx ss:000000f8&＃96;45278028&＃61;00000248000000250:102> k # Child-SP RetAddr Call Site00 000000f8&＃96;45277fb0 00007ff8&＃96;d4ea0b59 sqlmin!BTreeMgr::GetHPageIdWithKey&＃43;0x4a01000000f8&＃96;45278450 00007ff8&＃96;d4ea08b7 sqlmin!IndexPageManager::GetPageWithKey&＃43;0x11902000000f8&＃96;45278d20 00007ff8&＃96;d4eb22d1 sqlmin!GetRowForKeyValue&＃43;0x20303000000f8&＃96;45279880 00007ff8&＃96;d4eb2a39 sqlmin!IndexDataSetSession::GetRowByKeyValue&＃43;0x14104000000f8&＃96;45279a70 00007ff8&＃96;d4eb279b sqlmin!IndexDataSetSession::FetchRowByKeyValueInternal&＃43;0x23005000000f8&＃96;45279ed0 00007ff8&＃96;d4eb2883 sqlmin!RowsetNewSS::FetchRowByKeyValueInternal&＃43;0x43706000000f8&＃96;4527a000 00007ff8&＃96;d4eaadab sqlmin!RowsetNewSS::FetchRowByKeyValue&＃43;0x9607000000f8&＃96;4527a050 00007ff8&＃96;d4f93d60 sqlmin!CMEDScan::StartSearch&＃43;0x4f808 000000f8&＃96;4527a170 00007ff8&＃96;d4f93f3a sqlmin!CMEDCatYukonObject::GetTemporalCurrentTableId&＃43;0x10e09000000f8&＃96;4527a380 00007ff8&＃96;d801f0d1 sqlmin!CMEDProxyRelation::GetTemporalCurrentTableId&＃43;0x7a0a000000f8&＃96;4527a3c0 00007ff8&＃96;d801dfb8 sqllang!CAlgTableMetadata::FPartialBind&＃43;0xb580b000000f8&＃96;4527a580 00007ff8&＃96;d80394b3 sqllang!CAlgTableMetadata::Bind&＃43;0x3170c000000f8&＃96;4527a620 00007ff8&＃96;d800415d sqllang!CRelOp_Get::BindTree&＃43;0x78f0d000000f8&＃96;4527a890 00007ff8&＃96;d80418a1 sqllang!COptExpr::BindTree&＃43;0x850e000000f8&＃96;4527a8c0 00007ff8&＃96;d800415d sqllang!CRelOp_FromList::BindTree&＃43;0x310f000000f8&＃96;4527a920 00007ff8&＃96;d802c2a3 sqllang!COptExpr::BindTree&＃43;0x8510000000f8&＃96;4527a950 00007ff8&＃96;d800415d sqllang!CRelOp_QuerySpec::BindTree&＃43;0x2e811000000f8&＃96;4527aa60 00007ff8&＃96;d80042dd sqllang!COptExpr::BindTree&＃43;0x8512000000f8&＃96;4527aa90 00007ff8&＃96;d800415d sqllang!CRelOp_SelectQuery::BindTree&＃43;0x48913000000f8&＃96;4527ab80 00007ff8&＃96;d8003f23 sqllang!COptExpr::BindTree&＃43;0x8514000000f8&＃96;4527abb0 00007ff8&＃96;d8004e47 sqllang!CRelOp_Query::FAlgebrizeQuery&＃43;0x4bd15 000000f8&＃96;4527ae30 00007ff8&＃96;d7ff5576 sqllang!CProchdr::FNormQuery&＃43;0x8f16 000000f8&＃96;4527ae70 00007ff8&＃96;d7ff4a79 sqllang!CProchdr::FNormalizeStep&＃43;0x5bd17 000000f8&＃96;4527b4b0 00007ff8&＃96;d7ff5124 sqllang!CSQLSource::FCompile&＃43;0xea518000000f8&＃96;4527e1b0 00007ff8&＃96;d7e659c3 sqllang!CSQLSource::FCompWrapper&＃43;0xcb19000000f8&＃96;4527e280 00007ff8&＃96;d7e6387a sqllang!CSQLSource::Transform&＃43;0x7211a000000f8&＃96;4527e3e0 00007ff8&＃96;d7e6e67b sqllang!CSQLSource::Execute&＃43;0x4fa1b 000000f8&＃96;4527e8c0 00007ff8&＃96;d7e6d815 sqllang!process_request&＃43;0xca61c000000f8&＃96;4527efc0 00007ff8&＃96;d7e6d5ef sqllang!process_commands_internal&＃43;0x4b71d000000f8&＃96;4527f0f0 00007ff8&＃96;d4096523 sqllang!process_messages&＃43;0x1d61e 000000f8&＃96;4527f2d0 00007ff8&＃96;d4096e6d sqldk!SOS_Task::Param::Execute&＃43;0x2321f000000f8&＃96;4527f8d0 00007ff8&＃96;d4096c75 sqldk!SOS_Scheduler::RunTask&＃43;0xa520000000f8&＃96;4527f940 00007ff8&＃96;d40bb160 sqldk!SOS_Scheduler::ProcessTasks&＃43;0x39d21 000000f8&＃96;4527fa60 00007ff8&＃96;d40baa5b sqldk!SchedulerManager::WorkerEntryPoint&＃43;0x2a122000000f8&＃96;4527fb30 00007ff8&＃96;d40bafa4 sqldk!SystemThreadDispatcher::ProcessWorker&＃43;0x3ed23 000000f8&＃96;4527fe30 00007ff8&＃96;f6d86fd4 sqldk!SchedulerManager::ThreadEntryPoint&＃43;0x3b524000000f8&＃96;4527ff20 00007ff8&＃96;f865cec1 KERNEL32!BaseThreadInitThunk&＃43;0x1425000000f8&＃96;4527ff50 00000000&＃96;00000000 ntdll!RtlUserThreadStart&＃43;0x21

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

“总结”

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