Risolvere i problemi di blocco causati dall'escalation dei blocchi in SQL Server

Si applica a: SQL Server

Numero KB originale: 323630

Riepilogo

L'escalation blocchi converte molti blocchi con granularità fine ,ad esempio blocchi di riga o di pagina, in un singolo blocco di tabella. SQL Server attiva l'escalation dinamica in base al numero di blocchi o alla memoria di blocco e il comportamento predefinito migliora la velocità effettiva nella maggior parte dei carichi di lavoro. Tuttavia, alcune progettazioni di query o transazioni causano l'escalation a volte quando il blocco della tabella risultante blocca altri utenti. Questo articolo illustra come determinare se l'escalation dei blocchi causa il blocco in SQL Server, come impedirla e come (e quando) disabilitarla.

Determinare se l'escalation dei blocchi causa il blocco

L'escalation dei blocchi non causa la maggior parte dei problemi di blocco. Per determinare se l'escalation dei blocchi si verifica in corrispondenza o quasi del momento in cui si verificano problemi di blocco, avviare una sessione eventi estesi che include l'evento lock_escalation . Se non vengono visualizzati lock_escalation eventi, l'escalation dei blocchi non si verifica nel server e le informazioni contenute in questo articolo non si applicano alla situazione.

Se si verifica l'escalation dei blocchi, verificare che il blocco della tabella inoltrato blocchi blocchi altri utenti.

Per altre informazioni su come identificare il blocco head e la risorsa di blocco contenuta nel blocco head e che blocca altri ID dei processi del server, vedere Informazioni e risoluzione dei problemi di blocco SQL Server blocco.

Se il blocco che blocca altri utenti è diverso da un blocco TAB (a livello di tabella) con modalità di blocco S (condiviso) o X (esclusivo), l'escalation dei blocchi non è il problema. In particolare, se il blocco TAB è un blocco finalità (ad esempio una modalità di blocco di IS, IU o IX), non è causato dall'escalation dei blocchi. Se i problemi di blocco non sono causati dall'escalation dei blocchi, vedere la procedura per comprendere e risolvere SQL Server problemi di blocco.

Impedire l'escalation dei blocchi

Il metodo più semplice e sicuro per impedire l'escalation dei blocchi consiste nel mantenere le transazioni brevi e ridurre il footprint di blocco delle query costose in modo che le soglie di escalation dei blocchi non vengano superate. Usare una o più delle strategie seguenti.

Suddividere le operazioni di grandi dimensioni in transazioni più piccole

Suddividere le operazioni batch di grandi dimensioni in diverse operazioni più piccole. Ad esempio, si esegue la query seguente per rimuovere più di 100.000 record precedenti da una tabella di controllo e quindi si determina che la query ha causato un'escalation di blocchi che ha bloccato altri utenti:

DELETE FROM LogMessages WHERE LogDate < '20020102';

Rimuovendo questi record a poche centinaia di volte, è possibile ridurre notevolmente il numero di blocchi accumulati per transazione. Questo approccio impedisce l'escalation dei blocchi. Ad esempio, si esegue la query seguente:

DECLARE @done bit = 0;
WHILE (@done = 0)
BEGIN
    DELETE TOP(1000) FROM LogMessages WHERE LogDate < '20020102';
    IF @@rowcount < 1000 SET @done = 1;
END;

Ottimizzare query e indici per ridurre i blocchi

Ridurre il footprint di blocco della query rendendo la query il più efficiente possibile. Analisi di grandi dimensioni o molte ricerche di segnalibri possono aumentare la probabilità di escalation dei blocchi. Inoltre, aumentano la probabilità di deadlock e influiscono negativamente sulla concorrenza e sulle prestazioni. Dopo aver identificato la query che causa l'escalation dei blocchi, cercare le opportunità di creare nuovi indici o di aggiungere colonne a un indice esistente per rimuovere le analisi di indice o tabella e ottimizzare l'efficienza delle ricerche nell'indice. Esaminare il piano di esecuzione e creare potenzialmente nuovi indici non cluster per migliorare le prestazioni delle query. Per altre informazioni, si veda Architettura e guida per la progettazione degli indici SQL Server.

