waitresource = "SEITE: 6: 3: 70133"
waitresource = "KEY: 6: 72057594041991168 (ce52f92a058c)"
Manchmal enthält das riesige XML, das Sie lernen, mehr Informationen (festgefahrene Diagramme enthalten eine Liste von Ressourcen, mit denen Sie die Objekt- und Indexnamen herausfinden können), aber nicht immer.
Dieser Text hilft Ihnen, sie zu entziffern.
Alle Informationen, die hier sind, sind im Internet an verschiedenen Orten, es ist einfach hoch verbreitet! Ich möchte alles zusammenstellen - von DBCC PAGE über hobt_id bis hin zu undokumentierten %% physloc %% und %% lockres %% Funktionen.
Lassen Sie uns zunächst über das Warten auf PAGE-Sperren sprechen und dann mit den KEY-Sperren fortfahren.
1) waitresource = "PAGE: 6: 3: 70133" = Database_Id: FileId: PageNumber
Wenn Ihre Abfrage auf eine PAGE-Sperre wartet, gibt Ihnen SQL Server die URL dieser Seite.
Wenn wir "SEITE: 6: 3: 70133" aufschlüsseln, erhalten wir:
- database_id = 6
- data_file_id = 3
- page_numer = 70133
1.1) Entschlüsseln Sie database_id
Lassen Sie uns den Namen der Datenbank mithilfe der Abfrage ermitteln:
SELECT
name
FROM sys.databases
WHERE database_id=6;
GODies ist die öffentliche WideWorldImporters-Datenbank auf meinem SQL Server.
1.2) Suchen Sie nach dem Namen der Datendatei - wenn Sie interessiert sind
Wir werden im nächsten Schritt data_file_id verwenden, um den Tabellennamen zu finden. Sie können einfach mit dem nächsten Schritt fortfahren. Wenn Sie jedoch am Dateinamen interessiert sind, können Sie ihn finden, indem Sie eine Abfrage im Kontext der gefundenen Datenbank ausführen und in dieser Abfrage data_file_id ersetzen:
USE WideWorldImporters;
GO
SELECT
name,
physical_name
FROM sys.database_files
WHERE file_id = 3;
GOIn der WideWorldImporters-Datenbank ist dies eine Datei mit dem Namen WWI_UserData, die in meiner Datei C: \ MSSQL \ DATA \ WideWorldImporters_UserData.ndf wiederhergestellt wurde. (Ups, Sie haben mich beim Ablegen von Dateien auf der Systemfestplatte erwischt! Nein! Es war umständlich).
1.3) Rufen Sie den Namen des Objekts von der DBCC-SEITE ab
Wir wissen jetzt, dass Seite # 70133 in Datendatei 3 zur WorldWideImporters-Datenbank gehört. Wir können den Inhalt dieser Seite mit der undokumentierten DBCC-PAGE und dem Trace-Flag 3604 anzeigen.
Hinweis: Ich bevorzuge die Verwendung der DBCC-PAGE für ein Backup, das von einem Backup irgendwo auf einem anderen Server wiederhergestellt wurde, da es sich um ein undokumentiertes Backup handelt. In einigen Fällen kann dies zur Erstellung eines Speicherauszugs führen ( Kommentar des Übersetzers - der Link führt leider nirgendwo hin, aber nach der URL zu urteilen, handelt es sich 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);
GOWenn Sie zu den Ergebnissen scrollen, finden Sie die Objekt-ID und die Index-ID.
Fast fertig! Jetzt können Sie die Tabellen- und Indexnamen mithilfe 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 jetzt sehen wir, dass das Warten auf die Sperre im PK_Sales_OrderLines-Index der Sales.OrderLines-Tabelle erfolgte.
Hinweis: In SQL Server 2014 und höher kann der Objektname auch mithilfe des undokumentierten DMO sys.dm_db_database_page_allocations gefunden werden. Sie müssen jedoch jede Seite in der Datenbank abfragen, was für große Datenbanken nicht sehr cool aussieht. Deshalb habe ich DBCC PAGE verwendet.
1.4) Können Sie die Daten auf der Seite sehen, die blockiert wurde?
Nuuu, ja. Aber ... bist du sicher, dass du es wirklich brauchst?
Selbst auf kleinen Tischen ist es langsam. Aber das ist irgendwie cool, also seit du so weit gelesen hast ... lass uns über %% physloc %% sprechen!
%% physloc %% ist ein undokumentiertes Stück Magie, das für jeden Eintrag eine physische ID zurückgibt. Sie können %% physloc %% zusammen mit sys.fn_PhysLocFormatter in SQL Server 2008 und höher verwenden .
Nachdem wir nun wissen, dass wir die Seite in Sales.OrderLines blockieren wollten, können wir alle Daten in dieser Tabelle, die in der Datendatei Nr. 3 auf Seite Nr. 70133 gespeichert ist, mithilfe der folgenden Abfrage anzeigen:
Use WideWorldImporters;
GO
SELECT
sys.fn_PhysLocFormatter (%%physloc%%),
*
FROM Sales.OrderLines (NOLOCK)
WHERE sys.fn_PhysLocFormatter (%%physloc%%) like '(3:70133%'
GOWie gesagt - selbst auf winzigen Tischen ist es langsam. Ich habe der Abfrage NOLOCK hinzugefügt, da wir immer noch keine Garantie dafür haben, dass die Daten, die wir anzeigen möchten, genau die gleichen sind wie zu dem Zeitpunkt, als die Sperre gefunden wurde. So können wir sicher schmutzige Lesevorgänge durchführen.
Aber Hurra, die Abfrage gibt mir die gleichen 25 Zeilen zurück, für die unsere Abfrage gekämpft hat.
Genug von PAGE-Sperren. Was ist, wenn wir auf ein KEY-Schloss warten?
2) waitresource = "KEY: 6: 72057594041991168 (ce52f92a058c)" = Database_Id, HOBT_Id (magischer Hash, der mit %% lockres %% entschlüsselt werden kann, wenn Sie wirklich wollen)
Wenn Ihre Abfrage versucht, einen Indexeintrag zu sperren und sich selbst sperrt, erhalten Sie einen völlig anderen Adresstyp.
Wenn wir "6: 72057594041991168 (ce52f92a058c)" in Teile zerlegen, erhalten wir:
- database_id = 6
- hobt_id = 72057594041991168
- magischer Hash = (ce52f92a058c)
2.1) Entschlüsseln Sie database_id
Dies funktioniert genauso wie im obigen Beispiel! Suchen Sie den Namen der Datenbank mithilfe der Abfrage:
SELECT
name
FROM sys.databases
WHERE database_id=6;
GOIn meinem Fall ist dies dieselbe WideWorldImporters-Datenbank .
2.2) Entschlüsseln Sie die hobt_id
Im Kontext der gefundenen Datenbank müssen Sie eine Abfrage an sys.partitions mit einigen Verknüpfungen ausführen, mit deren Hilfe Sie die Tabellen- und Indexnamen ermitteln können ...
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;
GOEs zeigt mir, dass die Anforderung auf die Application.Countries-Sperre mit dem PK_Application_Countries-Index gewartet hat.
2.3) Nun einige magische %% lockres %% - wenn Sie herausfinden möchten, welcher Datensatz gesperrt wurde
Wenn ich wirklich wissen möchte, in welcher Zeile das Schloss benötigt wurde, kann ich es herausfinden, indem ich die Tabelle selbst abfrage. Wir können die undokumentierte %% lockres %% -Funktion verwenden, um den Eintrag zu finden, der dem magischen Hash entspricht.
Beachten Sie, dass diese Abfrage die gesamte Tabelle scannt. Bei großen Tabellen macht dies möglicherweise überhaupt keinen Spaß:
SELECT
*
FROM Application.Countries (NOLOCK)
WHERE %%lockres%% = '(ce52f92a058c)';
GOIch habe NOLOCK hinzugefügt ( auf Anraten von Klaus Aschenbrenner auf Twitter ), weil das Blockieren ein Problem sein kann. Wir möchten nur sehen, was jetzt da ist und nicht, was zu Beginn der Transaktion da war. Ich denke nicht, dass Datenkonsistenz für uns wichtig ist.
Voila, der Rekord, für den wir gekämpft haben!
Danksagung und weiterführende Literatur
Ich kann mich nicht erinnern, wer als erster viele dieser Dinge beschrieben hat, aber hier sind zwei Beiträge zu den am wenigsten dokumentierten Dingen, die Ihnen gefallen könnten:
- Paul Randals Beitrag über %% physloc %% und sys.fn_PhysLocFormatter (wie wir unsere Daten im ersten Beispiel machen)
- Eine Frage zu StackOverflow zur Verwendung von %% lockres %% (wie wir die Daten im zweiten Beispiel gefunden haben). Eine der Antworten geht auf Grant Fritcheys Beitrag über %% lockres %% aus dem Jahr 2010 .