Comment dépanner le blocage causé par les verrous de compilation

S’applique à : Microsoft SQL Server 2005 Standard EditionMicrosoft SQL Server 2005 Developer EditionMicrosoft SQL Server 2005 Enterprise Edition

Résumé


Dans Microsoft SQL Server, une seule copie d'un plan de procédure stocké est généralement en cache à la fois. Pour l'appliquer, il faut sérialiser certaines parties du processus de compilation, et cette synchronisation est réalisée en partie en utilisant des 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 est exécutée, les ID de session (SPID) peuvent commencer à se bloquer les uns les autres alors qu'ils tentent chacun d'obtenir un verrou de compilation exclusif sur le Objet.

Voici quelques caractéristiques typiques du blocage de compilation qui peuvent être observées dans la sortie de blocage :

  • waittype pour le blocage et (généralement) les SPID de session de blocage est LCK_M_X (exclusif) et la ressource d'attente est du formulaire "OBJECT: dbid:object_id [[COMPILE]," où "object_id" est l'ID objet de la procédure stockée.
  • Les bloqueurs ont le type d'attente NULL, statut runnable. Les bloques ont un type d'attente LCK_M_X (verrouillage exclusif), le statut de sommeil.
  • Bien que la durée de l'incident de blocage puisse être longue, il n'y a pas de SPID unique qui bloque les autres SPID pendant une longue période. Il y a un blocage de roulement. Dès qu'une compilation est terminée, un autre SPID prend le rôle de bloqueur de tête pendant quelques 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 colonne de ressource d'attente (" 6:834102"), 6 est l'ID de base de données et 834102 est l'ID objet. Veuillez noter que cet ID d'objet appartient à une procédure stockée, et non à une table.

Informations supplémentaires


La recompilation de procédure stockée est une explication pour compiler des verrous sur une procédure stockée ou un déclencheur. La solution dans ce cas est de réduire ou d'éliminer les recompilements. Pour une explication des raisons les plus courantes pour lesquelles une procédure stockée peut devoir être recompilée et pour obtenir des informations utiles sur la réduction de la fréquence des recompilements, voir l'article suivant microsoft Knowledge Base :

243586 Dépannage de la procédure stockée recompilation

Scénarios supplémentaires qui mènent à compiler des serrures :

  1. La procédure stockée est exécutée sans nom entièrement qualifié
    • L'utilisateur qui exécute la procédure stockée n'est pas le propriétaire de la procédure.
    • Le nom de procédure stocké n'est pas entièrement qualifié 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 en utilisant la commande "exec mystoredproc", la recherche initiale du cache par nom d'objet échoue parce que 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 se prépare à compiler la procédure. Cela comprend la résolution du nom de l'objet à un 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 du 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 oblige SQL Server à effectuer un deuxième cache de recherche et à obtenir un verrou de compilation exclusif avant que le programme ne détermine que le plan d'exécution en cache existant peut être réutilisé. L'obtention de la serrure et l'exécution des recherches et d'autres travaux qui sont nécessaires pour atteindre ce point peut 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. Sachez que même si vous ne voyez pas les SPID en attente de verrouillages 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 provoquer une utilisation inutilement élevée du processeur. La séquence suivante d'événements sera enregistrée dans une session SQL Server Extended Event lorsque ce problème se produit.

    Nom de l'événement Texte
    rpc_starting mystoredproc (en)
    sp_cache_miss mystoredproc (en)
    sql_batch_starting mystoredproc (en)
    sp_cache_hit mystoredproc (en)
    ... ...

    sp_cache_miss se produit lorsque le cache rechercher par nom échoue, mais alors un plan de mise en cache correspondant a finalement été trouvé en cache après le nom de l'objet ambiguos a été résolu à un ID d'objet et il ya un événement sp_cache_hit. La solution à ce problème de verrouillage de compilation est de s'assurer que les références aux procédures stockées sont qualifiées par le propriétaire. (Au lieu d'exec mystoredproc, utiliser exec dbo.mystoredproc.) Bien que la qualification du propriétaire soit importante pour des raisons de performances, vous n'avez pas à qualifier le proc stocké avec le nom de base de données pour empêcher la recherche de cache supplémentaire. Le blocage causé par les verrous de compilation peut être détecté en utilisant des méthodes de dépannage de blocage standard.

  2. La procédure stockée est préfixée avec "sp_"

    Si votre nom de procédure stocké commence par le préfixe « sp_ » et n'est pas dans la base de données principale, vous voyez sp_cache_miss avant que le cache ne frappe pour chaque exécution, même si vous qualifiez la procédure stockée. C'est parce que le préfixe sp_ indique SQL Server que la procédure stockée est une procédure stockée dans le système, et les procédures stockées système ont des règles de résolution de nom différentes. (L'emplacement « préféré » se trouve dans la base de données principale.) 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 invoquée à l'aide d'un cas différent (supérieur/inférieur)

    Si une procédure qualifiée par le propriétaire est exécutée en utilisant un boîtier différent (supérieur ou inférieur) du boîtier utilisé 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. Mais la demande d'un verrou COMPILE peut parfois provoquer une situation de « chaîne de blocage » s'il y a de nombreux SPID qui tentent d'exécuter la même procédure en utilisant un cas différent de celui qui a été utilisé pour le créer. Cela est vrai quel que soit l'ordre de tri ou de collation qui est utilisé sur le serveur ou sur la base de données. La raison de ce comportement est que l'algorithme qui est utilisé pour trouver la procédure en cache est basé sur les valeurs de hachet (pour les performances), et les valeurs de hachet peuvent changer si le cas est différent. La parcontournement consiste à laisser tomber et à créer la procédure en utilisant le même boîtier que celui qui est 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 le bon cas (supérieur ou inférieur).

  4. La procédure stockée est invoquée en tant qu'événement linguistique

    Si vous essayez d'exécuter une procédure stockée en tant qu'événement linguistique plutôt qu'en tant que RPC, SQL Server doit lire et compiler la requête d'événement linguistique, déterminer que la requête essaie d'exécuter la procédure particulière, puis essayer de trouver un plan en cache pour cette procédure. Pour éviter cette situation dans laquelle SQL Server doit analyser et compiler l'événement linguistique, 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 dans le système des procédures » dans l'article « Créer une procédure stockée ».

Problèmes connus

Voici quelques problèmes connus qui peuvent empêcher la capage de plan :

  • Vous utilisez les variables BLOB comme paramètre de procédure stockée. Pour plus d'informations, cliquez sur le numéro d'article suivant pour voir l'article dans la base de connaissances Microsoft :
    2380435 FIX: Le plan de requête pour une procédure stockée n'est pas mis en cache si la procédure stockée utilise une variable BLOB et la variable est utilisée dans une fonction de chaîne dans Microsoft SQL Server 2008
  • Vous utilisez OPEN SYMMETRIC KEY dans un groupe de procédures/requêtes stockées. Pour plus d'informations, voir l'entrée de blog MSDN suivante: