Descubrir bloqueos en SQL Server

Descubrir bloqueos en SQL Server

Los bloqueos son la principal causa de bajo rendimiento inexplicable, y generalmente son muy complicados de cazar, de detectar y de solucionar, hablaremos de bloqueos más adelante, pero ahora mismo vamos a poner un procedimiento almacenado que sirve para cazar a los procesos bloqueadores y pondremos una imagen en la que se ve cual es el resultado, en proximos capítulos pondremos como configurar el SQL Mail para que además nos lleguen por correo electrónico

 */   CREATE PROC CazaBloqueadores @waittime varchar(10) 
as 
begin 
begin try 
declare @t table (bloqueado int, bloqueador int) -- usaremos la variable de tipo tabla para esperar 
insert into @t (bloqueado,bloqueador) 
select session_id, blocking_session_id 
from sys.dm_exec_requests der where blocking_session_id!=0 -- Recogemos los objetos que están bloqueados.. 
waitfor delay @waittime 
SELECT der.session_id [Sesion bloqueada], det.text [Comando Bloqueado],
 der.[blocking_Session_id] [id del bloqueador], subq.TEXT [Comando Bloqueador], 
des.[host_name] [Nombre del host], des.[Program_name] [Aplicaci├│n], 
db_name(der.database_id) [Base de Datos], user_name(der.user_id) [usuario de bbdd], 
des.login_name [login], cast(des.context_info as varchar(100)) [Informacion Adicional], 
des.cpu_time [Tiempo de CPU], des.total_elapsed_time [Tiempo de espera], 
des.logical_reads [lecturas], des.writes [escrituras], der.start_time [Comenz├│], 
der.status [estado], 
case des.transaction_isolation_level 
when 0 then 'Sin especificar' 
when 1 then 'Read Uncommitted' 
when 2 then 'Read Committed' 
when 3 then 'Repeatable reads' 
when 4 then 'Serializable' 
when 5 then 'Snapshot' 
else 'Inesperado' end [Nivel de Aislamiento] 
FROM sys.dm_exec_requests der 
                      INNER JOIN sys.dm_exec_sessions des 
                                 ON der.session_id=des.session_id 
                            CROSS APPLY sys.dm_exec_sql_text(sql_handle) det 
                     LEFT JOIN  (select SPID session_id, det2.[text] from sys.sysprocesses der2 CROSS APPLY 
                                         sys.dm_exec_sql_text(sql_handle) det2 ) subq 
                    ON der.[blocking_Session_id]=subq.session_id 
WHERE der.blocking_session_id in 
       (select bloqueador from @t) 
return 0 
end try 
begin catch 
declare @error sysname 
set @error=ERROR_MESSAGE() 
raiserror (@error,16,1) return 1 
end catch 
end

y esta la versi├│n para SQL 2000

