Cómo identificar bloqueos en SQL Server

SQL Server es capaz recibir solicitudes de servicio de un gran número de usuarios concurrentes. Cuando SQL Server atiende las solicitudes de muchos clientes, hay una gran posibilidad de que se produzcan conflictos si diferentes procesos solicitan el acceso a los mismos recursos al mismo tiempo. Un conflicto en el que un proceso está esperando otro para liberar un recurso se llama bloqueo. Si bien en SQL Server un proceso bloqueado se resuelve cuando el primer proceso libera el recurso, pero hay veces en que un proceso mantiene un bloqueo de transacción y no lo libera. Aquí les dejo diferentes técnicas para detectar bloqueos y solucionar los problemas en SQL Server.

Nuevamente les dejo un resumen-traducción de una publicación de MSSQLTips escrita por Basit Farooq.

Ver los procesos bloqueados:

1. USE [master]
2. GO
3. SELECT session_id
4.     ,blocking_session_id
5.     ,wait_time
6.     ,wait_type
7.     ,last_wait_type
8.     ,wait_resource
9.     ,transaction_isolation_level
10.     ,lock_timeout
11. FROM sys.dm_exec_requests
12. WHERE blocking_session_id <> 0
13. GO

Ver los bloqueos según su estado (GRANT-WAIT-CONVERT):

1. USE [master]
2. GO
3. SELECT * from sys.dm_tran_locks
4. WHERE request_status = 'CONVERT'
5. GO

La columna request_session_id contiene el ID del proceso. Para ver el bloqueo en una base de datos particular, ejecutar la siguiente consulta, que une sys.dm_tran_locks con sys.partitions:

1. USE [master]
2. GO
3. SELECT tl.resource_type
4.     ,tl.resource_associated_entity_id
5.     ,OBJECT_NAME(p.object_id) AS object_name
6.     ,tl.request_status
7.     ,tl.request_mode
8.     ,tl.request_session_id
9.     ,tl.resource_description
10. FROM sys.dm_tran_locks tl
11.   LEFT JOIN sys.partitions p
12.   ON p.hobt_id = tl.resource_associated_entity_id
13. WHERE tl.resource_database_id = DB_ID()
14. GO

La vista dinámica sys.dm_os_waiting_tasks nos brinda información sobre bloqueos y procesos bloqueados. Los procesos bloqueados están listados en la columna session_id y los bloqueos en blocking_session_id.

1. USE [master]
2. GO
3. SELECT w.session_id
4.     ,w.wait_duration_ms
5.     ,w.wait_type
6.     ,w.blocking_session_id
7.     ,w.resource_description
8.     ,s.program_name
9.     ,t.text
10.     ,t.dbid
11.     ,s.cpu_time
12.     ,s.memory_usage
13. FROM sys.dm_os_waiting_tasks w
14.   INNER JOIN sys.dm_exec_sessions s
15.   ON w.session_id = s.session_id
16.   INNER JOIN sys.dm_exec_requests r
17.   ON s.session_id = r.session_id
18.   OUTER APPLY sys.dm_exec_sql_text (r.sql_handle) t
19. WHERE s.is_user_process = 1
20. GO

Los muy conocidos sp_who/sp_who2 nos brindan información sobre todas las sesiones establecidas con la BD y sus respectivos ID (ipid). Ambos procedimientos aceptan parámetros. La columna blk de sp_who y la columna blkby de sp_who2 contienen el spid de procesos bloqueados.

1. USE master
2. GO
3. EXEC sp_who 'active';
4. -- EXEC sp_who2 'active';
5. GO

Y para matar un proceso tenemos la instrucción KILL. Se puede utilizar la instrucción KILL para ver el estado de un proceso o matarlo:

1. USE master
2. GO
3. KILL spid | UOW [WITH STATUSONLY]
4. GO

Para mayor información sobre este tema los invito a visitar la artículo original haciendo click AQUÍ o enviándome un mensaje, saludos.