Condividi tramite

Identificare e risolvere gli errori bloccanti

  • Articolo

Si applica a: database SQL di Azuredatabase SQL in Fabric

L'articolo descrive il blocco nei database SQL di Azure e SQL di Fabric, e dimostra come individuare e risolvere i problemi di blocco.

Obiettivo

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 database SQL di Azure, 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, ma sono atomiche. Una connessione può bloccarne un'altra, indipendentemente dal client di origine.

Per informazioni sulla risoluzione dei deadlock, vedere Analizzare e prevenire i deadlock in Azure SQL Database e Fabric SQL database.

Nota

Questo contenuto è incentrato sul database SQL di Azure. Database SQL di Azure si basa sulla versione stabile più recente del motore di database di Microsoft SQL Server, quindi gran parte del contenuto è simile, anche se le opzioni e gli strumenti per la risoluzione dei problemi possono differire. Per altre informazioni sui blocchi in SQL Server, vedere Comprendere e risolvere i problemi di blocco di SQL Server. Il database SQL Fabric condivide molte funzionalità con il database SQL di Azure. Per altre informazioni sul monitoraggio delle prestazioni, vedere Monitorare il database SQL in Microsoft Fabric.

Comprendere il blocco

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. Il blocco in un database SQL di Azure si verifica quando una sessione mantiene 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. Questo comportamento è normale e può verificarsi molte volte durante il corso di un giorno senza effetti evidenti sulle prestazioni del sistema.

Per ogni nuovo database in Azure SQL Database è abilitata l'impostazione del database read committed snapshot (RCSI) per impostazione predefinita. Il blocco tra le sessioni che leggono i dati e quelle che scrivono i dati è ridotto sotto RCSI, che utilizza il versionamento delle righe per aumentare la concorrenza. Tuttavia, il bloccaggio e i deadlock possono verificarsi comunque nei database in database SQL di Azure perché:

  • Le query che modificano i dati potrebbero bloccarsi tra loro.
  • Le query possono essere eseguite con livelli di isolamento che aumentano i blocchi. I livelli di isolamento possono essere specificati nelle stringa di connessione dell'applicazione, negli hint di query o nelle istruzioni SET in Transact-SQL.
  • RCSI può essere disabilitato, causando l'uso dei blocchi condivisi (S) da parte del database per proteggere le istruzioni SELECT eseguite con il livello di isolamento Read Committed. Ciò può aumentare il bloccaggio e i deadlock.

Il livello di isolamento dello snapshot è abilitato per impostazione predefinita anche per i nuovi database in database SQL di Azure. L'isolamento dello snapshot è un livello di isolamento aggiuntivo basato su righe che garantisce coerenza a livello di transazione per i dati e che usa le versioni delle righe per selezionare le righe da aggiornare. Per usare l'isolamento dello snapshot, le query o le connessioni devono impostare in modo esplicito il livello di isolamento della transazione su SNAPSHOT. Tale operazione può essere eseguita solo quando l'isolamento dello snapshot è abilitato per il database.

È possibile identificare se l'isolamento RCSI e/o dello snapshot è abilitato con Transact-SQL. Connetti al database SQL di Azure ed esegui la query seguente:

SELECT name, is_read_committed_snapshot_on, snapshot_isolation_state_desc
FROM sys.databases
WHERE name = DB_NAME();
GO

Se è abilitato RCSI, la colonna is_read_committed_snapshot_on restituirà il valore 1. Se è abilitato l'isolamento dello snapshot, la colonna snapshot_isolation_state_desc restituirà il valore ON.

La durata e il contesto delle transazioni di una query determinano per quanto tempo i blocchi vengono mantenuti e l'effetto che hanno su altre query. Le istruzioni SELECT eseguite in RCSI non acquisiscono blocchi condivisi (S) sui dati letti e pertanto non bloccano le transazioni che modificano i dati. 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 esplicita, il tipo di blocchi e la durata per cui vengono mantenuti i blocchi sono determinati 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:

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 mantiene blocchi su un set di risorse per un periodo di tempo prolungato prima di rilasciarli. Questo tipo di bloccaggio si risolve nel tempo, ma può causare una riduzione delle prestazioni.

  • Uno SPID mantiene blocchi su un set di risorse senza mai rilasciarli. 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 mentre diversi SPID causano il blocco di risorse diverse nel tempo, creando un bersaglio 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.

