Cómo solucionar problemas de bloqueo causado por bloqueos de compilación

Se aplica a: Microsoft SQL Server 2005 Standard EditionMicrosoft SQL Server 2005 Developer EditionMicrosoft SQL Server 2005 Enterprise Edition

Resumen


En Microsoft SQL Server, solo una copia de un plan de procedimiento almacenado suele estar en caché a la vez. Para aplicar esto, se requiere la serialización de algunas partes del proceso de compilación y esta sincronización se realiza en parte mediante el uso de bloqueos de compilación. Si muchas conexiones ejecutan simultáneamente el mismo procedimiento almacenado y se debe obtener un bloqueo de compilación para ese procedimiento almacenado cada vez que se ejecuta, los datos de sesión (SPID) pueden comenzar a bloquearse entre sí, ya que cada uno intenta obtener un bloqueo de compilación exclusivo en el Objeto.

Las siguientes son algunas características típicas del bloqueo de compilación que se pueden observar en la salida de bloqueo:

  • waittype para los SPID de sesión bloqueados y (normalmente) de bloqueo se LCK_M_X (exclusivo) y waitresource tiene la forma "OBJECT: dbid:object_id [[COMPILE]]", donde "object_id" es el ID de objeto del procedimiento almacenado.
  • Los bloqueadores tienen el tipo de espera NULL, el estado se puede ejecutar. Los bloques tienen LCK_M_X de tipo de espera (bloqueo exclusivo), estado en suspensión.
  • Aunque la duración del incidente de bloqueo puede ser larga, no hay un solo SPID que esté bloqueando los otros SPID durante mucho tiempo. Hay bloqueo rodante. Tan pronto como se completa una compilación, otro SPID asume el papel de bloqueador de cabeza durante varios segundos o menos, y así sucesivamente.

La siguiente información proviene de una instantánea de sys.dm_exec_requests durante este tipo de bloqueo:

   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]] 

En la columna waitresource ("6:834102"), 6 es el identificador de base de datos y 834102 es el identificador del objeto. Tenga en cuenta que este identificador de objeto pertenece a un procedimiento almacenado, no a una tabla.

Más información


La recompilación de procedimientos almacenados es una explicación para los bloqueos de compilación en un procedimiento almacenado o desencadenador. La solución en este caso es reducir o eliminar las recompilaciones. Para obtener una explicación de las razones más comunes por las que puede tener que volver a compilar un procedimiento almacenado y para obtener información útil sobre cómo reducir la frecuencia de las recompilaciones, consulte el siguiente artículo de Microsoft Knowledge Base:

243586 Solución de problemas de recompilación de procedimientos almacenados

