Če uporabljate poročilo o blokiranem procesu ali občasno zbirate grafe zastoja, ki jih nudi SQL Server, boste naleteli na takšne stvari:
waitresource=“STRAN: 6:3:70133“
waitresource=“KEY: 6:72057594041991168 (ce52f92a058c)“
Včasih bo v tem velikanskem XML-ju, ki ga preučujete, več informacij (grafi zastoja vsebujejo seznam virov, ki vam pomagajo najti imena objektov in indeksov), vendar ne vedno.
To besedilo vam jih bo pomagalo razvozlati.
Vse informacije, ki so tukaj, so na internetu na različnih mestih, le zelo so porazdeljene! Želim združiti vse - od DBCC PAGE do hobt_id in do nedokumentiranih funkcij %%physloc%% in %%lockres%%.
Najprej se pogovorimo o čakanju na zaklepe STRANI, nato pa preidimo na zaklepanja KEY.
1) waitresource=“PAGE: 6:3:70133” = Database_Id: FileId: PageNumber
Če vaša poizvedba čaka na zaklepanje strani, vam bo SQL Server dal naslov te strani.
Če razdelimo »PAGE: 6:3:70133«, dobimo:
- ID_baze = 6
- ID_podatkovne_datoteke = 3
- številka_strani = 70133
1.1) Dešifrirajte database_id
Poiščimo ime baze podatkov s poizvedbo:
SELECT
name
FROM sys.databases
WHERE database_id=6;
GOTo je javno na mojem strežniku SQL.
1.2) Iskanje imena podatkovne datoteke - če vas zanima
V naslednjem koraku bomo uporabili data_file_id za iskanje imena tabele. Lahko preprosto preskočite na naslednji korak, če pa vas zanima ime datoteke, ga lahko poiščete tako, da zaženete poizvedbo v kontekstu najdene zbirke podatkov in v tej poizvedbi nadomestite data_file_id:
USE WideWorldImporters;
GO
SELECT
name,
physical_name
FROM sys.database_files
WHERE file_id = 3;
GOV bazi podatkov WideWorldImporters je to datoteka z imenom WWI_UserData in obnovil sem jo v C:MSSQLDATAWideWorldImporters_UserData.ndf. (Ojoj, ujeli ste me, ko sem spravljal datoteke na sistemski disk! Ne! To je bilo nerodno).
1.3) Pridobite ime objekta iz DBCC PAGE
Zdaj vemo, da stran #70133 v podatkovni datoteki 3 pripada bazi podatkov WorldWideImporters. Vsebino te strani si lahko ogledamo z nedokumentirano stranjo DBCC PAGE in zastavico za sledenje 3604.
Opomba: raje uporabljam DBCC PAGE na obnovljeni kopiji iz varnostne kopije nekje na drugem strežniku, ker je to nedokumentirana stvar. V nekaterih primerih ona (pribl. prevajalnik - povezava žal ne vodi nikamor, vendar sodeč po url-ju govorimo o filtriranih indeksih).
/* 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
Če se pomikate po rezultatih, lahko najdete object_id in index_id.
Skoraj končano! Zdaj lahko najdete imena tabel in indeksov s poizvedbo:
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;
GOIn zdaj vidimo, da je bilo čakanje na zaklepanje na indeksu PK_Sales_OrderLines tabele Sales.OrderLines.
Opomba: V SQL Server 2014 in novejših je ime objekta mogoče najti tudi z uporabo nedokumentiranega DMO sys.dm_db_database_page_allocations. Toda poizvedovati morate po vsaki strani v zbirki podatkov, kar ni videti zelo kul za velike baze podatkov, zato sem uporabil DBCC PAGE.
1.4) Ali je mogoče videti podatke na strani, ki je bila blokirana?
No ja. Ampak ... ste prepričani, da ga res potrebujete?
Tudi na majhnih mizah gre počasi. Ampak to je nekako kul, tako da, ker ste prebrali tako daleč ... pogovorimo se o %%physloc%%!
%%physloc%% je nedokumentiran del čarovnije, ki vrne fizični identifikator za vsak vnos. lahko uporabiš .
Zdaj, ko vemo, da smo želeli stran zakleniti v Sales.OrderLines, si lahko ogledamo vse podatke v tej tabeli, ki je shranjena v podatkovni datoteki #3 na strani #70133, s to poizvedbo:
Use WideWorldImporters;
GO
SELECT
sys.fn_PhysLocFormatter (%%physloc%%),
*
FROM Sales.OrderLines (NOLOCK)
WHERE sys.fn_PhysLocFormatter (%%physloc%%) like '(3:70133%'
GOKot sem rekel, je počasen tudi na majhnih mizah. Zahtevi sem dodal NOLOCK, ker še vedno nimamo nobenega zagotovila, da so podatki, ki si jih želimo ogledati, popolnoma enaki, kot so bili ob zaznavi zaklepanja – tako da lahko varno izvajamo umazano branje.
Ampak, hura, poizvedba mi vrne istih 25 vrstic, za katere se je borila naša poizvedba
Dovolj o zaklepanju STRANI. Kaj pa, če čakamo na ključavnico KLJUČ?
2) waitresource=»KEY: 6:72057594041991168 (ce52f92a058c)« = Database_Id, HOBT_Id (magični hash, ki ga je mogoče dešifrirati z %%lockres%%, če to res želite)
Če vaša poizvedba poskuša zakleniti zapis v indeksu in se sama zaklene, dobite popolnoma drugačno vrsto naslova.
Če razdelimo »6:72057594041991168 (ce52f92a058c)« na dele, dobimo:
- ID_baze = 6
- hobt_id = 72057594041991168
- čarobni hash = (ce52f92a058c)
2.1) Dešifrirajte database_id
To deluje popolnoma enako kot zgornji primer! Poiščite ime baze podatkov s poizvedbo:
SELECT
name
FROM sys.databases
WHERE database_id=6;
GOV mojem primeru je še vedno isto .
2.2) Dešifrirajte hobt_id
V kontekstu najdene baze podatkov morate izvesti poizvedbo do sys.partitions s parom združevanj, ki bodo pomagali določiti imena tabele in indeksa ...
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;
GOPove mi, da je zahteva čakala na zaklep Application.Countries z uporabo indeksa PK_Application_Countries.
2.3) Zdaj malo čarovnije %%lockres%% - če želite izvedeti, kateri vnos je bil zaklenjen
Če res želim vedeti, na kateri vrstici je bila ključavnica, lahko to ugotovim tako, da poizvedujem po sami tabeli. Za iskanje vnosa, ki se ujema s čarobnim zgoščevanjem, lahko uporabimo nedokumentirano funkcijo %%lockres%%.
Upoštevajte, da bo ta poizvedba pregledala celotno tabelo in na velikih tabelah to morda sploh ne bo zabavno:
SELECT
*
FROM Application.Countries (NOLOCK)
WHERE %%lockres%% = '(ce52f92a058c)';
GO
Dodal sem NOLOCK (), ker lahko blokade postanejo problem. Želimo samo pogledati, kaj je tam zdaj, in ne, kaj je bilo tam, ko se je transakcija začela – mislim, da nam doslednost podatkov ni pomembna.
Voila, rekord, za katerega smo se borili!
Zahvala in nadaljnje branje
Ne spomnim se, kdo je prvi opisal veliko teh stvari, toda tukaj sta dve objavi o najmanj dokumentiranih stvareh, ki bi vam lahko bile všeč:
- Objava Paula Randala o (kot imamo podatke v prvem primeru)
- Vprašanje na StackOverflow o (kot smo našli podatke v drugem primeru). Eden od odgovorov vodi do objave .
Vir: www.habr.com