Informazioni e risoluzione dei problemi di blocco di SQL Server

Si applica a: SQL Server (tutte le versioni supportate), Istanza gestita di SQL di Azure

Numero KB originale: 224453

Obiettivo

L'articolo descrive il blocco in SQL Server e illustra come risolvere i problemi di blocco.

In questo articolo il termine connessione fa riferimento a una singola sessione di accesso del database. Ogni connessione viene visualizzata come ID sessione (SPID) o session_id in molte DMV. Ognuno di questi SPID viene spesso definito processo, anche se non è un contesto di processo separato nel senso consueto. Ogni SPID è invece costituito dalle risorse del server e dalle strutture di dati necessarie per gestire le richieste di una singola connessione da un determinato client. Una singola applicazione client può avere una o più connessioni. Dal punto di vista di SQL Server, non esiste alcuna differenza tra più connessioni da una singola applicazione client in un singolo computer client e più connessioni da più applicazioni client o più computer client; sono atomici. Una connessione può bloccarne un'altra, indipendentemente dal client di origine.

Che cosa sta bloccando

Il blocco è una caratteristica inevitabile e di progettazione di qualsiasi sistema di gestione di database relazionali (RDBMS, Relational Database Management System) con concorrenza basata sul blocco. Come accennato in precedenza, in SQL Server il blocco si verifica quando una sessione contiene un blocco su una risorsa specifica e un secondo SPID tenta di acquisire un tipo di blocco in conflitto sulla stessa risorsa. In genere, l'intervallo di tempo per il quale il primo SPID blocca la risorsa è breve. Quando la sessione rilascia il blocco di cui è proprietaria, la seconda connessione è quindi libera di acquisire il proprio blocco sulla risorsa e può continuare l'elaborazione. Il blocco come descritto di seguito è un comportamento normale e può verificarsi molte volte durante il corso di un giorno senza effetti evidenti sulle prestazioni del sistema.

La durata e il contesto delle transazioni di una query determinano per quanto tempo vengono mantenuti i blocchi e, di conseguenza, il relativo effetto su altre query. Se la query non viene eseguita all'interno di una transazione e non vengono usati hint di blocco, i blocchi per le istruzioni SELECT verranno mantenuti solo su una risorsa al momento della lettura, non durante la query. Per le istruzioni INSERT, UPDATE e DELETE, i blocchi vengono mantenuti durante la query, sia per coerenza dei dati che per consentire il rollback della query, se necessario.

Per le query eseguite all'interno di una transazione, la durata per cui vengono mantenuti i blocchi viene determinata dal tipo di query, dal livello di isolamento della transazione e dall'uso degli hint di blocco nella query. Per una descrizione dei livelli di blocco, hint di blocco e isolamento delle transazioni, vedere gli articoli seguenti:

• Blocchi nel motore di database
• Personalizzazione dell'utilizzo di blocchi e del controllo delle versioni delle righe
• Modalità blocco
• Compatibilità tra blocchi
• Livelli di isolamento basati sul controllo delle versioni delle righe nel Motore di database
• Transazioni

Quando il blocco e il bloccaggio vengono mantenuti fino al punto in cui si verifica un effetto negativo sulle prestazioni del sistema, è dovuto a uno dei motivi seguenti:

• Uno SPID contiene blocchi per un set di risorse per un lungo periodo di tempo prima del rilascio. Questo tipo di bloccaggio si risolve nel tempo, ma può causare una riduzione delle prestazioni.

• Uno SPID contiene blocchi su un set di risorse e non li rilascia mai. Questo tipo di bloccaggio non si risolve e impedisce l'accesso alle risorse interessate a tempo indeterminato.

Nel primo scenario, la situazione può essere molto fluida come diversi SPID causano il blocco su risorse diverse nel tempo, creando una destinazione mobile. Queste situazioni sono difficili da risolvere usando SQL Server Management Studio per limitare il problema alle singole query. Al contrario, la seconda situazione comporta uno stato coerente che può essere più facile da diagnosticare.

Applicazioni e blocco

Potrebbe esserci una tendenza a concentrarsi sull'ottimizzazione lato server e sui problemi della piattaforma quando si riscontra un problema di blocco. Tuttavia, l'attenzione prestata solo al database potrebbe non causare una risoluzione e può assorbire il tempo e l'energia meglio indirizzati all'analisi dell'applicazione client e alle query inviate. Indipendentemente dal livello di visibilità esposto dall'applicazione per quanto riguarda le chiamate al database, un problema di blocco richiede tuttavia spesso sia l'ispezione delle istruzioni SQL esatte inviate dall'applicazione sia il comportamento esatto dell'applicazione relativamente all'annullamento delle query, alla gestione delle connessioni, al recupero di tutte le righe di risultato e così via. Se lo strumento di sviluppo non consente il controllo esplicito sulla gestione delle connessioni, l'annullamento delle query, il timeout delle query, il recupero dei risultati e così via, i problemi di blocco potrebbero non essere risolvibili. Questo potenziale deve essere esaminato attentamente prima di selezionare uno strumento di sviluppo di applicazioni per SQL Server, in particolare per gli ambienti OLTP sensibili alle prestazioni.

Prestare attenzione alle prestazioni del database durante la fase di progettazione e costruzione del database e dell'applicazione. In particolare, il consumo delle risorse, il livello di isolamento e la lunghezza del percorso delle transazioni devono essere valutati per ogni query. Le query e le transazioni devono essere il più leggere possibile. È necessario esercitare una buona disciplina di gestione delle connessioni, senza di essa, che l'applicazione potrebbe avere prestazioni accettabili a un numero ridotto di utenti, ma le prestazioni possono peggiorare significativamente man mano che il numero di utenti aumenta.

Con una progettazione corretta di applicazioni e query, SQL Server è in grado di supportare molte migliaia di utenti simultanei in un singolo server, con un blocco minimo.

Risolvere i problemi di blocco

