Se utilizzi il rapporto sui blocchi (blocked process report) o raccogli grafici sui deadlock forniti da SQL Server, di tanto in tanto ti imbatterai in cose come queste:
waitresource=“PAGE: 6:3:70133“
waitresource=“KEY: 6:72057594041991168 (ce52f92a058c)“
A volte, nel gigantesco XML che stai esaminando, troverai maggiori informazioni (le colonne dei deadlock contengono un elenco delle risorse che aiutano a conoscere i nomi degli oggetti e degli indici), ma non sempre.
Questo testo ti aiuterà a decifrarle.
Tutte le informazioni qui presenti possono essere trovate in vari luoghi su internet, sono semplicemente molto disperse! Voglio raccogliere tutto insieme - da DBCC PAGE a hobt_id e alle funzioni non documentate %%physloc%% e %%lockres%%.
Iniziamo a parlare delle attese sulle blocchi PAGE, per poi passare ai blocchi KEY.
1) waitresource=“PAGE: 6:3:70133” = Database_Id: FileId: PageNumber
Se la tua query è in attesa su un blocco PAGE, SQL Server ti fornirà l'indirizzo di questa pagina.
Analizzando “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 usando la query:
SELECT
name
FROM sys.databases
WHERE database_id=6;
GOÈ un pubblico sul mio SQL Server.
1.2) Cerchiamo il nome del file dei dati – se sei interessato
Utilizzeremo data_file_id nel passaggio successivo per trovare il nome della tabella. Puoi saltare direttamente 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 file è chiamato WWI_UserData ed è stato ripristinato nel percorso C:MSSQLDATAWideWorldImporters_UserData.ndf. (Oops, mi hai colto 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 dati 3 appartiene al database WorldWideImporters. Possiamo esaminare il contenuto di questa pagina utilizzando DBCC PAGE non documentato e il trace flag 3604.
Nota: preferisco utilizzare DBCC PAGE su una copia ripristinata da un backup su un altro server, perché è una cosa non documentata. In alcuni casi, potrebbe (Nota del traduttore: purtroppo il link porta a nulla, 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 ai risultati, è possibile trovare object_id e index_id.
Quasi pronto! Ora puoi trovare i nomi delle tabelle e degli indici con la seguente 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;
GOEd ecco che 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 utilizzando il DMO non documentato sys.dm_db_database_page_allocations. Ma dovrai interrogare ogni pagina nel DB, il che non è molto pratico per grandi basi di dati, quindi ho utilizzato DBCC PAGE.
1.4) È possibile vedere i dati sulla pagina che è stata bloccata?
Beh, sì. Ma... sei sicuro di averne davvero bisogno?
È lento anche su piccole tabelle. Ma è piuttosto interessante, quindi, dato che sei arrivato a questo punto... parliamo di %%physloc%%!
%%physloc%% è un pezzo di magia non documentata che restituisce l'identificatore fisico per ciascuna registrazione. Puoi utilizzare .
Ora che sappiamo che volevamo applicare un blocco sulla pagina in Sales.OrderLines, possiamo esaminare tutti i dati in questa tabella, che sono memorizzati nel file dati #3 nella pagina #70133, utilizzando 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 tabelle piccolissime. Ho aggiunto NOLOCK alla query perché non abbiamo comunque garanzie che i dati a cui vogliamo dare un'occhiata siano esattamente gli stessi di quando è stato rilevato il blocco — quindi possiamo tranquillamente effettuare letture sporche.
Ma, evviva, la query mi restituisce proprio quelle 25 righe per cui la nostra query stava lottando.
Basta parlare di blocchi PAGE. E se stessimo aspettando un blocco KEY?
2) waitresource='KEY: 6:72057594041991168 (ce52f92a058c)' = Database_Id, HOBT_Id (un hash magico che può essere decifrato con %%lockres%%, se proprio lo desideri)
Se la tua query sta cercando di applicare un blocco su una registrazione nell'indice e risulta bloccata a sua volta, ricevi un tipo completamente diverso di indirizzo.
Suddividendo '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 utilizzando 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 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 query stava aspettando sulla lock 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 era necessaria la lock, posso scoprirlo con una query sulla tabella stessa. Possiamo usare la funzione non documentata %%lockres%% per trovare il record corrispondente all'hash magico.
Tieni presente che questa query scansionerà l'intera tabella, e su tabelle grandi potrebbe non essere affatto divertente:
SELECT
*
FROM Application.Countries (NOLOCK)
WHERE %%lockres%% = '(ce52f92a058c)';
GO Ho aggiunto NOLOCK () perché le restrizioni possono diventare un problema. Vogliamo solo dare un'occhiata a cosa c'è adesso, non a cosa c'era quando è iniziata la transazione — non credo che la coerenza dei dati sia importante per noi.
Ecco, la registrazione per cui abbiamo lottato!
Riconoscimenti e letture future
Non ricordo chi abbia descritto per primo molte di queste cose, ma ecco due articoli sulle questioni meno documentate che potrebbero piacerti:
- Il post di Paul Randal su (come abbiamo ottenuto i nostri dati nel primo esempio)
- Una domanda su StackOverflow riguardo (come abbiamo trovato i dati nel secondo esempio). Una delle risposte rimanda a un post .
Fonte: habr.com