Blocco ottimizzato

Il blocco ottimizzato è una nuova funzionalità di motore di database che riduce drasticamente la memoria di blocco e il numero di blocchi necessari simultaneamente per le scritture. Il blocco ottimizzato utilizza due componenti principali: il blocco dell'ID transazione (TID), utilizzato anche in altre funzionalità di controllo delle versioni delle righe, e il blocco dopo la qualificazione (LAQ). Non richiede alcuna configurazione aggiuntiva.

Questo articolo si applica attualmente al comportamento del motore di database senza blocchi ottimizzati.

Per altre informazioni e per informazioni su dove è disponibile il blocco ottimizzato, vedere Blocco ottimizzato.

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 di database effettuate, un problema di blocco richiede spesso sia l'ispezione delle istruzioni SQL esatte inviate dall'applicazione, sia il comportamento esatto dell'applicazione relativo all'annullamento delle query, alla gestione della connessione, al recupero di tutte le righe dei risultati 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 database SQL di Azure, 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. Una buona disciplina di gestione delle connessioni deve essere esercitata. Senza di esso, l'applicazione può sembrare avere prestazioni accettabili a un numero ridotto di utenti, ma le prestazioni potrebbero peggiorare significativamente man mano che il numero di utenti aumenta.

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

Nota

Per altre indicazioni sullo sviluppo di applicazioni, vedere Risolvere i problemi di connettività e altri errori e gestione degli errori temporanei.

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 determina il resto della composizione di questo articolo. Il concetto consiste nel trovare il blocco principale e identificare cosa fa la query e perché sta bloccando. 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 sta verificando il blocco. Dopo aver compreso i motivi per cui è possibile apportare modifiche, è possibile riprogettare la query e la transazione.

Procedura per la risoluzione dei problemi:

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

  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 di database può usare script SQL che monitorano costantemente lo stato di blocco e blocco nel database in database SQL di Azure. Per raccogliere questi dati, esistono essenzialmente due metodi.

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 metodo consiste nell'usare XEvents per acquisire ciò che viene eseguito.

Raccogli 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. Cerca gli SPID delle vittime che vengono bloccati. Se uno SPID è bloccato da un altro SPID, analizzare lo SPID proprietario della risorsa (SPID bloccante). Anche il proprietario di SPID è bloccato? È possibile esaminare la catena per trovare il blocco principale e quindi indagare sul perché sta mantenendo il suo blocco.

Ricordarsi di eseguire ognuno di questi script nel database di destinazione in database SQL di Azure.

  • 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 più semplice da eseguire query e filtrare. È possibile trovare sys.dm_exec_sessions alla base 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. 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 alla colonna blocking_session_id in sys.dm_exec_requests. 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) viene elencata in sys.dm_exec_sessions. Creare questo join comune tra sys.dm_exec_requests e sys.dm_exec_sessions nella query successiva.

  • 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 dal campo text di sys.dm_exec_sql_text sono NULL, la query non è attualmente in esecuzione. In tal caso, il campo event_info contiene l'ultima stringa di comando sys.dm_exec_input_buffer passata al motore SQL. Questa query può essere utilizzata anche per identificare le sessioni che bloccano altre sessioni, compreso 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;
  • Esegui questa query di esempio più elaborata, fornita dal Supporto Tecnico Microsoft, per identificare il nodo di una catena di blocco multipla, incluso il testo della query delle sessioni coinvolte.
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 monitoraggio delle transazioni, consultare le viste e le funzioni di gestione dinamica correlate alle transazioni per ulteriori informazioni.
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 che si trova al livello di thread/attività di SQL. Vengono restituite informazioni sul tipo di attesa SQL attualmente riscontrato dalla richiesta. Come sys.dm_exec_requests, solo le richieste attive vengono restituite da sys.dm_os_waiting_tasks.

Nota

Per altre informazioni sui tipi di attesa, incluse le statistiche di attesa aggregate nel tempo, vedere la DMV sys.dm_db_wait_stats. Questa DMV restituisce statistiche di attesa aggregate solo per il database corrente.

  • Per ottenere informazioni più granulari sui blocchi inseriti dalle query, usare la DMV sys.dm_tran_locks. Questa DMV può restituire grandi quantità di dati in un database 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 fornisce punti dati che consentono di esaminare il blocco in un intervallo di tempo specificato per identificare tendenze o blocchi persistenti.