Indipendentemente dalla situazione di blocco in cui ci troviamo, la metodologia per la risoluzione dei problemi di blocco è la stessa. Queste separazioni logiche sono ciò che determinerà il resto della composizione di questo articolo. Il concetto consiste nel trovare il blocco head e identificare le operazioni che la query sta facendo e perché è bloccante. Una volta identificata la query problematica ,ovvero ciò che mantiene i blocchi per il periodo prolungato, il passaggio successivo consiste nell'analizzare e determinare il motivo per cui si verifica il blocco. Dopo aver compreso il motivo, è possibile apportare modifiche riprogettando la query e la transazione.

Procedura per la risoluzione dei problemi:

1. Identificare la sessione di blocco principale (blocco head)

2. Trovare la query e la transazione che causano il blocco (che contiene blocchi per un periodo prolungato)

3. Analizzare/comprendere perché si verifica il blocco prolungato

4. Risolvere il problema di bloccaggio riprogettando query e transazioni

Si esaminerà ora come individuare la sessione di blocco principale con un'acquisizione dei dati appropriata.

Raccolta di informazioni sul bloccaggio

Per contrastare la difficoltà di risoluzione dei problemi di blocco, un amministratore del database può usare script SQL che monitorano costantemente lo stato di blocco e blocco in SQL Server. Per raccogliere questi dati, sono disponibili due metodi gratuiti.

Il primo consiste nell'eseguire query sugli oggetti a gestione dinamica (DMO) e archiviare i risultati per il confronto nel tempo. Alcuni oggetti a cui si fa riferimento in questo articolo sono DMV e alcune sono funzioni a gestione dinamica (DMF).

Il secondo consiste nell'usare gli eventi estesi (XEvents) o le tracce di SQL Profiler per acquisire ciò che viene eseguito. Poiché traccia SQL e SQL Server Profiler sono deprecati, questa guida alla risoluzione dei problemi si concentrerà su XEvents.

Raccolta delle informazioni dalle DMV

Fare riferimento alle DMV per risolvere i problemi di blocco ha l'obiettivo di identificare lo SPID (ID sessione) all'inizio della catena di blocco e dell'istruzione SQL. Ricerca degli SPID vittima bloccati. Se uno SPID è bloccato da un altro SPID, analizzare lo SPID proprietario della risorsa (SPID bloccante). Anche il proprietario SPID è bloccato? È possibile camminare la catena per trovare il blocco head e quindi indagare sul motivo per cui sta mantenendo il blocco.

A tale scopo, è possibile usare uno dei metodi seguenti:

• In SQL Server Management Studio (SSMS) Esplora oggetti fare clic con il pulsante destro del mouse sull'oggetto server di primo livello, espandere Report, espandere Report standard e quindi selezionare Attività - Tutte le transazioni di blocco. Questo report mostra le transazioni correnti all'inizio di una catena di blocco. Se si espande la transazione, il report mostrerà le transazioni bloccate dalla transazione head. Questo report mostrerà anche l'istruzione SQL di blocco e l'istruzione SQL bloccata.

• Aprire Monitoraggio attività in SSMS e fare riferimento alla colonna Blocked By . Altre informazioni su Monitoraggio attività sono disponibili qui.

Sono disponibili anche metodi più dettagliati basati su query usando DMV:

• I sp_who comandi e sp_who2 sono comandi meno recenti per visualizzare tutte le sessioni correnti. La DMV sys.dm_exec_sessions restituisce più dati in un set di risultati più semplice da eseguire query e filtrare. All'interno delle altre query si troverà sys.dm_exec_sessions.

• Se è già stata identificata una sessione specifica, è possibile usare DBCC INPUTBUFFER(<session_id>) per trovare l'ultima istruzione inviata da una sessione. I risultati simili possono essere restituiti con la funzione a gestione dinamica (DMF) sys.dm_exec_input_buffer, in un set di risultati più semplice da eseguire query e filtrare, fornendo il session_id e il request_id. Ad esempio, per restituire la query più recente inviata da session_id 66 e request_id 0:

SELECT * FROM sys.dm_exec_input_buffer (66,0);

• Fare riferimento a e fare riferimento alla sys.dm_exec_requests blocking_session_id colonna . Quando blocking_session_id = 0, una sessione non viene bloccata. Mentre sys.dm_exec_requests elenca solo le richieste attualmente in esecuzione, qualsiasi connessione (attiva o meno) verrà elencata in sys.dm_exec_sessions. Creare questo join comune tra sys.dm_exec_requests e sys.dm_exec_sessions nella query successiva. Tenere presente che deve essere restituito da sys.dm_exec_requests, la query deve essere in esecuzione attivamente con SQL Server.

• Eseguire questa query di esempio per trovare le query in esecuzione attivamente e il testo corrente del batch SQL o del buffer di input, usando le DMV sys.dm_exec_sql_text o sys.dm_exec_input_buffer. Se i dati restituiti dalla text colonna di sys.dm_exec_sql_text è NULL, la query non è attualmente in esecuzione. In tal caso, la event_info colonna di conterrà l'ultima stringa di sys.dm_exec_input_buffer comando passata al motore SQL. Questa query può essere usata anche per identificare le sessioni che bloccano altre sessioni, incluso un elenco di session_ids bloccati per session_id.

WITH cteBL (session_id, blocking_these) AS 
(SELECT s.session_id, blocking_these = x.blocking_these FROM sys.dm_exec_sessions s 
CROSS APPLY    (SELECT isnull(convert(varchar(6), er.session_id),'') + ', '  
                FROM sys.dm_exec_requests as er
                WHERE er.blocking_session_id = isnull(s.session_id ,0)
                AND er.blocking_session_id <> 0
                FOR XML PATH('') ) AS x (blocking_these)
)
SELECT s.session_id, blocked_by = r.blocking_session_id, bl.blocking_these
, batch_text = t.text, input_buffer = ib.event_info, * 
FROM sys.dm_exec_sessions s 
LEFT OUTER JOIN sys.dm_exec_requests r on r.session_id = s.session_id
INNER JOIN cteBL as bl on s.session_id = bl.session_id
OUTER APPLY sys.dm_exec_sql_text (r.sql_handle) t
OUTER APPLY sys.dm_exec_input_buffer(s.session_id, NULL) AS ib
WHERE blocking_these is not null or r.blocking_session_id > 0
ORDER BY len(bl.blocking_these) desc, r.blocking_session_id desc, r.session_id;

