一次SQL調優 聊一聊 SQLSERVER 數據頁

来源:https://www.cnblogs.com/huangxincheng/archive/2022/12/30/17015643.html
-Advertisement-
Play Games

一：背景 1.講故事 最近給一位朋友做 SQL 慢語句 優化，花了些時間調優，遺憾的是 SQLSERVER 非源碼公開，玩起來不是那麼順利，不過從這次經歷中我覺得明年的一個重大任務就是好好研究一下它，爭取在 SQLSERVER 性能優化上做一些成績，哈哈！ 個人覺得要想深入研究 SQLSERVER， ...


一：背景

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


您的分享是我們最大的動力!

-Advertisement-
Play Games
更多相關文章
  • 從Java開源項目中總結出的常見壞實踐（Bad Practice）
    一些開源項目包含了各種編程的最佳實踐供人參考學習和借鑒。但是也有一些開源項目雖然初衷是好的。但是包含了一些代碼的壞實踐。特別是對於一部分剛入行的大學生來說，可能會給到一些錯誤的示範。於是在此列舉一些項目中的壞實踐。 1.方法的用意判斷是與否卻返回字元串的“0”或者“1” 如果一個方法明確返回是與否這 ...
  • Java方法引用
    體驗方法引用 通過方法引用，來使用已經存在的方案。 定義一個介面，裡面有一個抽象方法： public interface Printable { void printString(String s); } 定義一個測試類： public class PrintDemo { public static ...
  • .NET Core 實現後臺任務（定時任務)
    IHostedService和BackgroundService 前言 平時寫代碼的時候，大家多多少少都會遇到定時任務的要求，今天介紹兩種定時任務的寫法，嚴格來說其實是一種。 相對來說比較粗糙，請多多指教。 方法一 IHostedService 簡介 ######## IHostedService ...
  • 學習.NET MAUI Blazor（三）、創建.NET MAUI Blazor應用並使用AntDesignBlazor
    擴展方法名：Filter 支持參數：實體類、JObject 擴展代碼： //白色風車 public static class EntityFrameworkCoreExtensions { private static DbCommand CreateCommand(DatabaseFacade f ...
  • 主題 1 The Shell
    主題 1 The Shell 課程概覽與 shell · the missing semester of your cs education (missing-semester-cn.github.io) Shell是什麼？ 一旦你想脫離可視化界面讓你做的，然後做點別的事情，那麼Shell將是你和計 ...
  • 痞子衡嵌入式：Farewell, 我的寫博故事2016-2019
    -- 題圖：蘇州天平山楓葉 現在是 2022 年末，痞子衡又要起筆博文年終總結了，看著 2020 年之前的博文總結缺失，始終覺得缺憾，所以寫下此篇 2016 - 2019 總結合輯。2016 年之前，痞子衡也發表過一些文章，不過沒有持續性，那時候更多是以個人筆記形式留在硬碟里。2016 年是痞子衡正 ...
  • shell編程（超詳細）
    Shell變數 變數是任何一種編程語言都必不可少的組成部分，變數用來存放各種數據。腳本語言在定義變數時通常不需要指明類型，直接賦值就可以，Shell 變數也遵循這個規則。 在 Bash shell 中，每一個變數的值都是字元串，無論你給變數賦值時有沒有使用引號，值都會以字元串的形式存儲。 這意味著， ...
  • RabbitMQ學習筆記02：Hello World!
    參考資料：RabbitMQ tutorial - "Hello world!" — RabbitMQ 前言 RabbitMQ是一個中間人，它接受和轉發消息。我們可以把它想象成一個郵局：當你把郵件投入郵箱的時候，你可以確信它最終會被投遞到收件人的手中。RabbitMQ就是那個郵箱、郵局和郵差。區別就在 ...
