Início / dba / Perguntas / 274690
Accepted
Arun Gopinath
Asked: 2020-09-01 01:48:20 +0800 CST

Escalação de bloqueio e discrepância de contagem no evento estendido lock_acquired

  • 772

Estou tentando entender por que há uma discrepância na contagem de bloqueios sys.dm_tran_lockse no sqlserver.lock_acquiredevento estendido em certos casos. Aqui está meu script de reprodução, estou usando o StackOverflow2013banco de dados no SQL Server 2019 RTM, nível de compatibilidade 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);

Em seguida, atualizo as estatísticas da tabela com uma grande contagem de linhas falsas

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

Então eu abro uma transação e atualizo Scorepara Reputação, digamos56

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;

Recebo um monte de bloqueios de página (apesar de ter um índice de reputação). Eu estou supondo que as estimativas ruins realmente fizeram um número no otimizador lá.

page_locks

Eu também verifiquei usando sp_whoisactivee também retorna as mesmas informações.

<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>

Enquanto isso, eu também tenho um evento estendido acontecendo sqlserver.lock_acquiredseparadamente. Quando olho para os dados agrupados, vejo 8066 bloqueios de página em vez dos 6159 iniciais

extended_event_lock_count

Definitivamente, não vejo um escalonamento de bloqueio (verificado usando sqlserver.lock_escalationo evento), então acho que minha pergunta é por que o evento estendido está mostrando uma discrepância com um número maior de contagem de bloqueio?

sql-server locking

1 respostas

  1. Best Answer

    O XE está relatando um bloqueio de página sendo adquirido cada vez que uma linha é atualizada (um evento para cada uma das 8.066 linhas afetadas pela atualização). No entanto, essas linhas são armazenadas apenas em 6.159 páginas exclusivas, o que explica a discrepância.

    Não tenho o StackOverflow2013 nesta máquina, mas tenho uma experiência semelhante com o SO2010:

    • 1368 linhas atualizadas (e muitos XEvents disparados)
    • 958 bloqueios de página

    Você pode ver as mesmas páginas sendo bloqueadas repetidamente na saída do XE se classificar por resource_0:

    captura de tela da saída XE no SSMS

    Usando DBCC PAGE:

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

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

    Eu posso ver que a página 180020 tem 163 registros nela ( 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

    E que 3 deles correspondem aos critérios de atualização (colei a saída no notepad ++ e procurei por "Reputação = 56"):

    captura de tela mostrando 3 matemática

    Aqui está a primeira partida, como exemplo:

    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

    Acredito que esse comportamento seja devido à natureza em pipeline dos planos de execução e à maneira como esse XE específico é implementado.

    • 6

relate perguntas