Comprendere e risolvere i problemi di blocco di SQL Server

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

Numero originale della Knowledge Base: 224453

Obiettivo

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

In questo articolo il termine connessione si riferisce 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 si tratta di un contesto di processo separato nel vero senso del termine. Piuttosto, ogni SPID è costituito dalle risorse del server e dalle strutture di dati necessarie per gestire le richieste di una singola connessione da parte di un determinato client. Una singola applicazione client può avere una o più connessioni. Nell'ottica 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, poiché sono atomiche. Una connessione può bloccare un'altra connessione, indipendentemente dal client di origine.

Che cos'è il blocco

Il blocco è una caratteristica inevitabile e da progettazione di qualsiasi sistema di gestione di database relazionale (RDBMS) con concorrenza basata su 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 nella stessa risorsa. In genere, l'intervallo di tempo per il quale il primo SPID blocca la risorsa è ridotto. Quando la sessione proprietaria rilascia il blocco, la seconda connessione è quindi libera di acquisire il proprio blocco sulla risorsa e continuare l'elaborazione. Il blocco, come descritto qui, è un comportamento normale e può verificarsi molte volte nel corso di una giornata senza avere effetti evidenti sulle prestazioni del sistema.

La durata e il contesto della transazione di una query determinano per quanto tempo vengono mantenuti i blocchi e, di conseguenza, il relativo effetto sulle 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 effettiva, non durante la query. Per le istruzioni INSERT, UPDATE e DELETE, i blocchi vengono mantenuti durante la query, sia per la 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 è determinata dal tipo di query, dal livello di isolamento della transazione e dall'utilizzo di hint di blocco nella query. Per una descrizione dei blocchi, degli hint di blocco e dei livelli di isolamento delle transazioni, consultare gli articoli seguenti:

• Blocco nel motore di database
• Personalizzazione del blocco e del controllo delle versioni delle righe
• Modalità di blocco
• Compatibilità del blocco
• Livelli di isolamento basati sul controllo delle versioni delle righe nel motore di database
• Transazioni

Quando i blocchi vengono mantenuti fino al punto in cui si verifica un effetto negativo sulle prestazioni del sistema, ciò è dovuto a uno dei motivi seguenti:

• Uno SPID mantiene i blocchi su un set di risorse per un periodo di tempo prolungato prima di rilasciarli. Questo tipo di blocco si risolve nel tempo, ma può causare una riduzione delle prestazioni.

• Uno SPID mantiene i blocchi in un set di risorse e senza mai rilasciarli. Questo tipo di blocco non si risolve da solo e impedisce l'accesso alle risorse interessate in modo indefinito.

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

Applicazioni e blocco

Quando si verifica un problema di blocco, può verificarsi una tendenza a concentrarsi sull'ottimizzazione lato server e sui problemi della piattaforma. Tuttavia, l'attenzione rivolta solo al database potrebbe non portare a una risoluzione e può assorbire il tempo e l'energia che sarebbe meglio indirizzare all'esame dell'applicazione client e delle query da essa inviate. Indipendentemente dal livello di visibilità che l'applicazione espone per quanto riguarda le chiamate al database effettuate, un problema di blocco richiede tuttavia spesso sia l'ispezione delle istruzioni di SQL esatte inviate dall'applicazione che il comportamento esatto dell'applicazione in merito all'annullamento delle query, alla gestione delle connessioni, al recupero di tutte le righe dei risultati e così via. Se lo strumento di sviluppo non consente il controllo esplicito sulla gestione della connessione, sull'annullamento delle query, sul timeout delle query, sul 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 di risorse, il livello di isolamento e la lunghezza del percorso della transazione devono essere valutati per ogni query. Ogni query e transazione deve essere il più semplificata possibile. È necessario esercitare una buona disciplina di gestione della connessione, senza di essa, l'applicazione può sembrare avere prestazioni accettabili con un numero ridotto di utenti, ma le prestazioni possono peggiorare significativamente man mano che il numero di utenti aumenta.

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

Risolvere i problemi di bloccaggio

