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

Riepilogo

L'escalation dei blocchi è il processo di conversione di molti blocchi con granularità fine (ad esempio blocchi di riga o di pagina) in blocchi di tabella. Microsoft SQL Server determina in modo dinamico quando eseguire l'escalation dei blocchi. Quando prende questa decisione, SQL Server considera il numero di blocchi contenuti in una particolare analisi, il numero di blocchi mantenuti dall'intera transazione e la memoria usata per i blocchi nel sistema nel suo complesso. In genere, il comportamento predefinito di SQL Server causa l'escalation dei blocchi solo in quei momenti in cui migliora le prestazioni o quando è necessario ridurre la memoria di blocco di sistema eccessiva a un livello più ragionevole. Tuttavia, alcune progettazioni di applicazioni o query potrebbero attivare l'escalation dei blocchi alla volta in cui questa azione non è auspicabile e il blocco di tabella inoltrato potrebbe bloccare altri utenti. Questo articolo illustra come determinare se l'escalation dei blocchi causa il blocco e come gestire l'escalation di blocchi indesiderata.

Versione originale del prodotto: SQL Server
Numero KB originale: 323630

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 INF: Informazioni e risoluzione dei problemi di blocco di SQL Server.

Se il blocco che blocca altri utenti è diverso da un blocco TAB (a livello di tabella) con una modalità di blocco S (condivisa) o X (esclusiva), 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), questo non è causato dall'escalation dei blocchi. Se i problemi di blocco non sono causati dall'escalation dei blocchi, vedere la procedura inF: Informazioni e risoluzione dei problemi di blocco di SQL Server.

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. Esistono diversi metodi per raggiungere questo obiettivo, incluse le strategie seguenti:

• Suddividere le operazioni batch di grandi dimensioni in diverse operazioni più piccole. Ad esempio, si esegue la query seguente per rimuovere 100.000 record precedenti da una tabella di controllo e quindi determinare 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. In questo modo si 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;
  

• 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, questi aumentano la probabilità di deadlock e influiscono negativamente sulla concorrenza e sulle prestazioni. Dopo aver identificato che 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.

• 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. 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 (preventivo esclusivo) a livello di tabella non blocca alcuna riga o pagina, ma è comunque non compatibile con un blocco TAB S (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. In questo modo si 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).

• 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/indice. Ciò 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 funzione di sistema ISNULL(), 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.

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

Vedi anche

• Informazioni e risoluzione dei problemi di blocco di SQL Server
• Risolvere i problemi relativi alle query a esecuzione lenta in SQL Server