• Eseguire questa query di esempio più elaborata, fornita da supporto tecnico Microsoft, per identificare l'intestazione di una catena di blocco di più sessioni, incluso il testo della query delle sessioni coinvolte in una catena di blocco.

WITH cteHead ( session_id,request_id,wait_type,wait_resource,last_wait_type,is_user_process,request_cpu_time
,request_logical_reads,request_reads,request_writes,wait_time,blocking_session_id,memory_usage
,session_cpu_time,session_reads,session_writes,session_logical_reads
,percent_complete,est_completion_time,request_start_time,request_status,command
,plan_handle,sql_handle,statement_start_offset,statement_end_offset,most_recent_sql_handle
,session_status,group_id,query_hash,query_plan_hash) 
AS ( SELECT sess.session_id, req.request_id, LEFT (ISNULL (req.wait_type, ''), 50) AS 'wait_type'
    , LEFT (ISNULL (req.wait_resource, ''), 40) AS 'wait_resource', LEFT (req.last_wait_type, 50) AS 'last_wait_type'
    , sess.is_user_process, req.cpu_time AS 'request_cpu_time', req.logical_reads AS 'request_logical_reads'
    , req.reads AS 'request_reads', req.writes AS 'request_writes', req.wait_time, req.blocking_session_id,sess.memory_usage
    , sess.cpu_time AS 'session_cpu_time', sess.reads AS 'session_reads', sess.writes AS 'session_writes', sess.logical_reads AS 'session_logical_reads'
    , CONVERT (decimal(5,2), req.percent_complete) AS 'percent_complete', req.estimated_completion_time AS 'est_completion_time'
    , req.start_time AS 'request_start_time', LEFT (req.status, 15) AS 'request_status', req.command
    , req.plan_handle, req.[sql_handle], req.statement_start_offset, req.statement_end_offset, conn.most_recent_sql_handle
    , LEFT (sess.status, 15) AS 'session_status', sess.group_id, req.query_hash, req.query_plan_hash
    FROM sys.dm_exec_sessions AS sess
    LEFT OUTER JOIN sys.dm_exec_requests AS req ON sess.session_id = req.session_id
    LEFT OUTER JOIN sys.dm_exec_connections AS conn on conn.session_id = sess.session_id 
    )
, cteBlockingHierarchy (head_blocker_session_id, session_id, blocking_session_id, wait_type, wait_duration_ms,
wait_resource, statement_start_offset, statement_end_offset, plan_handle, sql_handle, most_recent_sql_handle, [Level])
AS ( SELECT head.session_id AS head_blocker_session_id, head.session_id AS session_id, head.blocking_session_id
    , head.wait_type, head.wait_time, head.wait_resource, head.statement_start_offset, head.statement_end_offset
    , head.plan_handle, head.sql_handle, head.most_recent_sql_handle, 0 AS [Level]
    FROM cteHead AS head
    WHERE (head.blocking_session_id IS NULL OR head.blocking_session_id = 0)
    AND head.session_id IN (SELECT DISTINCT blocking_session_id FROM cteHead WHERE blocking_session_id != 0)
    UNION ALL
    SELECT h.head_blocker_session_id, blocked.session_id, blocked.blocking_session_id, blocked.wait_type,
    blocked.wait_time, blocked.wait_resource, h.statement_start_offset, h.statement_end_offset,
    h.plan_handle, h.sql_handle, h.most_recent_sql_handle, [Level] + 1
    FROM cteHead AS blocked
    INNER JOIN cteBlockingHierarchy AS h ON h.session_id = blocked.blocking_session_id and h.session_id!=blocked.session_id --avoid infinite recursion for latch type of blocking
    WHERE h.wait_type COLLATE Latin1_General_BIN NOT IN ('EXCHANGE', 'CXPACKET') or h.wait_type is null
    )
SELECT bh.*, txt.text AS blocker_query_or_most_recent_query 
FROM cteBlockingHierarchy AS bh 
OUTER APPLY sys.dm_exec_sql_text (ISNULL ([sql_handle], most_recent_sql_handle)) AS txt;

• Per intercettare transazioni a esecuzione prolungata o di cui non è stato eseguito il commit, usare un altro set di DMV per visualizzare le transazioni aperte correnti, tra cui sys.dm_tran_database_transactions, sys.dm_tran_session_transactions, sys.dm_exec_connections e sys.dm_exec_sql_text. Esistono diverse DMV associate al rilevamento delle transazioni. Per altre DMV sulle transazioni, vedere qui.

SELECT [s_tst].[session_id],
[database_name] = DB_NAME (s_tdt.database_id),
[s_tdt].[database_transaction_begin_time], 
[sql_text] = [s_est].[text] 
FROM sys.dm_tran_database_transactions [s_tdt]
INNER JOIN sys.dm_tran_session_transactions [s_tst] ON [s_tst].[transaction_id] = [s_tdt].[transaction_id]
INNER JOIN sys.dm_exec_connections [s_ec] ON [s_ec].[session_id] = [s_tst].[session_id]
CROSS APPLY sys.dm_exec_sql_text ([s_ec].[most_recent_sql_handle]) AS [s_est];

• Riferimento sys.dm_os_waiting_tasks a livello di thread/attività di SQL Server. In questo modo vengono restituite informazioni sulle wait_type SQL in cui si sta verificando la richiesta. Come sys.dm_exec_requests, solo le richieste attive vengono restituite da sys.dm_os_waiting_tasks.

• Usare la DMV sys.dm_tran_locks per informazioni più dettagliate sui blocchi inseriti dalle query. Questa DMV può restituire grandi quantità di dati in un'istanza di SQL Server di produzione ed è utile per diagnosticare i blocchi attualmente mantenuti.

A causa dell’INNER JOIN su sys.dm_os_waiting_tasks, la query seguente limita l'output da sys.dm_tran_locks solo alle richieste attualmente bloccate, al relativo stato di attesa e ai relativi blocchi:

