Técnicas para identificar consultas bloqueadoras y causas de bloqueos de SQL Server

Los procesos bloqueados causados por bloqueos en los objetos de la base de datos son un problema común. El bloqueo asegura la integridad de los datos almacenados dentro de las bases de datos forzando cada transacción SQL Server ejecutada a pasar la prueba ACID que considera que cada transacción debe cumplir con los siguientes requerimientos:

  • Atomicidad –una transacción debe ser totalmente completada cuando se ejecuta, o no se ejecutará.
  • Consistencia – las transacciones deben crear un nuevo estado de datos que sea válido, o si cualquier fallo ocurre, debe retornar todos los datos al estado que estaba antes de que la transacción sea ejecutada/iniciada.
  • Aislamiento – una transacción que está en proceso de ejecución y no ha sido enviada aún, debe ser aislada de todas las otras transacciones.
  • Durabilidad  – los datos enviados deben ser grabados en una forma que mantendrá los datos en un estado correcto si la transacción no se completa, en el evento de una falla o un reinicio del sistema.

Como parte de la prueba ACID, el aislamiento es realizado bloqueando objetos y, como tal, el aislamiento puede afectar una o más filas de datos hasta que la transacción SQL Server que es ejecutada contra ese objeto sea enviada. Esto significa, en la práctica que, bloqueando el objeto, SQL Server evitará que cualquier otro proceso haga algún cambio contra los datos en ese objeto hasta que el bloqueo sea removido, ya sea enviando el cambio o retrotrayéndolo.

Imponer el bloqueo en un objeto causará que cualquier otro proceso que mande una solicitud contra el objeto bloqueado tenga que esperar hasta que el bloqueo es removido, lo cual retrasará la ejecución de ese proceso. El estado cuando una transacción está esperando para que el bloqueo sea removido o está parada es lo que se conoce como bloqueo. SQL Server está diseñado para trabajar de esta forma y, como tal, los bloqueos son una problemática inherente. Por tanto, sólo cuando un nivel alto de bloqueos ocurre, es detectado en SQL Server, o en un caso en el que el bloqueo ocurre y requiere de cantidades excesivas de tiempo para ser resuelto, debería ser tratado como un problema potencial.

Un ejemplo práctico de bloqueo de SQL Server es cuando la Transacción #1 está tratando de actualizar datos en la Tabla A, y mientras la Transacción #1 aún está corriendo y no está completada, la Transacción #2 intenta emplazar un nuevo bloqueo en la Tabla A; si la fila que debería ser eliminada es también la fila que será actualizada por la Transacción #2, entonces la Transacción #2 encontrará u bloqueo. Esto es porque la Transacción #1 tiene un bloqueo exclusivo impuesto en la tabla mientras la transacción esté corriendo.

Tomemos los siguientes scripts como un ejemplo.

Script 1

BEGIN TRAN
UPDATE [dbo].[TableX]
   SET [ZAP_ID] = 5
 WHERE [ZAP_ID] = 9

COMMIT

Script 2a

DELETE FROM [dbo].[TableX]
    WHERE [ZAP_ID] = 9

Script 2b

SELECT COUNT(*) FROM [dbo].[TableX]

Script 2c

TRUNCATE TABLE [dbo].[TableX]

Estos scripts serán usando para crear un bloqueo de SQL Server.

Primero, ejecute el Script 1, pero sólo la parte resaltada en amarillo (no ejecute el comando COMMIT). El script comenzará una transacción y emplazará un bloqueo en filas específicas en TableX.

Ahora, ejecute cualquiera de los otros scripts y ellos no podrán completarse porque la transacción inicial mantendrá su bloqueo exclusivo en las mismas filas en TableX que otros scripts están tratando de acceder. Como consecuencia de esto, los scripts tendrán que esperar a que transacción se complete (para realizar un COMMIT en la transacción).

Este simple ejemplo ilustra una situación cuando una transacción que está en progreso puede BLOQUEAR uno o más procesos que deben esperar hasta que la transacción con bloqueo exclusivo se complete o retrotraiga.

Así que, ¿cómo identificamos las instancias de SQL Server bloqueadas? Hay un par de maneras de hacer esto en SQL Server:

  • Usando un monitor de actividad
  • Usando DMVs de SQL Server

Usar el Monitor de Actividad (Activity Monitor)

Activity Monitor es una herramienta de SQL Server Mangement Studio que permite al usuario ver detalles de las conexiones SQL Server existentes, y puede ser usada para mostrar información acerca de los procesos abiertos y los bloqueos en los recursos de SQL Server.

Para abrir Activity Monitor en SQL Server Management Studio, haga clic derecho en el nombre del servidor en Object Explorer y haga clic en Activity Monitor.

Expanda Processes y localice el proceso que está esperando. Una vez que el proceso es localizado, dé un vistazo la columna Blocked para encontrar el ID del proceso que está causando el bloqueo.

Ahora, cuando el proceso que está causando el bloqueo es identificado, tiene que ser localizado en la misma página Processes.

Ahora, cuando la información acerca del proceso que está causando el bloqueo es encontrada, el usuario puede optar por matar ese proceso o permitirle correr hasta que se complete. Para matar el proceso, haga clic derecho en él y seleccione Kill Process.

Usando DMVs

La vista de administración dinámica sys.dm_exec_request es usada para identificar los bloqueos de SQL Server. Puede ser usada para listar los bloqueos actualmente activos ejecutando la siguiente consulta.

