Condividi tramite

Comprendere e risolvere i problemi di blocco di Database SQL di Azure

  • Articolo

Si applica a: Database SQL di Azure

L'articolo descrive il blocco nei database SQL di Azure e illustra come risolvere i problemi e risolvere il 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, consultare la sezione Analizzare e prevenire deadlock nel database SQL di Azure.

Nota

Questo contenuto è incentrato sul database SQL di Azure. Database SQL di Azure e Istanza gestita di SQL di Azure si basano 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.

Informazioni sulla funzione di 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. Si tratta di un 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 database SQL di Azure è abilitata per impostazione predefinita l'impostazione snapshot Read Committed (RCSI) del database. Il bloccaggio tra le sessioni che leggono i dati e le sessioni di scrittura dei dati è minimizzato in RCSI, che usa il controllo delle versioni 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 il bloccaggio. 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. Connettersi al database in database SQL di Azure ed eseguire 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 vengono mantenuti i blocchi e, di conseguenza, il relativo effetto 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 come diversi SPID causano il blocco su risorse diverse nel tempo, creando una destinazione mobile. Queste situazioni sono difficili da risolvere usando SQL Server Management Studio per limitare il problema alle singole query. Al contrario, la seconda situazione comporta uno stato coerente che può essere più facile da diagnosticare.

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 usa due componenti principali: blocco ID transazione (TID) (usato anche in altre funzionalità di controllo delle versioni delle righe) e 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 al database, un problema di blocco richiede tuttavia spesso sia l'ispezione delle istruzioni SQL esatte inviate dall'applicazione sia il comportamento esatto dell'applicazione relativamente all'annullamento delle query, alla gestione delle connessioni, al recupero di tutte le righe di risultato e così via. Se lo strumento di sviluppo non consente il controllo esplicito sulla gestione delle connessioni, l'annullamento delle query, il timeout delle query, il recupero dei risultati e così via, i problemi di blocco potrebbero non essere risolvibili. Questo potenziale deve essere esaminato attentamente prima di selezionare uno strumento di sviluppo di applicazioni per 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. È necessario esercitare una buona disciplina di gestione delle connessioni, senza di essa, che l'applicazione potrebbe avere prestazioni accettabili a un numero ridotto di utenti, ma le prestazioni possono peggiorare significativamente man mano che il numero di utenti aumenta.

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

Nota

Per altre informazioni, vedere Risoluzione dei problemi di connettività e di altri errori con il database SQL di Azure e Istanza gestita di SQL di Azure 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 determinerà il resto della composizione di questo articolo. Il concetto consiste nel trovare il blocco head e identificare le operazioni che la query sta facendo e perché è bloccante. Una volta identificata la query problematica, ovvero ciò che mantiene i blocchi per il periodo prolungato, il passaggio successivo consiste nell'analizzare e determinare il motivo per cui si sta verificando il blocco. Dopo aver compreso il motivo, è possibile apportare modifiche riprogettando la query e la transazione.

Procedura per la risoluzione dei problemi:

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

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

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

  4. Risolvere il problema di bloccaggio riprogettando query e transazioni

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

Raccolta di informazioni sul bloccaggio

Per contrastare la difficoltà di risoluzione dei problemi di blocco, un amministratore 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.

Raccolta delle informazioni dalle DMV

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

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. All'interno delle altre query si troverà sys.dm_exec_sessions.

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

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

  • Fare riferimento 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 usata anche per identificare le sessioni che bloccano altre sessioni, incluso un elenco di session_ids bloccati per session_id.