SELECT table_name = schema_name(o.schema_id) + '.' + o.name
, wt.wait_duration_ms, wt.wait_type, wt.blocking_session_id, wt.resource_description
, tm.resource_type, tm.request_status, tm.request_mode, tm.request_session_id
FROM sys.dm_tran_locks AS tm
INNER JOIN sys.dm_os_waiting_tasks as wt ON tm.lock_owner_address = wt.resource_address
LEFT OUTER JOIN sys.partitions AS p on p.hobt_id = tm.resource_associated_entity_id
LEFT OUTER JOIN sys.objects o on o.object_id = p.object_id or tm.resource_associated_entity_id = o.object_id
WHERE resource_database_id = DB_ID()
AND object_name(p.object_id) = '<table_name>';

Con le DMV, l'archiviazione dei risultati della query nel tempo fornirà punti dati che consentiranno di esaminare il blocco in un intervallo di tempo specificato per identificare tendenze o blocchi persistenti. Lo strumento go-to per CSS per risolvere questi problemi consiste nell'usare l'agente di raccolta dati PSSDiag. Questo strumento usa "SQL Server Perf Stats" per raccogliere i set di risultati dalle DMV a cui si fa riferimento in precedenza, nel corso del tempo. Poiché questo strumento è in continua evoluzione, esaminare la versione pubblica più recente di DiagManager su GitHub.

Raccogliere informazioni dagli eventi estesi

Oltre alle informazioni precedenti, è spesso necessario acquisire una traccia delle attività nel server per analizzare accuratamente un problema di blocco in SQL Server. Ad esempio, se una sessione esegue più istruzioni all'interno di una transazione, verrà rappresentata solo l'ultima istruzione inviata. Tuttavia, una delle istruzioni precedenti può essere il motivo per cui i blocchi vengono ancora mantenuti. Una traccia consentirà di visualizzare tutti i comandi eseguiti da una sessione all'interno della transazione corrente.

Esistono due modi per acquisire tracce in SQL Server; Eventi estesi (XEvent) e tracce profiler. Tuttavia, le tracce SQL che usano SQL Server Profiler sono deprecate. Gli XEvent sono la piattaforma di traccia più recente e superiore che consente maggiore versatilità e minore impatto sul sistema osservato e la sua interfaccia è integrata in SSMS.

Sono disponibili sessioni di eventi estesi predefinite pronte per l'avvio in SSMS, elencate in Esplora oggetti nel menu per XEvent Profiler. Per altre informazioni, vedere XEvent Profiler. È anche possibile creare sessioni personalizzate di eventi estesi in SSMS, vedere Creazione guidata nuova sessione eventi estesi. Per la risoluzione dei problemi di blocco, in genere si acquisiranno:

• Errori di categoria:
  • Attenzione
  • Blocked_process_report**
  • Error_reported (amministratore del canale)
  • Exchange_spill
  • Execution_warning

**Per configurare la soglia e la frequenza con cui vengono generati i report di processo bloccati, usare il comando sp_configure per configurare l'opzione soglia di processo bloccata, che può essere impostata in secondi. Per impostazione predefinita, non vengono generati report relativi ai processi bloccati.

• Avvisi categoria:

  • Hash_warning
  • Missing_column_statistics
  • Missing_join_predicate
  • Sort_warning

• Esecuzione categoria:

  • Rpc_completed
  • Rpc_starting
  • Sql_batch_completed
  • Sql_batch_starting

• Blocco categoria

  • Lock_deadlock

• Sessione categoria

  • Existing_connection
  • Account di accesso
  • Disconnessione

Identificare e risolvere scenari di blocco comuni

Esaminando le informazioni precedenti, è possibile determinare la causa della maggior parte dei problemi di blocco. Il resto di questo articolo illustra come usare queste informazioni per identificare e risolvere alcuni scenari di blocco comuni. Questa discussione presuppone che siano stati usati gli script di blocco (a cui si fa riferimento in precedenza) per acquisire informazioni sugli SPID di blocco e che l'attività dell'applicazione sia stata acquisita usando una sessione XEvent.

Analizzare i dati bloccanti

• Esaminare l'output delle DMV sys.dm_exec_requests e sys.dm_exec_sessions per determinare gli head delle catene bloccanti, usando blocking_these e session_id. Ciò identificherà in modo più chiaro quali richieste sono bloccate e quali bloccano. Esaminare ulteriormente le sessioni bloccate e bloccanti. C'è una radice o comune per la catena bloccante? Probabilmente condividono una tabella comune e una o più sessioni coinvolte in una catena di blocco eseguono un'operazione di scrittura.

