Als u het geblokkeerde procesrapport gebruikt of periodiek de deadlock-grafieken verzamelt die door SQL Server worden geleverd, zult u dit soort dingen tegenkomen:
waitresource="PAGINA: 6:3:70133"
waitresource=“KEY: 6:72057594041991168 (ce52f92a058c)“
Soms zal er meer informatie zijn in die gigantische XML die je bestudeert (deadlock-grafieken bevatten een lijst met bronnen die je helpen de namen van het object en de index te achterhalen), maar niet altijd.
Deze tekst helpt je ze te ontcijferen.
Alle informatie die hier staat, staat op verschillende plaatsen op internet, het is gewoon erg verspreid! Ik wil het allemaal samenvoegen, van DBCC PAGE tot hobt_id tot de ongedocumenteerde %%physloc%% en %%lockres%% functies.
Laten we het eerst hebben over wachten op PAGINA-vergrendelingen en dan verder gaan met SLEUTELvergrendelingen.
1) waitresource="PAGE: 6:3:70133" = Database_Id: FileId: PageNumber
Als uw verzoek wacht op een PAGE-lock, geeft SQL Server u het adres van die pagina.
Als we "PAGE: 6:3:70133" opsplitsen, krijgen we:
- database_id = 6
- gegevens_bestand_id = 3
- paginanummer = 70133
1.1) Decodeer database_id
Zoek de naam van de database met behulp van de query:
SELECT
name
FROM sys.databases
WHERE database_id=6;
GOHet is openbaar op mijn SQL-server.
1.2) Op zoek naar de naam van het gegevensbestand - als je geïnteresseerd bent
We gaan in de volgende stap data_file_id gebruiken om de tabelnaam te vinden. U kunt gewoon doorgaan naar de volgende stap, maar als u geïnteresseerd bent in de bestandsnaam, kunt u deze vinden door een query uit te voeren in de context van de gevonden database, waarbij data_file_id wordt vervangen door deze query:
USE WideWorldImporters;
GO
SELECT
name,
physical_name
FROM sys.database_files
WHERE file_id = 3;
GOIn de WideWorldImporters-database is dit een bestand met de naam WWI_UserData en ik heb het teruggezet naar C:MSSQLDATAWideWorldImporters_UserData.ndf. (Oeps, je betrapte me erop dat ik bestanden op de systeemschijf plaatste! Nee! Het is gênant).
1.3) Haal de objectnaam op van de DBCC-PAGINA
We weten nu dat pagina #70133 in datafile 3 behoort tot de WorldWideImporters database. We kunnen de inhoud van deze pagina bekijken met behulp van de ongedocumenteerde DBCC-PAGINA en traceervlag 3604.
Opmerking: ik geef er de voorkeur aan DBCC-PAGINA te gebruiken op een herstelde kopie ergens op een andere server, omdat het niet-gedocumenteerde dingen zijn. In sommige gevallen zij (ca. vertaler - de link leidt helaas nergens toe, maar te oordelen naar de url hebben we het over gefilterde 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 de object_id en index_id vinden.
Bijna klaar! Nu kunt u de tabel- en indexnamen vinden met een 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 het wachten op het slot was op de PK_Sales_OrderLines-index van de Sales.OrderLines-tabel.
Opmerking: in SQL Server 2014 en hoger kan de objectnaam ook worden gevonden met behulp van de ongedocumenteerde DMO sys.dm_db_database_page_allocations. Maar je moet elke pagina in de database doorzoeken, wat er niet erg cool uitziet voor grote databases, dus ik gebruikte DBCC PAGE.
1.4) Is het mogelijk om de gegevens op de geblokkeerde pagina te zien?
Nouuu, ja. Maar... weet je zeker dat je het echt nodig hebt?
Het is traag, zelfs op kleine tafels. Maar het is wel cool, dus aangezien je tot nu toe hebt gelezen... laten we het hebben over %%physloc%%!
%%physloc%% is een ongedocumenteerd stukje magie dat een fysieke ID retourneert voor elk item. je kunt gebruiken .
Nu we weten dat we de pagina in Sales.OrderLines wilden blokkeren, kunnen we alle gegevens in deze tabel bekijken, die is opgeslagen in gegevensbestand #3 op pagina #70133, met 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 kleine tafels. Ik heb NOLOCK aan het verzoek toegevoegd omdat we nog steeds geen garanties hebben dat de gegevens die we willen bekijken precies hetzelfde zijn als op het moment dat het slot werd ontdekt - dus we kunnen veilig vuile reads doen.
Maar hoera, de query geeft me precies de 25 rijen waarvoor onze query vocht.
Genoeg over PAGE-locks. Wat als we wachten op een SLEUTELslot?
2) waitresource="KEY: 6:72057594041991168 (ce52f92a058c)" = Database_Id, HOBT_Id (magische hash die kan worden gedecodeerd met %%lockres%% als je dat echt wilt)
Als uw zoekopdracht een indexvermelding probeert te vergrendelen en op zichzelf wordt vergrendeld, krijgt u een heel ander type adres.
Als we "6:72057594041991168 (ce52f92a058c)" in delen splitsen, krijgen we:
- database_id = 6
- hobt_id = 72057594041991168
- magische hasj = (ce52f92a058c)
2.1) Decodeer database_id
Het werkt precies hetzelfde als bij bovenstaand voorbeeld! We vinden de naam van de database met behulp van de query:
SELECT
name
FROM sys.databases
WHERE database_id=6;
GOIn mijn geval is het hetzelfde .
2.2) Decodeer hobt_id
In de context van de gevonden database moet u sys.partitions opvragen met een aantal joins die u helpen 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;
GOHet vertelt me dat het verzoek wachtte op de Application.Countries-vergrendeling met behulp van de PK_Application_Countries-index.
2.3) Nu wat %%lockres%% magie - als je wilt weten welk item vergrendeld was
Als ik echt wil weten op welke rij de vergrendeling nodig was, kan ik dat achterhalen door de tabel zelf te doorzoeken. We kunnen de ongedocumenteerde functie %%lockres%% gebruiken om een item te vinden dat overeenkomt met de magische hash.
Merk op dat deze query de hele tabel scant, en op grote tafels is dit misschien helemaal niet leuk:
SELECT
*
FROM Application.Countries (NOLOCK)
WHERE %%lockres%% = '(ce52f92a058c)';
GO
Ik heb NOLOCK toegevoegd () omdat sloten een probleem kunnen zijn. We willen alleen kijken naar wat er nu is, en niet naar wat er was toen de transactie begon - ik denk niet dat consistentie van gegevens belangrijk voor ons is.
Voila, het record waar we voor hebben gevochten!
Dankwoord en verder lezen
Ik kan me niet herinneren wie het eerst veel van deze dingen heeft beschreven, maar hier zijn twee berichten over de minst gedocumenteerde dingen die je misschien leuk vindt:
- Het bericht van Paul Randal over (zoals we onze gegevens hebben in het eerste voorbeeld)
- Vraag over StackOverflow over (zoals we de gegevens in het tweede voorbeeld vonden). Een van de antwoorden leidt naar de post .
Bron: www.habr.com