一次SQL調優 聊一聊 SQLSERVER 資料頁

一線碼農發表於2022-12-30

一:背景

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 的查詢效能。

相關文章