• Esaminare l'output delle DMV sys.dm_exec_requests e sys.dm_exec_sessions per informazioni sugli SPID all'inizio della catena di blocco. Cercare le colonne seguenti:

  • sys.dm_exec_requests.status

    Questa colonna mostra lo stato di una determinata richiesta. In breve, uno stato di sospensione indica che lo SPID ha completato l'esecuzione ed è in attesa che l'applicazione invii un'altra query o batch. Uno stato eseguibile o in esecuzione indica che lo SPID sta attualmente elaborando una query. Nella tabella seguente vengono fornite brevi spiegazioni dei vari valori di stato.

    Stato	Significato
    Background	Lo SPID esegue un'attività in background, ad esempio il rilevamento dei deadlock, il writer di log o il checkpoint.
    Sospeso	Lo SPID non è attualmente in esecuzione. Questo indica in genere che lo SPID è in attesa di un comando dall'applicazione.
    In esecuzione	Lo SPID è attualmente in esecuzione nell'utilità di pianificazione.
    Eseguibile	Lo SPID si trova nella coda eseguibile di un'utilità di pianificazione e in attesa di ottenere l'orario dell'utilità di pianificazione.
    Suspended	Lo SPID è in attesa di una risorsa, ad esempio un blocco o un latch.

  • sys.dm_exec_sessions.open_transaction_count

    Questa colonna indica il numero di transazioni aperte in questa sessione. Se questo valore è maggiore di 0, lo SPID si trova all'interno di una transazione aperta e può contenere blocchi acquisiti da qualsiasi istruzione all'interno della transazione. La transazione aperta potrebbe essere stata creata da un'istruzione attualmente attiva o da una richiesta di istruzione eseguita in passato e non è più attiva.

  • sys.dm_exec_requests.open_transaction_count

    Analogamente, questa colonna indica il numero di transazioni aperte in questa richiesta. Se questo valore è maggiore di 0, lo SPID si trova all'interno di una transazione aperta e può contenere blocchi acquisiti da qualsiasi istruzione attiva all'interno della transazione. A differenza di sys.dm_exec_sessions.open_transaction_count, se non è presente una richiesta attiva, questa colonna mostrerà 0.

  • sys.dm_exec_requests.wait_type, wait_time e last_wait_type

    Se sys.dm_exec_requests.wait_type è NULL, la richiesta non è attualmente in attesa di alcun elemento e il valore last_wait_type indica l'ultimo wait_type rilevato dalla richiesta. Per altre informazioni su sys.dm_os_wait_stats e una descrizione dei tipi di attesa più comuni, vedere sys.dm_os_wait_stats. Il valore wait_time può essere usato per determinare se la richiesta sta effettuando lo stato di avanzamento. Quando una query sulla tabella sys.dm_exec_requests restituisce un valore nella colonna wait_time minore del valore wait_time di una query precedente di sys.dm_exec_requests, indica che il blocco precedente è stato acquisito e rilasciato ed è ora in attesa di un nuovo blocco (presupponendo che wait_time sia diverso da zero). Questa operazione può essere verificata confrontando l'oggetto wait_resource tra l’output sys.dm_exec_requests, che visualizza la risorsa per cui la richiesta è in attesa.

  • sys.dm_exec_requests.wait_resource

    Questa colonna indica la risorsa in cui è in attesa una richiesta bloccata. La tabella seguente elenca i formati wait_resource comuni e il loro significato:

    Conto risorse	Formattazione	Esempio	Spiegazione
    Tabella	DatabaseID:ObjectID:IndexID	SCHEDA: 5:261575970:1	In questo caso, l'ID database 5 è il database di esempio pubs e object_id 261575970 è la tabella dei titoli e 1 è l'indice cluster.
    Pagina	DatabaseID:FileID:PageID	PAGINA: 5:1:104	In questo caso, l'ID database 5 è pubs, l'ID file 1 è il file di dati primario e la pagina 104 è una pagina appartenente alla tabella dei titoli. Per identificare il object_id a cui appartiene la pagina, usare la funzione a gestione dinamica sys.dm_db_page_info, passando databaseID, FileId, PageId da wait_resource.
    Chiave	DatabaseID:Hobt_id (valore hash per la chiave di indice)	CHIAVE: 5:72057594044284928 (3300a4f361aa)	In questo caso, l'ID database 5 è Pubs, Hobt_ID 72057594044284928 corrisponde a index_id 2 per object_id 261575970 (tabella dei titoli). Utilizzare la sys.partitions vista del catalogo per associare l'oggetto hobt_id a un oggetto specifico index_id e object_id. Non è possibile annullare l'hash della chiave di indice in un valore di chiave specifico.
    Riga	DatabaseID:FileID:PageID:Slot(row)	RID: 5:1:104:3	In questo caso, l'ID database 5 è pubs, l'ID file 1 è il file di dati primario, la pagina 104 è una pagina appartenente alla tabella dei titoli e lo slot 3 indica la posizione della riga nella pagina.
    Compile	DatabaseID:FileID:PageID:Slot(row)	RID: 5:1:104:3	In questo caso, l'ID database 5 è pubs, l'ID file 1 è il file di dati primario, la pagina 104 è una pagina appartenente alla tabella dei titoli e lo slot 3 indica la posizione della riga nella pagina.

  • sys.dm_tran_active_transactions La DMV sys.dm_tran_active_transactions contiene dati sulle transazioni aperte che possono essere unite ad altre DMV per un quadro completo delle transazioni in attesa del commit o del ripristino dello stato precedente. Usare la query seguente per restituire informazioni sulle transazioni aperte, unite ad altre DMV, tra cui sys.dm_tran_session_transactions. Considerare lo stato corrente di una transazione, transaction_begin_time e il relativo parametro e altri dati specifici per valutare se potrebbero trattarsi dell’origine del blocco.

    SELECT tst.session_id, [database_name] = db_name(s.database_id)
    , tat.transaction_begin_time
    , transaction_duration_s = datediff(s, tat.transaction_begin_time, sysdatetime()) 
    , transaction_type = CASE tat.transaction_type  WHEN 1 THEN 'Read/write transaction'
                                                    WHEN 2 THEN 'Read-only transaction'
                                                    WHEN 3 THEN 'System transaction'
                                                    WHEN 4 THEN 'Distributed transaction' END
    , input_buffer = ib.event_info, tat.transaction_uow     
    , transaction_state  = CASE tat.transaction_state    
                WHEN 0 THEN 'The transaction has not been completely initialized yet.'
                WHEN 1 THEN 'The transaction has been initialized but has not started.'
                WHEN 2 THEN 'The transaction is active - has not been committed or rolled back.'
                WHEN 3 THEN 'The transaction has ended. This is used for read-only transactions.'
                WHEN 4 THEN 'The commit process has been initiated on the distributed transaction.'
                WHEN 5 THEN 'The transaction is in a prepared state and waiting resolution.'
                WHEN 6 THEN 'The transaction has been committed.'
                WHEN 7 THEN 'The transaction is being rolled back.'
                WHEN 8 THEN 'The transaction has been rolled back.' END 
    , transaction_name = tat.name, request_status = r.status
    , tst.is_user_transaction, tst.is_local
    , session_open_transaction_count = tst.open_transaction_count  
    , s.host_name, s.program_name, s.client_interface_name, s.login_name, s.is_user_process
    FROM sys.dm_tran_active_transactions tat 
    INNER JOIN sys.dm_tran_session_transactions tst  on tat.transaction_id = tst.transaction_id
    INNER JOIN Sys.dm_exec_sessions s on s.session_id = tst.session_id 
    LEFT OUTER JOIN sys.dm_exec_requests r on r.session_id = s.session_id
    CROSS APPLY sys.dm_exec_input_buffer(s.session_id, null) AS ib;
    

  • Altre colonne

    Anche le colonne rimanenti in sys.dm_exec_sessions e sys.dm_exec_request possono fornire informazioni dettagliate sulla radice di un problema. La loro utilità varia a seconda delle circostanze del problema. Ad esempio, è possibile determinare se il problema si verifica solo da determinati client (hostname), in determinate librerie di rete (client_interface_name), quando l'ultimo batch inviato da un SPID era last_request_start_time in sys.dm_exec_sessions, per quanto tempo una richiesta era in esecuzione usando start_time in sys.dm_exec_requestse così via.