WITH cteBL (session_id, blocking_these) AS 
(SELECT s.session_id, blocking_these = x.blocking_these FROM sys.dm_exec_sessions s 
CROSS APPLY    (SELECT isnull(convert(varchar(6), er.session_id),'') + ', '  
                FROM sys.dm_exec_requests as er
                WHERE er.blocking_session_id = isnull(s.session_id ,0)
                AND er.blocking_session_id <> 0
                FOR XML PATH('') ) AS x (blocking_these)
)
SELECT s.session_id, blocked_by = r.blocking_session_id, bl.blocking_these
, batch_text = t.text, input_buffer = ib.event_info, * 
FROM sys.dm_exec_sessions s 
LEFT OUTER JOIN sys.dm_exec_requests r on r.session_id = s.session_id
INNER JOIN cteBL as bl on s.session_id = bl.session_id
OUTER APPLY sys.dm_exec_sql_text (r.sql_handle) t
OUTER APPLY sys.dm_exec_input_buffer(s.session_id, NULL) AS ib
WHERE blocking_these is not null or r.blocking_session_id > 0
ORDER BY len(bl.blocking_these) desc, r.blocking_session_id desc, r.session_id;
  • Eseguire questa query di esempio più elaborata, fornita da supporto tecnico Microsoft, per identificare l'intestazione di una catena di blocco di più sessioni, incluso il testo della query delle sessioni coinvolte in una catena di blocco.
WITH cteHead ( session_id,request_id,wait_type,wait_resource,last_wait_type,is_user_process,request_cpu_time
,request_logical_reads,request_reads,request_writes,wait_time,blocking_session_id,memory_usage
,session_cpu_time,session_reads,session_writes,session_logical_reads
,percent_complete,est_completion_time,request_start_time,request_status,command
,plan_handle,sql_handle,statement_start_offset,statement_end_offset,most_recent_sql_handle
,session_status,group_id,query_hash,query_plan_hash) 
AS ( SELECT sess.session_id, req.request_id, LEFT (ISNULL (req.wait_type, ''), 50) AS 'wait_type'
    , LEFT (ISNULL (req.wait_resource, ''), 40) AS 'wait_resource', LEFT (req.last_wait_type, 50) AS 'last_wait_type'
    , sess.is_user_process, req.cpu_time AS 'request_cpu_time', req.logical_reads AS 'request_logical_reads'
    , req.reads AS 'request_reads', req.writes AS 'request_writes', req.wait_time, req.blocking_session_id,sess.memory_usage
    , sess.cpu_time AS 'session_cpu_time', sess.reads AS 'session_reads', sess.writes AS 'session_writes', sess.logical_reads AS 'session_logical_reads'
    , CONVERT (decimal(5,2), req.percent_complete) AS 'percent_complete', req.estimated_completion_time AS 'est_completion_time'
    , req.start_time AS 'request_start_time', LEFT (req.status, 15) AS 'request_status', req.command
    , req.plan_handle, req.[sql_handle], req.statement_start_offset, req.statement_end_offset, conn.most_recent_sql_handle
    , LEFT (sess.status, 15) AS 'session_status', sess.group_id, req.query_hash, req.query_plan_hash
    FROM sys.dm_exec_sessions AS sess
    LEFT OUTER JOIN sys.dm_exec_requests AS req ON sess.session_id = req.session_id
    LEFT OUTER JOIN sys.dm_exec_connections AS conn on conn.session_id = sess.session_id 
    )
, cteBlockingHierarchy (head_blocker_session_id, session_id, blocking_session_id, wait_type, wait_duration_ms,
wait_resource, statement_start_offset, statement_end_offset, plan_handle, sql_handle, most_recent_sql_handle, [Level])
AS ( SELECT head.session_id AS head_blocker_session_id, head.session_id AS session_id, head.blocking_session_id
    , head.wait_type, head.wait_time, head.wait_resource, head.statement_start_offset, head.statement_end_offset
    , head.plan_handle, head.sql_handle, head.most_recent_sql_handle, 0 AS [Level]
    FROM cteHead AS head
    WHERE (head.blocking_session_id IS NULL OR head.blocking_session_id = 0)
    AND head.session_id IN (SELECT DISTINCT blocking_session_id FROM cteHead WHERE blocking_session_id != 0)
    UNION ALL
    SELECT h.head_blocker_session_id, blocked.session_id, blocked.blocking_session_id, blocked.wait_type,
    blocked.wait_time, blocked.wait_resource, h.statement_start_offset, h.statement_end_offset,
    h.plan_handle, h.sql_handle, h.most_recent_sql_handle, [Level] + 1
    FROM cteHead AS blocked
    INNER JOIN cteBlockingHierarchy AS h ON h.session_id = blocked.blocking_session_id and h.session_id!=blocked.session_id --avoid infinite recursion for latch type of blocking
    WHERE h.wait_type COLLATE Latin1_General_BIN NOT IN ('EXCHANGE', 'CXPACKET') or h.wait_type is null
    )
