Décrypter la clé et la page WaitResource dans les blocages et les verrous

Si vous utilisez un rapport de processus bloqué ou collectez des graphiques d'interblocage fournis par SQL Server de temps en temps, vous rencontrerez ces choses:

waitresource = "PAGE: 6: 3: 70133"



waitresource = "CLÉ: 6: 72057594041991168 (ce52f92a058c)"

Parfois, il y aura plus d'informations dans ce XML géant que vous apprenez (les graphiques bloqués contiennent une liste de ressources qui vous aide à trouver les noms d'objet et d'index), mais pas toujours.



Ce texte vous aidera à les déchiffrer.



Toutes les informations qui sont ici se trouvent sur Internet à divers endroits, elles sont simplement très diffusées! Je veux tout mettre ensemble - de DBCC PAGE à hobt_id et aux fonctions non documentées %% physloc %% et %% lockres %%.



Parlons d'abord de l'attente des verrous PAGE, puis passons aux verrous KEY.

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

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



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

• id_base_de_données = 6
• data_file_id = 3
• page_numer = 70133

1.1) Décrypter database_id

Trouvons le nom de la base de données à l'aide de la requête:

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

Il s'agit de la base de données publique WideWorldImporters sur mon serveur SQL.

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 le nom du fichier vous intéresse, vous pouvez le trouver en exécutant une requête dans le contexte du DB trouvé, en remplaçant 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, il s'agit d'un fichier nommé WWI_UserData et il a été restauré dans mon C: \ MSSQL \ DATA \ WideWorldImporters_UserData.ndf. (Oups, vous m'avez surpris en train de mettre des fichiers sur le disque système! Non! C'était gênant).

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

Nous savons maintenant que la page # 70133 du fichier de données 3 appartient à la base de données WorldWideImporters. Nous pouvons regarder le contenu de cette page en utilisant la PAGE DBCC non documentée et l'indicateur de trace 3604.

Remarque: Je préfère utiliser la PAGE DBCC sur une sauvegarde restaurée à partir d'une sauvegarde quelque part sur un serveur différent, car c'est une chose non documentée. Dans certains cas, cela peut conduire à la création d'un dump ( commentaire du traducteur - le lien, malheureusement, ne mène nulle part, mais à en juger par l'url, on parle 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 faisant défiler les résultats, vous pouvez 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 maintenant, nous voyons que l'attente sur le verrou était sur l'index PK_Sales_OrderLines de la table Sales.OrderLines.



Remarque: dans SQL Server 2014 et versions ultérieures, le nom de l'objet peut également être trouvé à l'aide du DMO non documenté sys.dm_db_database_page_allocations. Mais vous devez interroger chaque page de la base de données, ce qui n'a pas l'air très cool pour les grandes bases de données, j'ai donc utilisé DBCC PAGE.

1.4) Pouvez-vous voir les données sur la page qui a été bloquée?

Nuuu, oui. Mais ... êtes-vous sûr que vous en avez vraiment besoin?

C'est lent même sur de petites tables. Mais c'est plutôt cool, donc puisque vous avez lu jusqu'ici ... parlons de %% physloc %%!



%% physloc %% est un morceau de magie non documenté qui renvoie un identifiant physique pour chaque entrée. Vous pouvez utiliser %% physloc %% avec sys.fn_PhysLocFormatter dans SQL Server 2008 et versions ultérieures .



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

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 petites tables. J'ai ajouté NOLOCK à la requête car nous n'avons toujours aucune garantie que les données que nous voulons regarder sont exactement les mêmes que lorsque le verrou a été trouvé - afin que nous puissions faire des lectures incorrectes en toute sécurité.

Mais, hourra, la requête me renvoie les mêmes 25 lignes pour lesquelles notre requête s'est battue.



Assez parlé des verrous PAGE. Et si nous attendons un verrou KEY?

2) waitresource = "KEY: 6: 72057594041991168 (ce52f92a058c)" = Database_Id, HOBT_Id (hash magique qui peut être déchiffré avec %% lockres %% si vous le souhaitez vraiment)

Si votre requête tente de verrouiller une entrée d'index et se verrouille d'elle-même, vous obtenez un type d'adresse complètement différent.

En décomposant «6: 72057594041991168 (ce52f92a058c)» en plusieurs parties, nous obtenons:

• id_base_de_données = 6
• hobt_id = 72057594041991168
• hachage magique = (ce52f92a058c)

2.1) Décrypter database_id

Cela fonctionne exactement de la même manière que dans l'exemple ci-dessus! Recherchez le nom de la base de données à l'aide de la requête:

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

Dans mon cas, il s'agit de la même base de données WideWorldImporters .

2.2) Décrypter le hobt_id

Dans le contexte de la base de données trouvée, vous devez exécuter une requête sur sys.partitions avec quelques jointures qui vous aideront à déterminer les noms de table et d'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 m'indique que la demande était en attente sur le verrou Application.Countries à l'aide de l'index PK_Application_Countries.

2.3) Maintenant un peu de magie %% lockres %% - si vous voulez savoir quel enregistrement a été verrouillé

Si je veux vraiment savoir sur quelle ligne le verrou était nécessaire, je peux le comprendre en interrogeant la table elle-même. Nous pouvons utiliser la fonction %% lockres %% non documentée pour trouver l'entrée qui correspond au hachage magique.

Notez que cette requête analysera la table entière, et sur les grandes tables, cela peut ne pas être amusant du tout:

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

J'ai ajouté NOLOCK ( sur les conseils de Klaus Aschenbrenner sur Twitter ) car le blocage peut être un problème. Nous voulons simplement voir ce qu'il y a maintenant, et non ce qu'il y avait au début de la transaction - je ne pense pas que la cohérence des données soit importante pour nous.

Voila, le record pour lequel nous nous sommes battus!

Remerciements et lectures complémentaires

Je ne me souviens pas qui a été le premier à décrire beaucoup de ces choses, mais voici deux articles sur les choses les moins documentées que vous pourriez aimer:

• Message de Paul Randal sur %% physloc %% et sys.fn_PhysLocFormatter (comme nous le faisons pour nos données dans le premier exemple)
• Une question sur StackOverflow sur l' utilisation de %% lockres %% (comme nous avons trouvé les données dans le deuxième exemple). L'une des réponses va au message de Grant Fritchey sur %% lockres %%, écrit en 2010 .