Clever Geek Handbook

Descriptografar chave e página WaitResource em impasses e bloqueios

Se você usar um relatório de processo bloqueado ou coletar gráficos de deadlock fornecidos pelo SQL Server de tempos em tempos, você se deparará com o seguinte:



waitresource = “PÁGINA: 6: 3: 70133“



waitresource = “CHAVE: 6: 72057594041991168 (ce52f92a058c)“


Às vezes, haverá mais informações naquele XML gigante que você está aprendendo (os gráficos em conflito contêm uma lista de recursos que ajuda a descobrir os nomes dos objetos e índices), mas nem sempre.



Este texto ajudará você a decifrá-los.



Toda a informação que está aqui está na Internet em vários locais, é simplesmente altamente distribuída! Quero colocar tudo junto - de DBCC PAGE a hobt_id e funções %% physloc %% e %% lockres %% não documentadas.



Primeiro, vamos falar sobre a espera de bloqueios PAGE e, em seguida, passar para os bloqueios KEY.



1) waitresource = “PAGE: 6: 3: 70133” = Database_Id: FileId: PageNumber



Se sua consulta estiver aguardando um bloqueio de PÁGINA, o SQL Server fornecerá a URL dessa página.



Dividindo "PÁGINA: 6: 3: 70133", obtemos:



  • database_id = 6
  • data_file_id = 3
  • page_numer = 70133




1.1) Descriptografar database_id



Vamos encontrar o nome do banco de dados usando a consulta: 

SELECT 
    name 
FROM sys.databases 
WHERE database_id=6;
GO


Este é o banco de dados público WideWorldImporters em meu SQL Server.



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 ir para a próxima etapa, mas se estiver interessado no nome do arquivo, você pode 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;
GO


No banco de dados WideWorldImporters, este é um arquivo chamado WWI_UserData e eu o restaurei em C: \ MSSQL \ DATA \ WideWorldImporters_UserData.ndf. (Ops, você me pegou colocando arquivos no disco do sistema! Não! Foi estranho).



1.3) Pegue o nome do objeto da DBCC PAGE



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 o DBCC PAGE não documentado e o sinalizador de rastreamento 3604.

Observação: prefiro usar o DBCC PAGE em um backup restaurado de um backup em um servidor diferente porque é uma coisa não documentada. Em alguns casos, pode levar à criação de um dump ( comentário do tradutor - o link, infelizmente, não leva a lugar nenhum, mas a julgar pela 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


Role para baixo até os resultados, você pode encontrar o object_id e index_id.



Quase pronto! Agora você pode encontrar os nomes da tabela e do índice usando a 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;
GO


E agora vemos que a espera no bloqueio estava no índice PK_Sales_OrderLines da tabela Sales.OrderLines.



Nota: 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) Você consegue ver os dados da página que foi bloqueada?



Nuuu, sim. Mas ... tem certeza de que realmente precisa?

É lento mesmo em mesas pequenas. Mas isso é legal, então já que você leu até aqui ... vamos falar sobre %% physloc %%!



%% physloc %% é uma mágica não documentada que retorna uma identificação física para cada entrada. Você pode usar %% physloc %% junto com sys.fn_PhysLocFormatter no SQL Server 2008 e superior .



Agora que sabemos que queríamos bloquear a página em Sales.OrderLines, podemos visualizar todos os dados desta tabela, que estão armazenados no arquivo de dados nº 3 na página nº 70133, usando a seguinte consulta: 

Use WideWorldImporters;
GO
SELECT 
    sys.fn_PhysLocFormatter (%%physloc%%),
    *
FROM Sales.OrderLines (NOLOCK)
WHERE sys.fn_PhysLocFormatter (%%physloc%%) like '(3:70133%'
GO




Como eu disse - é lento mesmo em mesas pequenas. Eu adicionei NOLOCK à consulta porque ainda não temos garantias de que os dados que desejamos examinar sejam exatamente os mesmos de quando o bloqueio foi encontrado - portanto, podemos fazer leituras sujas com segurança.

Mas, ei, a consulta retorna as mesmas 25 linhas pelas quais nossa consulta lutou.



Chega de bloqueios de PAGE. 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 se bloquear, você obterá um tipo de endereço completamente diferente.

Dividindo “6: 72057594041991168 (ce52f92a058c)” em partes, obtemos:

  • database_id = 6
  • hobt_id = 72057594041991168
  • hash mágico = (ce52f92a058c)




2.1) Descriptografar database_id



Isso funciona exatamente da mesma maneira que no exemplo acima! Encontre o nome do banco de dados usando a consulta: 

SELECT 
    name 
FROM sys.databases 
WHERE database_id=6;
GO


No meu caso, este é o mesmo banco de dados WideWorldImporters .



2.2) Descriptografar o hobt_id



No contexto do banco de dados encontrado, você precisa executar uma consulta para sys.partitions com algumas junções que o 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;
GO


Ele me diz que a solicitação estava aguardando o bloqueio Application.Countries usando o índice PK_Application_Countries.



2.3) Agora alguns %% lockres %% mágicos - se você quiser descobrir qual registro foi bloqueado



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 a entrada que corresponde ao hash mágico.

Observe que essa consulta examinará toda a tabela e, em tabelas grandes, isso pode não ser divertido: 

SELECT
    *
FROM Application.Countries (NOLOCK)
WHERE %%lockres%% = '(ce52f92a058c)';
GO


Eu adicionei o NOLOCK ( seguindo o conselho de Klaus Aschenbrenner no Twitter ) porque o bloqueio pode ser um problema. Queremos apenas ver o que existe agora, e não o que estava 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 leituras adicionais



Não me lembro quem foi o primeiro a descrever muitas dessas coisas, mas aqui estão duas postagens sobre as coisas menos documentadas de que você pode gostar:



More articles:


All Articles