Solucionar problemas de bloqueo causados por bloqueos de compilación

En este artículo se describe cómo solucionar problemas de bloqueo causados por bloqueos de compilación.

Versión original del producto:   SQL Server
Número KB original:   263889

Resumen

En Microsoft SQL Server, solo una copia de un plan de procedimientos almacenados suele estar en caché a la vez. La aplicación de esto requiere serialización de algunas partes del proceso de compilación, y esta sincronización se realiza en parte mediante 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, es posible que los IDs de sesión (SPID) comiencen a bloquearse entre sí mientras intentan 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 el resultado de bloqueo:

  • waittype para los SPID de sesión bloqueados y (normalmente) de bloqueo es (exclusivo) y tiene el formato , donde se encuentra el identificador de objeto LCK_M_X waitresource del procedimiento OBJECT: dbid: object_id [[COMPILE]] object_id almacenado.

  • Los bloqueadores waittype tienen NULL, estado que se puede ejecutar. Los bloqueados tienen waittype LCK_M_X (bloqueo exclusivo), estado en estado de bloqueo.

  • Aunque la duración del incidente de bloqueo puede ser larga, no hay ningún SPID único que bloquee los otros SPID durante mucho tiempo. Hay bloqueos graduales. En cuanto se completa una compilación, otro SPID asume el rol de bloqueador de cabeza durante varios segundos o menos, y así sucesivamente.

La siguiente información es 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 (6:834102), 6 es el identificador de base de datos y waitresource 834102 es el identificador de objeto. 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 de los bloqueos de compilación en un procedimiento almacenado o desencadenador. La solución en este caso es reducir o eliminar los recompiles.

Escenarios adicionales que llevan 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 y otro usuario, ejecuta este procedimiento almacenado mediante el comando , se produce un error en la búsqueda de caché inicial por nombre de objeto porque el objeto no está calificado por dbo.mystoredproc Harry el exec mystoredproc propietario. (Todavía no se sabe si existe otro procedimiento almacenado Harry.mystoredproc denominado. Por lo SQL Server, no puede estar seguro de que el plan almacenado en caché dbo.mystoredproc sea el correcto para ejecutar). SQL Server obtiene un bloqueo de compilación exclusivo en el procedimiento y realiza los preparativos para compilar el procedimiento. Esto incluye la resolución del nombre del objeto en un identificador de objeto. Antes SQL Server compilar el plan, SQL Server usa este identificador de objeto para realizar una búsqueda más precisa de la memoria caché de procedimientos y puede buscar un plan compilado anteriormente, incluso sin cualificación del propietario.

    Si se encuentra un plan existente, SQL Server el plan almacenado en caché y no compila realmente el procedimiento almacenado. Sin embargo, la falta de cualificación del propietario obliga a SQL Server a realizar una segunda búsqueda en caché y obtener un bloqueo de compilación exclusivo antes de que el programa determine que el plan de ejecución en caché existente se puede volver a usar. 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 a bloqueos. 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. Incluso si no ve spids en espera de bloqueos de compilación, la falta de cualificación del propietario puede provocar retrasos en la ejecución de procedimientos almacenados y provocar un uso elevado de la CPU.

    Cuando se produzca este problema, se registrará la siguiente secuencia de eventos en SQL Server sesión de evento extendido.

    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 la memoria caché por su nombre, pero, a continuación, se encontró un plan en caché que coincide en última instancia después de que el nombre del objeto ambiguo se resolvió en un identificador de objeto y hay un sp_cache_hit evento.

    La solución a este problema de bloqueo de compilación es asegurarse de que las referencias a procedimientos almacenados están cualificadas por el propietario. (En lugar de exec mystoredproc , usa exec dbo.mystoredproc .) Aunque la cualificació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 el uso de 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 y no está en la base de datos maestra, verá sp_cache_miss antes de que la memoria caché se inicie para cada ejecución, incluso si el propietario califica el procedimiento sp_ almacenado. Esto se debe a que el prefijo indica SQL Server que el procedimiento almacenado es un procedimiento almacenado del sistema y que los procedimientos almacenados del sistema tienen reglas de resolución sp_ 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 con un caso diferente (superior /inferior)

    Si se ejecuta un procedimiento calificado por el propietario 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. Con el tiempo, el procedimiento usa el plan almacenado en caché y no se vuelve a compilar. Pero la solicitud de un bloqueo COMPILE a veces puede provocar una situación de cadena de bloqueo si hay muchos SPID que están intentando ejecutar el mismo procedimiento mediante un caso diferente del caso que se usó para crearlo. Esto es así independientemente del criterio de ordenación o intercalación que se usa en el servidor o en la base de datos. La razón de este comportamiento es que el algoritmo que se usa para buscar 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 alternativa es colocar y crear el procedimiento usando el mismo caso que el que se usa cuando la aplicación ejecuta el procedimiento. También puede asegurarse de que el procedimiento se ejecuta desde todas las aplicaciones usando 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 idioma en lugar de como RPC, SQL Server debe analizar y compilar la consulta de eventos de idioma, determinar que la consulta está intentando ejecutar el procedimiento en particular y, a continuación, intentar encontrar un plan en la memoria caché para ese procedimiento. Para evitar esta situación en la que SQL Server analizar y compilar el evento de idioma, asegúrese de que la consulta se envía a SQL como RPC.

    Para obtener más información, vea la sección Procedimientos almacenados del sistema en el artículo Books Online Creating a Stored Procedure.

Referencias

El comando OPEN SYMMETRIC KEY impide el almacenamiento en caché del plan de consultas