Cómo encontrar y eliminar bloqueos en Microsoft SQL Server

Gestionar bloqueos en MS SQL Server puede ser un verdadero dolor de cabeza, especialmente al intentar ejecutar consultas que se bloquean o agotan el tiempo de espera. Es bastante común que una transacción se bloquee, lo que retrasa otros procesos y provoca una cascada de respuestas lentas. Normalmente, esto se debe a transacciones no confirmadas o incompletas, como cuando alguien olvida confirmar o revertir, y SQL simplemente mantiene ese bloqueo. Si la situación empeora, es posible que incluso vea cómo sus consultas se bloquean o se bloquean por completo. Por lo tanto, saber cómo detectar y eliminar estos bloqueos es fundamental.

A veces es un poco complicado, sobre todo en servidores con mucha actividad o cuando no se sabe con certeza qué transacción causó el problema. Por suerte, SQL Server cuenta con herramientas y comandos integrados para ayudar a localizar a los culpables. Herramientas como consultar la tabla sysprocesses, usar el Monitor de Actividad o incluso crear procedimientos almacenados rápidos pueden ahorrarle mucha frustración. Tenga cuidado al finalizar procesos; a veces, un comando de finalización puede generar problemas si no está seguro de su función. Aun así, si necesita forzar el desbloqueo de una tabla o sesión, conocer los conceptos básicos simplifica el proceso.

Cómo encontrar y eliminar bloqueos en MS SQL Server

¿Qué está causando los bloqueos y cómo verlos?

En primer lugar, ¿por qué ayuda esto? Porque una vez que se identifican las sesiones o consultas que causan bloqueos, se tiene una idea de qué se debe abordar. Por ejemplo, si un proceso lleva mucho tiempo intentando hacer algo que está atascado, se puede detener o investigar más a fondo. Si se experimentan respuestas lentas a las consultas o SQL Server informa de un alto nivel de bloqueos, estos comandos son el punto de partida.

Entonces, para verificar si hay procesos bloqueados, ejecute esto en SQL Server Management Studio (SSMS):

select cmd, spid, blocked, waittype, waittime, waitresource from sys.sysprocesses where blocked > 0

Esto mostrará todos los procesos en ejecución que están bloqueados, junto con quién los bloquea. Las columnas pueden indicar si una sesión ha estado esperando demasiado tiempo (por ejemplo, más de unos segundos), lo que indica un problema de bloqueo.

Si desea limitar las cosas a una base de datos específica, ejecute:

SELECT spid, blocked, dbid, lastwaittype, waittime FROM master.dbo.sysprocesses WHERE dbid = DB_ID('yourdatabase') AND blocked <> 0 ORDER BY waittime DESC

Aquí puede ver el ID del proceso (SPID), la base de datos y el tiempo de espera de cada proceso. La columna de bloqueos muestra qué proceso está causando la interrupción. Si detecta algo que lleva un tiempo bloqueado, puede investigar esa sesión con:

DBCC INPUTBUFFER(spid)

Lo que da la última consulta exacta que ejecutó, como si estuviéramos mirando por encima de su hombro para ver qué estaba intentando hacer.

Rastreando el origen del bloqueo

Algunos bloqueos son causados por árboles de transacciones completos, es decir, un proceso padre con varios procesos hijos. Para encontrar el proceso inicial que contiene el bloqueo, puede ejecutar:

select * from master.dbo.sysprocesses where spid = your_spid

Reemplace your_spidcon el ID del proceso que encontró anteriormente. Normalmente, la causa raíz es el primer proceso con «blocked=0», lo que significa que es el que mantiene el bloqueo. En algunas configuraciones, esto ayuda a identificar si una transacción está esperando un recurso externo o bloqueada en algún punto de una transacción larga.

Cómo matar un proceso y liberar el bloqueo

Esto es un poco severo, pero a veces es la única opción. Si has identificado un proceso que causa el bloqueo y es seguro eliminarlo, ejecuta:

KILL spid

Por ejemplo:

KILL 59

Tenga en cuenta que, en algunos servidores, finalizar un proceso puede dejar las cosas en un estado extraño o provocar reversiones. Si trabaja en un sistema de producción, verifique bien antes de hacerlo, ya que, por supuesto, SQL Server tiene que complicarlo más de lo necesario. La mejor práctica es finalizar procesos solo cuando esté seguro de que no están realizando tareas críticas o cuando tenga copias de seguridad.

Automatizar comprobaciones de bloqueo con procedimientos almacenados

¿Te da pereza o buscas una forma más rápida de controlarlo? Puedes crear un pequeño procedimiento almacenado como este, que recupera la consulta que causó el bloqueo:

CREATE PROCEDURE GetCurrentQueryCode @SPID int AS DECLARE @sql_handle varbinary(64) DECLARE @sql_text nvarchar(max) SELECT @sql_handle = sql_handle FROM sys.dm_exec_sessions WHERE session_id = @SPID SET @sql_text = (SELECT text FROM sys.dm_exec_sql_text(@sql_handle)) PRINT @sql_text

Luego, simplemente ejecuta:

EXEC GetCurrentQueryCode 51

Esto permite obtener un vistazo rápido del último comando SQL ejecutado por esa sesión. Como alternativa, es posible obtener el SQL directamente de la caché con:

select * from sys.dm_exec_sql_text(0xYOURSQLHANDLE)

¿Dónde 0xYOURSQLHANDLEestá el identificador binario que obtienes de las consultas anteriores? Esto puede ser complicado, pero es muy útil para diagnosticar problemas de bloqueo en un servidor con mucha actividad.

Uso de las herramientas integradas de SQL Server Management Studio

Y, por supuesto, SSMS ofrece prácticas herramientas visuales para localizar bloqueos. Haga clic derecho en el servidor, seleccione Monitor de Actividad y expanda Procesos. Aparecerá información sobre las solicitudes, que muestra quién está esperando, quién está bloqueando y durante cuánto tiempo, especialmente si se observa el estado SUSPENDIDO. Como alternativa, puede ir a Informes > Todas las Transacciones Bloqueadas. Muestra una lista de las consultas bloqueadas, los SPID y los orígenes de los bloqueos. Otra herramienta útil si la situación se complica es habilitar Eventos Extendidos o SQL Server Profiler, pero esto es un poco más avanzado.

En definitiva, gestionar bloqueos se trata de encontrar el equilibrio entre saber qué ocurre entre bastidores y actuar sin causar más caos. Normalmente, una estrategia rápida de identificar y eliminar funciona si estás en apuros. Recuerda: los bloqueos ocurren por algo, y a veces solo hay que esperar para eliminarlos. Pero estar equipado con estos comandos te hace mucho más seguro cuando algo lo está retrasando todo.