Indipendentemente dalla situazione di bloccaggio in cui ci troviamo, la metodologia per la risoluzione dei problemi è 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 eseguite da tale query e il motivo per cui è bloccante. Una volta identificata la query problematica, ovvero ciò che contiene 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 (head blocker)

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

3. Analizzare/comprendere il motivo per cui si verifica il blocco prolungato

4. Risolvere il problema di blocco riprogettando query e transazioni

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

Raccogliere informazioni di blocco

Per contrastare la difficoltà di risoluzione dei problemi di blocco, un amministratore di database può usare script SQL che monitorano costantemente lo stato di blocco su 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 viste a gestione dinamica (DMV) e alcuni sono funzioni a gestione dinamica (DMF).

Il secondo consiste nell'usare Eventi estesi (XEvents) o tracce del profiler SQL per acquisire ciò che è in esecuzione. Poiché SQL Trace e SQL Server Profiler sono obsoleti, questa guida alla risoluzione dei problemi si concentrerà su XEvents.

Raccogliere le informazioni dalle DMV

Il 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 l'istruzione SQL. Cercare gli SPID interessati che vengono bloccati. Se qualsiasi SPID viene bloccato da un altro SPID, esaminare lo SPID proprietario della risorsa (lo SPID che blocca). Anche lo SPID proprietario è bloccato? È possibile percorrere la catena per trovare il blocco principale e quindi indagare sul motivo per cui sta mantenendo il blocco.

A tale scopo, si può utilizzare uno dei seguenti metodi:

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

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

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

• I comandi sp_who 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 su cui è più semplice eseguire query e filtri. sys.dm_exec_sessions si trova al centro di altre query.

• Se è già stata identificata una sessione specifica, è possibile usare DBCC INPUTBUFFER(<session_id>) per trovare l'ultima istruzione inviata da una sessione. Risultati simili possono essere restituiti con la funzione di gestione dinamica (DMF) sys.dm_exec_input_buffer, in un set di risultati su cui è più semplice eseguire query e filtri, 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 sys.dm_exec_requests e alla colonna blocking_session_id. 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. Compilare questo join comune tra sys.dm_exec_requests e sys.dm_exec_sessions nella query successiva. Tenere presente che per essere restituita da sys.dm_exec_requests, la query deve essere eseguita attivamente con SQL Server.

• Eseguire questa query di esempio per trovare le query in esecuzione attiva e il relativo testo batch o testo del buffer di input corrente SQL, usando le DMV sys.dm_exec_sql_text o sys.dm_exec_input_buffer. Se i dati restituiti dalla colonna text di sys.dm_exec_sql_text sono NULL, la query non è attualmente in esecuzione. In tal caso, la colonna event_info di sys.dm_exec_input_buffer conterrà l'ultima stringa di 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 bloccate 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 dal supporto tecnico Microsoft, per identificare la testa 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 le transazioni a esecuzione prolungata o senza 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, vedere altre DMV in transazioni 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];

• Fare riferimento sys.dm_os_waiting_tasks che si trova al livello thread/attività di SQL Server. Vengono restituite informazioni sulle wait_type SQL attualmente riscontrate dalla richiesta. Analogamente a 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ù granulari 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 quali blocchi sono attualmente in stallo.

A causa di INNER JOIN in sys.dm_os_waiting_tasks, la query seguente limita l'output da sys.dm_tran_locks solo alle richieste attualmente bloccate, il relativo stato di attesa e i 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 di accesso per CSS per risolvere tali problemi è l'agente di raccolta dati PSSDiag. Questo strumento usa le "statistiche di SQL Server Perf" per raccogliere i set di risultati, nel tempo, dalle DMV a cui si è fatto riferimento in precedenza. Poiché questo strumento è in continua evoluzione, esaminare la versione pubblica più recente di DiagManager su GitHub.

Raccogliere informazioni da eventi estesi

Oltre alle informazioni precedenti, è spesso necessario acquisire una traccia delle attività sul server per analizzare a fondo 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 potrebbe essere il motivo per cui i blocchi vengono ancora mantenuti. Una traccia consente 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 (XEvents) e tracce del profiler. Tuttavia, le tracce SQL che usano SQL Server Profiler sono obsolete. XEvents è la piattaforma di traccia più recente e di qualità superiore, che consente una maggiore versatilità e meno 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 ulteriori informazioni, vedere Profiler XEvent. È anche possibile creare sessioni di eventi estesi personalizzate in SSMS, vedere Creazione guidata nuova sessione eventi estesi. Per la risoluzione dei problemi di blocco, in genere si acquisiranno:

