Errore humanum est, SQL Server ogni tanto può darci qualche piccolo grattacapo. E’ essenziale ragionare senza agire d’istinto (kill all) quando ci si trova ad affrontare questo tipo di problemi. Ecco pochi semplici passi su come risolvere un blocco di un processo su SQL Server causato da processi annidati. Creiamo un nostro caso reale, abbiamo lanciato una creazione di una chiave primaria su una tabella esistente attraverso l’esecuzione della seguente query:

ALTER TABLE dbo.Person ADD CONSTRAINT PK_Person
PRIMARY KEY NONCLUSTERED (PersonID);

PROBLEMA: Ci accorgiamo che la query ci mette troppo tempo per essere eseguita.

Prendiamo “scalpello e martello” e Apriamo SQL Server Management Studio.

Sulla barra delle applicazioni Standard, apriamo il “Monitor Attività“.

Monitor attività ci permette di visualizzare tutti i processi in esecuzione sul nostro database.

Cerchiamo il nostro processo filtrando la colonna Tempo di Attesa (Wait Time), che è in millisecondi, in modo decrescente. Il primo processo in lista è il nostro problema.

Per verificare meglio i problemi del processo eseguiamo questa query

select * 
from sys.dm_exec_requests r
join sys.dm_os_tasks t on r.session_id = t.session_id
where r.session_id = 'PIDPROCESSOBLOCCATO'

Andiamo ad analizzare il risultato.

Nella colonna Stato Attività (Status) viene riportato il valore SUSPENDED. Verifichiamo a quale tipo di blocco è sottoposto il processo nella colonna Last_wait_type nel nostro caso troviamo il valore LCK_M_SCH_M questo indica che il server sta cercando di modificare lo Schema. Se avete altri tipi di risultati vi rimando alla guida ufficiale Microsoft.

Ci accorgiamo che questo processo è bloccato perché è legato un altro processo. Per verificare a quale altro processo è legato guardate la colonna Blocking_session_id nel nostro caso troviamo un id di sessione da verificare.

Verifichiamolo dentro il Monitor Attività utilizzando i filtri. Ora le strade possono essere diverse, se utilizziamo la forza bruta e vogliamo risolvere alla male e peggio il problema killiamo il processo. Ma lo sconsiglio fortemente sopratutto se si lavora su ambienti di produzione.

Altrimenti uno degli errori più comuni di questo tipo di problemi è che qualcuno magari involontariamente ha disabilitato l’autocommit e si è dimenticato di fare un commit dell’ultima operazione eseguita sulla tabella dove stiamo creando la chiave primaria. In questo caso dobbiamo verificare chi è l’utente che ha eseguito l’operazione dirgli di fare un commit e il problema è presto risolto.

Ovviamente i problemi che possono causare uno stato SUSPENDED possono anche essere momentanei, causati da qualche deadlock o lock di tabelle, il database gestisce questo tipo di stato in maniera automatica.

Se avete avuto un’esperienza simile, lasciatemi un commento a riguardo.

Cheers!