Description du blocage de SQL Server provoquée par les verrous de compilation

Traductions disponibles Traductions disponibles
Numéro d'article: 263889 - Voir les produits auxquels s'applique cet article
Agrandir tout | Réduire tout

Sommaire

Résumé

Dans Microsoft SQL Server, qu'une seule copie d'un plan de procédure stockée est généralement dans le cache à la fois. Cette application nécessite la sérialisation de certaines parties du processus de compilation, et cette synchronisation est réalisée en partie à l'aide de verrous de compilation. Si le nombre de connexions s'exécutent simultanément la même procédure stockée et un verrou de la compilation doit être obtenu pour cette procédure stockée à chaque fois qu'elle est exécutée, ID (SPID) du processus système peut commencer à bloquer un l'autre car chaque ils essaient d'obtenir le verrouillage exclusif de compilation de l'objet.

Plus d'informations

Recompilation de procédure stockée est une seule explication pour les verrous de compilation sur une procédure stockée ou un déclencheur. Dans ce cas, la solution est de réduire ou d'éliminer les recompilations. Pour obtenir une explication des causes plus courantes susceptibles d'avoir de recompiler une procédure stockée et pour des informations utiles sur la réduction de la fréquence des recompilations, consultez l'article suivant de la base de connaissances Microsoft :
243586Résolution des problèmes de recompilation de procédure stockée
Un autre scénario dans lequel les verrous de compilation se produisent est lorsque les conditions suivantes sont remplies :
  • 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ée n'est pas qualifié complet avec le nom du propriétaire de l'objet.
Par exemple, si l'utilisateur «dbo» est propriétaire d'objetdbo.MyStoredProcet un autre utilisateur, «Harry,» s'exécute cette procédure stockée à l'aide de la commande «exec mystoredproc, «recherche de cache initiale par l'objet nom é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 mis en cache pour dbo.mystoredproc est correct pour exécuter.) SQL Server puis obtient un verrou exclusif compilation sur la procédure et rend les préparations pour compiler la procédure. Cela inclut la résolution de nom de l'objet un ID d'objet. Avant SQL Server compile le plan, SQL Server utilise cet identificateur d'objet pour effectuer une recherche plus précise du cache de procédure et peut rechercher un plan compilé précédemment même sans qualification de propriétaire.

Si un plan existant est détecté, SQL Server réutilise le plan mis en cache et ne compile pas réellement de la procédure stockée. Toutefois, le manque de qualification du propriétaire force SQL Server pour effectuer une recherche de cache de seconde et obtenir le verrouillage exclusif de compilation avant que le programme détermine qu'il est possible de réutiliser le plan mis en cache d'exécution existant. Obtaining the lock and performing lookups and other work that is needed to reach this point can introduce a delay for the compile locks that leads to blocking. This is especially true if many users who are not the stored procedure's owner concurrently run the procedure without supplying the owner's name. Be aware that even if you do not see SPIDs waiting for compile locks, lack of owner-qualification can introduce delays in stored procedure execution and cause unnecessarily high CPU utilization.

The following sequence of events will be recorded in a SQL Server Profiler trace when this problem occurs. (To trace cache-related events, you must enable advanced events. To do this, clickOptionson theOutilsmenu, and then selectAll event classes.)

Réduire ce tableauAgrandir ce tableau
Event classText
RPC:Startingmystoredproc
SP:CacheMissmystoredproc
SP:ExecContextHitmystoredproc
SP:Startingmystoredproc
......

SP:CacheMissoccurs when the cache lookup by name fails. The followingSP:ExecContextHitindicates that a matching cached plan was ultimately found in cache after the ambiguous object name was resolved to an object ID. Depending on the circumstances,SP:CacheHitmay appear instead ofSP:ExecContextHit.

The solution to this problem of compile locking is to make sure that references to stored procedures are owner-qualified. (Instead ofexec mystoredproc, use execdbo.mystoredproc.) While owner-qualification is important for performance reasons, you do not have to qualify the stored proc with the database name to prevent the additional cache lookup.

Blocking that is caused by compile locks can be detected by using blocking scripts such as those that are defined in the following Microsoft Knowledge Base articles:
251004INF: How to monitor SQL Server 7.0 blocking
271509INF: How to monitor SQL Server 2000 blocking
The following are some typical characteristics of compile blocking that can be observed in the blocking script output:
  • lastwaittypefor the blocked and (usually) blocking SPIDs is LCK_M_X (exclusive) andwaitresourceis of the form "TAB: dbid:object_id[[COMPILE]]," where "object_id" is the object ID of the stored procedure.
  • Blockers havewaittype0x0000, status runnable. Blockees havewaittype0x000e (exclusive lock), status sleeping.
  • Although the duration of the blocking incident may be long, there is no single SPID that is blocking the other SPIDs for a long time. There is rolling blocking. As soon as one compilation is complete, another SPID takes over the role of head blocker for a several seconds or less, and so on.
The following information is from a snapshot ofsysprocessesduring this kind of blocking:
   spid  blocked  waittype  waittime  lastwaittype  waitresource
   ----  -------  --------  --------  ------------  -------------------------
   
   221    29      0x000e    2141      LCK_M_X       TAB: 6:834102 [[COMPILE]]
   228    29      0x000e    2235      LCK_M_X       TAB: 6:834102 [[COMPILE]]
    29   214      0x000e    3937      LCK_M_X       TAB: 6:834102 [[COMPILE]]
    13   214      0x000e    1094      LCK_M_X       TAB: 6:834102 [[COMPILE]]
    68   214      0x000e    1968      LCK_M_X       TAB: 6:834102 [[COMPILE]]
   214     0      0x0000       0      LCK_M_X       TAB: 6:834102 [[COMPILE]]