-- ***************************************************************************
-- Copyright (C) PortalSQL 2006
--
-- Fichero: CazaBloqueadores.sql
-- Descr.: Busca bloqueos que se mantengan
-- durante el tiempo que le pasemos por parámetro, si hay bloqueos que se mantienen
-- muestra informaci├│n relevante
-- autor: Miguel Egea G├│mez -- Solid Quality Learning IberoAmericana
--
-- comentarios: Puede ser adecuado meter ejecutar este procedimiento desde un job y
-- usar DatabaseMail para reportar sus resultados al administrador de base
-- de datos.
-- He usado la vista de compatibilidad sys.sysprocesses, esto es porque he c
-- comprobado que algunas ocasiones la tabla sys.dm_exec_request no tiene registros
-- para los bloqueadores.
--
-- Parámetros de entrada:
-- @waittime --> Tiempo que debe esperarse para considerar que hay un bloqueo
-- Valores de retorno : 0--> Funcion├│ de forma correcta
-- 1--> En cualquir otro caso
--
-- 
-- yyyy/mm/dd by description
-- ========== ======= ==========================================================
-- 2006/06/04 MEG Creaci├│n.
--
-- ***************************************************************************
-- Ejemplo de llamada
--
/*
DECLARE @Valor varchar(10) ,@result int
SET @Valor='00:00:02'
EXEC @result =CazaBloqueadores @valor
select @result

Aquí puedes ver una imagen en la que se ejecuta un comando que genera un bloqueo, y un comando que se ve bloqueado por ese bloqueo, cuando se crea una tabla dentro de una transacción se genera un bloqueo, un select que consulte esa tabla espera a que se haga commit o rollback en la creación, para devolver los registros o un error. 

a continuación puedes ver más resultados devueltos por el procedimiento almacenado

Disfruta del c├│digo

Modificado para incluir Versi├│n SQL Server 2000 en marzo de 2011

Si buscas un código equivalente, pero para SQL Server 2000, este podría estar bien

 

create procedure cazabloqueos2000
as
begin 
create table #bloqueos(spidbloqueador int,hostnamebloqueador sysname,loginamebloqueador sysname,
					   spidbloqueado int,hostnamebloqueado sysname,loginamebloqueado sysname)
insert into #bloqueos					   
select a.spid,a.hostname,a.loginame,b.spid,b.hostname,b.loginame From (
select * from sysprocesses where blocked!=0) a inner join
master.sys.sysprocesses b on a.blocked=b.spid
declare @spid int

alter table #bloqueos add sentenciabloqueador varchar(8000);
alter table #bloqueos add sentenciabloqueado varchar(8000);

declare cr cursor for select distinct spidbloqueador from #bloqueos
open cr
fetch next from cr into @spid
create table #temp (eventtype varchar(100),paremeters varchar(100),eventinfo varchar(8000) )
declare @sql nvarchar(1000)

 while (@@FETCH_STATUS=0)
  begin
   select @sql='dbcc inputbuffer(' +cast(@spid as varchar(10))  +')'
   insert into #temp exec(@sql)    
   
    update b set sentenciabloqueador=eventinfo  
     from #bloqueos b cross join #temp
       where b.spidbloqueador=@spid
    delete from #temp
   fetch next from cr into @spid
  end
close cr 
deallocate cr 



declare cr cursor for select distinct spidbloqueado from #bloqueos
open cr
fetch next from cr into @spid


 while (@@FETCH_STATUS=0)
  begin
   select @sql='dbcc inputbuffer(' +cast(@spid as varchar(10))  +')'
   insert into #temp exec(@sql)    
   
    update b set sentenciabloqueado=eventinfo  
     from #bloqueos b cross join #temp
       where b.spidbloqueado=@spid
    delete from #temp
   fetch next from cr into @spid
  end
close cr 
deallocate cr 

select * From #bloqueos
end

13 Comentarios en “Descubrir bloqueos en SQL Server

  1. Un consejo, instalate el plugin codebox para que sea mas entendible para todos nosotros tu codigo 😀

  2. Lo dicho eres una p… m├íquina. Interesant├¡simo como todos tus art├¡culos. Por cierto, haber cuando nos deleitas con uno dedicado a directivas, ya sabes, por pedir que no quede 🙂

    Nos vemos ….

  3. Instalado el plugging, realmente instalado por segunda vez, porque lo instal├® la primera y algo debi├│ subirse mal, pens├® que no funcionaba ya hora… me parece una maravilla, gracias GeekZero

  4. Muchas gracias por el aporte. Espero que me sea de utilidad en algún momento, que seguro que así será.

    Un saludo

  5. M sale error “sql_handle” no es una opci├│n reconocida de sugerencias de tabla. Si se debe usar como un par├ímetro en una funci├│n con valores de tabla, aseg├║rese de que el modo de compatibilidad de la base de datos est├í establecido en 90.
    Tengo sql2008,,

  6. Efectivaamente Rafael, esto solo funciona en bases de datos SQL 2005 y adelante, es decir nivel de compatibilidad 90 y adelante. Para compatibilidad 2000 hay que usar dbcc inputbuffer.

  7. Miguel, me interesa mucho tu proceso, pero me pasa lo mismo que a Rafael, pero no se como colocar el dbcc inputbuffer, me puedes ayudar por favor, Gracias

  8. Incluida versi├│n para SQL Server 2000, espero que resulte ├║tlil. y que funcione, porque lo hice en un 2008 intentando acordarme de la sintaxis 🙂

  9. Hola Miguel, el codigo me resulta muy util, me has salvado.
    Una pregunta, lo quiero meter en un job, pero no quiero afectar mas el performance del servidor. Que frecuencia recomiendas para que se ejecute el job sin darle sobrecarga al servidor?

  10. Hola Miguel, el nivel de compatibilidad tiene que ser igual o mayor a 90 , en sql tiene por default 80 y por ende tu store no funciona

  11. Hola Miguel, el nivel de compatibilidad tiene que ser igual o mayor a 90 , en sql 2000 tiene por default 80 y por ende tu store no funciona.

  12. Tampoco puedo subir mi compatibilidad a mi sql 2000 porque hay otras funciones que dejarian de funcionar. he leido en un post anterior y debo incluir el dbcc inputbuffer , pero no especificas donde ?

  13. Disculpad que no lo viera antes, el código para la versión 2000 está en el ejemplo final, si lo despliegas es todo el código necesario, y ya usa dbcc inputbuffer

Deja un comentario

Tu dirección de correo electrónico no será publicada. Los campos obligatorios están marcados con *