• Errori 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 del processo bloccato, usare il comando sp_configure per configurare l'opzione di soglia del processo bloccato, che può essere impostata in secondi. Per impostazione predefinita, non vengono generati report di processi bloccati.

• Avvisi di 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
  • 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. In questa discussione si 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 tramite una sessione XEvent.

Analizzare i dati di blocco

• Esaminare l'output delle DMV sys.dm_exec_requests e sys.dm_exec_sessions per determinare gli inizi delle catene di blocco, utilizzando blocking_these e session_id. In questo modo si potranno identificare più chiaramente quali richieste sono bloccate e quali stanno causando il blocco. Esaminare ulteriormente le sessioni bloccate e quelle che causano il blocco. È presente una radice o una radice comune nella catena di blocco? 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 richiesta specifica. In genere, uno stato di sospensione indica che lo SPID ha completato l'esecuzione ed è in attesa che l'applicazione invii un'altra query o un altro batch. Uno stato eseguibile o in esecuzione indica che lo SPID sta attualmente elaborando una query. Nella tabella seguente vengono fornite brevi spiegazioni dei diversi valori di stato.

    Stato	Significato
    Background	Lo SPID esegue un'attività in background, ad esempio il rilevamento di deadlock, il writer del log o il checkpoint.
    Sospensione	Lo SPID attualmente non è in esecuzione. Questo indica in genere che lo SPID è in attesa di un comando da parte dell'applicazione.
    In esecuzione	Lo SPID è attualmente in esecuzione in un'utilità di pianificazione.
    Eseguibile	Lo SPID si trova nella coda eseguibile di un'utilità di pianificazione e in attesa di ottenere l'ora dell'utilità di pianificazione.
    Sospeso	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.

  • 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 all'interno della transazione.

  • 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 riscontrato dalla richiesta. Per altre informazioni relative a sys.dm_os_wait_stats e una descrizione dei tipi di attesa più comuni, consultare sys.dm_os_wait_stats. Il valore wait_time può essere utilizzato per determinare se la richiesta sta progredendo. Quando una query sulla tabella sys.dm_exec_requests restituisce un valore nella colonna wait_time che è inferiore rispetto al valore wait_time valore di una query precedente di sys.dm_exec_requests, questo indica che il blocco precedente è stato acquisito e rilasciato e ora è in attesa di un nuovo blocco (presupponendo un blocco diverso da zero wait_time). È possibile verificarlo confrontando sys.dm_exec_requests nell'output wait_resource che visualizza la risorsa per cui la richiesta è in attesa.

  • sys.dm_exec_requests.wait_resource Questa colonna indica la risorsa per cui la richiesta bloccata è in attesa. Nella tabella seguente sono elencati i formati comuni wait_resource e il relativo significato:

    Risorsa	Formato	Esempio	Spiegazione
    Tabella	DatabaseID:ObjectID:IndexID	TAB: 5:261575970:1	In questo caso, l'ID del database 5 è il database di esempio pub e object_id 261575970 è la tabella titles e 1 è l'indice del cluster.
    Pagina	DatabaseID:FileID:PageID	PAGINA: 5:1:104	In questo caso, l'ID database 5 è pub, l'ID file 1 è il file di dati primario e la pagina 104 è una pagina appartenente alla tabella titles. Per identificare l'object_id a cui appartiene la pagina, utilizzare a funzione di gestione dinamica sys.dm_db_page_info, passando nel 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 di database 5 è Pub, Hobt_ID 72057594044284928 corrisponde a index_id 2 per object_id 261575970 (tabella titles). Utilizzare la vista del catalogo sys.partitions per associare hobt_id a un particolare 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 di database 5 è pub, l'ID file 1 è il file di dati primario, la pagina 104 è una pagina appartenente alla tabella titles e lo slot 3 indica la posizione della riga nella pagina.
    Compila	DatabaseID:FileID:PageID:Slot(row)	RID: 5:1:104:3	In questo caso, l'ID di database 5 è pub, l'ID file 1 è il file di dati primario, la pagina 104 è una pagina appartenente alla tabella titles e lo slot 3 indica la posizione della riga nella pagina.

  • sys.dm_tran_active_transactions Il sys.dm_tran_active_transactions DMV contiene dati sulle transazioni aperte che possono essere unite ad altre DMV per un quadro completo delle transazioni in attesa di commit o rollback. Utilizzare la query seguente per restituire le informazioni sulle transazioni aperte, unite ad altre DMV, tra cui sys.dm_tran_session_transactions. Prendere in considerazione lo stato corrente di una transazione, transaction_begin_time e altri dati situazionali per valutare se potrebbe essere un'origine di 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 uno SPID si trovava last_request_start_time in sys.dm_exec_sessions, per quanto tempo una richiesta era in esecuzione utilizzando start_time in sys.dm_exec_requests e così via.