SELECT bh.*, txt.text AS blocker_query_or_most_recent_query 
FROM cteBlockingHierarchy AS bh 
OUTER APPLY sys.dm_exec_sql_text (ISNULL ([sql_handle], most_recent_sql_handle)) AS txt;

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 è il 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.

  • Usare la DMV sys.dm_tran_locks per informazioni più dettagliate sui blocchi inseriti dalle query. Questa DMV può restituire grandi quantità di dati in un 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 fornirà punti dati che consentiranno 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, verrà rappresentata solo l'ultima istruzione inviata. Tuttavia, una delle istruzioni precedenti può essere il motivo per cui i blocchi vengono ancora mantenuti. Una traccia consentirà di visualizzare tutti i comandi eseguiti da una sessione all'interno della transazione corrente.

Esistono due modi per acquisire tracce in SQL Server: Eventi estesi (XEvent) e tracce profiler. SQL Server Profiler, tuttavia, è una tecnologia di traccia deprecata per database SQL di Azure. Eventi estesi è la tecnologia di traccia più recente che consente maggiore versatilità e minore impatto sul sistema osservato e presenta un’interfaccia integrata in SQL Server Management Studio (SSMS).

Fare riferimento al documento che illustra come usare la Creazione guidata 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 eventi estesi per acquisire questi eventi utili:

  • Errori categoria:

    • Attenzione
    • Error_reported
    • Execution_warning

  • Avvisi categoria:

    • Missing_join_predicate

  • Esecuzione categoria:

    • Rpc_completed
    • Rpc_starting
    • Sql_batch_completed
    • Sql_batch_starting

  • Categoria Deadlock_monitor

    • database_xml_deadlock_report

  • Sessione categoria

    • Existing_connection
    • Account di accesso
    • Disconnessione

Nota

Per informazioni sui deadlock, consultare la sezione Analizzare e prevenire deadlock nel database SQL di Azure.

Identificare e risolvere scenari di blocco comuni

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