Raccogliere informazioni dagli eventi estesi

Oltre alle informazioni precedenti, è spesso necessario acquisire una traccia delle attività nel server per analizzare attentamente un problema di blocco in database SQL di Azure. Ad esempio, se una sessione esegue più istruzioni all'interno di una transazione, viene rappresentata solo l'ultima istruzione inviata. Tuttavia, una delle affermazioni precedenti potrebbe essere il motivo per cui i blocchi sono ancora in corso. 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. SQL Server Profiler, tuttavia, è una tecnologia di traccia deprecata e non supportata per il database SQL di Azure. gli eventi estesi è la tecnologia di traccia più recente che consente maggiore versatilità e minore effetto sul sistema osservato e la relativa interfaccia è integrata in SQL Server Management Studio (SSMS).

Consultare il documento che illustra come usare la Creazione guidata per una nuova sessione di Eventi Estesi in SSMS. Per i database SQL di Azure, tuttavia, SSMS fornisce una sottocartella Eventi estesi in ogni database in Esplora oggetti. Usare una sessione guidata di Extended Events per acquisire questi eventi utili:

  • Errori di categoria

    • Attenzione
    • Errore_segnalato
    • Avviso_di_esecuzione

  • Avvisi categoria:

    • Predicato_di_join_mancante

  • Categoria esecuzione:

    • Rpc_completed
    • Rpc_starting
    • Batch_sql_completato
    • Sql_batch_starting

  • Categoria Monitoraggio delle Interruzioni

    • rapporto_di_impasse_di_database_xml

  • Categoria della sessione

    • Connessione_esistente
    • Account di accesso
    • Disconnessione

Nota

Per informazioni dettagliate sui deadlock, vedere Analizzare e prevenire deadlock nel database SQL di Azure e nel database SQL dell'infrastruttura.

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 le teste delle catene di blocco, utilizzando blocking_these e session_id. Ciò identifica in modo più chiaro quali richieste vengono bloccate e quali bloccano. Esaminare ulteriormente le sessioni bloccate e bloccanti. C'è 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 DMVs sys.dm_exec_requests e sys.dm_exec_sessions per informazioni sugli SPIDs all'inizio della catena di blocco. Cercare i seguenti campi:

    • sys.dm_exec_requests.status
      Questa colonna mostra lo stato di una determinata richiesta. 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 batch. Uno stato pronto per l'esecuzione o in esecuzione indica che lo SPID sta attualmente elaborando una query. Nella tabella seguente vengono fornite brevi spiegazioni dei vari valori di stato.
    Status Significato
    Background Lo SPID esegue un'attività in background, ad esempio il rilevamento dei blocchi, lo scrittore di log o il checkpoint.
    Dormire 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 su un pianificatore.
    Eseguibile Lo SPID si trova nella coda eseguibile di uno scheduler ed è in attesa di ottenere il tempo dello scheduler.
    Sospeso Lo SPID è in attesa di una risorsa, ad esempio un blocco o un latch.

    • sys.dm_exec_sessions.open_transaction_count
      Questo campo 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, questo campo 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 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 progredendo. 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 si può verificare confrontando l'uscita wait_resource tra sys.dm_exec_requests, che visualizza la risorsa in attesa di cui è la richiesta.

    • sys.dm_exec_requests.wait_resource Questo campo 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 Formato Esempio Spiegazione
    Tabella DatabaseID:ObjectID:IndexID SCHEDA: 5:261575970:1 In questo caso, l'ID database 5 è il database di esempio pubs e l'ID oggetto 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 is 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 di gestione dinamica sys.dm_db_page_info, passando databaseID, FileId, PageId dal wait_resource.
    Chiave DatabaseID:Hobt_id (valore hash per la chiave di indice) CHIAVE: 5:72057594044284928 (3300a4f361aa) In questo caso, l'ID database 5 è pubse Hobt_ID 72057594044284928 corrisponde a index_id 2 per object_id 261575970 (tabella dei titoli). Utilizzare la vista del catalogo sys.partitions per associare l'hobt_id a un determinato 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.
    Compila 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 altri dati situazionali per valutare se potrebbero essere l'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