Scenari comuni relativi al blocco

La tabella seguente associa i sintomi comuni alle rispettive cause probabili.

Le colonne wait_type, open_transaction_count e status fanno riferimento alle informazioni restituite da sys.dm_exec_request, le altre colonne possono essere restituite da sys.dm_exec_sessions. La colonna "Risolve?" indica se il blocco si risolverà da solo o se la sessione deve essere terminata tramite il comando KILL. Per maggiori informazioni, consultare sys.dm_io_virtual_file_stats (Transact-SQL).

Scenari di blocco dettagliati

Scenario 1: blocco causato da una query in esecuzione normale con un periodo di esecuzione lungo

In questo scenario, una query in esecuzione attiva ha acquisito blocchi e i blocchi non vengono rilasciati (sono interessati dal livello di isolamento della transazione). Pertanto, le altre sessioni attenderanno i blocchi finché non vengono rilasciati.

Risoluzione:

La soluzione a questo tipo di problema di blocco consiste nel cercare modi per ottimizzare la query. Questa classe di problema di blocco può anche essere un problema relativo alle prestazioni e richiede che venga risolto come tale. Per informazioni sulla risoluzione dei problemi di una query specifica a esecuzione lenta, consultare Come risolvere i problemi relativi alle query con esecuzione lenta in SQL Server. Per maggiori informazioni, consultare Monitoraggio e ottimizzazione per la performance.

Anche i report predefiniti per SSMS dal Query Store (introdotto in SQL Server 2016) sono uno strumento altamente consigliato e prezioso per identificare le query più dispendiose 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, provare a spostarla da un ambiente OLTP a un sistema di report dedicato. È anche possibile utilizzare i gruppi di disponibilità Always On per sincronizzare una replica di sola lettura del database.

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

Questo tipo di blocco può spesso essere identificato da uno SPID in sospensione o in attesa di un comando, ma il cui livello di nidificazione delle transazioni (@@TRANCOUNT, open_transaction_count da sys.dm_exec_requests) è maggiore di zero. Ciò può verificarsi se l'applicazione riscontra un timeout di query oppure genera un annullamento senza inviare anche il numero richiesto di istruzioni ROLLBACK e/o COMMIT. Quando uno SPID riceve un timeout di query o un annullamento, termina la query e il batch correnti, ma non esegue automaticamente il rollback o il commit della transazione. L'applicazione è responsabile di questo, in quanto SQL Server non può presumere che sia necessario eseguire il rollback di un'intera transazione a causa dell'annullamento di una singola query. Il timeout o l'annullamento della query verrà visualizzato come evento segnale ATTENTION per lo SPID nella sessione degli Eventi estesi.

Per illustrare una transazione esplicita non sottoposta a commit, inviare 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 conteggio delle transazioni è uno. Tutti i blocchi acquisiti nella transazione vengono mantenuti fino a quando non è stato eseguito il commit o il rollback della transazione. Se le applicazioni aprono e eseguono il commit esplicito delle transazioni, una comunicazione o un altro errore potrebbe lasciare la sessione e la transazione in uno stato aperto.

