Wenn Sie den Bericht über blockierte Prozesse (blocked process report) verwenden oder regelmäßig Deadlock-Grafen, die von SQL Server bereitgestellt werden, sammeln, werden Sie gelegentlich auf folgende Dinge stoßen:
waitresource="PAGE: 6:3:70133"
waitresource="KEY: 6:72057594041991168 (ce52f92a058c)"
Manchmal enthält das riesige XML, das Sie analysieren, mehr Informationen (Deadlock-Grafen enthalten eine Liste der Ressourcen, die helfen, die Namen von Objeckten und Indizes zu identifizieren), aber nicht immer.
Dieser Text wird Ihnen helfen, sie zu entschlüsseln.
Alle Informationen, die hier vorhanden sind, sind an verschiedenen Stellen im Internet verfügbar, sie sind nur stark verteilt! Ich möchte alles zusammenführen — von DBCC PAGE zu hobt_id und zu den undocumented %%physloc%% und %%lockres%% Funktionen.
Zuerst sprechen wir über das Warten auf PAGE-Blockaden, und dann gehen wir zu KEY-Blockaden über.
1) waitresource="PAGE: 6:3:70133" = Database_Id: FileId: PageNumber
Wenn Ihre Anfrage auf eine PAGE-Blockade wartet, wird SQL Server Ihnen die Adresse dieser Seite geben.
Durch das Zerlegen von "PAGE: 6:3:70133" erhalten wir:
- database_id = 6
- data_file_id = 3
- page_number = 70133
1.1) Entschlüsseln von database_id
Lassen Sie uns den Namen der Datenbank mit der Abfrage finden:
SELECT
name
FROM sys.databases
WHERE database_id=6;
GOEs ist die öffentliche auf meinem SQL Server.
1.2) Wir suchen den Namen der Datendatei — falls Sie interessiert sind.
Wir werden data_file_id im nächsten Schritt verwenden, um den Tabellennamen zu finden. Sie können einfach zum nächsten Schritt übergehen, aber wenn Sie an dem Dateinamen interessiert sind, können Sie ihn durch Ausführen einer Abfrage im Kontext der gefundenen Datenbank herausfinden, indem Sie data_file_id in diese Abfrage einfügen:
USE WideWorldImporters;
GO
SELECT
name,
physical_name
FROM sys.database_files
WHERE file_id = 3;
GOIn der Datenbank WideWorldImporters handelt es sich um die Datei mit dem Namen WWI_UserData, die bei mir unter C:MSSQLDATAWideWorldImporters_UserData.ndf wiederhergestellt wurde. (Ups, Sie haben mich dabei erwischt, wie ich Dateien auf dem Systemlaufwerk abgelegt habe! Nein! Das war peinlich).
1.3) Den Objektnamen aus DBCC PAGE abrufen
Jetzt wissen wir, dass Seite #70133 in der Datendatei 3 zur Datenbank WideWorldImporters gehört. Wir können den Inhalt dieser Seite mit dem undocumented DBCC PAGE und trace-Flag 3604 anzeigen.
Hinweis: Ich bevorzuge es, DBCC PAGE auf einer aus einem Backup wiederhergestellten Kopie auf einem anderen Server zu verwenden, da dies eine undocumented Funktion ist. In einigen Fällen kann sie (Hinweis des Übersetzers — der Link führt leider ins Leere, aber laut URL geht es um gefilterte Indizes.).
/* 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 Wenn man zu den Ergebnissen blättert, kann man object_id und index_id finden.
Fast fertig! Jetzt können Sie die Namen von Tabellen und Indizes mit der Abfrage finden:
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;
GOUnd hier sehen wir, dass das Warten auf die Sperre am Index PK_Sales_OrderLines der Tabelle Sales.OrderLines war.
Hinweis: In SQL Server 2014 und höher kann der Objektnamen auch mithilfe der nicht dokumentierten DMO sys.dm_db_database_page_allocations gefunden werden. Aber Sie müssen jede Seite in der Datenbank abfragen, was für große Datenbanken nicht besonders elegant aussieht. Daher habe ich DBCC PAGE verwendet.
1.4) Kann man die Daten auf der Seite sehen, die gesperrt war?
Nun, ja. Aber… sind Sie sich sicher, dass Sie das wirklich brauchen?
Es ist selbst bei kleinen Tabellen langsam. Aber es ist irgendwie cool, also, da Sie bis hierher gelesen haben… lassen Sie uns über %%physloc%% sprechen!
%%physloc%% ist ein nicht dokumentiertes Stück Magie, das die physische Kennung für jeden Datensatz zurückgibt. Sie können es verwenden .
Jetzt, wo wir wissen, dass wir die Seite in Sales.OrderLines sperren wollten, können wir alle Daten in dieser Tabelle betrachten, die in der Datendatei #3 auf Seite #70133 gespeichert sind, mithilfe dieser Abfrage:
Verwende WideWorldImporters;
GO
SELECT
sys.fn_PhysLocFormatter (%%physloc%%),
*
FROM Sales.OrderLines (NOLOCK)
WHERE sys.fn_PhysLocFormatter (%%physloc%%) like '(3:70133%'
GOWie ich schon sagte — das ist selbst bei winzigen Tabellen langsam. Ich habe die NOLOCK-Klausel zur Abfrage hinzugefügt, weil wir sowieso keine Garantien haben, dass die Daten, die wir betrachten möchten, die gleichen sind, wie sie zu dem Zeitpunkt waren, als die Sperre entdeckt wurde — wir können also gelassen schmutzige Lesevorgänge durchführen.
Aber, hurra, die Abfrage gibt mir genau die 25 Zeilen zurück, um die unser Abfrage gekämpft hat.
Genug von PAGE-Sperren. Was ist, wenn wir eine KEY-Sperre erwarten?
2) waitresource="KEY: 6:72057594041991168 (ce52f92a058c)" = Database_Id, HOBT_Id (ein magischer Hash, der mit %%lockres%% entschlüsselt werden kann, wenn Sie das wirklich wollen)
Wenn Ihre Abfrage versucht, eine Sperre auf einen Datensatz im Index zu setzen und selbst blockiert wird, erhalten Sie einen ganz anderen Typ von Adresse.
Wenn wir "6:72057594041991168 (ce52f92a058c)" in Teile zerlegen, erhalten wir:
- database_id = 6
- hobt_id = 72057594041991168
- magischer Hash = (ce52f92a058c)
2.1) Entschlüsseln der database_id
Das funktioniert genau wie im obigen Beispiel! Wir finden den Namen der DB mit einer Abfrage:
SELECT
name
FROM sys.databases
WHERE database_id=6;
GOIn meinem Fall ist es immer noch die gleiche .
2.2) Entschlüsseln der hobt_id
Im Kontext der gefundenen DB muss eine Abfrage an sys.partitions mit ein paar Joins ausgeführt werden, um die Namen der Tabelle und des Index zu bestimmen...
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;
GOEr sagt mir, dass die Abfrage auf eine Sperre von Application.Countries gewartet hat, unter Verwendung des Index PK_Application_Countries.
2.3) Jetzt ein bisschen Magie %%lockres%% — wenn Sie herausfinden möchten, welcher Datensatz gesperrt wurde
Wenn ich wirklich wissen möchte, auf welcher Zeile die Sperre benötigt wurde, kann ich das mit einer Abfrage an die Tabelle selbst herausfinden. Wir können die undocumented Funktion %%lockres%% verwenden, um den Datensatz zu finden, der mit dem magischen Hash übereinstimmt.
Beachten Sie, dass diese Abfrage die gesamte Tabelle scannen wird, und bei großen Tabellen könnte das ziemlich unangenehm sein:
SELECT
*
FROM Application.Countries (NOLOCK)
WHERE %%lockres%% = '(ce52f92a058c)';
GO Ich habe NOLOCK hinzugefügt () denn Sperrungen können problematisch werden. Wir wollen ja einfach nur sehen, was es jetzt gibt und nicht, was war, als die Transaktion begann — ich denke nicht, dass uns die Datenkonsistenz wichtig ist.
Voilà, der Eintrag, um den wir gekämpft haben!
Dank und weiterführende Lektüre
Ich erinnere mich nicht, wer viele dieser Dinge zuerst beschrieben hat, aber hier sind zwei Beiträge über die am wenigsten dokumentierten Kleinigkeiten, die Ihnen gefallen könnten:
- Der Beitrag von Paul Randal über (wie wir unsere Daten im ersten Beispiel)
- Frage auf StackOverflow über (wie wir die Daten im zweiten Beispiel gefunden haben). Eine der Antworten führt zu einem Beitrag von .
Quelle: habr.com
