Se utilizzi il report sui blocchi (blocked process report) o raccogli le informazioni sui deadlock fornite da SQL Server, periodicamente ti imbatterai in cose come queste:
waitresource="PAGE: 6:3:70133"
waitresource="KEY: 6:72057594041991168 (ce52f92a058c)"
A volte, nel grande XML che stai esaminando, ci sarà più informazione (i grafi dei deadlock contengono un elenco di risorse che aiutano ad identificare i nomi degli oggetti e degli indici), ma non sempre.
Questo testo ti aiuterà a decifrarli.
Tutte le informazioni qui presenti sono disponibili su Internet in posti diversi, sono semplicemente molto distribuite! Voglio raccogliere tutto insieme — da DBCC PAGE a hobt_id e alle funzioni non documentate %%physloc%% e %%lockres%%.
Iniziamo a parlare degli attese sui blocchi PAGE, e poi passeremo ai blocchi KEY.
1) waitresource="PAGE: 6:3:70133" = Database_Id: FileId: PageNumber
Se la tua query sta aspettando su un blocco PAGE, SQL Server ti fornirà l'indirizzo di questa pagina.
Scomponendo "PAGE: 6:3:70133" otteniamo:
- database_id = 6
- data_file_id = 3
- page_number = 70133
1.1) Decifriamo database_id
Troviamo il nome del database utilizzando la query:
SELECT
name
FROM sys.databases
WHERE database_id=6;
GOQuesto è un database pubblico sul mio SQL Server.
1.2) Cerchiamo il nome del file di dati — se sei interessato
Utilizzeremo data_file_id nel passaggio successivo per trovare il nome della tabella. Puoi semplicemente passare al passaggio successivo, ma se ti interessa il 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 file è denominato WWI_UserData ed è ripristinato nel mio C:MSSQLDATAWideWorldImporters_UserData.ndf. (Ops, mi hai beccato mentre mettevo i file sul disco di sistema! No! È stato imbarazzante).
1.3) Otteniamo il nome dell'oggetto da DBCC PAGE
Ora sappiamo che la pagina #70133 nel file di dati 3 appartiene al database WorldWideImporters. Possiamo guardare il contenuto di questa pagina utilizzando il non documentato DBCC PAGE e il trace flag 3604.
Nota: Preferisco usare DBCC PAGE su una copia ripristinata da un backup su un altro server, perché si tratta di qualcosa di non documentato. In alcuni casi, esso (nota del traduttore — il link porta a una pagina non valida, ma a giudicare dall’url, si parla 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 i risultati, puoi trovare object_id e index_id.
Quasi pronto! Ora puoi trovare i nomi dello schema e dell'indice con la 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 per il 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 tramite la DMO non documentata sys.dm_db_database_page_allocations. Ma dovrai interrogare ogni pagina nel DB, il che non sembra molto elegante per database di grandi dimensioni, quindi ho utilizzato DBCC PAGE.
1.4) Posso vedere i dati sulla pagina che è stata bloccata?
Beh, sì. Ma… sei sicuro di averne bisogno?
È lento anche su piccole tabelle. Ma sembra un po' interessante, quindi, dato che sei arrivato a questo punto… parliamo di %%physloc%%!
%%physloc%% è un pezzo di magia non documentata che restituisce un identificatore fisico per ciascun record. Puoi usare .
Ora che sappiamo che volevamo applicare un blocco sulla pagina in Sales.OrderLines, possiamo vedere tutti i dati in questa tabella, memorizzati nel file dati #3 sulla pagina #70133, con una query del genere:
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 tavole minuscole. Ho aggiunto NOLOCK alla query perché non abbiamo alcuna garanzia che i dati a cui vogliamo dare un'occhiata siano esattamente quelli che erano nel momento in cui è stato rilevato il blocco — quindi possiamo tranquillamente fare letture sporche.
Ma, ecco, la query mi restituisce quelle 25 righe per cui la nostra query ha lottato
Basta parlare di blocchi PAGE. Cosa succede se stiamo aspettando un blocco KEY?
2) waitresource="KEY: 6:72057594041991168 (ce52f92a058c)" = Database_Id, HOBT_Id (un hash magico che può essere decifrato tramite %%lockres%%, se davvero lo vuoi)
Se la tua query sta cercando di applicare un blocco su un record nell'indice e si trova bloccata, ottieni un tipo completamente diverso di indirizzo.
Dividendo "6:72057594041991168 (ce52f92a058c)" in parti, otteniamo:
- database_id = 6
- hobt_id = 72057594041991168
- hash magico = (ce52f92a058c)
2.1) Decifriamo database_id
Funziona esattamente come nell'esempio sopra! Troviamo il nome del DB usando la query:
SELECT
name
FROM sys.databases
WHERE database_id=6;
GONel mio caso, è sempre lo stesso .
2.2) Decifriamo hobt_id
Nel contesto del DB trovato, è necessario eseguire una query su sys.partitions con una coppia 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 query era in attesa a causa del blocco di Application.Countries, utilizzando l'indice PK_Application_Countries.
2.3) Ora un po' di magia %%lockres%% — se vuoi scoprire quale record è stato bloccato
Se voglio davvero sapere su quale riga fosse necessario il blocco, posso scoprirlo eseguendo una query sulla stessa tabella. Possiamo usare la funzione non documentata %%lockres%% per trovare il record che corrisponde all'hash misterioso.
Tieni presente che questa query eseguirà una 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 diventare un problema. Vogliamo solo dare un'occhiata a cosa c'è adesso, non cosa c'era quando è iniziata la transazione — non penso che la coerenza dei dati ci interessi.
Ecco, il record per cui stavamo lottando!
Riconoscimenti e letture ulteriori
Non ricordo chi abbia descritto per primo molte di queste cose, ma ecco due post su alcune delle meno documentate, che potrebbero piacerti:
- Post di Paul Randal su (come abbiamo trovato i nostri dati nel primo esempio)
- Domanda su StackOverflow riguardo (come abbiamo trovato i dati nel secondo esempio). Una delle risposte porta a un post .
Fonte: habr.com