Utilizzare lo script precedente in questo articolo sulla base di 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 relativo alle prestazioni e richiede che venga risolto come tale. Se il tempo di esecuzione della query può essere ridotto, il timeout o l'annullamento della query non si verificherebbe. È importante che l'applicazione possa gestire gli scenari di timeout o annullamento se si dovessero verificare, ma è anche possibile sfruttare l'esame delle prestazioni della query.

• Le applicazioni devono gestire correttamente i livelli di nidificazione delle transazioni oppure potrebbero causare un problema di blocco in seguito all'annullamento della query in questo modo. Tenere in considerazione gli aspetti seguenti:

  • 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. È necessario verificare la presenza di transazioni aperte, perché una stored procedure chiamata durante il batch potrebbe aver avviato una transazione senza che l'applicazione client ne sia a conoscenza. Alcune condizioni, ad esempio l'annullamento della query, impediscono l'esecuzione oltre l'istruzione corrente, quindi anche se la procedura possiede la logica per controllare IF @@ERROR <> 0 e interrompere la transazione, in questi casi il codice di rollback non verrà eseguito.

  • Se il pool di connessioni viene usato in un'applicazione che apre la connessione ed esegue alcune query prima di inviare nuovamente la connessione al pool, ad esempio un'applicazione basata sul Web, la disabilitazione temporanea del pool di connessioni può contribuire a ovviare a questo 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 da SQL Server, causando il rollback delle transazioni aperte da parte del server.

  • Utilizzare SET XACT_ABORT ON per la connessione o in tutte le stored procedure che avviano le transazioni e non eseguono la pulizia in seguito a un errore. In caso di errore di runtime, questa impostazione interrompe tutte le transazioni aperte e restituisce il controllo al client. Per maggiori informazioni, consultare SET XACT_ABORT (Transact-SQL).

Scenario 3: blocco causato da uno SPID la cui applicazione client corrispondente non ha recuperato tutte le righe dei risultati al completamento

Dopo aver inviato una query al server, tutte le applicazioni devono recuperare immediatamente tutte le righe dei risultati al completamento. Se un'applicazione non recupera tutte le righe dei risultati, i blocchi possono essere lasciati nelle tabelle, bloccando gli altri utenti. Se si usa un'applicazione che invia istruzioni SQL in modo trasparente al server, l'applicazione deve recuperare tutte le righe dei risultati. In caso contrario (e se non può essere configurata per farlo), potrebbe non essere possibile risolvere il problema di blocco. Per evitare il problema, è possibile limitare le applicazioni con comportamento non corretto a un database di creazione di report o di supporto delle decisioni, 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'utilizzo 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 distribuito di un client/server

A differenza di un deadlock convenzionale, un deadlock distribuito non è rilevabile utilizzando la 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 si trova a livello di applicazione client, su cui SQL Server non esercita alcun controllo. Nelle due sessioni seguenti sono riportati esempi di come questo può verificarsi e dei possibili modi in cui l'applicazione può evitarlo.

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, potrebbe verificarsi il deadlock distribuito seguente. Notare che il termine dbproc qui utilizzato 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 dell'applicazione client ha due connessioni aperte. Invia in modo asincrono un'operazione SQL su dbproc1. Ciò significa che non attende la chiamata a restituire prima di procedere. L'applicazione invia quindi un'altra operazione SQL su dbproc2 e attende i risultati per avviare l'elaborazione dei dati restituiti. Quando i dati iniziano ad essere restituiti (a seconda di quale dbproc risponda per la prima volta, supponiamo che si tratti di dbproc1), l'applicazione li elabora 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 viene effettivamente bloccato su dbproc1 a livello di applicazione perché il thread singolo di esecuzione per l'applicazione è in uso da dbproc1. Ciò comporta un deadlock che SQL Server non è in grado di rilevare o di 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, è comunque possibile che si verifichi una variante di questo deadlock distribuito, 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 del fatto che dbproc2 e SPID2 eseguono un'istruzione SELECT con l'intenzione di eseguire l'elaborazione di una sola riga alla volta e di passare ogni riga tramite un buffer a dbproc1 per un'istruzione INSERT, UPDATE o DELETE nella stessa tabella. Infine, lo SPID1 (che esegue INSERT, UPDATE o DELETE) viene bloccato su un blocco mantenuto da SPID2 (che esegue SELECT). SPID2 scrive una riga di risultati nel client dbproc2. Dbproc2 tenta quindi di passare la riga in un buffer a dbproc1, ma rileva che dbproc1 è occupato (viene bloccato in attesa di SPID1 per completare l'oggetto corrente INSERT che è 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 è in grado di rilevare o di 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 di query, se si verifica il deadlock distribuito, questo verrà interrotto quando si verifica il timeout. Per altre informazioni sull'uso di un timeout di query, fare riferimento alla documentazione del provider di connessione.