Escenarios adicionales que conducen a bloqueos de compilación:

  1. El procedimiento almacenado se ejecuta sin nombre completo
    • El usuario que ejecuta el procedimiento almacenado no es el propietario del procedimiento.
    • El nombre del procedimiento almacenado no está completo con el nombre del propietario del objeto.

    Por ejemplo, si el usuario "dbo" posee el objeto dbo.mystoredproc y otro usuario, "Harry", ejecuta este procedimiento almacenado mediante el comando "exec mystoredproc", se produce un error en la búsqueda de caché inicial por nombre de objeto porque el objeto no está calificado para el propietario. (Todavía no se sabe si existe otro procedimiento almacenado llamado Harry.mystoredproc. Por lo tanto, SQL ServerSQL Server no puede estar seguro de que el plan almacenado en caché para dbo.mystoredproc es el correcto para ejecutar.) A continuación, SQL ServerSQL Server obtiene un bloqueo de compilación exclusivo en el procedimiento y realiza preparativos para compilar el procedimiento. Esto incluye la resolución del nombre del objeto en un identificador de objeto. Antes de que SQL ServerSQL Server compile el plan, SQL ServerSQL Server usa este identificador de objeto para realizar una búsqueda más precisa de la caché de procedimientos y puede buscar un plan compilado previamente incluso sin la calificación del propietario. Si se encuentra un plan existente, SQL ServerSQL Server reutiliza el plan almacenado en caché y no compila realmente el procedimiento almacenado. Sin embargo, la falta de calificación del propietario obliga a SQL ServerSQL Server a realizar una segunda búsqueda de caché y obtener un bloqueo de compilación exclusivo antes de que el programa determine que se puede reutilizar el plan de ejecución almacenado en caché existente. Obtener el bloqueo y realizar búsquedas y otro trabajo necesario para llegar a este punto puede introducir un retraso para los bloqueos de compilación que conduce al bloqueo. Esto es especialmente cierto si muchos usuarios que no son el propietario del procedimiento almacenado ejecutan simultáneamente el procedimiento sin proporcionar el nombre del propietario. Tenga en cuenta que incluso si no ve SPID esperando bloqueos de compilación, la falta de calificación del propietario puede introducir retrasos en la ejecución del procedimiento almacenado y provocar un uso innecesariamente alto de la CPU. La siguiente secuencia de eventos se registrará en una sesión de evento extendido de SQL Server sql Serversql Server cuando se produzca este problema.

    Nombre del evento Texto
    rpc_starting mystoredproc
    sp_cache_miss mystoredproc
    sql_batch_starting mystoredproc
    sp_cache_hit mystoredproc
    ... ...

    sp_cache_miss se produce cuando se produce un error en la búsqueda de caché por nombre, pero, a continuación, se encontró un plan en caché coincidente en la memoria caché después de que el nombre del objeto ambiguos se resolviera en un identificador de objeto y hubiera un evento sp_cache_hit. La solución a este problema de bloqueo de compilación es asegurarse de que las referencias a procedimientos almacenados están calificadas por el propietario. (En lugar de exec mystoredproc, use exec dbo.mystoredproc.) Aunque la calificación del propietario es importante por motivos de rendimiento, no es necesario calificar el proceso almacenado con el nombre de la base de datos para evitar la búsqueda de caché adicional. El bloqueo causado por bloqueos de compilación se puede detectar mediante métodos de solución de problemas de bloqueo estándar.

  2. El procedimiento almacenado tiene el prefijo "sp_"

    Si el nombre del procedimiento almacenado comienza con el prefijo "sp_" y no está en la base de datos maestra, verá sp_cache_miss antes de que se golpee la memoria caché para cada ejecución, incluso si califica al propietario del procedimiento almacenado. Esto se debe a que el prefijo sp_ indica a SQL ServerSQL Server que el procedimiento almacenado es un procedimiento almacenado del sistema y que los procedimientos almacenados del sistema tienen reglas de resolución de nombres diferentes. (La ubicación "preferida" está en la base de datos maestra.) Los nombres de los procedimientos almacenados creados por el usuario no deben comenzar con "sp_".

  3. El procedimiento almacenado se invoca utilizando un caso diferente (superior /inferior)

    Si un procedimiento calificado por el propietario se ejecuta mediante un caso diferente (superior o inferior) del caso que se usó para crearlo, el procedimiento puede desencadenar un evento CacheMiss o solicitar un bloqueo COMPILE. Finalmente, el procedimiento utiliza el plan almacenado en caché y no se vuelve a compilar. Pero la solicitud de un bloqueo COMPILE a veces puede causar una situación de "cadena de bloqueo" si hay muchos SPID que están intentando ejecutar el mismo procedimiento utilizando un caso diferente al caso que se usó para crearlo. Esto es cierto independientemente del criterio de ordenación o intercalación que se esté utilizando en el servidor o en la base de datos. La razón de este comportamiento es que el algoritmo que se utiliza para encontrar el procedimiento en la memoria caché se basa en valores hash (para el rendimiento) y los valores hash pueden cambiar si el caso es diferente. La solución consiste en quitar y crear el procedimiento utilizando el mismo caso que el que se utiliza cuando la aplicación ejecuta el procedimiento. También puede asegurarse de que el procedimiento se ejecuta desde todas las aplicaciones utilizando el caso correcto (superior o inferior).

  4. El procedimiento almacenado se invoca como un evento Language

    Si intenta ejecutar un procedimiento almacenado como un evento de lenguaje en lugar de como un RPC, SQL ServerSQL Server debe analizar y compilar la consulta de eventos de lenguaje, determinar que la consulta está intentando ejecutar el procedimiento determinado y, a continuación, intente buscar un plan en la memoria caché para ese procedimiento. Para evitar esta situación en la que SQL ServerSQL Server debe analizar y compilar el evento de lenguaje, asegúrese de que la consulta se envía a SQL como RPC.

    Para obtener más información, consulte la sección "Procedimientos almacenados del sistema" en el artículo de Libros en pantalla "Creación de un procedimiento almacenado."

Problemas conocidos

Estos son algunos problemas conocidos que pueden evitar el almacenamiento en caché del plan:

  • Las variables BLOB se utilizan como parámetro de procedimiento almacenado. Para obtener más información, haga clic en el número de artículo siguiente para verlo en Microsoft Knowledge Base:
    2380435 FIX: El plan de consulta para un procedimiento almacenado no se almacena en caché si el procedimiento almacenado utiliza una variable BLOB y la variable se utiliza en una función de cadena en Microsoft SQL Server 2008
  • Utilice OPEN SYMMETRIC KEY en un lote de procedimientos almacenados/consultas. Para obtener más información, consulte la siguiente entrada de blog de MSDN: