Transact-SQL: processi bloccanti e relative query

Utilizzando SQL Server Enterprise Manager è possibile visualizzare l’attività corrente del server,  sotto forma di elenco di processi e loro situazione:

Può capitare che processi siano bloccati da altri processi. Ed è possibile visualizzare l’ultimo comando TSQL eseguito dai processi:

Unendo le informazioni trovate in questi due siti

http://www.sqlservercentral.com/Forums/Topic365627-146-1.aspx

http://www.kodyaz.com/articles/identify-kill-blocking-sql-server-processes.aspx

ho realizzato uno script che mostra i processi bloccanti, ed il relativo comando TSQL:

use master
GO
declare @spid smallint

select * from sysprocesses (nolock) where blocked = 0 and spid in (
select blocked from sysprocesses (nolock) where blocked <> 0 )

DECLARE c_curs CURSOR FOR
select spid from sysprocesses (nolock) where blocked = 0 and spid in (
select blocked from sysprocesses (nolock) where blocked <> 0
)

OPEN c_curs
FETCH NEXT FROM c_curs
INTO @spid

WHILE @@FETCH_STATUS = 0
BEGIN

dbcc inputbuffer(@spid)

FETCH NEXT FROM c_curs
INTO @spid

END --while

CLOSE c_curs
DEALLOCATE c_curs

Breve spiegazione: prima viene eseguita la query:

select * from sysprocesses (nolock) where blocked = 0 and spid in (
select blocked from sysprocesses (nolock) where blocked <> 0 )

che restituisce l’elenco dei processi bloccanti, poi sulla stessa query viene definito un cursore, e di ogni processo vado a prendere il comando TSQL che sta eseguendo:

dbcc inputbuffer(@spid)

Qualche nota:

• sysprocesses: é una tabella di sistema, e “contiene informazioni sui processi in esecuzione” (http://msdn.microsoft.com/it-it/library/ms179881(SQL.90).aspx)
• NOLOCK: un ottimo articolo su questa direttiva è qui: http://www.karpach.com/Using-WITH-NOLOCK-T-SQL.htm. NOLOCK permette a SQL Server di ignorare i lock presenti sulle tabelle. Incrementa le prestazione su query su tabelle con un grossa mole di dati. Da usare con la massima cautela.
• dbcc: Direttamente da mamma Microsoft: Il linguaggio di programmazione Transact-SQL include istruzioni DBCC che fungono da comandi DBCC (Database Console Command) per SQL Server 2005: http://msdn.microsoft.com/en-us/library/ms188796.aspx
• dbcc inputbuffer: http://msdn.microsoft.com/en-us/library/ms187730(SQL.90).aspx