Dans lawaitresourcecolumn ("6:834102"), 6 is the database ID and 834102 is the object ID. Be aware that this object ID belongs to a stored procedure, not to a table (despite the "TAB" lock type).

Notes
  • If you are using SQL Server 2005, many of the system tables from SQL Server 2000 are now implemented as a set of views. These views are known as compatibility views, and they are meant for backward compatibility only. The compatibility views expose the same metadata that was available in SQL Server 2000. For more information about mapping between the SQL Server 2000 system tables and the SQL Server 2005 system views, see the "Mapping SQL Server 2000 System Tables to SQL Server 2005 System Views" topic in SQL Server 2005 Books Online.
  • If your stored procedure name starts with the "sp_" prefix and is not in the master database, you seeSP:CacheMissbefore the cache hit for each execution even if you owner-qualify the stored procedure. This is because the sp_ prefix tells SQL Server that the stored procedure is a system stored procedure, and system stored procedures have different name resolution rules. (The "preferred" location is in the master database.) The names of user-created stored procedures should not start with "sp_".
  • If an owner-qualified procedure is executed with a different case than the owner-qualified procedure was created as, the owner-qualified procedure can obtain aCacheMissor request a COMPILE lock but eventually use the cached plan. Therefore, this would not actually recompile the procedure and should not cause much of an overhead. But in certain situations, the request for a COMPILE lock can cause a "blocking chain" situation if there are many SPIDs trying to execute the same procedure with a different case than the procedure was created as. This is true regardless of the sort order or collation that is being used on the server or on the database. The reason for this behavior is that the algorithm that is being used to find the procedure in cache is based on hash values (for performance reasons), which can change if the case is different.

    The workaround is to drop and create the procedure with the same case as the procedure is executed by the application. You can also make sure that the procedure is executed from all applications that use the same case.
  • If you try to execute a stored procedure as a Language Event instead of as an RPC, SQL Server must parse and compile the language event query, determine that the query is trying to execute the particular procedure, and then try to find a plan in cache for that procedure. To avoid this situation in which SQL Server must parse and compile the language event, make sure that the query is sent to SQL as an RPC.

    For more information, see the "System Stored Procedures" section in the Books Online article "Creating a Stored Procedure."


Known issues

Here are some known issues that can prevent plan caching:
  • You use BLOB variables as a Stored Procedure parameter. Pour plus d'informations, cliquez sur le numéro ci-dessous pour afficher l'article correspondant dans la base de connaissances Microsoft :
    2380435FIX: The query plan for a stored procedure is not cached if the stored procedure uses a BLOB variable and the variable is used in a string function in Microsoft SQL Server 2008
  • You use OPEN SYMMETRIC KEY in a Stored Procedure/Query Batch. For more information, see the following MSDN blog entry:
    http://blogs.msdn.com/b/sqlserverfaq/archive/2010/09/08/Open-Symmetric-Key-Command-prevents-query-plan-Caching.aspx

Propriétés

Numéro d'article: 263889 - Dernière mise à jour: mercredi 24 novembre 2010 - Version: 1.0
Les informations contenues dans cet article s'appliquent au(x) produit(s) suivant(s):
  • Microsoft SQL Server 7.0 Standard
  • Microsoft SQL Server 2000 Standard
  • Microsoft SQL Server 2005 Standard Edition
  • Microsoft SQL Server 2005 Developer Edition
  • Microsoft SQL Server 2005 Enterprise Edition
  • Microsoft SQL Server 2005 Express Edition
  • Microsoft SQL Server 2005 Workgroup Edition
Mots-clés : 
kbinfo kbmt KB263889 KbMtfr
Traduction automatique
IMPORTANT : Cet article est issu du système de traduction automatique mis au point par Microsoft (http://support.microsoft.com/gp/mtdetails). Un certain nombre d?articles obtenus par traduction automatique sont en effet mis à votre disposition en complément des articles traduits en langue française par des traducteurs professionnels. Cela vous permet d?avoir accès, dans votre propre langue, à l?ensemble des articles de la base de connaissances rédigés originellement en langue anglaise. Les articles traduits automatiquement ne sont pas toujours parfaits et peuvent comporter des erreurs de vocabulaire, de syntaxe ou de grammaire (probablement semblables aux erreurs que ferait une personne étrangère s?exprimant dans votre langue !). Néanmoins, mis à part ces imperfections, ces articles devraient suffire à vous orienter et à vous aider à résoudre votre problème. Microsoft s?efforce aussi continuellement de faire évoluer son système de traduction automatique.
La version anglaise de cet article est la suivante: 263889
L'INFORMATION CONTENUE DANS CE DOCUMENT EST FOURNIE PAR MICROSOFT SANS GARANTIE D'AUCUNE SORTE, EXPLICITE OU IMPLICITE. L'UTILISATEUR ASSUME LE RISQUE DE L'UTILISATION DU CONTENU DE CE DOCUMENT. CE DOCUMENT NE PEUT ETRE REVENDU OU CEDE EN ECHANGE D'UN QUELCONQUE PROFIT.

Envoyer des commentaires

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com