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 dinamicamente quando eseguire l'escalation dei blocchi. Quando prende questa decisione, SQL Server considera il numero di blocchi contenuti in una determinata analisi, il numero di blocchi contenuti nell'intera transazione e la memoria usata per i blocchi nel sistema nel suo complesso. In genere, il comportamento predefinito di SQL Server fa sì che l'escalation dei blocchi si verifichi solo in quei momenti in cui migliorerebbe le prestazioni o quando è necessario ridurre la memoria di blocco del sistema eccessiva a un livello più ragionevole. Tuttavia, alcune progettazioni di applicazioni o query potrebbero attivare l'escalation dei blocchi in un momento in cui questa azione non è auspicabile e il blocco della tabella escalation potrebbe bloccare altri utenti. Questo articolo illustra come determinare se l'escalation dei blocchi causa il blocco e come gestire l'escalation dei 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 vicino al 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 un'escalation dei blocchi, verificare che il blocco della tabella con escalation blocchi altri utenti.

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

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 di 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 di risoluzione dei problemi in INF: Understanding and resolving SQL Server blocking problems.

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, tra cui le strategie seguenti:

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

  DELETE FROM LogMessages WHERE LogDate < '20020102';
  

  Rimuovendo questi record alcune centinaia alla volta, è possibile ridurre notevolmente il numero di blocchi che si accumulano per ogni transazione. Ciò impedirà 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 le probabilità di escalation dei blocchi. Inoltre, aumentano le 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 di indice. Esaminare il piano di esecuzione e creare potenzialmente nuovi indici non cluster per migliorare le prestazioni delle query. Per altre informazioni, vedere SQL Server Index Architecture and Design Guide.For more information, see SQL Server Index Architecture and Design Guide.

  Uno degli obiettivi di questa ottimizzazione è fare in modo che l'indice cerchi di restituire il minor numero possibile di righe per ridurre al minimo il costo delle ricerche di segnalibri (ottimizzare la selettività dell'indice per la query). Se SQL Server stima che un operatore logico Ricerca segnalibro restituirà molte righe, potrebbe usare una PREFETCH clausola per eseguire la ricerca dei segnalibri. Se SQL Server usa PREFETCH per una ricerca di segnalibri, deve aumentare il livello di isolamento della transazione di una parte della query in "lettura ripetibile" per una parte della query. Ciò significa che ciò che può sembrare un'istruzione SELECT a un livello di isolamento "read-committed" potrebbe acquisire molte migliaia di blocchi di chiave (sia nell'indice cluster che in un indice non cluster). Ciò può causare il superamento delle soglie di escalation dei blocchi da parte di una query di questo tipo. Ciò è particolarmente importante se si rileva che il blocco escalation è un blocco di tabella condiviso, anche se questi non sono comunemente visualizzati al livello di isolamento predefinito "read-committed". Se una clausola Bookmark Lookup WITH PREFETCH causa l'escalation, provare ad aggiungere colonne all'indice non cluster visualizzato in Index Seek o all'operatore logico Index Scan sotto l'operatore logico Ricerca segnalibro nel piano di query. Potrebbe essere possibile creare un indice di copertura (un indice che include tutte le colonne di una tabella usate nella query) o almeno un indice che copra le colonne usate per i criteri di join o nella clausola WHERE se non è pratico includere tutti gli elementi nell'elenco "select column".

  Anche un join a ciclo annidato può usare PREFETCHe ciò 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 viene sempre inoltrata a un blocco di tabella e non a un blocco di pagina. Inoltre, se un tentativo di escalation dei blocchi ha esito negativo 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. Al contrario, continua ad acquisire blocchi al livello originale più granulare (riga, chiave o pagina), effettuando periodicamente ulteriori tentativi di escalation. Pertanto, un metodo per impedire l'escalation dei blocchi in una determinata tabella consiste nell'acquisire e mantenere un blocco su una connessione diversa non compatibile con il tipo di blocco escalation. Un blocco IX (intent exclusive) a livello di tabella non blocca righe o pagine, ma non è ancora compatibile con un blocco TAB S (condiviso) o X (esclusivo) escalation. Si supponga, ad esempio, di eseguire un processo batch che modifichi molte righe nella tabella mytable e che abbia causato il blocco a causa dell'escalation dei blocchi. Se il 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 diversi minuti prima dell'ora di inizio del processo batch:

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

  Questa query acquisisce e mantiene un blocco IX in mytable per un'ora. Ciò impedisce l'escalation dei blocchi nella tabella durante tale periodo di tempo. 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 i blocchi di pagina o di riga usando 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 join. Per altre informazioni sulla sargability, vedere Considerazioni sulle query all'interno della guida alla progettazione. Ad esempio, una query abbastanza semplice che non sembra richiedere molte righe, o forse una singola riga, può comunque finire per analizzare un'intera tabella/indice. Ciò può verificarsi se è presente una funzione o un calcolo nel lato sinistro di una clausola WHERE. Tali esempi che non hanno sargability includono conversioni implicite o esplicite del tipo di dati, la funzione di sistema ISNULL(), una funzione definita dall'utente con la colonna passata come parametro o un calcolo sulla colonna, ad WHERE CONVERT(INT, column1) = @a esempio 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 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 rispetto alle colonne nella clausola WHERE, garantendo condizioni sargable.

Disabilitare l'escalation dei blocchi

Anche se è 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 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 è utile per SQL Server ottimizzando l'efficienza delle query altrimenti rallentate dal sovraccarico dovuto all'acquisizione e al rilascio di diverse migliaia di blocchi. L'escalation dei blocchi consente anche di 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 del blocco aumenta sufficientemente, 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
La SQL Server non è in grado di ottenere una risorsa LOCK in questo momento. Eseguire di nuovo l'istruzione quando sono presenti meno utenti attivi o chiedere all'amministratore di sistema di controllare la configurazione SQL Server blocco e memoria.

È 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 immediatamente effettivo.
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 eventuali escalation dei blocchi future, ma non inverte le escalation dei blocchi che si sono già verificate 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 dei blocchi

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

• Soglia di memoria raggiunta: viene raggiunta una soglia di memoria pari al 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 è impostata sul 40% della memoria di blocco. Si tratta del 40% del 60% del pool di buffer o del 24%. Se la memoria di blocco supera il limite del 60% (è molto più probabile che l'escalation dei blocchi sia disabilitata), tutti i tentativi di allocare blocchi aggiuntivi hanno esito negativo e 1204 vengono generati errori.

• Viene raggiunta una soglia di blocco : dopo aver verificato la 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 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 .

Suggerimenti

I metodi illustrati nella sezione Impedisci escalation blocchi sono opzioni migliori rispetto alla disabilitazione dell'escalation a livello di tabella o di istanza. Inoltre, i metodi di prevenzione 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 i blocchi gravi causati dall'escalation dei blocchi mentre altre opzioni, ad esempio quelle descritte in questo articolo, sono in fase di analisi.

Vedere anche

• Comprendere e risolvere i problemi di blocco di SQL Server
• Risolvere i problemi relativi alle query a esecuzione lenta in SQL Server