Wenn Sie den Blockierungsbericht (blocked process report) verwenden oder die Deadlock-Grafiken, die von SQL Server bereitgestellt werden, gelegentlich sammeln, werden Sie irgendwann auf solche Dinge stoßen:
waitresource="PAGE: 6:3:70133"
waitresource="KEY: 6:72057594041991168 (ce52f92a058c)"
Manchmal enthält das riesige XML, das Sie analysieren, mehr Informationen (die Deadlock-Grafiken enthalten eine Liste von Ressourcen, die helfen, die Namen des Objekts und des Indexes herauszufinden), aber nicht immer.
Dieser Text hilft Ihnen, sie zu entschlüsseln.
Alle Informationen, die hier vorhanden sind, gibt es im Internet an verschiedenen Stellen, sie sind nur sehr verstreut! Ich möchte alles zusammenbringen – von DBCC PAGE über hobt_id bis hin zu den undocumented %%physloc%% und %%lockres%% Funktionen.
Zuerst sprechen wir über Wartezeiten bei PAGE-Sperren und danach über KEY-Sperren.
1) waitresource="PAGE: 6:3:70133" = Datenbank_Id: FileId: PageNumber
Wenn Ihre Anfrage auf eine PAGE-Sperre wartet, gibt SQL Server Ihnen die Adresse dieser Seite.
Wenn wir "PAGE: 6:3:70133" aufschlüsseln, erhalten wir:
- database_id = 6
- data_file_id = 3
- page_number = 70133
1.1) Entschlüsseln des database_id
Lassen Sie uns den Namen der Datenbank mit der Abfrage finden:
SELECT
name
FROM sys.databases
WHERE database_id=6;
GODas ist öffentlich zugänglich auf meinem SQL Server.
1.2) Suchen des Namens der Datendatei – falls es Sie interessiert
Wir werden data_file_id im nächsten Schritt verwenden, um den Namen der Tabelle zu finden. Sie können einfach zum nächsten Schritt übergehen, aber wenn Sie am Namen der Datei interessiert sind, können Sie ihn finden, indem Sie die Abfrage im Kontext der gefundenen DB ausführen und data_file_id in diese Abfrage einsetzen:
USE WideWorldImporters;
GO
SELECT
name,
physical_name
FROM sys.database_files
WHERE file_id = 3;
GOIn der DB WideWorldImporters ist dies eine Datei mit dem Namen WWI_UserData und sie wurde bei mir in C:MSSQLDATAWideWorldImporters_UserData.ndf wiederhergestellt. (Ups, Sie haben mich dabei erwischt, wie ich die Dateien auf dem Systemlaufwerk abgelegt habe! Nein! Das war peinlich).
1.3) Erhalten des Objekt-Namens aus DBCC PAGE
Jetzt wissen wir, dass die Seite #70133 in der Datendatei 3 zur DB WorldWideImporters gehört. Wir können den Inhalt dieser Seite mit dem undocumented DBCC PAGE und dem Trace-Flag 3604 anschauen.
Hinweis: Ich ziehe es vor, DBCC PAGE auf einer wiederhergestellten Kopie aus einem Backup auf einem anderen Server zu verwenden, da es eine undocumented Funktion ist. In einigen Fällen kann es (Hinweis des Übersetzers – der Link führt leider ins Leere, aber laut URL dürfte es sich um gefilterte Indizes handeln).
/* 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 Sie zu den Ergebnissen scrollen, können Sie object_id und index_id finden.
Fast fertig! Jetzt können Sie die Namen des Schemas und des Index mit dieser 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 so sehen wir, dass das Warten auf die Sperre auf dem Index PK_Sales_OrderLines der Tabelle Sales.OrderLines war.
Hinweis: In SQL Server 2014 und höher können Sie den Objektnamen auch über die undocumented DMO sys.dm_db_database_page_allocations finden. Sie müssen jedoch jede Seite in der DB abfragen, was für große Datenbanken nicht wirklich schön aussieht; deshalb habe ich DBCC PAGE verwendet.
1.4) Kann man die Daten auf der Seite sehen, die blockiert war?
Naja, 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 undocumented Stück Magie, das die physische ID für jeden Datensatz zurückgibt. Sie können es verwenden .
Jetzt, da wir wissen, dass wir eine Sperre auf die Seite in Sales.OrderLines anwenden wollten, können wir alle Daten in dieser Tabelle, die in der Datendatei #3 auf Seite #70133 gespeichert sind, mit dieser Abfrage ansehen:
Use WideWorldImporters;
GO
SELECT
sys.fn_PhysLocFormatter (%%physloc%%),
*
FROM Sales.OrderLines (NOLOCK)
WHERE sys.fn_PhysLocFormatter (%%physloc%%) like '(3:70133%'
GOWie ich sagte – es ist selbst bei winzigen Tabellen langsam. Ich habe NOLOCK zur Abfrage hinzugefügt, weil wir sowieso keine Garantien haben, dass die Daten, die wir sehen wollen, genau die gleichen sind, die zum Zeitpunkt der Blockierung vorhanden waren – wir können also ruhig dirty reads durchführen.
Aber hurra, die Abfrage liefert mir genau die 25 Zeilen, um die sich unsere Abfrage gestritten hat
Genug von PAGE-Sperren. Was, wenn wir auf eine KEY-Sperre warten?
2) waitresource="KEY: 6:72057594041991168 (ce52f92a058c)" = Database_Id, HOBT_Id (magischer Hash, der mit %%lockres%% entschlüsselt werden kann, falls Sie das wirklich wollen)
Wenn Ihre Abfrage versucht, eine Sperre auf einen Datensatz im Index anzuwenden und selbst blockiert wird, erhalten Sie einen ganz anderen Typ von Adresse.
Zerlegen wir "6:72057594041991168 (ce52f92a058c)" in Teile, erhalten wir:
- database_id = 6
- hobt_id = 72057594041991168
- magischer Hash = (ce52f92a058c)
2.1) Entschlüsseln der database_id
Es funktioniert genau wie das Beispiel oben! Wir finden den DB-Namen mit einer Abfrage:
SELECT
name
FROM sys.databases
WHERE database_id=6;
GOIn meinem Fall ist es immer noch dasselbe .
2.2) Entschlüsseln von hobt_id
Im Kontext der gefundenen DB muss eine Abfrage an sys.partitions mit einem Paar Joins durchgeführt werden, die helfen, 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 die Sperre von Application.Countries gewartet hat, wobei der Index PK_Application_Countries verwendet wird.
2.3) Jetzt ein wenig Magie %%lockres%% — wenn Sie herausfinden möchten, welcher Datensatz gesperrt war
Wenn ich wirklich wissen möchte, auf welcher Zeile die Sperre benötigt wurde, kann ich dies mit einer Abfrage auf die Tabelle selbst herausfinden. Wir können die nicht dokumentierte Funktion %%lockres%% verwenden, um den Datensatz zu finden, der mit dem magischen Hash übereinstimmt.
Beachten Sie, dass diese Abfrage die gesamte Tabelle durchsuchen wird, und bei großen Tabellen kann das ziemlich unangenehm sein:
SELECT
*
FROM Application.Countries (NOLOCK)
WHERE %%lockres%% = '(ce52f92a058c)';
GO Ich habe NOLOCK hinzugefügt () weil Sperren problematisch werden können. Wir möchten einfach nur sehen, was dort jetzt ist, und nicht, was dort war, als die Transaktion begann – ich denke nicht, dass uns die Konsistenz der Daten wichtig ist.
Voilà, der Datensatz um den wir gekämpft haben!
Danksagungen und weiterführende Literatur
Ich erinnere mich nicht, wer viele dieser Dinge zuerst beschrieben hat, aber hier sind zwei Posts über die am wenigsten dokumentierten Techniken, die Ihnen gefallen könnten:
- Paul Randals Post über (wie wir unsere Daten im ersten Beispiel)
- Die Frage auf StackOverflow über (wie wir die Daten im zweiten Beispiel gefunden haben). Eine der Antworten führt zu dem Post .
Quelle: habr.com