Scenari di bloccaggio comuni

La tabella seguente esegue il mapping dei sintomi comuni alle loro probabili cause.

Le wait_typecolonne , open_transaction_counte status fanno riferimento alle informazioni restituite da sys.dm_exec_request, altre colonne possono essere restituite da sys.dm_exec_sessions. La colonna "Resolves?" indica se il blocco verrà risolto autonomamente o se la sessione deve essere terminata tramite il KILL comando . Per altre informazioni, vedere KILL (Transact-SQL).

Scenari di blocco dettagliati

Scenario 1: Blocco causato da una query in esecuzione normalmente con un tempo di esecuzione lungo

In questo scenario, una query in esecuzione attiva ha acquisito blocchi e i blocchi non vengono rilasciati (è interessato dal livello di isolamento della transazione). Così le altre sessioni attenderanno i blocchi fino a quando non vengono rilasciate.

Risoluzione:

La soluzione a questo problema di blocco consiste nell'cercare modi per ottimizzare la query. Questa classe di problema di blocco può essere un problema di prestazioni e richiede di trattarla come tale. Per informazioni su come risolvere i problemi relativi a una query a esecuzione lenta, consultare la sezione Risolvere i problemi relativi alle query a esecuzione lenta in SQL Server. Per altre informazioni, vedere Monitorare e ottimizzare le prestazioni.

I report incorporati in SSMS da Query Store (introdotti in SQL Server 2016) sono anche uno strumento consigliato e prezioso per identificare le query più costose e i piani di esecuzione non ottimali.

Se si dispone di una query a esecuzione prolungata che blocca altri utenti e non può essere ottimizzata, è consigliabile spostarla da un ambiente OLTP a un sistema di report dedicato. È anche possibile usare i gruppi di disponibilità AlwaysOn per sincronizzare una replica di sola lettura del database.

Scenario 2: Blocco causato da un SPID in sospensione con una transazione di cui non è stato eseguito il commit

Questo tipo di blocco può spesso essere identificato da un SPID in attesa o in attesa di un comando con un livello di annidamento delle transazioni (@@TRANCOUNT, open_transaction_count da sys.dm_exec_requests) maggiore di zero. Questa situazione può verificarsi se l'applicazione riscontra un timeout della query o genera un annullamento senza emettere il numero richiesto di istruzioni ROLLBACK e/o COMMIT. Quando uno SPID riceve un timeout della query o un annullamento, termina la query e il batch corrente, ma non esegue automaticamente il rollback o il commit della transazione. L'applicazione è responsabile di questo problema, perché SQL Server non può presupporre che sia necessario eseguire il rollback di un'intera transazione a causa di una singola query annullata. Il timeout o l'annullamento della query verranno visualizzati come evento di segnale ATTENTION per lo SPID nella sessione degli eventi estesi.

Per illustrare una transazione esplicita di cui non è stato eseguito il commit, eseguire la query seguente:

CREATE TABLE #test (col1 INT);
INSERT INTO #test SELECT 1;
GO
BEGIN TRAN
UPDATE #test SET col1 = 2 where col1 = 1;

Eseguire quindi questa query nella stessa finestra:

SELECT @@TRANCOUNT;
ROLLBACK TRAN
DROP TABLE #test;

L'output della seconda query indica che il numero di transazioni è uno. Tutti i blocchi acquisiti nella transazione vengono mantenuti fino a quando non viene eseguito il commit o il rollback della transazione. Se le applicazioni aprono ed eseguono il commit in modo esplicito delle transazioni, una comunicazione o un altro errore potrebbero lasciare la sessione e la relativa transazione in uno stato aperto.

Usare lo script riportato in precedenza in questo articolo in base a sys.dm_tran_active_transactions per identificare le transazioni attualmente non sottoposte a commit nell'istanza.

Risoluzioni:

• Questa classe di problema di blocco può anche essere un problema di prestazioni e richiede di trattarla come tale. Se il tempo di esecuzione della query può essere ridotto, il timeout o l'annullamento della query potrebbero non verificarsi. È importante che l'applicazione possa gestire il timeout o annullare gli scenari in cui si verificano, ma può anche trarre vantaggio dall'analisi delle prestazioni della query.

• Le applicazioni devono gestire correttamente i livelli di annidamento delle transazioni oppure possono causare un problema di blocco dopo l'annullamento della query in questo modo. Considerare quanto segue:

  • Nel gestore degli errori dell'applicazione client eseguire IF @@TRANCOUNT > 0 ROLLBACK TRAN in seguito a qualsiasi errore, anche se l'applicazione client non ritiene che una transazione sia aperta. Il controllo delle transazioni aperte è necessario perché una stored procedure chiamata durante il batch potrebbe aver avviato una transazione senza conoscere l'applicazione client. Alcune condizioni, ad esempio l'annullamento della query, impediscono l'esecuzione della routine oltre l'istruzione corrente, quindi anche se la routine ha la logica per controllare IF @@ERROR <> 0 e interrompere la transazione, questo codice di rollback non verrà eseguito in tali casi.

  • Se il pool di connessioni viene usato in un'applicazione che apre la connessione ed esegue alcune query prima di rilasciare nuovamente la connessione al pool, ad esempio un'applicazione basata sul Web, la disabilitazione temporanea del pool di connessioni può contribuire ad alleviare il problema fino a quando l'applicazione client non viene modificata per gestire gli errori in modo appropriato. Disabilitando il pool di connessioni, il rilascio della connessione causerà una disconnessione fisica della connessione DI SQL Server, con conseguente rollback delle transazioni aperte da parte del server.

  • Usare SET XACT_ABORT ON per la connessione o in qualsiasi stored procedure che iniziano le transazioni e non vengono ripulite dopo un errore. In caso di errore di run-time, questa impostazione interromperà le transazioni aperte e restituirà il controllo al client. Per altre informazioni, vedere SET XACT_ABORT (Transact-SQL).