Analizzare i dati bloccanti

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

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

    • sys.dm_exec_requests.status
      Questa colonna mostra lo stato di una determinata richiesta. In breve, uno stato di sospensione indica che lo SPID ha completato l'esecuzione ed è in attesa che l'applicazione invii un'altra query o batch. Uno stato eseguibile o in esecuzione indica che lo SPID sta attualmente elaborando una query. Nella tabella seguente vengono fornite brevi spiegazioni dei vari valori di stato.
    Status Significato
    Background Lo SPID esegue un'attività in background, ad esempio il rilevamento dei deadlock, il writer di log o il checkpoint.
    Sospeso Lo SPID non è attualmente in esecuzione. Questo indica in genere che lo SPID è in attesa di un comando dall'applicazione.
    In esecuzione Lo SPID è attualmente in esecuzione nell'utilità di pianificazione.
    Eseguibile Lo SPID si trova nella coda eseguibile di un'utilità di pianificazione e in attesa di ottenere l'orario dell'utilità di pianificazione.
    Suspended Lo SPID è in attesa di una risorsa, ad esempio un blocco o un latch.

    • sys.dm_exec_sessions.open_transaction_count
      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 effettuando lo stato di avanzamento. Quando una query sulla tabella sys.dm_exec_requests restituisce un valore nella colonna wait_time minore del valore wait_time di una query precedente di sys.dm_exec_requests, indica che il blocco precedente è stato acquisito e rilasciato ed è ora in attesa di un nuovo blocco (presupponendo che wait_time sia diverso da zero). Questa operazione può essere verificata confrontando l'oggetto wait_resource tra l’output sys.dm_exec_requests, che visualizza la risorsa per cui la richiesta è in attesa.

    • sys.dm_exec_requests.wait_resource 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 Formattazione 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 è pubs, l'ID file 1 è il file di dati primario e la pagina 104 è una pagina appartenente alla tabella dei titoli. Per identificare il object_id a cui appartiene la pagina, usare la funzione a gestione dinamica sys.dm_db_page_info, passando databaseID, FileId, PageId da wait_resource.
    Chiave DatabaseID:Hobt_id (valore hash per la chiave di indice) CHIAVE: 5:72057594044284928 (3300a4f361aa) In questo caso, l'ID database 5 è Pubs, Hobt_ID 72057594044284928 corrisponde a index_id 2 per object_id 261575970 (tabella dei titoli). Utilizzare la vista del catalogo sys.partitions per associare l’hobt_id a dei index_id e object_idparticolari. Non è possibile annullare l'hash della chiave di indice in un valore di chiave specifico.
    Riga DatabaseID:FileID:PageID:Slot(row) RID: 5:1:104:3 In questo caso, l'ID database 5 è pubs, l'ID file 1 è il file di dati primario, la pagina 104 è una pagina appartenente alla tabella dei titoli e lo slot 3 indica la posizione della riga nella pagina.
    Compile DatabaseID:FileID:PageID:Slot(row) RID: 5:1:104:3 In questo caso, l'ID database 5 è pubs, l'ID file 1 è il file di dati primario, la pagina 104 è una pagina appartenente alla tabella dei titoli e lo slot 3 indica la posizione della riga nella pagina.
    • sys.dm_tran_active_transactions La DMV sys.dm_tran_active_transactions contiene dati sulle transazioni aperte che possono essere unite ad altre DMV per un quadro completo delle transazioni in attesa del commit o del ripristino dello stato precedente. Usare la query seguente per restituire informazioni sulle transazioni aperte, unite ad altre DMV, tra cui sys.dm_tran_session_transactions. Considerare lo stato corrente di una transazione, transaction_begin_time e il relativo parametro e altri dati specifici per valutare se potrebbero trattarsi dell’origine del blocco.
    SELECT tst.session_id, [database_name] = db_name(s.database_id)
, tat.transaction_begin_time
, transaction_duration_s = datediff(s, tat.transaction_begin_time, sysdatetime()) 
, transaction_type = CASE tat.transaction_type  WHEN 1 THEN 'Read/write transaction'
                                                WHEN 2 THEN 'Read-only transaction'
                                                WHEN 3 THEN 'System transaction'
                                                WHEN 4 THEN 'Distributed transaction' END
, input_buffer = ib.event_info, tat.transaction_uow     
, transaction_state  = CASE tat.transaction_state    
            WHEN 0 THEN 'The transaction has not been completely initialized yet.'
            WHEN 1 THEN 'The transaction has been initialized but has not started.'
            WHEN 2 THEN 'The transaction is active - has not been committed or rolled back.'
            WHEN 3 THEN 'The transaction has ended. This is used for read-only transactions.'
            WHEN 4 THEN 'The commit process has been initiated on the distributed transaction.'
            WHEN 5 THEN 'The transaction is in a prepared state and waiting resolution.'
            WHEN 6 THEN 'The transaction has been committed.'
            WHEN 7 THEN 'The transaction is being rolled back.'
            WHEN 8 THEN 'The transaction has been rolled back.' END 
, transaction_name = tat.name, request_status = r.status
, 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_Tran e Status fanno riferimento alle informazioni restituite da sys.dm_exec_request, è possibile che altre colonne vengano restituite da sys.dm_exec_sessions. La colonna "Resolves?" indica se il blocco verrà risolto autonomamente o se la sessione deve essere terminata tramite il KILL comando . Per altre informazioni, vedere KILL (Transact-SQL).