USE [master]
GO
SELECT  session_id,
blocking_session_id,
wait_time,
wait_type,
last_wait_type,
wait_resource,
transaction_isolati,
on_level,
lock_timeout
FROM sys.dm_exec_requests
WHERE blocking_session_id <> 0
GO

Puede verse en los resultados qué proceso está bloqueado (sesión_id), qué proceso está bloqueando (block_session_id), qué tipo de espera está involucrado y cuál es el tiempo de espera de ese bloqueo, así como los recursos involucrados.

Para terminar la sesión que bloquea, ID 74 en este caso, usamos el comando KILL.

KILL 74
GO

 

Quick tip icon

Consejo rápido:

Tenga cuidado al matar un proceso y asegúrese de que hay un completo entendimiento de qué está haciendo ese proceso y por qué está causando bloqueos. Cuando se mata una transacción, todos los cambios de datos hecho por ese proceso pueden ser retrotraídos a un estado previo.

Pero estos métodos nativos de SQL Server pueden ser usados sólo para detectar e identificar bloqueos activos. En muchas situaciones, cuando un bloqueo es detectado e identificado, es importante saber si este constituye un comportamiento normal y si ocurre raramente o sólo una vez. Sin tal información, no es posible realizar la resolución de problemas requerida, simplemente porque no está disponible ningún dato histórico como la frecuencia del bloqueo y la extensión del bloqueo.

Es por esto que herramientas de monitoreo de desempeño de terceros como ApexSQL Monitor pueden ser útiles.

Identificando consultas bloqueadoras en SQL Server

ApexSQL Monitor es una herramienta de monitoreo de desempeño que puede rastrear estadísticas de espera a nivel individual de la consulta y almacenar datos en una base de datos de repositorio central para asegurar el acceso fácil a información histórica.

Para usar ApexSQL Monitor para identificar los bloqueos de SQL Server:

  1. Seleccione la instancia de SQL Server en el panel izquierdo, para la cual hay que solucionar problemas.
  2. Vaya a la pestaña Query waits seleccionado Charts en el menú principal.
  3. Ahora seleccione la pestaña Query waits y seleccione el botón radial Bloqued queries.


Si hay consultas bloqueadas, ellas serán listadas en la cuadrícula y mostradas en el gráfico.

Expandiendo la consulta específica con el signo “+” al principio de la fila, los detalles acerca de ese bloqueo de consulta serán mostrados.

Aquí, la información más importante está relacionada a la sección Blockers: Información acerca del número SPID del proceso que está causando el bloqueo y los Recursos involucrados en este bloqueo (Wait resource). Para una mejor comprensión de cómo descifrar el recurso, por favor refiérase al artículo de Microsoft INF: Entendiendo y resolviendo problemas de bloqueo de SQL Server

Cada bloqueo es realmente una situación cuando un proceso está esperando a que otro proceso libere el bloqueo, de manera que SQL Server registrará esa espera usando el tipo de espera LCK_M_XXX. Dependiendo de qué está esperando la consulta bloqueada, esta espera de consulta será mostrada. Haciendo clic en el nombre del tipo de espera, la información de ayuda acerca de ese tipo de espera y un consejo acerca de la resolución de problemas con enlaces adicionales a recursos acerca de ese tipo de espera será mostrada.

Así que, ahora, es fácil para el usuario obtener información detallada acerca no sólo del bloqueo actual, sino también acerca de todos los bloqueos que ocurrieron en el SQL Server monitoreado. Los usuarios pueden ahora analizar fácilmente cuán a menudo una consulta específica ha sido bloqueada y por qué proceso(s). Para revisar el historial de los bloqueos para esa consulta en particular, acceda a la pestaña History.

Como puede verse en la imagen, esta consulta ha sido bloqueada sólo una vez durante el día por el proceso SPID 74, pero si revisamos las otras consultas bloqueadas, puede verse que siempre es el proceso de SPID 74 que está involucrado en todos los bloqueos. Esto parece información accionable que podría ser investigada más a fondo.

Adicionalmente, es una buena idea revisar los días previos por bloqueos que ocurrieron, para entender mejor cuándo, por qué y cómo ocurrieron. Para hacer eso, seleccione el día previo, por ejemplo, desde el seleccionador de fechas en la pestaña Query waits.

Como puede verse en la imagen, el SPID 72 fue bloqueado 4 veces durante el día previo, y después de dar un vistazo más cercano a los detalles, puede verse que el SPID 72 fue bloqueado por SPID 74. Basados en esto, hay evidencia confiable de que este no es sólo un caso raro de bloqueo, sino que el bloqueo ocurre regularmente y es causado por SPID 74. Por lo tanto, SPID 74 y la consulta detrás de ese ID de proceso está causando los bloqueos y la consulta de SPID 74 debe ser investigada a más profundidad.

Para rastrear la consulta o consultas bloqueadas para una inspección más fácil y rápida en el futuro, asignar un alias a esa consulta (por ejemplo, “BlockedUpdateTableX”) asegurará que la consulta será notada fácilmente si la situación se repite.

Para hacer eso:

  1. Vaya a la pestaña Rename.
  2. Inserte el nombre deseado (descriptivo o de acuerdo a la conveción de la compañía).
  3. Grabe el cambio con Save.

 

Recursos útiles:

 

julio 7, 2017