lock_acquired 擴展事件中的鎖升級和計數差異

我試圖理解為什麼在某些情況下鎖定計數sys.dm_tran_locks和擴展事件存在差異。sqlserver.lock_acquired這是我的複制腳本，我StackOverflow2013在 SQL Server 2019 RTM 上使用數據庫，兼容級別 150。

/* Initial Setup */
IF OBJECT_ID('dbo.HighQuestionScores', 'U') IS NOT NULL 
DROP TABLE dbo.HighQuestionScores; 

CREATE TABLE dbo.HighQuestionScores 
(
   Id INT PRIMARY KEY CLUSTERED,
   DisplayName NVARCHAR(40) NOT NULL,
   Reputation BIGINT NOT NULL,
   Score BIGINT
)

INSERT dbo.HighQuestionScores  
       (Id, DisplayName, Reputation, Score)
SELECT u.Id, 
      u.DisplayName,
      u.Reputation, 
      NULL
FROM dbo.Users AS u;

CREATE INDEX ix_HighQuestionScores_Reputation ON dbo.HighQuestionScores  (Reputation);

接下來，我使用大量假行數更新表統計資訊

/* Chaotic Evil. */
UPDATE STATISTICS dbo.HighQuestionScores WITH ROWCOUNT = 99999999999999;
DBCC FREEPROCCACHE WITH NO_INFOMSGS;

然後我打開一個事務並更新Score聲譽，比如說56

BEGIN TRAN;
UPDATE dbo.HighQuestionScores  
SET Score = 1
WHERE Reputation = 56 /* 8066 records */
AND 1 = (SELECT 1);
/* Source: https://www.erikdarlingdata.com/sql-server/helpers-views-and-functions-i-use-in-presentations/ Thanks, Erik */
SELECT *
FROM dbo.WhatsUpLocks(@@SPID) AS wul 
WHERE wul.locked_object = N'HighQuestionScores'
ROLLBACK;

我得到了一堆頁面鎖（儘管有一個關於聲譽的索引）。我猜糟糕的估計確實在那裡的優化器上做了一個數字。

我還仔細檢查了使用sp_whoisactive，它也返回了相同的資訊。

<Object name="HighQuestionScores" schema_name="dbo">
 <Locks>
   <Lock resource_type="OBJECT" request_mode="IX" request_status="GRANT" request_count="1" />
   <Lock resource_type="PAGE" page_type="*" index_name="PK__HighQues__3214EC072EE1ADBA" request_mode="X" request_status="GRANT" request_count="6159" />
 </Locks>
</Object>

同時，我還有一個sqlserver.lock_acquired單獨的擴展活動。當我查看分組數據時，我看到8066頁鎖而不是初始6159

我絕對看不到鎖升級（使用sqlserver.lock_escalation事件驗證），所以我想我的問題是為什麼擴展事件顯示與更高數量的鎖計數存在差異？

XE 報告每次更新行時獲取頁鎖（受更新影響的 8066 行中的每一行都有一個事件）。但是，這些行僅儲存在 6159 個唯一頁面上，這解釋了差異。

我在這台機器上沒有 StackOverflow2013，但使用 SO2010 獲得了類似的體驗：

• 更新了 1368 行（並且觸發了許多 XEvent）
• 958頁鎖

如果您按以下方式排序，則可以在 XE 輸出中看到相同的頁面被重複鎖定resource_0：

使用DBCC PAGE：

DBCC TRACEON (3604); -- needed for the next one to work
GO

DBCC PAGE (StackOverflow2010, 1, 180020, 3);
GO

我可以看到頁面 180020 上有 163 條記錄（m_slotCnt = 163）：

Page @0x000002C278C62000

m_pageId = (1:180020)               m_headerVersion = 1                 m_type = 1
m_typeFlagBits = 0x0                m_level = 0                         m_flagBits = 0x0
m_objId (AllocUnitId.idObj) = 174   m_indexId (AllocUnitId.idInd) = 256 
Metadata: AllocUnitId = 72057594049331200                                
Metadata: PartitionId = 72057594044350464                                Metadata: IndexId = 1
Metadata: ObjectId = 1525580473     m_prevPage = (1:180019)             m_nextPage = (1:180021)
pminlen = 24                        m_slotCnt = 163                     m_freeCnt = 31
m_freeData = 7835                   m_reservedCnt = 0                   m_lsn = (203:19986:617)
m_xactReserved = 0                  m_xdesId = (0:0)                    m_ghostRecCnt = 0
m_tornBits = 1894114769             DB Frag ID = 1                

其中 3 個符合更新條件（我將輸出粘貼到 notepad++ 並蒐索“Reputation = 56”）：

這是第一場比賽，例如：

Record Type = PRIMARY_RECORD        Record Attributes =  NULL_BITMAP VARIABLE_COLUMNS
Record Size = 39                    
Memory Dump @0x000000EDB2B7821D

0000000000000000:   30001800 86540000 38000000 00000000 6041c2e4  0...T..8.......`AÂä
0000000000000014:   c3020000 04000801 00270045 00720069 006300    Ã........'.E.r.i.c.

Slot 9 Column 1 Offset 0x4 Length 4 Length (physical) 4

Id = 21638                          

Slot 9 Column 2 Offset 0x1f Length 8 Length (physical) 8

DisplayName = Eric                  

Slot 9 Column 3 Offset 0x8 Length 8 Length (physical) 8

Reputation = 56                     

Slot 9 Column 4 Offset 0x0 Length 0 Length (physical) 0

Score = [NULL]                      

Slot 9 Offset 0x0 Length 0 Length (physical) 0

KeyHashValue = (e1caffa60313)       
Slot 10 Offset 0x244 Length 43

我相信這種行為是由於執行計劃的流水線性質，以及這種特定 XE 的實現方式。

引用自：https://dba.stackexchange.com/questions/274690