Scenario Waittype Open_tran Status Resolves? Altri sintomi
1 NOT NULL >=0 runnable Sì, al termine della query. Nelle colonne sys.dm_exec_sessions, reads, cpu_time e/o memory_usage aumenterà nel tempo. La durata della query sarà elevata al termine.
2 NULL >0 sleeping No, ma lo SPID può essere terminato. 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 runnable No. Non verrà risolto fino a quando 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 COMMMITTED), questa è una causa probabile.
4 Variabile >=0 runnable No. Non verrà risolto finché il client non annulla le query o chiude le connessioni. Gli SPID possono essere terminati, ma possono richiedere fino a 30 secondi. La colonna hostname in sys.dm_exec_sessions per SPID all'inizio di una catena di blocco sarà uguale a quella di uno SPID che sta bloccando.
5 NULL >0 rollback Sì. Un segnale di attenzione può essere visualizzato nella sessione Eventi estesi per questo SPID, a indicare che si è verificato un timeout o un annullamento della query o semplicemente un'istruzione di rollback.
6 NULL >0 sleeping Eventualmente. Quando Windows NT determina che la sessione non è più attiva, la connessione al database SQL di Azure verrà 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 normalmente in esecuzione con un tempo di esecuzione prolungato

    Soluzione: La soluzione per questo tipo di problema di blocco consiste nel cercare modi per ottimizzare il blocco head, ovvero la query bloccante. 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. Vedere inoltre la sezione Prestazioni intelligenti del portale di Azure per il database SQL di Azure, incluse le Prestazioni dettagliate 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 uno SPID in sospensione con una transazione senza commit

    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 uno SPID riceve un timeout della query o un annullamento, termina la query e il batch corrente, ma non esegue automaticamente il rollback o il commit della transazione. L'applicazione è responsabile di 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 verranno visualizzati come evento di segnale ATTENTION per lo SPID nella sessione degli eventi estesi.

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

    CREATE TABLE #test (col1 INT);
INSERT INTO #test SELECT 1;
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 vengono mantenuti fino a quando non viene eseguito il commit o il rollback della transazione. Se le applicazioni aprono ed eseguono il commit in modo esplicito delle transazioni, una comunicazione o un altro errore potrebbero lasciare la sessione e la relativa transazione in uno stato aperto.

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

    Risoluzioni:

    • 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 quanto segue:

      • Nel gestore degli errori dell'applicazione client eseguire IF @@TRANCOUNT > 0 ROLLBACK TRAN in seguito a qualsiasi errore, anche se l'applicazione client non ritiene che una transazione sia aperta. È 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 verrà eseguito in tali casi.
      • Se il pool di connessioni viene usato in un'applicazione che apre la connessione ed esegue un numero ridotto di query prima di rilasciare nuovamente la connessione al pool, ad esempio un'applicazione basata sul Web, la disabilitazione temporanea del pool di connessioni può contribuire ad alleviare il problema finché 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 al database SQL di Azure, con conseguente esecuzione del rollback per le transazioni aperte da parte del server.
      • Utilizzare SET XACT_ABORT ON per la connessione o in qualsiasi stored procedure che iniziano le transazioni e non vengono ripulite in seguito a un errore. In caso di errore di run-time, questa impostazione interrompe le transazioni aperte e restituirà il controllo al client. Per altre informazioni, vedere SET XACT_ABORT (Transact-SQL).

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

    In seguito a SET XACT_ABORT ON, le seguenti istruzioni T-SQL a seguito di un'istruzione che causa un errore non verranno eseguite. Ciò potrebbe influire sul flusso previsto del codice esistente.

  3. Blocco causato da uno SPID la cui applicazione client corrispondente non ha recuperato tutte le righe di risultati al completamento

    Dopo aver inviato una query al server, tutte le applicazioni devono recuperare immediatamente tutte le righe di risultati al completamento. Se un'applicazione non recupera tutte le righe dei risultati, è possibile che vengano lasciati blocchi sulle tabelle, bloccando altri utenti. Se si usa un'applicazione che invia in modo trasparente istruzioni SQL al server, l'applicazione deve recuperare tutte le righe dei risultati. Se 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

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

    Grazie alla funzionalità di ripristino accelerato del database introdotta 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 di Database SQL di Azure, il client sembra ancora presente e tutti i blocchi acquisiti possono comunque essere mantenuti. Per altre informazioni, vedere Risoluzione dei problemi di connessione a 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