Pokud používáte zprávu o zablokovaném procesu nebo pravidelně sbíráte grafy zablokování poskytované SQL Serverem, setkáte se s věcmi jako je toto:
waitresource="STRÁNKA: 6:3:70133"
waitresource=“KEY: 6:72057594041991168 (ce52f92a058c)“
Někdy bude v tom obřím XML, který studujete, více informací (grafy uváznutí obsahují seznam zdrojů, které vám pomohou zjistit názvy objektů a indexů), ale ne vždy.
Tento text vám je pomůže rozluštit.
Všechny informace, které zde jsou, jsou na internetu na různých místech, jsou prostě velmi distribuované! Chci to dát dohromady, od DBCC PAGE přes hobt_id až po nezdokumentované funkce %%physloc%% a %%lockres%%.
Nejprve si povíme něco o čekání na uzamčení PAGE a pak přejdeme k zámkům KEY.
1) waitresource="PAGE: 6:3:70133" = ID_databáze: FileId: PageNumber
Pokud váš požadavek čeká na uzamčení PAGE, SQL Server vám poskytne adresu této stránky.
Rozdělením "PAGE: 6:3:70133" dostaneme:
- Database_id = 6
- data_file_id = 3
- číslo_stránky = 70133
1.1) Dešifrujte database_id
Najděte název databáze pomocí dotazu:
SELECT
name
FROM sys.databases
WHERE database_id=6;
GOJe to veřejné na mém SQL serveru.
1.2) Hledání názvu datového souboru - pokud máte zájem
V dalším kroku použijeme data_file_id k nalezení názvu tabulky. Můžete jen přeskočit na další krok, ale pokud vás zajímá název souboru, můžete jej najít spuštěním dotazu v kontextu nalezené databáze a do tohoto dotazu dosadit data_file_id:
USE WideWorldImporters;
GO
SELECT
name,
physical_name
FROM sys.database_files
WHERE file_id = 3;
GOV databázi WideWorldImporters je to soubor s názvem WWI_UserData a mám ho obnovený do C:MSSQLDATAWideWorldImporters_UserData.ndf. (Jejda, přistihli jste mě, jak dávám soubory na systémový disk! Ne! Je to trapné).
1.3) Získejte název objektu ze stránky DBCC
Nyní víme, že stránka #70133 v datovém souboru 3 patří do databáze WorldWideImporters. Na obsah této stránky se můžeme podívat pomocí nezdokumentované DBCC PAGE a příznaku trasování 3604.
Poznámka: Dávám přednost použití DBCC PAGE na obnovené kopii někde na jiném serveru, protože jde o nezdokumentované věci. V některých případech ona (Cca. překladač - odkaz bohužel nikam nevede, ale soudě podle url se bavíme o filtrovaných indexech).
/* 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
Posouváním k výsledkům můžete najít object_id a index_id.
Skoro hotovo! Nyní můžete najít názvy tabulek a indexů pomocí dotazu:
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;
GOA zde vidíme, že čekání na zámek bylo na indexu PK_Sales_OrderLines tabulky Sales.OrderLines.
Poznámka: V SQL Server 2014 a novějších lze název objektu nalézt také pomocí nezdokumentovaného DMO sys.dm_db_database_page_allocations. Ale musíte se dotazovat na každou stránku v databázi, což u velkých databází nevypadá moc cool, takže jsem použil DBCC PAGE.
1.4) Je možné vidět data na stránce, která byla zablokována?
Nuuu, ano. Ale... jsi si jistý, že to opravdu potřebuješ?
Je to pomalé i na malých stolech. Ale je to tak trochu cool, takže když jste dočetli až sem... pojďme mluvit o %%physloc%%!
%%physloc%% je nezdokumentovaný kousek magie, který vrací fyzické ID pro každý záznam. Můžeš použít .
Nyní, když víme, že jsme chtěli zablokovat stránku v Sales.OrderLines, můžeme se podívat na všechna data v této tabulce, která je uložena v datovém souboru #3 na stránce #70133, pomocí následujícího dotazu:
Use WideWorldImporters;
GO
SELECT
sys.fn_PhysLocFormatter (%%physloc%%),
*
FROM Sales.OrderLines (NOLOCK)
WHERE sys.fn_PhysLocFormatter (%%physloc%%) like '(3:70133%'
GOJak jsem řekl, je to pomalé i na malinkých stolech. K požadavku jsem přidal NOLOCK, protože stále nemáme žádné záruky, že data, na která se chceme podívat, jsou přesně stejná jako v době, kdy byl zámek objeven – takže můžeme bezpečně provádět špinavé čtení.
Ale hurá, dotaz mi vrátí přesně 25 řádků, o které náš dotaz bojoval.
Dost o zámcích PAGE. Co když čekáme na zámek KLÍČE?
2) waitresource="KEY: 6:72057594041991168 (ce52f92a058c)" = Database_Id, HOBT_Id (magický hash, který lze dešifrovat pomocí %%lockres%%, pokud opravdu chcete)
Pokud se váš dotaz pokusí uzamknout položku rejstříku a sám se uzamkne, získáte úplně jiný typ adresy.
Rozdělením „6:72057594041991168 (ce52f92a058c)“ na části získáme:
- Database_id = 6
- hobt_id = 72057594041991168
- magický hash = (ce52f92a058c)
2.1) Dešifrujte database_id
Funguje to úplně stejně jako ve výše uvedeném příkladu! Název databáze zjistíme pomocí dotazu:
SELECT
name
FROM sys.databases
WHERE database_id=6;
GOV mém případě je to stejné .
2.2) Dešifrujte hobt_id
V kontextu nalezené databáze se musíte dotazovat sys.partitions s několika spojeními, které pomohou určit názvy tabulky a indexu ...
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;
GOŘíká mi to, že požadavek čekal na zámek Application.Countries pomocí indexu PK_Application_Countries.
2.3) Nyní trochu magie %%lockres%% - pokud chcete zjistit, který záznam byl zamčen
Pokud opravdu chci vědět, na kterém řádku byl zámek potřeba, mohu to zjistit dotazem na samotnou tabulku. Můžeme použít nezdokumentovanou funkci %%lockres%% k nalezení záznamu, který odpovídá magickému hash.
Všimněte si, že tento dotaz prohledá celou tabulku a na velkých tabulkách to nemusí být vůbec zábavné:
SELECT
*
FROM Application.Countries (NOLOCK)
WHERE %%lockres%% = '(ce52f92a058c)';
GO
Přidal jsem NOLOCK (), protože zámky mohou být problémem. Chceme se jen podívat na to, co tam je teď, a ne na to, co tam bylo, když transakce začala – nemyslím si, že je pro nás konzistence dat důležitá.
Voila, rekord, o který jsme bojovali!
Poděkování a další čtení
Nepamatuji si, kdo první popsal mnoho z těchto věcí, ale zde jsou dva příspěvky o nejméně zdokumentovaných věcech, které by se vám mohly líbit:
- Příspěvek Paula Randala o (jak máme naše data v prvním příkladu)
- Otázka na StackOverflow o (jak jsme našli data v druhém příkladu). Jedna z odpovědí vede k příspěvku .
Zdroj: www.habr.com