, azure_dtc_state = CASE tat.dtc_state 
                    WHEN 1 THEN 'ACTIVE'
                    WHEN 2 THEN 'PREPARED'
                    WHEN 3 THEN 'COMMITTED'
                    WHEN 4 THEN 'ABORTED'
                    WHEN 5 THEN 'RECOVERED' END
, 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 (nome host), in determinate librerie di rete (net_library), quando l'ultimo batch inviato da uno 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_requests e così via.

Scenari di bloccaggio comuni

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

Le colonne Waittype, Open_Trane Status fanno riferimento alle informazioni restituite da sys.dm_exec_request. Altre colonne potrebbero essere restituite da sys.dm_exec_sessions. La colonna "Resolves?" indica se il blocco viene risolto autonomamente o se la sessione deve essere terminata tramite il comando KILL. Per altre informazioni, vedere KILL.

Scenario Tipo di Attesa Open_tran Status Risolto? Altri sintomi
1 NON NULLO >= 0 eseguibile Sì, al termine dell'interrogazione. Nelle colonne sys.dm_exec_sessions, reads, cpu_timee/o memory_usage, l'aumento avviene nel tempo. La durata della query è elevata quando viene completata.
2 NULL >0 dormire No, ma lo SPID può essere disattivato. Un segnale di attenzione può essere visualizzato nella sessione degli eventi estesi per questo SPID, a indicare che si è verificato un timeout o un annullamento della query.
3 NULL >= 0 eseguibile No. Non viene risolto finché il client non recupera tutte le righe o chiude la connessione. Lo SPID può essere terminato, ma può richiedere fino a 30 secondi. Se open_transaction_count = 0 e lo SPID mantiene i blocchi mentre il livello di isolamento della transazione è predefinito (READ COMMITTED), questo rappresenta una causa probabile.
4 Variabile >= 0 eseguibile No. Non viene risolto fino a quando il client non annulla le query o chiude le connessioni. Gli SPID possono essere terminati, ma ciò potrebbe richiedere fino a 30 secondi. La colonna hostname in sys.dm_exec_sessions per l'SPID a capo di una catena di blocco è la stessa di uno degli SPID che blocca.
5 NULL >0 ripristino Sì. Un segnale di attenzione può essere visualizzato nella sessione Eventi estesi per questo SPID, indicando che si è verificato un timeout o un annullamento della query oppure che è stata semplicemente emessa un'istruzione di rollback.
6 NULL >0 dormire Eventualmente. Quando Windows determina che la sessione non è più attiva, la connessione al database SQL di Azure viene interrotta. Il valore last_request_start_time in sys.dm_exec_sessions risale a molto tempo prima rispetto all'ora corrente.

