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 est LCK_M_X (exclusif) et waitresource est au format OBJECT: 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 ont waittypeLCK_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

  1. 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 commande exec 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ée Harry.mystoredproc existe. Par conséquent, SQL Server ne peut pas être sûr que le plan mis en cache pour dbo.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 un sp_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 exec dbo.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.

  2. 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, le sp_ 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 par sp_.

  3. 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).

  4. 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