Si vous utilisez le rapport de processus bloqué ou collectez périodiquement les graphiques de blocage fournis par SQL Server, vous rencontrerez des problèmes comme ceci :
waitresource="PAGE : 6:3:70133"
waitresource=“KEY: 6:72057594041991168 (ce52f92a058c)“
Parfois, il y aura plus d'informations dans le XML géant que vous étudiez (les graphiques de blocage contiennent une liste de ressources qui vous aident à trouver les noms d'objet et d'index), mais pas toujours.
Ce texte vous aidera à les déchiffrer.
Toutes les informations qui sont ici sont sur Internet à divers endroits, elles sont juste très diffusées ! Je veux tout rassembler - de DBCC PAGE à hobt_id et aux fonctions non documentées %%physloc%% et %%lockres%%.
Parlons d’abord des attentes sur les verrous PAGE, puis nous passerons 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'adresse de cette page.
En décomposant « PAGE : 6:3:70133 », nous obtenons :
- id_base de données = 6
- data_file_id = 3
- numéro_page = 70133
1.1) Décrypter la base de données_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;
GOC'est public sur mon serveur SQL.
1.2) Rechercher le nom du fichier de données - si vous êtes intéressé
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 de la base de données trouvée, 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;
GODans la base de données WideWorldImporters, il s'agit d'un fichier appelé WWI_UserData et je l'ai restauré dans C:MSSQLDATAWideWorldImporters_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 examiner 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 DBCC PAGE sur une copie restaurée à partir d'une sauvegarde quelque part sur un autre serveur, car il s'agit d'une chose non documentée. Dans certains cas, elle (environ. traducteur - le lien, malheureusement, ne mène nulle part, mais à en juger par l'URL, nous parlons 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 fini! 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;
GOEt maintenant, nous voyons que l'attente de verrouillage é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) Est-il possible de voir les données sur la page qui a été bloquée ?
Hé bien oui. Mais... êtes-vous sûr d'en avoir vraiment besoin ?
C'est lent même sur les petites tables. Mais c'est plutôt cool, alors 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 .
Maintenant que nous savons que nous voulions verrouiller la page dans Sales.OrderLines, nous pouvons examiner toutes les données de cette table, qui est stockée dans le fichier de données n°3 à la page n°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%'
GOComme je l'ai dit, c'est lent même sur de petites tables. J'ai ajouté NOLOCK à la demande car nous n'avons toujours aucune garantie que les données que nous voulons examiner sont exactement les mêmes que lorsque le verrou a été détecté - nous pouvons donc effectuer des lectures sales 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 une serrure à CLÉ ?
2) waitresource="KEY: 6:72057594041991168 (ce52f92a058c)" = Database_Id, HOBT_Id (hachage magique qui peut être déchiffré en utilisant %%lockres%% si vous le voulez vraiment)
Si votre requête tente de verrouiller un enregistrement dans l'index et se verrouille elle-même, vous vous retrouvez avec un type d'adresse complètement différent.
En divisant « 6:72057594041991168 (ce52f92a058c) » en plusieurs parties, nous obtenons :
- id_base de données = 6
- hobt_id = 72057594041991168
- hachage magique = (ce52f92a058c)
2.1) Décrypter la base de données_id
Cela fonctionne exactement de la même manière que 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;
GODans mon cas c'est toujours pareil .
2.2) Décrypter hobt_id
Dans le contexte de la base de données trouvée, vous devez exécuter une requête sur sys.partitions avec une paire de 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;
GOCela me dit que la demande attendait le verrou Application.Countries à l'aide de l'index PK_Application_Countries.
2.3) Maintenant un peu de magie %%lockres%% - si vous voulez savoir quelle entrée a été verrouillée
Si je veux vraiment savoir sur quelle ligne se trouvait le verrou, je peux le savoir en interrogeant la table elle-même. Nous pouvons utiliser la fonction non documentée %%lockres%% pour trouver une entrée qui correspond au hachage magique.
Veuillez noter 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 () car les blocages peuvent devenir un problème. Nous voulons simplement examiner ce qui existe actuellement, et non ce qui existait au début de la transaction. Je ne pense pas que la cohérence des données soit importante pour nous.
Voilà, le record pour lequel nous nous sommes battus !
Remerciements et lectures complémentaires
Je ne me souviens pas qui a décrit en premier bon nombre de ces choses, mais voici deux articles sur les choses les moins documentées qui pourraient vous plaire :
- Le message de Paul Randal sur (comme nous avons nos données dans le premier exemple)
- Question sur StackOverflow à propos de (comme nous avons trouvé les données dans le deuxième exemple). Une des réponses mène à un post .
Source: habr.com