Scenario 3: Blocco causato da un SPID la cui applicazione client corrispondente non ha recuperato tutte le righe di risultato fino al completamento

Dopo aver inviato una query al server, tutte le applicazioni devono recuperare immediatamente tutte le righe di risultati al completamento. Se un'applicazione non recupera tutte le righe dei risultati, è possibile che vengano lasciati blocchi sulle tabelle, bloccando altri utenti. Se si usa un'applicazione che invia in modo trasparente istruzioni SQL al server, l'applicazione deve recuperare tutte le righe dei risultati. Se non lo è (e se non è possibile configurarlo per farlo), potrebbe non essere possibile risolvere il problema di blocco. Per evitare il problema, è possibile limitare le applicazioni con comportamenti non ottimali a un database di reporting o di supporto decisionale, separato dal database OLTP principale.

Risoluzione:

L'applicazione deve essere riscritta per recuperare tutte le righe del risultato fino al completamento. Ciò non esclude l'uso di OFFSET e FETCH nella clausola ORDER BY di una query per eseguire il paging sul lato server.

Scenario 4: Blocco causato da un deadlock client/server distribuito

A differenza di un deadlock convenzionale, un deadlock distribuito non è rilevabile tramite gestione blocchi RDBMS. Ciò è dovuto al fatto che solo una delle risorse coinvolte nel deadlock è un blocco di SQL Server. L'altro lato del deadlock è a livello di applicazione client, su cui SQL Server non ha alcun controllo. Le due sezioni seguenti illustrano esempi di come ciò può verificarsi e dei possibili modi in cui l'applicazione può evitarla.

Esempio A: Deadlock distribuito client/server con un singolo thread client

Se il client dispone di più connessioni aperte e di un singolo thread di esecuzione, può verificarsi il deadlock distribuito seguente. Si noti che il termine dbproc usato qui fa riferimento alla struttura di connessione client.

 SPID1------blocked on lock------->SPID2
   /\ (waiting to write results back to client)
   | 
   | |
   | | Server side
   | ================================|==================================
   | <-- single thread --> | Client side
   | \/
   dbproc1 <------------------- dbproc2
   (waiting to fetch (effectively blocked on dbproc1, awaiting
   next row) single thread of execution to run)

Nel caso illustrato in precedenza, un singolo thread applicazione client ha due connessioni aperte. Invia in modo asincrono un'operazione SQL in dbproc1. Ciò significa che non attende la chiamata per tornare prima di procedere. L'applicazione invia quindi un'altra operazione SQL in dbproc2 e attende i risultati per avviare l'elaborazione dei dati restituiti. Quando i dati iniziano a essere restituiti (a seconda del quale dbproc risponde per la prima volta, presuppone che si tratti di dbproc1), vengono elaborati per completare tutti i dati restituiti in tale dbproc. Recupera i risultati da dbproc1 fino a quando SPID1 non viene bloccato in un blocco mantenuto da SPID2 (perché le due query vengono eseguite in modo asincrono nel server). A questo punto, dbproc1 attenderà a tempo indeterminato altri dati. SPID2 non è bloccato in un blocco, ma tenta di inviare dati al client dbproc2. Tuttavia, dbproc2 è effettivamente bloccato in dbproc1 a livello di applicazione perché il singolo thread di esecuzione per l'applicazione è in uso da dbproc1. Ciò comporta un deadlock che SQL Server non riesce a rilevare o risolvere perché solo una delle risorse coinvolte è una risorsa di SQL Server.

Esempio B: Deadlock distribuito client/server con un thread per connessione

Anche se esiste un thread separato per ogni connessione nel client, una variante di questo deadlock distribuito può comunque verificarsi come illustrato di seguito.

SPID1------blocked on lock-------->SPID2
  /\ (waiting on net write) Server side
  | |
  | |
  | INSERT |SELECT
  | ================================|==================================
  | <-- thread per dbproc --> | Client side
  | \/
  dbproc1 <-----data row------- dbproc2
  (waiting on (blocked on dbproc1, waiting for it
  insert) to read the row from its buffer)

Questo caso è simile all'esempio A, ad eccezione di dbproc2 e SPID2, che esegue un'istruzione SELECT con l'intenzione di eseguire l'elaborazione delle righe in fase di esecuzione e di distribuire ogni riga tramite un buffer a dbproc1 per un'istruzione INSERT, UPDATEo DELETE nella stessa tabella. Alla fine, SPID1 (eseguendo INSERT, UPDATEo DELETE) viene bloccato in un blocco mantenuto da SPID2 (eseguendo ).SELECT SPID2 scrive una riga di risultato nel client dbproc2. Dbproc2 tenta quindi di passare la riga in un buffer a dbproc1, ma trova che dbproc1 è occupato (è bloccato in attesa di SPID1 per completare l'oggetto corrente INSERT, bloccato in SPID2). A questo punto, dbproc2 viene bloccato a livello di applicazione da dbproc1 il cui SPID (SPID1) è bloccato a livello di database da SPID2. Anche in questo caso, si verifica un deadlock che SQL Server non riesce a rilevare o risolvere perché solo una delle risorse coinvolte è una risorsa di SQL Server.

Entrambi gli esempi A e B sono problemi fondamentali di cui gli sviluppatori di applicazioni devono essere a conoscenza. Devono codificare le applicazioni per gestire questi casi in modo appropriato.

