Se você utiliza o relatório de bloqueios (blocked process report) ou coleta informações de deadlocks fornecidas pelo SQL Server, ocasionalmente, você encontrará coisas como estas:
waitresource="PAGE: 6:3:70133"
waitresource="KEY: 6:72057594041991168 (ce52f92a058c)"
Às vezes, naquele imenso XML que você está analisando, haverá mais informações (as informações de deadlocks contêm uma lista de recursos que ajudam a identificar nomes de objeto e índice), mas nem sempre.
Este texto ajudará a decifrá-las.
Toda a informação contida aqui está disponível na internet em vários lugares, mas está bastante dispersa! Quero reunir tudo — do DBCC PAGE ao hobt_id e às funções não documentadas %%physloc%% e %%lockres%%.
Primeiro, vamos falar sobre esperas em bloqueios de PAGE e depois vamos abordar bloqueios de KEY.
1) waitresource="PAGE: 6:3:70133" = Database_Id: FileId: PageNumber
Se sua consulta está aguardando um bloqueio de PAGE, o SQL Server fornecerá o endereço dessa página.
Decompondo "PAGE: 6:3:70133" obtemos:
- database_id = 6
- data_file_id = 3
- page_number = 70133
1.1) Decifrando database_id
Vamos encontrar o nome do banco de dados usando a consulta:
SELECT
name
FROM sys.databases
WHERE database_id=6;
GOEste é o público no meu SQL Server.
1.2) Procurando o nome do arquivo de dados — se você estiver interessado
Vamos usar o data_file_id na próxima etapa para encontrar o nome da tabela. Você pode pular para a próxima etapa, mas se você está interessado no nome do arquivo, pode encontrá-lo executando a 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 BD WideWorldImporters, este é o arquivo chamado WWI_UserData e ele está recuperado no meu C:MSSQLDATAWideWorldImporters_UserData.ndf. (Ops, você me pegou colocando arquivos no disco do sistema! Não! Que situação desconfortável).
1.3) Obtenção do nome do objeto a partir do DBCC PAGE
Agora sabemos que a página #70133 no arquivo de dados 3 pertence ao BD WorldWideImporters. Podemos examinar o conteúdo dessa página usando o DBCC PAGE não documentado e a trace flag 3604.
Nota: prefiro usar o DBCC PAGE em uma cópia restaurada de um backup em outro servidor, porque isso é uma coisa não documentada. Em alguns casos, ela (nota do tradutor — o link, infelizmente, leva a lugar nenhum, mas pelo que parece no url, trata-se 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 Rolando para os resultados, pode-se encontrar 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;
GOE aqui vemos que a espera por bloqueio foi no índice PK_Sales_OrderLines da tabela Sales.OrderLines.
Nota: no SQL Server 2014 e versões posteriores, o nome do objeto também pode ser encontrado usando o DMO não documentado sys.dm_db_database_page_allocations. Mas você terá que consultar cada página no banco de dados, o que não parece nada legal para bancos de dados grandes, por isso eu usei DBCC PAGE.
1.4) Posso ver os dados naquela página que estava bloqueada?
Bem, sim. Mas… você tem certeza de que realmente precisa disso?
Isso é lento até mesmo em tabelas pequenas. Mas é legal, então, uma vez que você leu até aqui… vamos falar sobre %%physloc%%!
%%physloc%% é um pedaço de mágica não documentado que retorna o identificador físico para cada registro. Você pode usar .
Agora que sabemos que queremos aplicar bloqueio na página em Sales.OrderLines, podemos ver todos os dados nesta tabela que estão armazenados no arquivo de dados #3 na página #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%'
GOComo eu disse — isso é lento mesmo em tabelas minúsculas. Adicionei NOLOCK à consulta porque não temos garantia de que os dados que queremos olhar sejam exatamente os mesmos que estavam no momento em que o bloqueio foi detectado — então podemos fazer leituras sujas sem problemas.
Mas, uau, a consulta retorna exatamente aquelas 25 linhas pelas quais nossa consulta lutou.
Chega de bloqueios de PÁGINA. E se estivermos aguardando um bloqueio de CHAVE?
2) waitresource="KEY: 6:72057594041991168 (ce52f92a058c)" = Database_Id, HOBT_Id (um hash mágico que pode ser decifrado com %%lockres%%, se você realmente quiser)
Se sua consulta tentar aplicar um bloqueio a um registro no índice e acabar sendo bloqueada, você recebe um tipo completamente diferente de endereço.
Dividindo "6:72057594041991168 (ce52f92a058c)" em partes, obtemos:
- database_id = 6
- hobt_id = 72057594041991168
- hash mágico = (ce52f92a058c)
2.1) Decifrando database_id
Isso funciona exatamente como no exemplo acima! Vamos encontrar o nome do BD com a consulta:
SELECT
name
FROM sys.databases
WHERE database_id=6;
GONo meu caso, é o mesmo .
2.2) Decifrando hobt_id
No contexto do BD encontrado, precisamos executar uma consulta ao sys.partitions com um par de joins 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 consulta estava aguardando no bloqueio Application.Countries, usando o índice PK_Application_Countries.
2.3) Agora um pouco de mágica %%lockres%% — se você quiser descobrir qual registro foi bloqueado
Se eu realmente quiser saber em qual linha o bloqueio foi necessário, posso descobrir isso com uma consulta à própria tabela. Podemos usar a função não documentada %%lockres%% para encontrar o registro que corresponde ao hash mágico.
Tenha em mente que esta consulta irá escanear toda a tabela, e em tabelas grandes isso pode não ser nada divertido:
SELECT
*
FROM Application.Countries (NOLOCK)
WHERE %%lockres%% = '(ce52f92a058c)';
GO Eu adicionei NOLOCK () porque bloqueios podem se tornar um problema. Queremos apenas ver o que está lá agora, e não o que havia lá quando a transação começou — não acho que a consistência dos dados seja importante para nós.
Voilà, o registro pelo qual estávamos lutando!
Agradecimentos e leitura adicional
Não lembro quem primeiro descreveu muitas dessas coisas, mas aqui estão dois posts sobre as coisas menos documentadas que podem te interessar:
- Post de Paul Randal sobre (como obtemos 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