Uno degli obiettivi di questa ottimizzazione consiste nel rendere l'indice il minor numero possibile di righe per ridurre al minimo il costo delle ricerche di segnalibri (massimizzare la selettività dell'indice per la query). Se SQL Server stima che un operatore logico Bookmark Lookup restituirà molte righe, potrebbe usare una PREFETCH clausola per eseguire la ricerca del segnalibro. Se SQL Server usa PREFETCH per una ricerca di segnalibri, è necessario aumentare il livello di isolamento delle transazioni di una parte della query in "lettura ripetibile" per una parte della query. Ciò significa che l'aspetto di un'istruzione SELECT a un livello di isolamento "read-committed" potrebbe acquisire molte migliaia di blocchi chiave (sia nell'indice cluster che in un indice non cluster). In questo modo una query di questo tipo supera le soglie di escalation dei blocchi. Ciò è particolarmente importante se si rileva che il blocco con escalation è un blocco di tabella condiviso, anche se questi non vengono comunemente visualizzati a livello di isolamento "read-committed" predefinito. Se una clausola Bookmark Lookup WITH PREFETCH causa l'escalation, è consigliabile aggiungere colonne all'indice non cluster visualizzato in Index Seek o l'operatore logico Index Scan sotto l'operatore logico Bookmark Lookup nel piano di query. Potrebbe essere possibile creare un indice di copertura (un indice che include tutte le colonne in una tabella usata nella query) o almeno un indice che copre le colonne utilizzate per i criteri di join o nella clausola WHERE se è poco pratico includere tutti gli elementi nell'elenco "select column".

Un join a ciclo annidato può anche usare PREFETCHe questo causa lo stesso comportamento di blocco.

Mantenere un blocco a livello di tabella compatibile su una connessione separata

L'escalation dei blocchi non può verificarsi se uno SPID diverso contiene attualmente un blocco di tabella incompatibile. L'escalation dei blocchi esegue sempre l'escalation a un blocco di tabella e non a un blocco di pagina. Inoltre, se un tentativo di escalation blocchi non riesce perché un altro SPID contiene un blocco TAB incompatibile, la query che ha tentato l'escalation non si blocca durante l'attesa di un blocco TAB. Continua invece ad acquisire blocchi al livello originale, più granulare (riga, chiave o pagina), effettuando periodicamente ulteriori tentativi di escalation.

Note

Questa tecnica è una soluzione alternativa, non una raccomandazione generale. Richiede la conservazione di una transazione aperta e un blocco IX per la durata che si desidera eliminare l'escalation. Preferisce i miglioramenti delle query e delle transazioni descritti in precedenza.

Pertanto, un metodo per evitare l'escalation blocchi in una determinata tabella consiste nell'acquisire e mantenere attivo un blocco su una connessione diversa non compatibile con il tipo di blocco oggetto dell'escalation. Un blocco IX (esclusivo) a livello di tabella non blocca righe o pagine, ma non è ancora compatibile con un blocco TAB (condiviso) o X (esclusivo). Si supponga, ad esempio, di dover eseguire un processo batch che modifica molte righe nella tabella mytable e che ha causato il blocco a causa dell'escalation dei blocchi. Se questo processo termina sempre in meno di un'ora, è possibile creare un processo Transact-SQL contenente il codice seguente e pianificare l'avvio del nuovo processo per alcuni minuti prima dell'ora di inizio del processo batch:

BEGIN TRAN;
SELECT * FROM mytable WITH (UPDLOCK, HOLDLOCK) WHERE 1 = 0;
WAITFOR DELAY '1:00:00';
COMMIT TRAN;

Questa query acquisisce e mantiene un blocco IX sulla tabella mytable per un'ora. Questo blocco impedisce l'escalation dei blocchi nella tabella durante tale periodo. Questo batch non modifica dati o blocca altre query (a meno che l'altra query non forza un blocco di tabella usando l'hint TABLOCK o se un amministratore ha disabilitato blocchi di pagina o di riga tramite ALTER INDEX).

Assicurarsi che i predicati siano SARGable

Eliminare l'escalation dei blocchi causata dalla mancanza di SARGability, un termine di database relazionale usato per descrivere se una query può usare indici per predicati e colonne di join. Per altre informazioni su SARGability, vedere Inside Design Guide Query Considerations .For more information on SARGability, see Inside Design Guide Query Considerations. Ad esempio, una query abbastanza semplice che non sembra richiedere molte righe, o forse una singola riga, potrebbe comunque terminare l'analisi di un'intera tabella o di un indice. Questa situazione può verificarsi se è presente una funzione o un calcolo a sinistra di una clausola WHERE. Tali esempi che non dispongono di SARGability includono conversioni implicite o esplicite dei tipi di dati, la ISNULL() funzione di sistema, una funzione definita dall'utente con la colonna passata come parametro o un calcolo sulla colonna, ad esempio WHERE CONVERT(INT, column1) = @a o WHERE Column1*Column2 = 5. In questi casi, la query non può CERCARE l'indice esistente, anche se contiene le colonne appropriate, perché tutti i valori di colonna devono essere recuperati per primi e passati alla funzione. Ciò comporta un'analisi dell'intera tabella o dell'indice e comporta l'acquisizione di un numero elevato di blocchi. In tali circostanze, SQL Server può raggiungere la soglia di escalation del numero di blocchi. La soluzione consiste nell'evitare l'uso di funzioni sulle colonne nella clausola WHERE, assicurando condizioni SARGable.

Disabilitare l'escalation dei blocchi

Sebbene sia possibile disabilitare l'escalation dei blocchi in SQL Server, non è consigliabile. Usare invece le strategie di prevenzione descritte nella sezione Impedisci escalation blocchi.

  • Livello tabella: è possibile disabilitare l'escalation dei blocchi a livello di tabella. Vedere ALTER TABLE ... SET (LOCK_ESCALATION = DISABLE). Per determinare la tabella di destinazione, esaminare le query T-SQL. Se non è possibile, usare eventi estesi, abilitare l'evento lock_escalation ed esaminare la colonna object_id . In alternativa, usare l'evento Lock:Escalation ed esaminare la ObjectID2 colonna usando SQL Profiler.
  • Livello di istanza: è possibile disabilitare l'escalation dei blocchi abilitando uno dei flag di traccia 1211 o 1224 o entrambi per l'istanza. Tuttavia, questi flag di traccia disabilitano tutte le escalation dei blocchi a livello globale nell'istanza di SQL Server. L'escalation dei blocchi serve uno scopo utile in SQL Server ottimizzando l'efficienza delle query che altrimenti rallentano l'overhead dell'acquisizione e del rilascio di diverse migliaia di blocchi. L'escalation blocchi è anche utile per ridurre al minimo la memoria necessaria per tenere traccia dei blocchi. La memoria che SQL Server può allocare dinamicamente per le strutture di blocco è limitata. Pertanto, se si disabilita l'escalation dei blocchi e la memoria di blocco aumenta sufficientemente grande, qualsiasi tentativo di allocare blocchi aggiuntivi per qualsiasi query potrebbe non riuscire e generare la voce di errore seguente:

Errore: 1204, gravità: 19, stato: 1
Sql Server non riesce a ottenere una risorsa LOCK al momento. Eseguire di nuovo l'istruzione quando sono presenti meno utenti attivi o chiedere all'amministratore di sistema di controllare la configurazione del blocco e della memoria di SQL Server.

Note

Quando si verifica un errore 1204, interrompe l'elaborazione dell'istruzione corrente e genera un rollback della transazione attiva. Il rollback stesso potrebbe bloccare gli utenti o causare un lungo tempo di ripristino del database se si riavvia il servizio SQL Server.

È possibile aggiungere questi flag di traccia (-T1211 o -T1224) usando Gestione configurazione SQL Server. Per rendere effettivo un nuovo parametro di avvio, è necessario riavviare il servizio SQL Server. Se si esegue la DBCC TRACEON (1211, -1) query o DBCC TRACEON (1224, -1) , il flag di traccia diventa effettivo immediatamente.
Tuttavia, se non si aggiunge -T1211 o -T1224 come parametro di avvio, l'effetto di un DBCC TRACEON comando viene perso quando viene riavviato il servizio SQL Server. L'attivazione del flag di traccia impedisce le escalation di blocchi future, ma non inverte le escalation di blocchi già avvenute in una transazione attiva.

Se si usa un hint di blocco, ad esempio ROWLOCK, questo modifica solo il piano di blocco iniziale. Gli hint di blocco non impediscono l'escalation dei blocchi.

Soglie di escalation blocchi

L'escalation dei blocchi può verificarsi in una delle condizioni seguenti:

  • Soglia di memoria raggiunta: viene raggiunta una soglia di memoria del 40% della memoria di blocco. Quando la memoria di blocco supera il 24% del pool di buffer, è possibile attivare un'escalation dei blocchi. La memoria di blocco è limitata al 60% del pool di buffer visibile. La soglia di escalation dei blocchi viene impostata al 40% della memoria di blocco. Questo è il 40% del 60% del pool di buffer o il 24%. Se la memoria di blocco supera il limite del 60% (molto più probabile se l'escalation dei blocchi è disabilitata), tutti i tentativi di allocare blocchi aggiuntivi hanno esito negativo e 1204 vengono generati errori.

  • Viene raggiunta una soglia di blocco: dopo la verifica della soglia di memoria, viene valutato il numero di blocchi acquisiti nella tabella o nell'indice corrente. Se il numero supera 5.000, viene attivata un'escalation dei blocchi.

Per comprendere quale soglia è stata raggiunta, usare gli eventi estesi, abilitare l'evento lock_escalation ed esaminare le colonne escalated_lock_count e escalation_cause . In alternativa, usare l'evento Lock:Escalation ed esaminare il EventSubClass valore , dove "0 - LOCK_THRESHOLD" indica che l'istruzione ha superato la soglia di blocco e "1 - MEMORY_THRESHOLD" indica che l'istruzione ha superato la soglia di memoria. Esaminare anche le IntegerData colonne e IntegerData2 .

Elementi consigliati

I metodi descritti nella sezione Impedisci escalation blocchi sono opzioni migliori rispetto alla disabilitazione dell'escalation a livello di tabella o istanza. Inoltre, i metodi preventivi producono in genere prestazioni migliori per la query rispetto alla disabilitazione dell'escalation dei blocchi. Microsoft consiglia di abilitare questo flag di traccia solo per attenuare il blocco grave causato dall'escalation dei blocchi mentre vengono esaminate altre opzioni, ad esempio quelle descritte in questo articolo.

Contenuti correlati

  • Last updated on