Si utilitzeu l'informe de procés bloquejat o recopilau els gràfics de bloqueig proporcionats per SQL Server periòdicament, trobareu coses com aquestes:
waitresource="PÀGINA: 6:3:70133"
waitresource=“KEY: 6:72057594041991168 (ce52f92a058c)“
De vegades, hi haurà més informació en aquest XML gegant que estudieu (els gràfics de bloqueig contenen una llista de recursos que us ajudaran a esbrinar els noms de l'objecte i l'índex), però no sempre.
Aquest text us ajudarà a desxifrar-los.
Tota la informació que hi ha aquí està a Internet en diversos llocs, simplement està molt distribuïda! Vull combinar-ho tot, des de la PÀGINA DBCC fins a hobt_id fins a les funcions no documentades %%physloc%% i %%lockres%%.
Primer, parlem de les esperes als bloquejos de PÀGINA i, a continuació, passem als bloquejos de CLAU.
1) waitresource="PAGE: 6:3:70133" = Database_Id: FileId: PageNumber
Si la vostra sol·licitud està pendent d'un bloqueig de PÀGINA, SQL Server us donarà l'adreça d'aquesta pàgina.
Desglossant "PÀGINA: 6:3:70133" obtenim:
- database_id = 6
- data_file_id = 3
- número_pàgina = 70133
1.1) Desxifrar database_id
Trobeu el nom de la base de dades mitjançant la consulta:
SELECT
name
FROM sys.databases
WHERE database_id=6;
GOÉs públic al meu servidor SQL.
1.2) Cerqueu el nom del fitxer de dades - si us interessa
Utilitzarem data_file_id al següent pas per trobar el nom de la taula. Només podeu passar al pas següent, però si esteu interessats en el nom del fitxer, podeu trobar-lo executant una consulta en el context de la base de dades trobada, substituint data_file_id en aquesta consulta:
USE WideWorldImporters;
GO
SELECT
name,
physical_name
FROM sys.database_files
WHERE file_id = 3;
GOA la base de dades WideWorldImporters, aquest és un fitxer anomenat WWI_UserData i el tinc restaurat a C:MSSQLDATAWideWorldImporters_UserData.ndf. (Vaja, m'has atrapat posant fitxers a la unitat del sistema! No! És vergonyós).
1.3) Obteniu el nom de l'objecte de DBCC PAGE
Ara sabem que la pàgina #70133 del fitxer de dades 3 pertany a la base de dades WorldWideImporters. Podem mirar el contingut d'aquesta pàgina utilitzant la PÀGINA DBCC no documentada i el senyalador de traça 3604.
Nota: prefereixo utilitzar DBCC PAGE en una còpia restaurada en algun lloc d'un altre servidor, perquè són coses no documentades. En alguns casos, ella (aprox. traductor: l'enllaç, malauradament, no porta enlloc, però a jutjar per l'URL, estem parlant d'índexs filtrats).
/* 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
En desplaçar-vos als resultats, podeu trobar object_id i index_id.
Quasi fet! Ara podeu trobar els noms de la taula i l'índex amb una consulta:
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;
GOI aquí veiem que l'espera al bloqueig estava a l'índex PK_Sales_OrderLines de la taula Sales.OrderLines.
Nota: a SQL Server 2014 i posteriors, el nom de l'objecte també es pot trobar mitjançant el DMO no documentat sys.dm_db_database_page_allocations. Però heu de consultar totes les pàgines de la base de dades, cosa que no sembla genial per a bases de dades grans, així que vaig utilitzar DBCC PAGE.
1.4) És possible veure les dades a la pàgina bloquejada?
Nuuu, sí. Però... estàs segur que realment ho necessites?
És lent fins i tot en taules petites. Però és genial, així que com que has llegit fins aquí... parlem de %%physloc%%!
%%physloc%% és una peça de màgia no documentada que retorna un identificador físic per a cada entrada. pots fer servir .
Ara que sabem que volíem bloquejar la pàgina a Sales.OrderLines, podem mirar totes les dades d'aquesta taula, que s'emmagatzemen al fitxer de dades #3 a la pàgina #70133, amb la consulta següent:
Use WideWorldImporters;
GO
SELECT
sys.fn_PhysLocFormatter (%%physloc%%),
*
FROM Sales.OrderLines (NOLOCK)
WHERE sys.fn_PhysLocFormatter (%%physloc%%) like '(3:70133%'
GOCom he dit, és lent fins i tot en taules petites. He afegit NOLOCK a la sol·licitud perquè encara no tenim garanties que les dades que volem consultar siguin exactament les mateixes que en el moment en què es va descobrir el bloqueig, de manera que podem fer lectures brutes amb seguretat.
Però, hurra, la consulta em retorna les 25 files per les quals va lluitar la nostra consulta.
Ja n'hi ha prou amb els bloquejos de PAGE. Què passa si estem esperant un pany de CLAU?
2) waitresource="KEY: 6:72057594041991168 (ce52f92a058c)" = Database_Id, HOBT_Id (hash màgic que es pot desxifrar amb %%lockres%% si realment ho voleu)
Si la vostra consulta intenta bloquejar-se en una entrada d'índex i es bloqueja per si mateixa, obtindreu un tipus d'adreça completament diferent.
Dividint "6:72057594041991168 (ce52f92a058c)" en parts, obtenim:
- database_id = 6
- hobt_id = 72057594041991168
- hash màgic = (ce52f92a058c)
2.1) Desxifrar database_id
Funciona exactament igual que amb l'exemple anterior! Trobem el nom de la base de dades mitjançant la consulta:
SELECT
name
FROM sys.databases
WHERE database_id=6;
GOEn el meu cas, és el mateix .
2.2) Desxifrar hobt_id
En el context de la base de dades trobada, heu de consultar sys.partitions amb un parell d'unions que us ajudaran a determinar els noms de la taula i l'índex...
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;
GOEm diu que la sol·licitud estava esperant al bloqueig de l'aplicació.Països mitjançant l'índex PK_Application_Countries.
2.3) Ara per una mica de màgia %%lockres%% - si voleu esbrinar quina entrada s'ha bloquejat
Si realment vull saber en quina fila es necessitava el bloqueig, ho puc saber consultant la mateixa taula. Podem utilitzar la funció %%lockres%% no documentada per trobar una entrada que coincideixi amb el hash màgic.
Tingueu en compte que aquesta consulta escanejarà tota la taula i, en taules grans, això pot no ser gens divertit:
SELECT
*
FROM Application.Countries (NOLOCK)
WHERE %%lockres%% = '(ce52f92a058c)';
GO
He afegit NOLOCK () perquè els panys poden ser un problema. Només volem mirar què hi ha ara, i no què hi havia quan va començar la transacció; no crec que la coherència de les dades sigui important per a nosaltres.
Voila, el rècord pel qual hem lluitat!
Agraïments i lectura posterior
No recordo qui va descriure per primera vegada moltes d'aquestes coses, però aquí teniu dues publicacions sobre les coses menys documentades que us poden agradar:
- Publicació de Paul Randal sobre (com tenim les nostres dades al primer exemple)
- Pregunta a StackOverflow sobre (tal com hem trobat les dades al segon exemple). Una de les respostes porta a la publicació .
Font: www.habr.com