Résoudre les problèmes de blocage causés par les verrous de compilation
Cet article explique comment résoudre les problèmes de blocage causés par les verrous de compilation.
Version du produit d’origine : SQL Server
Numéro de la base de connaissances d’origine : 263889
Résumé
Dans Microsoft SQL Server, une seule copie d’un plan de procédure stockée est généralement dans le cache à la fois. L’application de cela nécessite la sérialisation de certaines parties du processus de compilation, et cette synchronisation s’effectue en partie à l’aide de verrous de compilation. Si de nombreuses connexions exécutent simultanément la même procédure stockée et qu’un verrou de compilation doit être obtenu pour cette procédure stockée chaque fois qu’elle s’exécute, les ID de session (SPID) peuvent commencer à se bloquer les uns les autres à mesure qu’ils tentent d’obtenir un verrou de compilation exclusif sur l’objet.
Voici quelques caractéristiques typiques du blocage de compilation qui peuvent être observées dans la sortie de blocage :
waittype
pour les SPID de session bloquée et (généralement) bloquante estLCK_M_X
(exclusif) etwaitresource
est au formatOBJECT: dbid: object_id [[COMPILE]]
, oùobject_id
est l’ID d’objet de la procédure stockée.Les bloqueurs ont
waittype
NULL, status exécutable. Les blocages ontwaittype
LCK_M_X
(verrou exclusif), status en veille.Bien que la durée de l’incident de blocage puisse être longue, il n’existe aucun SPID unique qui bloque les autres SPID pendant une longue période. Il y a un blocage propagé. Dès qu’une compilation est terminée, un autre SPID prend le rôle de bloqueur principal pendant plusieurs secondes ou moins, et ainsi de suite.
Les informations suivantes proviennent d’un instantané de sys.dm_exec_requests
pendant ce type de blocage :
session_id blocking_session_id wait_type wait_time waitresource ---------- ------------------- --------- --------- ----------------------------
221 29 LCK_M_X 2141 OBJECT: 6:834102
[[COMPILE]]
228 29 LCK_M_X 2235 OBJECT: 6:834102
[[COMPILE]]
29 214 LCK_M_X 3937 OBJECT: 6:834102
[[COMPILE]]
13 214 LCK_M_X 1094 OBJECT: 6:834102
[[COMPILE]]
68 214 LCK_M_X 1968 OBJECT: 6:834102
[[COMPILE]]
214 0 LCK_M_X 0 OBJECT: 6:834102
[[COMPILE]]
Dans la waitresource
colonne (6 :834102), 6 est l’ID de base de données et 834102 est l’ID d’objet. Cet ID d’objet appartient à une procédure stockée, et non à une table.
Plus d’informations
La recompilation de procédure stockée est une explication des verrous de compilation sur une procédure stockée ou un déclencheur. Dans ce cas, la solution consiste à réduire ou à éliminer les recompilations.
Scénarios supplémentaires qui conduisent à des verrous de compilation
La procédure stockée est exécutée sans nom complet
- L’utilisateur qui exécute la procédure stockée n’est pas le propriétaire de la procédure.
- Le nom de la procédure stockée n’est pas complet avec le nom du propriétaire de l’objet.
Par exemple, si l’utilisateur dbo possède l’objet
dbo.mystoredproc
et qu’un autre utilisateur,Harry
, exécute cette procédure stockée à l’aide de la commandeexec mystoredproc
, la recherche initiale du cache par nom d’objet échoue, car l’objet n’est pas qualifié par le propriétaire. (On ne sait pas encore si une autre procédure stockée nomméeHarry.mystoredproc
existe. Par conséquent, SQL Server ne peut pas être sûr que le plan mis en cache pourdbo.mystoredproc
est le bon à exécuter.) SQL Server obtient alors un verrou de compilation exclusif sur la procédure et effectue des préparations pour compiler la procédure. Cela inclut la résolution du nom de l’objet en ID d’objet. Avant SQL Server compile le plan, SQL Server utilise cet ID d’objet pour effectuer une recherche plus précise dans le cache de procédure et peut localiser un plan compilé précédemment même sans qualification de propriétaire.Si un plan existant est trouvé, SQL Server réutilise le plan mis en cache et ne compile pas réellement la procédure stockée. Toutefois, l’absence de qualification du propriétaire force SQL Server à effectuer une deuxième recherche de cache et à obtenir un verrou de compilation exclusif avant que le programme détermine que le plan d’exécution mis en cache existant peut être réutilisé. L’obtention du verrou et l’exécution de recherches et d’autres travaux nécessaires pour atteindre ce point peuvent introduire un délai pour les verrous de compilation qui conduit au blocage. Cela est particulièrement vrai si de nombreux utilisateurs qui ne sont pas le propriétaire de la procédure stockée exécutent simultanément la procédure sans fournir le nom du propriétaire. Même si vous ne voyez pas de SPID en attente de verrous de compilation, l’absence de qualification du propriétaire peut entraîner des retards dans l’exécution de la procédure stockée et entraîner une utilisation élevée du processeur.
La séquence d’événements suivante est enregistrée dans une session d’événements étendus SQL Server lorsque ce problème se produit.
Nom de l'événement Texte rpc_starting mystoredproc sp_cache_miss mystoredproc sql_batch_starting mystoredproc sp_cache_hit mystoredproc ... ... sp_cache_miss
se produit lorsque la recherche du cache par nom échoue, mais qu’un plan mis en cache correspondant a finalement été trouvé dans le cache après que le nom d’objet ambigu a été résolu en ID d’objet et qu’il y a unsp_cache_hit
événement.La solution à ce problème de verrouillage de compilation consiste à s’assurer que les références aux procédures stockées sont qualifiées par le propriétaire. (Au lieu de exec
mystoredproc
, utilisez execdbo.mystoredproc
.) Bien que la qualification du propriétaire soit importante pour des raisons de performances, vous n’avez pas besoin de qualifier la procédure stockée avec le nom de la base de données pour empêcher la recherche de cache supplémentaire.Les blocages provoqués par les verrous de compilation peuvent être détectés à l’aide des méthodes de résolution des problèmes de blocage standard.
La procédure stockée est précédée de sp_
Si le nom de votre procédure stockée commence par le
sp_
préfixe et n’est pas dans la base de données master, vous voyez sp_cache_miss avant l’accès au cache pour chaque exécution, même si vous qualifiez la procédure stockée par le propriétaire. En effet, lesp_
préfixe indique SQL Server que la procédure stockée est une procédure stockée système et que les procédures stockées système ont des règles de résolution de noms différentes. (L’emplacement préféré se trouve dans la base de données master.) Les noms des procédures stockées créées par l’utilisateur ne doivent pas commencer parsp_
.La procédure stockée est appelée à l’aide d’une casse différente (majuscule/inférieure)
Si une procédure qualifiée par le propriétaire est exécutée à l’aide d’une casse différente (supérieure ou inférieure) de la casse utilisée pour la créer, la procédure peut déclencher un événement CacheMiss ou demander un verrou COMPILE. Finalement, la procédure utilise le plan mis en cache et n’est pas recompilée. Toutefois, la demande d’un verrou COMPILE peut parfois provoquer une situation de chaîne de blocage si de nombreux SPID tentent d’exécuter la même procédure en utilisant un cas différent de celui utilisé pour la créer. Cela est vrai quel que soit l’ordre de tri ou le classement utilisé sur le serveur ou sur la base de données. La raison de ce comportement est que l’algorithme utilisé pour rechercher la procédure dans le cache est basé sur des valeurs de hachage (pour les performances) et que les valeurs de hachage peuvent changer si le cas est différent.
La solution de contournement consiste à supprimer et à créer la procédure en utilisant le même cas que celui utilisé lorsque l’application exécute la procédure. Vous pouvez également vous assurer que la procédure est exécutée à partir de toutes les applications en utilisant la casse correcte (supérieure ou inférieure).
La procédure stockée est appelée en tant qu’événement Language
Si vous essayez d’exécuter une procédure stockée en tant qu’événement de langage plutôt qu’en tant que RPC, SQL Server devez analyser et compiler la requête d’événement de langage, déterminer que la requête tente d’exécuter la procédure particulière, puis essayer de trouver un plan dans le cache pour cette procédure. Pour éviter cette situation dans laquelle SQL Server doit analyser et compiler l’événement de langage, assurez-vous que la requête est envoyée à SQL en tant que RPC.
Pour plus d’informations, consultez la section Procédures stockées système dans la documentation en ligne de l’article Création d’une procédure stockée.
References
La commande OPEN SYMMETRIC KEY empêche la mise en cache du plan de requête
Commentaires
https://aka.ms/ContentUserFeedback.
Bientôt disponible : tout au long de 2024, nous allons éliminer progressivement GitHub Issues comme mécanisme de commentaires pour le contenu et le remplacer par un nouveau système de commentaires. Pour plus d'informations, consultez :Envoyer et afficher des commentaires pour