Décryptage des Key et Page WaitResource dans les blocages et deadlocks

Si vous utilisez le rapport sur les blocages (blocked process report) ou collectez des graphiques de blocage fournis par SQL Server, vous rencontrerez parfois des éléments tels que :

waitresource="PAGE: 6:3:70133"

waitresource="KEY: 6:72057594041991168 (ce52f92a058c)"

Parfois, dans ce gigantesque XML que vous étudiez, il y aura plus d'informations (les graphiques de blocage contiennent une liste de ressources qui aide à identifier les noms des objets et des index), mais ce n'est pas toujours le cas.

Ce texte vous aidera à les décoder.

Toutes les informations présentes ici sont sur Internet à divers endroits, elles sont simplement très dispersées ! Je veux rassembler tout cela - de DBCC PAGE à hobt_id et aux fonctions non documentées %%physloc%% et %%lockres%%.

D'abord, parlons des attentes sur les blocages PAGE, puis passons aux blocages KEY.

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

Si votre requête attend un blocage PAGE, SQL Server vous donnera l'adresse de cette page.

En décomposant «PAGE: 6:3:70133», nous obtenons :

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

1.1) Décodons database_id

Trouvons le nom de la base de données avec la requête :

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

C'est une base de données publique WideWorldImporters sur mon SQL Server.

1.2) Recherche du nom du fichier de données - si cela vous intéresse

Nous allons utiliser data_file_id à l'étape suivante pour trouver le nom de la table. Vous pouvez simplement passer à l'étape suivante, mais si vous êtes intéressé par le nom du fichier, vous pouvez le trouver en exécutant une requête dans le contexte de la base de données trouvée, en substituant data_file_id dans cette requête :

USE WideWorldImporters;
GO
SELECT 
    name, 
    physical_name
FROM sys.database_files
WHERE file_id = 3;
GO

Dans la base de données WideWorldImporters, ce fichier est nommé WWI_UserData et il est restauré chez moi dans C:MSSQLDATAWideWorldImporters_UserData.ndf. (Oups, vous m'avez pris en train de mettre des fichiers sur un disque système ! Non ! Ça a mal tourné).

1.3) Obtenir le nom de l'objet à partir de DBCC PAGE

Maintenant, nous savons que la page #70133 dans le fichier de données 3 appartient à la base de données WorldWideImporters. Nous pouvons examiner le contenu de cette page en utilisant DBCC PAGE non documenté et le trace flag 3604.
Remarque : je préfère utiliser DBCC PAGE sur une copie récupérée à partir d'une sauvegarde quelque part sur un autre serveur, car c'est une chose non documentée. Dans certains cas, cela peut entraîner la création d'un dump (Note du traducteur - le lien mène malheureusement nulle part, mais d'après l'url, il s'agit d'index filtrés).

/* 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

En consultant les résultats, on peut trouver object_id et index_id.

Presque prêt ! Vous pouvez maintenant trouver les noms de table et d'index à l'aide de la requête :

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

Et nous pouvons voir que l'attente sur le verrou était sur l'index PK_Sales_OrderLines de la table Sales.OrderLines.

Remarque : dans SQL Server 2014 et version ultérieure, le nom de l'objet peut également être trouvé à l'aide de la DMO non documentée sys.dm_db_database_page_allocations. Mais vous devrez interroger chaque page dans la base de données, ce qui n'est pas très élégant pour les grandes bases de données, c'est pourquoi j'ai utilisé DBCC PAGE.

1.4) Peut-on voir les données à la page qui a été verrouillée ?

Eh bien, oui. Mais… êtes-vous sûrs que c’est vraiment ce dont vous avez besoin ?
C'est lent même sur de petites tables. Mais c'est plutôt amusant, donc, puisque vous avez lu jusqu'ici… parlons de %%physloc%% !

%%physloc%% est un morceau non documenté de magie qui retourne l'identifiant physique pour chaque enregistrement. Vous pouvez utiliser %%physloc%% avec sys.fn_PhysLocFormatter dans SQL Server 2008 et version ultérieure.

Maintenant que nous savons que nous voulions verrouiller la page dans Sales.OrderLines, nous pouvons voir toutes les données dans cette table, qui sont stockées dans le fichier de données #3 à la page #70133, à l'aide de cette requête :

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

Comme je l'ai dit, c'est lent même sur de minuscules tables. J'ai ajouté NOLOCK à la requête car nous n'avons de toute façon aucune garantie que les données sur lesquelles nous voulons jeter un coup d'œil sont exactement les mêmes que celles qui étaient présentes au moment où le verrou a été découvert — donc nous pouvons faire des lectures non fiables.
Mais, hourra, la requête me retourne ces 25 lignes pour lesquelles notre requête a lutté.

Assez parlé des verrous PAGE. Que se passe-t-il si nous attendons un verrou KEY ?

2) waitresource=“KEY: 6:72057594041991168 (ce52f92a058c)” = Database_Id, HOBT_Id (le hachage magique qui peut être déchiffré à l'aide de %%lockres%%, si vous le souhaitez vraiment)

Si votre requête tente de verrouiller une ligne dans l'index et se retrouve elle-même bloquée, vous obtenez un type d'adresse complètement différent.
En décomposant “6:72057594041991168 (ce52f92a058c)” en parties, nous obtenons :

• database_id = 6
• hobt_id = 72057594041991168
• hachage magique = (ce52f92a058c)

2.1) Déchiffrons database_id

Cela fonctionne exactement de la même façon que dans l'exemple ci-dessus ! Trouvons le nom de la base de données grâce à la requête :

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

Dans mon cas, c'est toujours la même WideWorldImporters.

2.2) Déchiffrons hobt_id

Dans le contexte de la base de données trouvée, il faut exécuter une requête à sys.partitions avec quelques jointures qui aideront à déterminer les noms de la table et de l'index…

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

Il me dit que la requête était en attente sur le verrou Application.Countries, utilisant l'index PK_Application_Countries.

2.3) Maintenant un peu de magie %%lockres%% — si vous voulez déterminer quel enregistrement a été verrouillé

Si je veux vraiment savoir sur quelle ligne le verrou était nécessaire, je peux le découvrir grâce à une requête sur la table elle-même. Nous pouvons utiliser la fonction non documentée %%lockres%% pour trouver l'enregistrement correspondant au hash magique.
Notez que cette requête va scanner toute la table, et sur de grandes tables, cela peut ne pas être très agréable :

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

J'ai ajouté NOLOCK (sur les conseils de Klaus Aschenbrenner sur Twitter) car les verrous peuvent devenir un problème. Nous voulons juste jeter un œil à ce qu'il y a maintenant, et non à ce qu'il y avait quand la transaction a commencé — je ne pense pas que la cohérence des données nous importe.
Voilà, l'enregistrement pour lequel nous nous sommes battus !

Remerciements et lectures supplémentaires

Je ne me souviens pas qui a décrit pour la première fois beaucoup de ces choses, mais voici deux articles sur les éléments les moins documentés que vous pourriez apprécier :

• Article de Paul Randal sur %%physloc%% et sys.fn_PhysLocFormatter (comme nous l'avons fait avec nos données dans le premier exemple)
• Question sur StackOverflow concernant l'utilisation de %%lockres%% (comme nous avons trouvé les données dans le deuxième exemple). L'une des réponses mène à un article de Grant Fritchey sur %%lockres%%, écrit en 2010.

Source : habr.com