Scenario 5: blocco causato da una sessione in stato di rollback

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

Verrà eseguito il rollback di una query di modifica dei dati interrotta o annullata all'esterno di una transazione definita dall'utente. Ciò può verificarsi anche come effetto collaterale del riavvio del computer client e della 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ù rapidamente rispetto all'applicazione iniziale delle modifiche. Ad esempio, se un'istruzione DELETE, INSERT o UPDATE è in esecuzione da un'ora, l'esecuzione del rollback potrebbe richiedere almeno un'ora. Si tratta di un comportamento previsto, perché è necessario eseguire il rollback delle modifiche apportate altrimenti l'integrità transazionale e fisica nel database sarebbero compromesse. Perché ciò possa accadere, SQL Server contrassegna lo SPID in uno stato "golden" o rollback (che significa che non può essere terminato oppure selezionato come vittima di deadlock). Questa operazione può essere spesso identificata osservando l'output di sp_who che può indicare il comando ROLLBACK. La colonna status di sys.dm_exec_sessions indicherà uno stato ROLLBACK.

Risoluzione:

È necessario attendere che la sessione completi il rollback delle modifiche apportate.

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

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

Scenario 6: blocco causato da una transazione orfana

Si tratta di uno scenario di un problema comune che si sovrappone in parte allo scenario 2. Se l'applicazione client si arresta, la workstation client viene riavviata oppure si verifica un errore di interruzione in batch, tutte queste operazioni potrebbero lasciare aperta una transazione. Questa situazione può verificarsi se l'applicazione non esegue il rollback della transazione nei blocchi dell'applicazione FINALLY oppure CATCH o se in caso contrario non gestisce questa situazione.

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

Per illustrare 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 conteggio delle 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à stato interrotto dalla query, l'applicazione che lo esegue potrebbe continuare a eseguire altre query nella stessa sessione senza pulire la transazione ancora aperta. Il blocco verrà mantenuto fino a quando la sessione non viene interrotta o l'istanza SQL Server non viene riavviata.

Risoluzioni:

• Il modo migliore per evitare questa condizione consiste nel migliorare la gestione degli errori/eccezioni dell'applicazione, in particolare per le interruzioni improvvise. Assicurarsi di usare un blocco Try-Catch-Finally nel codice dell'applicazione e di eseguire il rollback della transazione in caso di eccezione.
• Prendere in considerazione l'utilizzo di SET XACT_ABORT ON per la connessione o in tutte le stored procedure che avviano le transazioni e non eseguono la pulizia in seguito a un errore. In caso di un errore di runtime che interrompe il batch, questa impostazione eseguirà il rollback in automatico di tutte le transazioni aperte e restituirà il controllo al client. Per maggiori informazioni, consultare SET XACT_ABORT (Transact-SQL).
• Per risolvere una connessione orfana di un'applicazione client disconnessa senza pulirne in modo appropriato le risorse, è possibile terminare lo SPID usando il comando KILL. Per le informazioni di riferimento, consultare KILL (Transact-SQL).

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

KILL 99

Vedere anche

• Monitoraggio delle prestazioni utilizzando il Query Store
• Risolvere i problemi di blocco causati dall'escalation dei blocchi in SQL Server
• Guida al blocco delle transazioni e al controllo delle versioni delle righe
• IMPOSTARE IL LIVELLO DI ISOLAMENTO DELLE TRANSAZIONI
• Avvio rapido: eventi estesi in SQL Server