一周排行
    -Advertisement-
    Play Games
  • C#TMS系統學習（BaseCity頁面）
    C#TMS系統代碼-基礎頁面BaseCity學習 本人純新手，剛進公司跟領導報道，我說我是java全棧，他問我會不會C#，我說大學學過，他說這個TMS系統就給你來管了。外包已經把代碼給我了，這幾天先把增刪改查的代碼背一下，說不定後面就要趕鴨子上架了 Service頁面 //using => impo ...
  • C# - 委托與事件
    委托與事件 委托 委托的定義 委托是C#中的一種類型，用於存儲對方法的引用。它允許將方法作為參數傳遞給其他方法，實現回調、事件處理和動態調用等功能。通俗來講，就是委托包含方法的記憶體地址，方法匹配與委托相同的簽名，因此通過使用正確的參數類型來調用方法。 委托的特性 引用方法：委托允許存儲對方法的引用， ...
  • 解析 ABP vNext 依賴註入實現【屬性註入】的原理
    前言 這幾天閑來沒事看看ABP vNext的文檔和源碼，關於關於依賴註入(屬性註入)這塊兒產生了興趣。 我們都知道。Volo.ABP 依賴註入容器使用了第三方組件Autofac實現的。有三種註入方式，構造函數註入和方法註入和屬性註入。 ABP的屬性註入原則參考如下： 這時候我就開始疑惑了，因為我知道 ...
  • C#TMS系統學習（ShippingNotice頁面）
    C#TMS系統代碼-業務頁面ShippingNotice學習 學一個業務頁面，ok，領導開完會就被裁掉了，很突然啊，他收拾東西的時候我還以為他要旅游提前請假了，還在尋思為什麼回家連自己買的幾箱飲料都要叫跑腿帶走，怕被偷嗎？還好我在他開會之前拿了兩瓶芬達 感覺感覺前面的BaseCity差不太多，這邊的 ...
  • C#動態查詢：巧用Expression組合多條件表達式
    概述：在C#中，通過`Expression`類、`AndAlso`和`OrElse`方法可組合兩個`Expression<Func<T, bool>>`，實現多條件動態查詢。通過創建表達式樹，可輕鬆構建複雜的查詢條件。 在C#中，可以使用AndAlso和OrElse方法組合兩個Expression< ...
  • 在Biwen.QuickApi中整合一個極簡的發佈訂閱(事件匯流排)
    閑來無聊在我的Biwen.QuickApi中實現一下極簡的事件匯流排,其實代碼還是蠻簡單的,對於初學者可能有些幫助 就貼出來,有什麼不足的地方也歡迎板磚交流~ 首先定義一個事件約定的空介面 public interface IEvent{} 然後定義事件訂閱者介面 public interface I ...
  • WPF 穩定的全屏化視窗方法
    1. 案例 成某三甲醫預約系統， 該項目在2024年初進行上線測試，在正常運行了兩天後，業務系統報錯：The connection pool has been exhausted, either raise MaxPoolSize (currently 800) or Timeout (curren ...
  • 在 WPF 中集成 ASP.NET Core 和 WebView2 用於集成 SPA 應用
    背景 我們有些工具在 Web 版中已經有了很好的實踐，而在 WPF 中重新開發也是一種費時費力的操作，那麼直接集成則是最省事省力的方法了。 思路解釋 為什麼要使用 WPF？莫問為什麼，老 C# 開發的堅持，另外因為 Windows 上已經裝了 Webview2/edge 整體打包比 electron ...
  • C#事件
    EDP是一套集組織架構，許可權框架【功能許可權，操作許可權，數據訪問許可權，WebApi許可權】，自動化日誌，動態Interface，WebApi管理等基礎功能於一體的，基於.net的企業應用開發框架。通過友好的編碼方式實現數據行、列許可權的管控。 ...
  • .Net8.0 Blazor Hybird 桌面端 (WPF/Winform) 發佈到 Win7+
    .Net8.0 Blazor Hybird 桌面端 (WPF/Winform) 實測可以完整運行在 win7sp1/win10/win11. 如果用其他工具打包,還可以運行在mac/linux下, 傳送門BlazorHybrid 發佈為無依賴包方式 安裝 WebView2Runtime 1.57 M ...
所有分類