Se utilizzi il rapporto sui processi bloccati o raccogli periodicamente i grafici dei deadlock forniti da SQL Server, incontrerai cose come questa:
waitresource="PAGINA: 6:3:70133"
waitresource=“KEY: 6:72057594041991168 (ce52f92a058c)“
A volte, ci saranno più informazioni in quel gigantesco XML che studi (i grafici di deadlock contengono un elenco di risorse che ti aiutano a scoprire i nomi dell'oggetto e dell'indice), ma non sempre.
Questo testo ti aiuterà a decifrarli.
Tutte le informazioni che sono qui sono su Internet in vari posti, sono semplicemente molto distribuite! Voglio mettere tutto insieme, da DBCC PAGE a hobt_id alle funzioni %%physloc%% e %%lockres%% non documentate.
Innanzitutto, parliamo delle attese sui blocchi PAGE, quindi passiamo ai blocchi KEY.
1) waitresource="PAGE: 6:3:70133" = Database_Id: FileId: PageNumber
Se la tua richiesta è in attesa di un blocco PAGE, SQL Server ti fornirà l'indirizzo di quella pagina.
Scomponendo "PAGE: 6:3:70133" otteniamo:
- ID_database = 6
- id_file_dati = 3
- numero_pagina = 70133
1.1) Decrittografare database_id
Trova il nome del database usando la query:
SELECT
name
FROM sys.databases
WHERE database_id=6;
GOÈ pubblico sul mio SQL Server.
1.2) Cercare il nome del file di dati - se sei interessato
Utilizzeremo data_file_id nel passaggio successivo per trovare il nome della tabella. Puoi semplicemente saltare al passaggio successivo, ma se sei interessato al nome del file, puoi trovarlo eseguendo una query nel contesto del database trovato, sostituendo data_file_id in questa query:
USE WideWorldImporters;
GO
SELECT
name,
physical_name
FROM sys.database_files
WHERE file_id = 3;
GONel database WideWorldImporters questo è un file chiamato WWI_UserData e l'ho ripristinato in C:MSSQLDATAWideWorldImporters_UserData.ndf. (Oops, mi hai sorpreso a inserire file nell'unità di sistema! No! È imbarazzante).
1.3) Ottieni il nome dell'oggetto dalla PAGINA DBCC
Ora sappiamo che la pagina #70133 nel file di dati 3 appartiene al database WorldWideImporters. Possiamo esaminare il contenuto di questa pagina utilizzando la PAGINA DBCC non documentata e il flag di traccia 3604.
Nota: preferisco usare DBCC PAGE su una copia ripristinata da qualche parte su un altro server, perché è roba non documentata. In alcuni casi, lei (ca. traduttore - il link, purtroppo, non porta da nessuna parte, ma a giudicare dall'url si tratta di indici filtrati).
/* This trace flag makes DBCC PAGE output go to our Messages tab
instead of the SQL Server Error Log file */
DBCC TRACEON (3604);
GO
/* DBCC PAGE (DatabaseName, FileNumber, PageNumber, DumpStyle)*/
DBCC PAGE ('WideWorldImporters',3,70133,2);
GO
Scorrendo fino ai risultati, puoi trovare object_id e index_id.
Quasi fatto! Ora puoi trovare i nomi delle tabelle e degli indici con una query:
USE WideWorldImporters;
GO
SELECT
sc.name as schema_name,
so.name as object_name,
si.name as index_name
FROM sys.objects as so
JOIN sys.indexes as si on
so.object_id=si.object_id
JOIN sys.schemas AS sc on
so.schema_id=sc.schema_id
WHERE
so.object_id = 94623380
and si.index_id = 1;
GOE qui vediamo che l'attesa sul blocco era sull'indice PK_Sales_OrderLines della tabella Sales.OrderLines.
Nota: in SQL Server 2014 e versioni successive, il nome dell'oggetto può essere trovato anche utilizzando il DMO non documentato sys.dm_db_database_page_allocations. Ma devi interrogare ogni pagina nel database, il che non sembra molto interessante per database di grandi dimensioni, quindi ho usato DBCC PAGE.
1.4) È possibile vedere i dati della pagina che è stata bloccata?
Nuuu, sì. Ma... sei sicuro di averne davvero bisogno?
È lento anche su piccoli tavoli. Ma è piuttosto figo, quindi visto che hai letto fin qui... parliamo di %%physloc%%!
%%physloc%% è un pezzo di magia non documentato che restituisce un ID fisico per ogni voce. Puoi usare .
Ora che sappiamo che vogliamo bloccare la pagina in Sales.OrderLines, possiamo esaminare tutti i dati in questa tabella, archiviati nel file di dati n. 3 a pagina n. 70133, con la seguente query:
Use WideWorldImporters;
GO
SELECT
sys.fn_PhysLocFormatter (%%physloc%%),
*
FROM Sales.OrderLines (NOLOCK)
WHERE sys.fn_PhysLocFormatter (%%physloc%%) like '(3:70133%'
GOCome ho detto, è lento anche su tavoli piccoli. Ho aggiunto NOLOCK alla richiesta perché non abbiamo ancora garanzie che i dati che vogliamo esaminare siano esattamente gli stessi di quando è stato scoperto il blocco, quindi possiamo tranquillamente eseguire letture sporche.
Ma, evviva, la query mi restituisce proprio le 25 righe per le quali la nostra query ha combattuto.
Basta con i blocchi PAGINA. E se stiamo aspettando un blocco KEY?
2) waitresource="KEY: 6:72057594041991168 (ce52f92a058c)" = Database_Id, HOBT_Id (hash magico che può essere decifrato con %%lockres%% se lo desideri)
Se la tua query tenta di bloccare una voce di indice e viene bloccata da sola, ottieni un tipo di indirizzo completamente diverso.
Rompendo "6:72057594041991168 (ce52f92a058c)" in parti, otteniamo:
- ID_database = 6
- hobt_id = 72057594041991168
- hash magico = (ce52f92a058c)
2.1) Decrittografare database_id
Funziona esattamente come nell'esempio sopra! Troviamo il nome del database usando la query:
SELECT
name
FROM sys.databases
WHERE database_id=6;
GONel mio caso è lo stesso .
2.2) Decrittografare hobt_id
Nel contesto del database trovato, è necessario interrogare sys.partitions con un paio di join che aiuteranno a determinare i nomi della tabella e dell'indice ...
USE WideWorldImporters;
GO
SELECT
sc.name as schema_name,
so.name as object_name,
si.name as index_name
FROM sys.partitions AS p
JOIN sys.objects as so on
p.object_id=so.object_id
JOIN sys.indexes as si on
p.index_id=si.index_id and
p.object_id=si.object_id
JOIN sys.schemas AS sc on
so.schema_id=sc.schema_id
WHERE hobt_id = 72057594041991168;
GOMi dice che la richiesta era in attesa sul blocco Application.Countries utilizzando l'indice PK_Application_Countries.
2.3) Ora un po' di magia %%lockres%% - se vuoi scoprire quale voce è stata bloccata
Se voglio davvero sapere su quale riga era necessario il blocco, posso scoprirlo interrogando la tabella stessa. Possiamo usare la funzione %%lockres%% non documentata per trovare una voce che corrisponda all'hash magico.
Tieni presente che questa query eseguirà la scansione dell'intera tabella e su tabelle di grandi dimensioni potrebbe non essere affatto divertente:
SELECT
*
FROM Application.Countries (NOLOCK)
WHERE %%lockres%% = '(ce52f92a058c)';
GO
Ho aggiunto NOLOCK () perché i blocchi possono essere un problema. Vogliamo solo vedere cosa c'è adesso e non cosa c'era quando è iniziata la transazione: non credo che la coerenza dei dati sia importante per noi.
Voilà, il record per cui abbiamo lottato!
Ringraziamenti e ulteriori letture
Non ricordo chi abbia descritto per primo molte di queste cose, ma ecco due post sulle cose meno documentate che potrebbero piacerti:
- Il post di Paul Randal su (poiché abbiamo i nostri dati nel primo esempio)
- Domanda su StackOverflow su (come abbiamo trovato i dati nel secondo esempio). Una delle risposte porta al post .
Fonte: habr.com