Als u het blokkade-rapport (blocked process report) gebruikt of de deadlock-grafieken die door SQL Server worden geleverd regelmatig verzamelt, zult u af en toe met dit soort dingen geconfronteerd worden:
waitresource="PAGE: 6:3:70133"
waitresource="KEY: 6:72057594041991168 (ce52f92a058c)"
Soms, in die gigantische XML die u bestudeert, zal er meer informatie zijn (de deadlock-grafieken bevatten een lijst met bronnen die helpt bij het identificeren van object- en indexnamen), maar niet altijd.
Deze tekst zal u helpen ze te ontcijferen.
Alle informatie die hier aanwezig is, is verspreid op verschillende plekken op internet! Ik wil alles samenbrengen - van DBCC PAGE tot hobt_id en de niet-gemoderniseerde functies %%physloc%% en %%lockres%%.
Laten we eerst praten over wachten bij PAGE-locks, en daarna gaan we verder met KEY-locks.
1) waitresource="PAGE: 6:3:70133" = Database_Id: FileId: PageNumber
Als uw query wacht op een PAGE-lock, geeft SQL Server u het adres van deze pagina.
Door "PAGE: 6:3:70133" te ontleden verkrijgen we:
- database_id = 6
- data_file_id = 3
- page_number = 70133
1.1) Ontcijfering van database_id
Laten we de naam van de database vinden met behulp van de volgende query:
SELECT
name
FROM sys.databases
WHERE database_id=6;
GODit is de openbare op mijn SQL Server.
1.2) Op zoek naar de naam van het databestand - als je geïnteresseerd bent
We gaan data_file_id gebruiken in de volgende stap om de naam van de tabel te vinden. U kunt gewoon naar de volgende stap gaan, maar als u geïnteresseerd bent in de naam van het bestand, kunt u deze vinden door de query in de context van de gevonden DB uit te voeren, met data_file_id in te voeren in deze query:
USE WideWorldImporters;
GO
SELECT
name,
physical_name
FROM sys.database_files
WHERE file_id = 3;
GOIn de database WideWorldImporters is dit bestand genaamd WWI_UserData en het is hersteld in C:MSSQLDATAWideWorldImporters_UserData.ndf. (Oops, je betrapte me terwijl ik bestanden op de systeemdisk aan het plaatsen was! Nee! Dit is ongemakkelijk).
1.3) Verkrijgen van de naam van het object uit DBCC PAGE
Nu weten we dat pagina #70133 in databestand 3 behoort tot de database WorldWideImporters. We kunnen de inhoud van deze pagina bekijken met de niet-gemoderniseerde DBCC PAGE en trace-vlag 3604.
Opmerking: ik geef er de voorkeur aan DBCC PAGE te gebruiken op een back-up van een herstelde kopie op een andere server, omdat dit een niet-gemoderniseerde functie is. In sommige gevallen kan het (opmerking van de vertaler - de link leidt helaas nergens heen, maar op basis van de URL lijkt het te gaan over filtered-indexen.).
/* 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 Door naar de resultaten te scrollen, kunt u object_id en index_id vinden.
Bijna klaar! Nu kunnen we de namen van het schema en de index vinden met de volgende 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;
GOEn hier zien we dat de wachttijd op de blokkering zich voordeed op de index PK_Sales_OrderLines van de tabel Sales.OrderLines.
Opmerking: in SQL Server 2014 en hoger kan de objectnaam ook gevonden worden met de niet gedocumenteerde DMO sys.dm_db_database_page_allocations. Maar je moet elke pagina in de database opvragen, wat niet geweldig is voor grote databases, daarom heb ik DBCC PAGE gebruikt.
1.4) Kunnen we de gegevens op de pagina die geblokkeerd was, zien?
Nou, ja. Maar... ben je er zeker van dat je dit echt nodig hebt?
Het is traag, zelfs op kleine tabellen. Maar het is een soort van cool, dus, aangezien je tot hier hebt gelezen... laten we het hebben over %%physloc%%!
%%physloc%% is een niet gedocumenterd stukje magie dat de fysieke identificatie voor elke record teruggeeft. Je kunt het gebruiken .
Nu we weten dat we de blokkering op de pagina in Sales.OrderLines willen opheffen, kunnen we alle gegevens in deze tabel bekijken die zijn opgeslagen in databasenaam #3 op pagina #70133, met behulp van de volgende query:
Use WideWorldImporters;
GO
SELECT
sys.fn_PhysLocFormatter (%%physloc%%),
*
FROM Sales.OrderLines (NOLOCK)
WHERE sys.fn_PhysLocFormatter (%%physloc%%) like '(3:70133%'
GOZoals ik al zei — het is traag, zelfs op piepkleine tabellen. Ik heb NOLOCK aan de query toegevoegd omdat we toch geen garanties hebben dat de gegevens, waar we naar willen kijken, precies dezelfde zijn als op het moment dat de blokkering werd ontdekt — dus we kunnen gerust ongefilterde lezing doen.
Maar, hoera, de query geeft me precies die 25 rijen terug waar onze query voor vocht.
Laten we het niet meer hebben over PAGE-blokkeringen. Wat als we wachten op een KEY-blokkering?
2) waitresource=“KEY: 6:72057594041991168 (ce52f92a058c)” = Database_Id, HOBT_Id (de magische hash die kan worden ontcijferd met %%lockres%%, als je dat echt wilt)
Als je query probeert een blokkering op een record in de index toe te passen en zelf wordt geblokkeerd, krijg je een heel ander type adres.
Door “6:72057594041991168 (ce52f92a058c)” op te splitsen, krijgen we:
- database_id = 6
- hobt_id = 72057594041991168
- magische hash = (ce52f92a058c)
2.1) Ontcijfering van database_id
Het werkt precies zoals in het bovenstaande voorbeeld! We vinden de database naam met behulp van de query:
SELECT
name
FROM sys.databases
WHERE database_id=6;
GOIn mijn geval is het nog steeds dezelfde .
2.2) We decodeer hobt_id
In de context van de gevonden database, moet een query naar sys.partitions worden uitgevoerd met een paar joins die helpen om de namen van de tabel en index te bepalen…
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;
GOHij vertelt me dat de query wachtte op de blokkering van Application.Countries, met gebruik van index PK_Application_Countries.
2.3) Nu een beetje magie %%lockres%% — als je wilt achterhalen welke record werd geblokkeerd
Als ik echt wil weten op welke rij de blokkering nodig was, kan ik dat uitzoeken met een query naar de tabel zelf. We kunnen de niet-gedocumenteerde functie %%lockres%% gebruiken om de record te vinden die overeenkomt met de magische hash.
Houd er rekening mee dat deze query de hele tabel zal scannen, en op grote tabellen kan dit heel onaangenaam zijn:
SELECT
*
FROM Application.Countries (NOLOCK)
WHERE %%lockres%% = '(ce52f92a058c)';
GO Ik heb NOLOCK toegevoegd () omdat blokkeringen een probleem kunnen zijn. We willen gewoon zien wat daar nu is, en niet wat er was toen de transactie begon — ik denk niet dat de consistentie van de gegevens voor ons belangrijk is.
Voilà, de record om welke we vochten!
Dankbetuigingen en verder lezen
Ik weet niet meer wie als eerste veel van deze dingen beschreef, maar hier zijn twee posts over de minst gedocumenteerde zaken die je misschien interessant vindt:
- Post van Paul Randal over (zoals we onze gegevens in het eerste voorbeeld)
- Vraag op StackOverflow over (zoals we de gegevens in het tweede voorbeeld vonden). Een van de antwoorden leidt naar een post .
Bron: habr.com
