Si utiliza el informe de bloqueos (blocked process report) o recopila gráficos de deadlocks proporcionados por SQL Server de manera periódica, se encontrará con cosas como estas:
waitresource="PAGE: 6:3:70133"
waitresource="KEY: 6:72057594041991168 (ce52f92a058c)"
A veces, en ese gigantesco XML que está examinando, habrá más información (los gráficos de deadlocks contienen una lista de recursos que ayudan a identificar los nombres de los objetos y los índices), pero no siempre.
Este texto le ayudará a descifrarlas.
Toda la información que aquí hay está en internet en diferentes lugares, ¡está muy dispersa! Quiero reunir todo: desde DBCC PAGE hasta hobt_id y las funciones no documentadas %%physloc%% y %%lockres%%.
Primero hablemos sobre las esperas en bloqueos de PAGE, y luego pasaremos a los bloqueos de KEY.
1) waitresource="PAGE: 6:3:70133" = Database_Id: FileId: PageNumber
Si su consulta está esperando en un bloqueo de PAGE, SQL Server le proporcionará la dirección de esta página.
Desglosando "PAGE: 6:3:70133" obtenemos:
- database_id = 6
- data_file_id = 3
- page_number = 70133
1.1) Descifrando database_id
Encontraremos el nombre de la base de datos con la siguiente consulta:
SELECT
name
FROM sys.databases
WHERE database_id=6;
GOEs una base de datos pública en mi SQL Server.
1.2) Buscando el nombre del archivo de datos — si le interesa
Vamos a usar data_file_id en el siguiente paso para encontrar el nombre de la tabla. Puede simplemente avanzar al siguiente paso, pero si está interesado en el nombre del archivo, puede encontrarlo ejecutando una consulta en el contexto de la base de datos encontrada, sustituyendo data_file_id en esta consulta:
USE WideWorldImporters;
GO
SELECT
name,
physical_name
FROM sys.database_files
WHERE file_id = 3;
GOEn la base de datos WideWorldImporters este archivo se llama WWI_UserData y está restaurado en C:MSSQLDATAWideWorldImporters_UserData.ndf. (Ups, me atrapó mirando cómo coloco archivos en el disco del sistema. ¡No! Salió un poco incómodo).
1.3) Obteniendo el nombre del objeto desde DBCC PAGE
Ahora sabemos que la página #70133 en el archivo de datos 3 pertenece a la base de datos WorldWideImporters. Podemos ver el contenido de esta página utilizando el no documentado DBCC PAGE y el trace flag 3604.
Nota: Prefiero usar DBCC PAGE en una copia restaurada de un respaldo en algún otro servidor, porque esta es una funcionalidad no documentada. En algunos casos, puede (nota del traductor — el enlace, desafortunadamente, no lleva a ninguna parte, pero por la URL parece referirse a índices filtrados.).
/* 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 Desplazándose hacia los resultados, se puede encontrar object_id e index_id.
¡Casi listo! Ahora puedes encontrar los nombres de la tabla y del índice con la 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;
GOY aquí vemos que la espera por bloqueo fue en el índice PK_Sales_OrderLines de la tabla Sales.OrderLines.
Nota: en SQL Server 2014 y versiones posteriores, el nombre del objeto también se puede encontrar mediante el DMO no documentado sys.dm_db_database_page_allocations. Pero tendrás que consultar cada página en la base de datos, lo que no es muy atractivo para bases de datos grandes, así que utilicé DBCC PAGE.
1.4) ¿Podemos ver los datos en esa página que estaba bloqueada?
Bueno, sí. Pero... ¿estás seguro de que realmente lo necesitas?
Es lento incluso en tablas pequeñas. Pero es como interesante, así que, ya que has leído hasta aquí... hablemos de %%physloc%%!
%%physloc%% es un pedazo de magia no documentada que devuelve el identificador físico para cada registro. Puedes usar .
Ahora que sabemos que queríamos bloquear la página en Sales.OrderLines, podemos ver todos los datos en esta tabla que se almacenan en el archivo de datos #3 en la página #70133, con la siguiente consulta:
Use WideWorldImporters;
GO
SELECT
sys.fn_PhysLocFormatter (%%physloc%%),
*
FROM Sales.OrderLines (NOLOCK)
WHERE sys.fn_PhysLocFormatter (%%physloc%%) like '(3:70133%'
GOComo dije, es lento incluso en tablas diminutas. Añadí NOLOCK a la consulta porque no tenemos garantía de que los datos que queremos ver son exactamente los mismos que estaban en el momento en que se detectó el bloqueo, así que podemos hacer lecturas sucias.
Pero, hurra, la consulta me devuelve esas 25 filas por las que nuestra consulta estaba luchando
Suficiente sobre bloqueos de PÁGINA. ¿Qué pasa si estamos esperando un bloqueo de CLAVE?
2) waitresource="KEY: 6:72057594041991168 (ce52f92a058c)" = Database_Id, HOBT_Id (un hash mágico que se puede descifrar con %%lockres%%, si realmente quieres)
Si tu consulta está intentando bloquear un registro en el índice y queda bloqueada a su vez, obtienes un tipo de dirección completamente diferente.
Al descomponer "6:72057594041991168 (ce52f92a058c)" en partes, obtenemos:
- database_id = 6
- hobt_id = 72057594041991168
- hash mágico = (ce52f92a058c)
2.1) Desciframos database_id
Esto funciona exactamente igual que el ejemplo anterior. Encontramos el nombre de la base de datos mediante una consulta:
SELECT
name
FROM sys.databases
WHERE database_id=6;
GOEn mi caso, sigue siendo la misma .
2.2) Desciframos hobt_id
En el contexto de la base de datos encontrada, es necesario realizar una consulta a sys.partitions con un par de joins que ayudarán a determinar los nombres de la tabla y el índice…
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;
GOMe dice que la consulta estaba esperando por el bloqueo Application.Countries, utilizando el índice PK_Application_Countries.
2.3) Ahora un poco de magia %%lockres%% — si quieres averiguar qué fila fue bloqueada
Si realmente quiero saber en qué fila se necesitaba el bloqueo, puedo averiguarlo con una consulta a la misma tabla. Podemos usar una función no documentada %%lockres%% para encontrar el registro que coincide con el hash mágico.
Tenga en cuenta que esta consulta escaneará toda la tabla, y en tablas grandes esto puede no ser muy divertido:
SELECT
*
FROM Application.Countries (NOLOCK)
WHERE %%lockres%% = '(ce52f92a058c)';
GO He añadido NOLOCK () porque los bloqueos pueden convertirse en un problema. Queremos simplemente ver qué hay ahora, no qué había cuando comenzó la transacción — no creo que la consistencia de los datos sea importante para nosotros.
¡Voilá, el registro por el que luchamos!
Agradecimientos y lectura adicional
No recuerdo quién describió primero muchas de estas cosas, pero aquí hay dos publicaciones sobre las cuestiones menos documentadas que podrían interesarte:
- Publicación de Paul Randal sobre (como encontramos nuestros datos en el primer ejemplo)
- Pregunta en StackOverflow sobre (como encontramos los datos en el segundo ejemplo). Una de las respuestas lleva a la publicación .
Fuente: habr.com
