Résoudre les problèmes de blocage causés par les verrous de compilation

Cet article explique comment résoudre et résoudre les problèmes de blocage causés par les verrous de compilation.

Version du produit d’origine : SQL Server
Numéro de 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 en cache à la fois. L’application de ce paramètre nécessite la sérialisation de certaines parties du processus de compilation, et cette synchronisation est effectuée 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 essaient 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 bloquante :

• waittype pour les SPID de session bloqués et (généralement) bloquants est LCK_M_X (exclusif) et waitresource est de la forme OBJECT: dbid: object_id [[COMPILE]], où object_id est l’ID d’objet de la procédure stockée.

• Les bloqueurs ont waittype la valeur NULL, l’état pouvant être exécuté. Les sessions bloquées ont waittypeLCK_M_X (verrouillage exclusif), veille d’état.

• Bien que la durée globale de l’incident de blocage puisse être longue, il n’existe aucune session unique (SPID) qui bloque les autres SPID pendant une longue période. Il y a des blocages propagés ; Dès qu’une compilation est terminée, une 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 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.

Scénarios qui mènent à la compilation de verrous

Les scénarios suivants décrivent les causes des verrous de compilation exclusifs sur les procédures stockées ou les déclencheurs.

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 un objet dbo.mystoredproc et un autre utilisateur, Harryexé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. (Il n’est pas encore connu 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 dbo.mystoredproc mis en cache est le bon à exécuter.) SQL Server obtient ensuite 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 que SQL Server compile le plan, SQL Server utilise cet ID d’objet pour effectuer une recherche plus précise du cache de procédure et peut localiser un plan précédemment compilé, 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, le manque 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 entraînent le 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 les SPID en attente de verrous de compilation, le manque de qualification du propriétaire peut introduire des retards dans l’exécution de 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.

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 supplémentaire dans le cache.

Le blocage provoqué par les verrous de compilation peut être détecté à l’aide de méthodes de résolution des problèmes de blocage standard.

La procédure stockée est recompilée fréquemment

La recompilation est une explication pour les verrous de compilation sur une procédure stockée ou un déclencheur. Façons de faire en sorte qu’une procédure stockée recompile include EXECUTE... WITH RECOMPILE, CREATE PROCEDURE ...WITH RECOMPILEou using sp_recompile. Pour plus d’informations, consultez Recompiler une procédure stockée. Dans ce cas, la solution consiste à réduire ou à éliminer la recompilation.

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 qu’il ne se trouve pas dans la base de données master, vous voyez sp_cache_miss avant que le cache n’atteigne chaque exécution, même si vous qualifiez la procédure stockée par le propriétaire. Cela est dû au fait que 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_.

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 (majuscule 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. Pour illustrer, notez le cas de lettre différent utilisé par rapport EXEC dbo.salesdataà CREATE PROCEDURE dbo.SalesData ... . Finalement, la procédure utilise le plan mis en cache et n’est pas recompilée. Toutefois, la demande de verrou COMPILE peut parfois entraîner une situation de chaîne de blocage décrite précédemment. La chaîne de blocage peut se produire s’il existe de nombreuses sessions (SPID) qui tentent d’exécuter la même procédure à l’aide d’un cas différent du cas utilisé pour le 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. Le motif 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 les valeurs de hachage peuvent changer si le cas est différent.

La solution consiste à supprimer et à créer la procédure à l’aide de la même lettre que celle utilisée lors de l’exécution de la procédure par l’application. Vous pouvez également vous assurer que la procédure est exécutée à partir de toutes les applications à l’aide de la casse correcte (majuscule 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 au lieu d’un RPC, SQL Server doit 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 Server en tant que RPC. Par exemple, dans le code .NET, vous pouvez utiliser SqlCommand.CommandType.StoredProcedure pour garantir un événement RPC.

Procédure stockée ou sp_executesql utilise un paramètre de chaîne supérieur à 8 Ko

Si vous appelez une procédure stockée ou sp_executesql et passez un paramètre de chaîne de plus de 8 Ko, SQL Server utilise un type de données BLOB (Binary Large Object) pour stocker le paramètre. Par conséquent, le plan de requête pour cette exécution n’est pas conservé dans le cache du plan. Par conséquent, chaque exécution de la procédure stockée ou sp_executesql doit acquérir un verrou de compilation pour compiler un nouveau plan. Ce plan est ignoré lorsque l’exécution est terminée. Pour plus d’informations, consultez la note dans la mise en cache et la réutilisation du plan d’exécution concernant les littéraux de chaîne de plus de 8 Ko. Pour éviter le verrou de compilation dans ce scénario, réduisez la taille du paramètre à moins de 8 Ko.

References

La commande OPEN SYMMETRIC KEY empêche la mise en cache du plan de requête