Wenn Sie den Bericht über blockierte Prozesse verwenden oder regelmäßig die von SQL Server bereitgestellten Deadlock-Diagramme sammeln, werden Sie auf Dinge wie diese stoßen:
waitresource="SEITE: 6:3:70133"
waitresource=“KEY: 6:72057594041991168 (ce52f92a058c)“
Manchmal enthält das riesige XML, das Sie studieren, mehr Informationen (Deadlock-Diagramme enthalten eine Liste von Ressourcen, die Ihnen dabei helfen, die Namen des Objekts und des Index herauszufinden), aber nicht immer.
Dieser Text hilft Ihnen, sie zu entschlüsseln.
Alle Informationen, die es hier gibt, liegen im Internet an den verschiedensten Stellen, sie sind einfach sehr verbreitet! Ich möchte alles zusammenfügen, von DBCC PAGE über hobt_id bis hin zu den undokumentierten Funktionen %%physloc%% und %%lockres%%.
Lassen Sie uns zunächst über Wartezeiten bei PAGE-Sperren sprechen und dann zu KEY-Sperren übergehen.
1) waitresource="PAGE: 6:3:70133" = Database_Id: FileId: PageNumber
Wenn Ihre Anfrage auf eine PAGE-Sperre wartet, gibt Ihnen SQL Server die Adresse dieser Seite.
Wenn wir „SEITE: 6:3:70133“ aufschlüsseln, erhalten wir:
- Datenbank_ID = 6
- data_file_id = 3
- Seitennummer = 70133
1.1) Datenbank_ID entschlüsseln
Suchen Sie den Namen der Datenbank mithilfe der Abfrage:
SELECT
name
FROM sys.databases
WHERE database_id=6;
GOEs ist öffentlich auf meinem SQL Server.
1.2) Suchen Sie nach dem Namen der Datendatei – falls Sie interessiert sind
Im nächsten Schritt werden wir 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 data_file_id in diese Abfrage ersetzen:
USE WideWorldImporters;
GO
SELECT
name,
physical_name
FROM sys.database_files
WHERE file_id = 3;
GOIn der WideWorldImporters-Datenbank ist dies eine Datei namens WWI_UserData und ich habe sie in C:MSSQLDATAWideWorldImporters_UserData.ndf wiederhergestellt. (Ups, Sie haben mich dabei erwischt, wie ich Dateien auf dem Systemlaufwerk abgelegt habe! Nein! Es ist peinlich.)
1.3) Objektnamen von der DBCC-Seite abrufen
Wir wissen jetzt, dass Seite Nr. 70133 in Datendatei 3 zur WorldWideImporters-Datenbank gehört. Wir können den Inhalt dieser Seite mit der undokumentierten DBCC-Seite und dem Trace-Flag 3604 betrachten.
Hinweis: Ich bevorzuge die Verwendung von DBCC PAGE für eine wiederhergestellte Kopie irgendwo auf einem anderen Server, da es sich um undokumentierte Dinge handelt. In einigen Fällen sie (ca. Übersetzer - der Link führt leider nirgendwo hin, aber der URL nach 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);
GO
Wenn Sie zu den Ergebnissen scrollen, können Sie die Objekt-ID und die Index-ID finden.
Fast fertig! Jetzt können Sie die Tabellen- und Indexnamen mit einer 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 auf die Sperre auf den PK_Sales_OrderLines-Index der Tabelle Sales.OrderLines gewartet wurde.
Hinweis: In SQL Server 2014 und höher kann der Objektname auch über das undokumentierte DMO sys.dm_db_database_page_allocations gefunden werden. Aber man muss jede Seite in der Datenbank abfragen, was für große Datenbanken nicht besonders cool aussieht, also habe ich DBCC PAGE verwendet.
1.4) Ist es möglich, die Daten der gesperrten Seite einzusehen?
Nuuu, ja. Aber... sind Sie sicher, dass Sie es wirklich brauchen?
Selbst auf kleinen Tischen ist es langsam. Aber es ist irgendwie cool, also reden wir, da Sie bis hierhin gelesen haben, über %%physloc%%!
%%physloc%% ist ein undokumentiertes Stück Magie, das für jeden Eintrag eine physische ID zurückgibt. Sie können verwenden .
Da wir nun wissen, dass wir die Seite in Sales.OrderLines blockieren wollten, können wir uns mit der folgenden Abfrage alle Daten in dieser Tabelle ansehen, die in der Datendatei Nr. 3 auf Seite Nr. 70133 gespeichert ist:
Use WideWorldImporters;
GO
SELECT
sys.fn_PhysLocFormatter (%%physloc%%),
*
FROM Sales.OrderLines (NOLOCK)
WHERE sys.fn_PhysLocFormatter (%%physloc%%) like '(3:70133%'
GOWie gesagt, es ist selbst auf kleinen Tischen langsam. Ich habe der Anfrage NOLOCK hinzugefügt, da wir immer noch keine Garantie dafür haben, dass die Daten, die wir betrachten möchten, genau dieselben sind wie zum Zeitpunkt der Entdeckung der Sperre – wir können also sicher Dirty Reads durchführen.
Aber Hurra, die Abfrage gibt mir genau die 25 Zeilen zurück, um die unsere Abfrage gekämpft hat
Genug über PAGE-Sperren. Was ist, wenn wir auf ein Schlüsselschloss warten?
2) waitresource="KEY: 6:72057594041991168 (ce52f92a058c)" = Database_Id, HOBT_Id (magischer Hash, der mit %%lockres%% entschlüsselt werden kann, wenn Sie es wirklich wollen)
Wenn Ihre Abfrage versucht, einen Indexeintrag zu sperren und von selbst gesperrt wird, erhalten Sie einen völlig anderen Adresstyp.
Wenn wir „6:72057594041991168 (ce52f92a058c)“ in Teile zerlegen, erhalten wir:
- Datenbank_ID = 6
- hobt_id = 72057594041991168
- magischer Hash = (ce52f92a058c)
2.1) Datenbank_ID entschlüsseln
Es funktioniert genauso wie im obigen Beispiel! Den Namen der Datenbank finden wir mit der Abfrage:
SELECT
name
FROM sys.databases
WHERE database_id=6;
GOIn meinem Fall ist es das Gleiche .
2.2) hobt_id entschlüsseln
Im Kontext der gefundenen Datenbank müssen Sie sys.partitions mit ein paar Joins abfragen, die dabei helfen, die Namen der Tabelle und des Index zu ermitteln ...
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 sagt mir, dass die Anfrage auf die Application.Countries-Sperre unter Verwendung des PK_Application_Countries-Index gewartet hat.
2.3) Nun zu etwas %%lockres%%-Magie – wenn Sie herausfinden möchten, welcher Eintrag gesperrt wurde
Wenn ich wirklich wissen möchte, für welche Zeile die Sperre erforderlich war, kann ich dies herausfinden, indem ich die Tabelle selbst abfrage. Wir können die undokumentierte Funktion %%lockres%% verwenden, um einen Eintrag zu finden, der mit dem magischen Hash übereinstimmt.
Beachten Sie, dass diese Abfrage die gesamte Tabelle durchsucht, was bei großen Tabellen möglicherweise überhaupt keinen Spaß macht:
SELECT
*
FROM Application.Countries (NOLOCK)
WHERE %%lockres%% = '(ce52f92a058c)';
GO
Ich habe NOLOCK hinzugefügt (), da Sperren ein Problem darstellen können. Wir wollen nur schauen, was jetzt da ist, und nicht, was zu Beginn der Transaktion da war – ich glaube nicht, dass Datenkonsistenz für uns wichtig ist.
Voila, der Rekord, für den wir gekämpft haben!
Danksagungen und weiterführende Literatur
Ich kann mich nicht erinnern, wer viele dieser Dinge zuerst beschrieben hat, aber hier sind zwei Beiträge zu den am wenigsten dokumentierten Dingen, die Ihnen gefallen könnten:
- Paul Randals Beitrag über (da wir unsere Daten im ersten Beispiel haben)
- Frage zu StackOverflow zu (wie wir die Daten im zweiten Beispiel gefunden haben). Eine der Antworten führt zum Beitrag .
Source: habr.com