Scenari di blocco dettagliati

  1. Blocco causato da una query che si esegue normalmente con un tempo di esecuzione lungo

    Soluzione: La soluzione per questo tipo di problema di blocco consiste nel cercare modi per ottimizzare la query. In realtà, questa classe di problema di blocco può essere solo un problema di prestazioni e richiede di seguirla 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 di Query Store in SSMS sono anche uno strumento estremamente consigliato e prezioso per identificare le query più costose, piani di esecuzione non ottimali. Esaminare anche Approfondimenti sulle Prestazioni delle Query.

    Se la query esegue solo operazioni SELECT, è consigliabile eseguire l'istruzione con isolamento dello snapshot se è abilitata nel database, soprattutto se RCSI è stato disabilitato. Come quando RCSI è abilitato, le query che leggono i dati non richiedono blocchi condivisi (S) sotto il livello di isolamento dello snapshot. Inoltre, l'isolamento dello snapshot fornisce la coerenza a livello di transazione per tutte le istruzioni in una transazione esplicita con più istruzioni. L'isolamento dello snapshot potrebbe essere già abilitato nel database. L'isolamento dello snapshot può essere usato anche con le query che eseguono modifiche, ma è necessario gestire i conflitti di aggiornamento.

    Nel caso 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 reporting dedicato, una replica sincrona di sola lettura del database.

  2. Bloccaggio causato da un SPID dormiente con una transazione non confermata

    Questo tipo di bloccaggio può spesso essere identificato da uno SPID in sospensione o in attesa di un comando, ma il cui livello di annidamento delle transazioni (@@TRANCOUNT, open_transaction_count in sys.dm_exec_requests) è maggiore di zero. Ciò può verificarsi se l'applicazione riscontra un timeout delle query o genera un annullamento senza emettere anche il numero necessario di istruzioni ROLLBACK e/o COMMIT. Quando un SPID riceve un timeout della richiesta o una cancellazione, termina la query e il batch corrente, ma non esegue automaticamente il rollback o il commit della transazione. L'applicazione è responsabile di queste operazioni, perché Database SQL di Azure non può presumere che debba essere eseguito il rollback di un'intera transazione a causa dell'annullamento di una singola query. Il timeout o l'annullamento della query viene visualizzato come evento di segnale ATTENTION per SPID nella sessione di 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;
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 livello di annidamento della transazione è uno. Tutti i blocchi acquisiti nella transazione rimangono acquisiti fino a quando la transazione non viene eseguita o annullata. 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:

    • Inoltre, questa classe di problema di blocco può anche essere un problema di prestazioni e richiede di seguirla come tale. Se il tempo di esecuzione della query può essere ridotto, il timeout o l'annullamento della query non si verificherà. È importante che l'applicazione sia in grado di gestire gli scenari di timeout o annullamento, 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:

      • 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 sia a conoscenza. 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 viene 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 potrebbe 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 provoca una disconnessione fisica dal database SQL di Azure, portando il server ad eseguire il rollback delle transazioni aperte.
      • Utilizzare SET XACT_ABORT ON per la connessione, oppure in tutte le stored procedure che iniziano le transazioni e non vengono ripulite dopo un errore. In caso di errore di run-time, questa impostazione interrompe le transazioni aperte e restituirà il controllo al client. Per ulteriori informazioni, consultare SET XACT_ABORT.

    Nota

    La connessione non viene reimpostata finché non viene riutilizzata dal pool di connessioni, quindi è possibile che un utente possa aprire una transazione e quindi rilasciare la connessione al pool di connessioni, ma potrebbe non essere riutilizzata per diversi secondi, durante la quale la transazione rimarrà aperta. Se la connessione non viene riutilizzata, la transazione viene interrotta quando si verifica il timeout della connessione e viene rimossa dal pool di connessioni. Pertanto, è ottimale per l'applicazione client interrompere le transazioni nel gestore errori o usare SET XACT_ABORT ON per evitare questo potenziale ritardo.

    Attenzione

    Dopo SET XACT_ABORT ON, le istruzioni T-SQL che seguono un'istruzione che causa un errore non vengono eseguite. Ciò potrebbe influire sul flusso previsto del codice esistente.

  3. Blocco causato da uno SPID la cui applicazione client corrispondente non ha completato il recupero di tutte le righe di risultati.

    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 ciò non accade, e l'applicazione non può essere configurata a tale scopo, potrebbe non essere possibile risolvere il problema di bloccaggio. 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.

    L'impatto di questo scenario è ridotto quando lo snapshot Read Committed è abilitato nel database, ovvero la configurazione predefinita in Database SQL di Azure. Per altre informazioni, vedere la sezione Informazioni sul bloccaggio di questo articolo.

    Nota

    Vedere le indicazioni per la logica di ripetizione dei tentativi per le applicazioni che si connettono a database SQL di Azure.

    Soluzione: 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.

  4. Bloccaggio causato da una sessione nello stato di rollback

    Viene 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 sys.dm_exec_requests, che può indicare il comando ROLLBACK e la colonna percent_complete può mostrare lo stato di avanzamento.

    Grazie a ripristino accelerato del database introdotto nel 2019, i rollback lunghi dovrebbero essere rari.

    Soluzione: attendere il completamento del rollback delle modifiche apportate dallo SPID.

    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à.

  5. Bloccaggio causato da una connessione orfana

    Se l'applicazione client intercetta degli errori oppure se la workstation client viene riavviata, la sessione di rete al server potrebbe non essere annullata immediatamente in alcune condizioni. Dal punto di vista del database SQL di Azure, il client sembra ancora presente e gli eventuali blocchi acquisiti potrebbero ancora essere trattenuti. Per ulteriori informazioni, vedere Come risolvere i problemi di connessioni orfane in SQL Server.

    Risoluzione: se l'applicazione client si è disconnessa senza pulire correttamente le risorse, è possibile terminare la sessione usando il comando KILL. Il comando KILL accetta il valore dello SPID come input. Ad esempio, per terminare SPID 99, eseguire il comando seguente:

    KILL 99

Contenuto correlato