Risoluzione:

Quando è stato specificato un timeout della query, se si verifica il deadlock distribuito, verrà interrotto quando si verifica il timeout. Per altre informazioni sull'uso di un timeout della query, fare riferimento alla documentazione del provider di connessione.

Scenario 5: Blocco causato da una sessione in uno stato di rollback

Verrà eseguito il rollback di una query di modifica dei dati terminata o annullata all'esterno di una transazione definita dall'utente. Ciò può verificarsi anche come effetto collaterale della disconnessione della sessione di rete client o quando una richiesta viene selezionata come vittima del deadlock. Questa operazione può essere spesso identificata osservando l'output di , che può indicare rollback commande la percent_complete colonna può mostrare lo stato di sys.dm_exec_requestsavanzamento.

Verrà eseguito il rollback di una query di modifica dei dati terminata o annullata all'esterno di una transazione definita dall'utente. Ciò può verificarsi anche come effetto collaterale del riavvio del computer client e della relativa disconnessione della sessione di rete. Analogamente, verrà eseguito il rollback di una query selezionata come vittima del deadlock. Spesso non è possibile eseguire il rollback di una query di modifica dei dati più velocemente rispetto all'applicazione iniziale delle modifiche. Ad esempio, se un'istruzione DELETE, INSERTo UPDATE era in esecuzione per un'ora, potrebbe essere necessaria almeno un'ora per eseguire il rollback. Si tratta di un comportamento previsto, perché è necessario eseguire il rollback delle modifiche oppure l'integrità transazionale e fisica nel database verrebbe compromessa. Poiché questo deve verificarsi, SQL Server contrassegna lo SPID in uno stato di rollback o dorato (il che significa che non può essere ucciso o selezionato come vittima del deadlock). Questa operazione può essere spesso identificata osservando l'output di sp_who, che può indicare il comando ROLLBACK. La status colonna di sys.dm_exec_sessions indicherà uno stato ROLLBACK.

Risoluzione:

È necessario attendere il completamento del rollback delle modifiche apportate alla sessione.

Se l'istanza viene arrestata durante questa operazione, il database sarà in modalità di ripristino al riavvio e sarà inaccessibile fino a quando non vengono elaborate tutte le transazioni aperte. Il ripristino di avvio richiede essenzialmente la stessa quantità di tempo per transazione come ripristino in fase di esecuzione e il database non è accessibile durante questo periodo. Pertanto, forzare il server a correggere un SPID in uno stato di rollback spesso sarà controproducente. In SQL Server 2019 con ripristino accelerato del database abilitato non dovrebbe verificarsi.

Per evitare questa situazione, non eseguire operazioni di scrittura batch di grandi dimensioni o operazioni di creazione o manutenzione degli indici durante le ore di lavoro nei sistemi OLTP. Se possibile, eseguire tali operazioni durante i periodi di bassa attività.

Scenario 6: Blocco causato da una transazione orfana

Si tratta di uno scenario di problema comune e si sovrappone in parte allo scenario 2. Se l'applicazione client si arresta, la workstation client viene riavviata o si verifica un errore di interruzione in batch, è possibile che tutti lascino aperta una transazione. Questa situazione può verificarsi se l'applicazione non esegue il rollback della transazione nei blocchi o FINALLY nell'applicazione CATCH o se non gestisce in altro modo questa situazione.

In questo scenario, mentre l'esecuzione di un batch SQL è stata annullata, l'applicazione lascia aperta la transazione SQL. Dal punto di vista dell'istanza di SQL Server, il client sembra ancora essere presente e tutti i blocchi acquisiti vengono conservati.

Per dimostrare una transazione orfana, eseguire la query seguente, che simula un errore di interruzione batch inserendo dati in una tabella inesistente:

CREATE TABLE #test2 (col1 INT);
INSERT INTO #test2 SELECT 1;
go
BEGIN TRAN
UPDATE #test2 SET col1 = 2 where col1 = 1;
INSERT INTO #NonExistentTable values (10)

Eseguire quindi questa query nella stessa finestra:

SELECT @@TRANCOUNT;

L'output della seconda query indica che il numero di transazioni è uno. Tutti i blocchi acquisiti nella transazione vengono mantenuti fino a quando non viene eseguito il commit o il rollback della transazione. Poiché il batch è già interrotto dalla query, l'applicazione che esegue può continuare a eseguire altre query nella stessa sessione senza pulire la transazione ancora aperta. Il blocco verrà mantenuto fino all'interruzione della sessione o al riavvio dell'istanza di SQL Server.

Risoluzioni:

• Il modo migliore per evitare questa condizione consiste nel migliorare la gestione degli errori o delle eccezioni dell'applicazione, soprattutto per le terminazioni impreviste. Assicurarsi di usare un Try-Catch-Finally blocco nel codice dell'applicazione e di eseguire il rollback della transazione in caso di eccezione.
• È consigliabile usare SET XACT_ABORT ON per la sessione o in qualsiasi stored procedure che iniziano le transazioni e non vengono ripulite in seguito a un errore. In caso di errore di runtime che interrompe il batch, questa impostazione eseguirà automaticamente il rollback di tutte le transazioni aperte e restituirà il controllo al client. Per altre informazioni, vedere SET XACT_ABORT (Transact-SQL).
• Per risolvere una connessione orfana di un'applicazione client disconnessa senza pulire correttamente le risorse, è possibile terminare lo SPID usando il KILL comando . Per informazioni di riferimento, vedere KILL (Transact-SQL).

Il comando KILL accetta il valore dello SPID come input. Ad esempio, per terminare SPID 9, eseguire il comando seguente:

KILL 99

Vedi anche

• Monitoraggio delle prestazioni con Query Store
• Risolvere i problemi di blocco causati dall'escalation dei blocchi in SQL Server
• Guida per il controllo delle versioni delle righe e il blocco della transazione
• SET TRANSACTION ISOLATION LEVEL
• Guida introduttiva: Eventi estesi in SQL Server