Se você usar o relatório de processo bloqueado ou coletar os gráficos de bloqueio fornecidos pelo SQL Server periodicamente, encontrará coisas como esta:
waitresource="PÁGINA: 6:3:70133"
waitresource=“KEY: 6:72057594041991168 (ce52f92a058c)“
Às vezes, haverá mais informações naquele XML gigante que você estuda (os grafos de deadlock contêm uma lista de recursos que ajudam a descobrir os nomes do objeto e do índice), mas nem sempre.
Este texto irá ajudá-lo a decifrá-los.
Todas as informações que estão aqui estão na Internet em vários lugares, simplesmente estão muito distribuídas! Quero juntar tudo, de DBCC PAGE a hobt_id às funções não documentadas %%physloc%% e %%lockres%%.
Primeiro, vamos falar sobre esperas em bloqueios de PÁGINA e, em seguida, passar para bloqueios de CHAVE.
1) waitresource="PAGE: 6:3:70133" = Database_Id: FileId: PageNumber
Se sua solicitação estiver aguardando um bloqueio de PÁGINA, o SQL Server fornecerá o endereço dessa página.
Desmembrando "PAGE: 6:3:70133", obtemos:
- banco de dados_id = 6
- data_file_id = 3
- page_number = 70133
1.1) Descriptografar database_id
Encontre o nome do banco de dados usando a consulta:
SELECT
name
FROM sys.databases
WHERE database_id=6;
GOé público no meu servidor SQL.
1.2) Procurando o nome do arquivo de dados - se você estiver interessado
Vamos usar data_file_id na próxima etapa para encontrar o nome da tabela. Você pode simplesmente pular para a próxima etapa, mas se estiver interessado no nome do arquivo, poderá encontrá-lo executando uma consulta no contexto do banco de dados encontrado, substituindo data_file_id nesta consulta:
USE WideWorldImporters;
GO
SELECT
name,
physical_name
FROM sys.database_files
WHERE file_id = 3;
GONo banco de dados WideWorldImporters, este é um arquivo chamado WWI_UserData e eu o restaurei em C:MSSQLDATAWideWorldImporters_UserData.ndf. (Oops, você me pegou colocando arquivos na unidade do sistema! Não! É embaraçoso).
1.3) Obtenha o nome do objeto da PÁGINA DBCC
Agora sabemos que a página #70133 no arquivo de dados 3 pertence ao banco de dados WorldWideImporters. Podemos examinar o conteúdo desta página usando a página DBCC não documentada e o sinalizador de rastreamento 3604.
Nota: Prefiro usar DBCC PAGE em uma cópia restaurada em algum outro servidor, porque é um material não documentado. Em alguns casos, ela (Aproximadamente. tradutor - o link, infelizmente, não leva a lugar nenhum, mas a julgar pelo URL, estamos falando 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 rolar para os resultados, você pode encontrar o object_id e o index_id.
Quase pronto! Agora você pode encontrar os nomes da tabela e do índice com uma 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 aqui vemos que a espera no bloqueio estava no índice PK_Sales_OrderLines da tabela Sales.OrderLines.
Observação: no SQL Server 2014 e superior, o nome do objeto também pode ser encontrado usando o DMO não documentado sys.dm_db_database_page_allocations. Mas você tem que consultar todas as páginas do banco de dados, o que não parece muito legal para bancos de dados grandes, então usei DBCC PAGE.
1.4) É possível visualizar os dados da página bloqueada?
Nuuu, sim. Mas... você tem certeza que precisa mesmo?
É lento mesmo em mesas pequenas. Mas é bem legal, então já que você leu até aqui... vamos falar sobre %%physloc%%!
%%physloc%% é uma peça mágica não documentada que retorna um ID físico para cada entrada. você pode usar .
Agora que sabemos que queríamos bloquear a página em Sales.OrderLines, podemos ver todos os dados desta tabela, que está armazenada no arquivo de dados nº 3 da página nº 70133, com a seguinte consulta:
Use WideWorldImporters;
GO
SELECT
sys.fn_PhysLocFormatter (%%physloc%%),
*
FROM Sales.OrderLines (NOLOCK)
WHERE sys.fn_PhysLocFormatter (%%physloc%%) like '(3:70133%'
GOComo eu disse, é lento mesmo em mesas minúsculas. Adicionei NOLOCK à solicitação porque ainda não temos garantia de que os dados que queremos ver sejam exatamente os mesmos que estavam no momento em que o bloqueio foi descoberto - para que possamos fazer leituras sujas com segurança.
Mas, viva, a consulta me retorna as 25 linhas pelas quais nossa consulta lutou
Chega de bloqueios de PÁGINA. E se estivermos esperando por um bloqueio de CHAVE?
2) waitresource="KEY: 6:72057594041991168 (ce52f92a058c)" = Database_Id, HOBT_Id (hash mágico que pode ser descriptografado com %%lockres%% se você realmente quiser)
Se sua consulta tentar bloquear uma entrada de índice e for bloqueada por conta própria, você obterá um tipo de endereço completamente diferente.
Dividindo “6:72057594041991168 (ce52f92a058c)” em partes, obtemos:
- banco de dados_id = 6
- hobt_id=72057594041991168
- hash mágico = (ce52f92a058c)
2.1) Descriptografar database_id
Funciona exatamente como no exemplo acima! Encontramos o nome do banco de dados usando a consulta:
SELECT
name
FROM sys.databases
WHERE database_id=6;
GONo meu caso é isso mesmo .
2.2) Descriptografar hobt_id
No contexto do banco de dados encontrado, você precisa consultar sys.partitions com algumas junções que ajudarão a determinar os nomes da tabela 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;
GOEle me diz que a solicitação estava aguardando o bloqueio Application.Countries usando o índice PK_Application_Countries.
2.3) Agora, para alguma mágica %%lockres%% - se você quiser descobrir qual entrada foi bloqueada
Se eu realmente quiser saber em qual linha o bloqueio foi necessário, posso descobrir consultando a própria tabela. Podemos usar a função %%lockres%% não documentada para encontrar uma entrada que corresponda ao hash mágico.
Observe que esta consulta examinará a tabela inteira e, em tabelas grandes, isso pode não ser nada divertido:
SELECT
*
FROM Application.Countries (NOLOCK)
WHERE %%lockres%% = '(ce52f92a058c)';
GO
Eu adicionei NOLOCK () porque os bloqueios podem ser um problema. Queremos apenas ver o que existe agora, e não o que existia quando a transação começou - não acho que a consistência dos dados seja importante para nós.
Voila, o recorde pelo qual lutamos!
Agradecimentos e leitura adicional
Não me lembro quem primeiro descreveu muitas dessas coisas, mas aqui estão dois posts sobre as coisas menos documentadas que você pode gostar:
- Post de Paul Randal sobre (como temos nossos dados no primeiro exemplo)
- Pergunta no StackOverflow sobre (como encontramos os dados no segundo exemplo). Uma das respostas leva ao post .
Fonte: habr.com