Solución de problemas de bloqueo causados por bloqueos de compilación

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

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

Resumen

En Microsoft SQL Server, solo una copia de un plan de procedimientos almacenados suele incluirse 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, los identificadores de sesión (SPID) pueden empezar a bloquearse entre sí, ya que cada uno intenta obtener un bloqueo de compilación exclusivo en el objeto.

A continuación se muestran 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 es LCK_M_X (exclusivo) y waitresource tiene el formato OBJECT: dbid: object_id [[COMPILE]], donde object_id es el identificador de objeto del procedimiento almacenado.

• Los bloqueadores tienen waittype un valor NULL y un estado ejecutable. Las sesiones bloqueadas tienen waittypeLCK_M_X (bloqueo exclusivo), estado en suspensión.

• Aunque la duración general del incidente de bloqueo puede ser larga, no hay ninguna sesión única (SPID) que bloquee los demás SPID durante mucho tiempo. Hay bloqueos graduales; tan pronto como se complete una compilación, otro SPID toma el rol de bloqueador de cabezas durante varios segundos o menos, y así sucesivamente.

La siguiente información procede 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 waitresource columna (6:834102), 6 es el identificador de la base de datos y 834102 es el identificador de objeto. Este identificador de objeto pertenece a un procedimiento almacenado, no a una tabla.

Escenarios que conducen a bloqueos de compilación

En los escenarios siguientes se describen las causas de bloqueos de compilación exclusivos en procedimientos almacenados o desencadenadores.

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 inicial de caché por nombre de objeto porque el objeto no está calificado por el propietario. (Todavía no se sabe si existe otro procedimiento almacenado denominado Harry.mystoredproc . Por lo tanto, SQL Server no puede asegurarse de que el plan almacenado en caché de dbo.mystoredproc sea el correcto para ejecutarlo). A continuación, SQL Server obtiene un bloqueo de compilación exclusivo en el procedimiento y realiza preparativos para compilar el procedimiento. Esto incluye resolver el nombre del objeto en un identificador de objeto. Antes de que SQL Server compile 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 localizar un plan compilado previamente incluso sin calificación de propietario.

Si se encuentra un plan existente, SQL Server reutiliza el plan almacenado en caché y no compila realmente el procedimiento almacenado. Sin embargo, la falta de calificación de propietario obliga a SQL 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 en caché existente. La obtención del bloqueo y la realización de búsquedas y otro trabajo necesario para llegar a este punto puede introducir un retraso para los bloqueos de compilación que conducen al bloqueo. Esto es especialmente cierto si muchos usuarios que no son el propietario del procedimiento almacenado, ejecuten simultáneamente el procedimiento sin proporcionar el nombre del propietario. Incluso si no ve SPID esperando bloqueos de compilación, la falta de calificación del propietario puede introducir retrasos en la ejecución de procedimientos almacenados y provocar un uso elevado de la CPU.

La siguiente secuencia de eventos se registra en una sesión de eventos extendidos de SQL Server cuando se produce 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 después se encontró un plan en caché coincidente después de que el nombre de 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 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 procedimiento 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.

El procedimiento almacenado se vuelve a compilar con frecuencia

La recompilación es una explicación de los bloqueos de compilación en un procedimiento almacenado o desencadenador. Las formas de hacer que un procedimiento almacenado vuelva a compilar incluye EXECUTE... WITH RECOMPILE, CREATE PROCEDURE ...WITH RECOMPILEo mediante sp_recompile. Para más información, vea Volver a compilar un procedimiento almacenado. La solución en este caso es reducir o eliminar la recompilación.

El procedimiento almacenado tiene el prefijo sp_**

Si el nombre del procedimiento almacenado comienza con el sp_ prefijo y no está en la base de datos maestra, verá sp_cache_miss antes de que se alcance la memoria caché para cada ejecución incluso si el propietario califica el procedimiento almacenado. Esto se debe a que el sp_ prefijo indica a SQL Server que el procedimiento almacenado es un procedimiento almacenado del sistema y 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 empezar por sp_.

El procedimiento almacenado se invoca mediante un caso diferente (superior /lower)

Si se ejecuta un procedimiento calificado por el propietario mediante un caso de letra diferente (mayúscula o inferior) del caso que se usó para crearlo, el procedimiento puede desencadenar un evento CacheMiss o solicitar un bloqueo COMPILE. Para ilustrarlo, observe el caso de letra diferente usado en frente CREATE PROCEDURE dbo.SalesData ... a EXEC dbo.salesdata. Finalmente, 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 descrita anteriormente. La cadena de bloqueo puede producirse si hay muchas sesiones (SPID) que intentan ejecutar el mismo procedimiento mediante un caso diferente al que se usó para crearlo. Esto es cierto independientemente del criterio de ordenación o intercalación que se usa en el servidor o en la base de datos. El motivo 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 consiste en quitar y crear el procedimiento mediante el mismo caso de letra 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 mediante el uso del caso correcto (superior o inferior).

El procedimiento almacenado se invoca como un evento Language

Si intenta ejecutar un procedimiento almacenado como un evento de lenguaje en lugar de como RPC, SQL Server debe analizar y compilar la consulta de eventos de lenguaje, determine que la consulta intenta ejecutar el procedimiento determinado y, a continuación, intente buscar un plan en caché para ese procedimiento. Para evitar esta situación en la que SQL Server debe analizar y compilar el evento de lenguaje, asegúrese de que la consulta se envía a SQL Server como RPC. Por ejemplo, en el código de .NET, puede usar SqlCommand.CommandType.StoredProcedure para garantizar un evento RPC.

El procedimiento almacenado o sp_executesql usa un parámetro de cadena mayor que 8 KB

Si llama a un procedimiento almacenado o sp_executesql y pasa un parámetro de cadena superior a 8 KB, SQL Server usa un tipo de datos de objetos binarios grandes (BLOB) para almacenar el parámetro. Como resultado, el plan de consulta para esta ejecución no se conserva en la memoria caché del plan. Por lo tanto, cada ejecución del procedimiento almacenado o sp_executesql tiene que adquirir un bloqueo de compilación para compilar un nuevo plan. Este plan se descarta cuando se completa la ejecución. Para obtener más información, consulte la nota del almacenamiento en caché del plan de ejecución y reutilización con respecto a los literales de cadena mayores de 8 KB. Para evitar el bloqueo de compilación en este escenario, reduzca el tamaño del parámetro a menos de 8 KB.

Referencias

Comando OPEN SYMMETRIC KEY impide el almacenamiento en caché del plan de consulta