Se usa o informe do proceso bloqueado ou recolle os gráficos de bloqueo proporcionados por SQL Server periodicamente, atopará cousas como esta:
waitresource="PÁXINA: 6:3:70133"
waitresource=“KEY: 6:72057594041991168 (ce52f92a058c)“
Ás veces, haberá máis información nese XML xigante que estudas (os gráficos de bloqueo conteñen unha lista de recursos que che axudan a descubrir os nomes do obxecto e do índice), pero non sempre.
Este texto axudarache a descifralos.
Toda a información que hai aquí está en Internet en varios lugares, simplemente está moi distribuída! Quero xuntalo todo, dende DBCC PAGE ata hobt_id ata as funcións non documentadas %%physloc%% e %%lockres%%.
Primeiro, imos falar sobre as esperas nos bloqueos de PAGE e, a continuación, pasemos aos bloqueos de KEY.
1) waitresource="PAGE: 6:3:70133" = Database_Id: FileId: PageNumber
Se a túa solicitude está agardando nun bloqueo de PÁXINA, SQL Server darache o enderezo desa páxina.
Desglosando "PAGE: 6:3:70133" obtemos:
- database_id = 6
- data_file_id = 3
- número_páxina = 70133
1.1) Descifrar database_id
Busca o nome da base de datos mediante a consulta:
SELECT
name
FROM sys.databases
WHERE database_id=6;
GOÉ público no meu servidor SQL.
1.2) Buscando o nome do ficheiro de datos - se estás interesado
Imos usar data_file_id no seguinte paso para atopar o nome da táboa. Podes pasar ao seguinte paso, pero se estás interesado no nome do ficheiro, podes atopalo executando unha consulta no contexto da base de datos atopada, substituíndo data_file_id nesta consulta:
USE WideWorldImporters;
GO
SELECT
name,
physical_name
FROM sys.database_files
WHERE file_id = 3;
GONa base de datos WideWorldImporters este é un ficheiro chamado WWI_UserData e téñoo restaurado en C:MSSQLDATAWideWorldImporters_UserData.ndf. (Vaia, captachesme poñendo ficheiros na unidade do sistema! Non! É vergonzoso).
1.3) Obter o nome do obxecto de DBCC PAGE
Agora sabemos que a páxina #70133 do ficheiro de datos 3 pertence á base de datos WorldWideImporters. Podemos ver o contido desta páxina usando a DBCC PAGE non documentada e a marca de rastrexo 3604.
Nota: prefiro usar DBCC PAGE nunha copia restaurada nalgún lugar doutro servidor, porque é cousas non documentadas. Nalgúns casos, ela (aprox. tradutor: a ligazón, por desgraza, non leva a ningún lado, pero a xulgar polo URL, estamos a falar de í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
Ao desprazarse ata os resultados, podes atopar o object_id e o index_id.
Casi feito! Agora podes atopar os nomes de táboa e índice cunha 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;
GOE aquí vemos que a espera no bloqueo estaba no índice PK_Sales_OrderLines da táboa Sales.OrderLines.
Nota: en SQL Server 2014 e superior, o nome do obxecto tamén se pode atopar usando o DMO non documentado sys.dm_db_database_page_allocations. Pero tes que consultar todas as páxinas da base de datos, o que non parece moi bo para grandes bases de datos, polo que usei DBCC PAGE.
1.4) É posible ver os datos da páxina que foi bloqueada?
Nuuu, si. Pero... estás seguro de que realmente o necesitas?
É lento mesmo en mesas pequenas. Pero é algo xenial, así que xa que leches ata aquí...falemos de %%physloc%%!
%%physloc%% é unha peza de maxia non documentada que devolve unha identificación física para cada entrada. podes usar .
Agora que sabemos que queriamos bloquear a páxina en Sales.OrderLines, podemos ver todos os datos desta táboa, que se almacena no ficheiro de datos #3 na páxina #70133, coa seguinte consulta:
Use WideWorldImporters;
GO
SELECT
sys.fn_PhysLocFormatter (%%physloc%%),
*
FROM Sales.OrderLines (NOLOCK)
WHERE sys.fn_PhysLocFormatter (%%physloc%%) like '(3:70133%'
GOComo dixen, é lento mesmo en mesas pequenas. Engadín NOLOCK á solicitude porque aínda non temos garantía de que os datos que queremos consultar sexan exactamente os mesmos que estaban no momento en que se descubriu o bloqueo, polo que podemos facer lecturas sucias con seguridade.
Pero, oh, a consulta devólveme as 25 filas polas que pelexou a nosa consulta.
Basta con bloqueos de PAGE. E se estamos agardando por un bloqueo da chave?
2) waitresource="KEY: 6:72057594041991168 (ce52f92a058c)" = Database_Id, HOBT_Id (hash máxico que se pode descifrar con %%lockres%% se realmente o queres)
Se a túa consulta intenta bloquear unha entrada de índice e bloquearase por si mesma, obterás un tipo de enderezo completamente diferente.
Dividindo "6:72057594041991168 (ce52f92a058c)" en partes, obtemos:
- database_id = 6
- hobt_id = 72057594041991168
- hash máxico = (ce52f92a058c)
2.1) Descifrar database_id
Funciona exactamente igual que co exemplo anterior! Buscamos o nome da base de datos mediante a consulta:
SELECT
name
FROM sys.databases
WHERE database_id=6;
GONo meu caso, é o mesmo .
2.2) Descifrar hobt_id
No contexto da base de datos atopada, cómpre consultar sys.partitions cun par de unións que axudarán a determinar os nomes da táboa e do í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;
GODime que a solicitude estaba agardando na aplicación. Bloqueo de países mediante o índice PK_Application_Countries.
2.3) Agora por algo de maxia %%lockres%% - se queres saber que entrada foi bloqueada
Se realmente quero saber en que fila se necesitaba o bloqueo, podo descubrilo consultando a propia táboa. Podemos usar a función %%lockres%% non documentada para atopar unha entrada que coincida co hash máxico.
Teña en conta que esta consulta analizará toda a táboa e, en táboas grandes, pode non ser divertido:
SELECT
*
FROM Application.Countries (NOLOCK)
WHERE %%lockres%% = '(ce52f92a058c)';
GO
Engadín NOLOCK () porque as pechaduras poden ser un problema. Só queremos ver o que hai agora, e non o que había cando comezou a transacción. Non creo que a coherencia dos datos sexa importante para nós.
Voila, o disco polo que loitamos!
Agradecementos e lecturas posteriores
Non recordo quen describiu por primeira vez moitas destas cousas, pero aquí tes dúas publicacións sobre as cousas menos documentadas que che poden gustar:
- Publicación de Paul Randal sobre (como temos os nosos datos no primeiro exemplo)
- Pregunta sobre StackOverflow sobre (como atopamos os datos no segundo exemplo). Unha das respostas leva á publicación .
Fonte: www.habr.com