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

Riepilogo

L'escalation è il processo di conversione molti blocchi specifici (ad esempio i blocchi di riga o pagina) in blocchi di tabella. Microsoft SQL Server determina dinamicamente quando eseguire l'escalation dei blocchi. Quando si effettua questa decisione, SQL Server prende in considerazione il numero di blocchi che vengono mantenuti in una scansione particolare, il numero di blocchi che vengono mantenuti per l'intera transazione e la memoria utilizzata per i blocchi del sistema nel suo complesso. In genere, il comportamento predefinito del SQL Server comporta escalation dei blocchi che si verificano solo nei punti in cui possono migliorare le prestazioni o quando è necessario ridurre l'eccessivo di memoria di blocco a un livello accettabile. Tuttavia, alcuni schemi di applicazione o di una query potrebbero attivare l'escalation dei blocchi in un momento non è auspicabile e riassegnate table lock potrebbe impedire ad altri utenti. In questo articolo viene descritto come determinare se l'escalation è causato dal blocco e come affrontare l'escalation dei blocchi indesiderati.

Ulteriori informazioni

Come determinare se l'Escalation è causato dal blocco

L'escalation non causa la maggior parte dei problemi di blocco. Per determinare se l'escalation si verifica al momento quando si verificano problemi di blocco, avviare una traccia SQL Profiler che include l'evento Lock: Escalation . Se non è presente alcun evento Lock: Escalation , l'escalation non viene eseguita sul server e le informazioni contenute in questo articolo non si applicano alla propria situazione.

Se si verifica l'escalation, verificare che il blocco della tabella riassegnate blocca altri utenti.

Per ulteriori informazioni su come identificare il blocco popup di testa e come identificare la risorsa di blocco intestata il blocco di testa che blocca altri processo server (SPID), ID fare clic sul numero dell'articolo della Microsoft Knowledge Base riportato di seguito:

224453 la comprensione e la risoluzione dei problemi di blocco 2000 o di SQL Server 7.0

Se il blocco che impedisce ad altri utenti è diverso da un blocco di scheda (a livello di tabella) con una modalità di blocco di S (condivisa) o X (esclusivo), l'escalation è il problema. In particolare, se il blocco scheda un blocco preventivo (ad esempio una modalità di blocco di IS, IU o IX), questo è il risultato dell'escalation. Se i problemi di blocco non sono causati dall'escalation, vedere l'articolo Q224453 i problemi.

Come impedire l'Escalation dei blocchi

Il modo più semplice e sicuro per impedire l'escalation dei blocchi è di mantenere le transazioni brevi e per ridurre il footprint di blocco delle query costose, in modo che non vengano superate le soglie di escalation del blocco. Esistono diversi modi per ottenere questo obiettivo, molti dei quali sono elencati:
  • Suddividere le operazioni batch di grandi dimensioni in diverse operazioni di dimensioni inferiori. Ad esempio, se si esegue la query seguente per rimuovere i vecchi record di diverse centinaia di migliaia di una tabella di controllo e quindi è rilevato che la causa di un'escalation che altri utenti bloccati:
    DELETE FROM LogMessages WHERE LogDate < '2/1/2002'
    Rimuovendo questi record poche centinaia alla volta, è possibile ridurre notevolmente il numero di blocchi che vengono accumulati per ogni transazione e impedire l'escalation dei blocchi. Per esempio:
    SET ROWCOUNT 500delete_more:
    DELETE FROM LogMessages WHERE LogDate < '2/1/2002'
    IF @@ROWCOUNT > 0 GOTO delete_more
    SET ROWCOUNT 0
  • Eseguendo la query più efficienti possibile, ridurre l'impronta di blocco della query. Scansioni di grandi dimensioni o di un gran numero di ricerche segnalibro può aumentare le probabilità di escalation dei blocchi; Inoltre, le probabilità di deadlock e in genere influisce negativamente sulle prestazioni e della concorrenza. Dopo aver individuato la query che fa sì che l'escalation, mira a cercare opportunità per creare nuovi indici o aggiungere colonne a un indice esistente per rimuovere le scansioni di tabella o indice e per massimizzare l'efficienza dell'indice. Si consiglia di incollare la query in una finestra di query Query Analyzer per eseguire un'analisi automatica indice su di esso. A tale scopo, dal menu Query , fare clic su Ottimizzazione guidata indici in SQL Server 2000 o fare clic su Esegui analisi indici in SQL Server 7.0.

    Uno degli obiettivi di questa ottimizzazione è per rendere l'indice è possibile restituire il minor numero di righe possibile per ridurre al minimo il costo di ricerche segnalibro (Ingrandisci la selettività dell'indice per una determinata query). Se SQL Server stima che un operatore logico segnalibro ricerca può restituire più righe, è possibile utilizzare una PRELETTURA per eseguire la ricerca di segnalibro. Se SQL Server utilizza la PRELETTURA per la ricerca di un segnalibro, è necessario aumentare il livello di isolamento delle transazioni di una parte della query di lettura ripetibile per una parte della query. Ciò significa che cosa potrebbe essere simile a un'istruzione SELECT di un livello di isolamento read committed possono acquisire molte migliaia di blocchi chiave (in corrispondenza dell'indice cluster sia un indice non cluster), che possono provocare tale query superano le soglie di escalation del blocco. Ciò è particolarmente importante se il blocco di escalation è un blocco di tabella condiviso, che, tuttavia, non verifica in genere al livello di isolamento read committed predefinito. Se una clausola segnalibro ricerca WITH PREFETCH provoca l'escalation, è possibile aggiungere colonne aggiuntive per l'indice non cluster che appare nell'indice di ricerca o l'operatore logico Index Scan seguito l'operatore logico ricerca segnalibro nel piano della query. Potrebbe essere possibile creare un indice di copertura (indice che include tutte le colonne in una tabella in cui sono state utilizzate nella query), o almeno un indice che include le colonne che sono state utilizzate i criteri di join o nella clausola WHERE se tutti gli elementi inclusi nell'elenco Seleziona colonna è poco pratico.

    Un join di Loop nidificati può inoltre utilizzare PREFETCH, e in questo modo, il funzionamento del blocco stesso.

    Per ulteriori informazioni, fare clic sul seguente numero di articolo per visualizzare l'articolo della Microsoft Knowledge Base:

    Join di loop Nested 260652 che utilizza un "SEGNALIBRO ricerca... CON la PRELETTURA"che può contenere più blocchi

  • Escalation dei blocchi non può verificarsi se uno SPID diverso attualmente detiene un blocco di tabella non compatibile. Escalation dei blocchi sempre viene inoltrata a un blocco di tabella e mai per blocchi di pagina. Inoltre, se un tentativo di escalation del blocco non riesce perché un'altra SPID gestisce un blocco scheda incompatibile, la query che ha tentato di riassegnazione non si blocca in attesa di un blocco di scheda. Al contrario, continua ad acquisire blocchi al livello più granulare, originale (riga, chiave o pagina), effettua periodicamente escalation ulteriori tentativi. Pertanto, un metodo per impedire l'escalation dei blocchi su una determinata tabella è di acquisire e di un blocco su un'altra connessione che non è compatibile con il tipo di blocco riassegnate. Un blocco IX (preventivi esclusivi) livello di tabella non bloccare righe o pagine, ma è ancora non è compatibile con una S riassegnata (condivisa) o X blocco scheda (esclusivo). Si supponga, ad esempio, che è necessario eseguire un processo batch che modifica un numero elevato di righe della tabella mytable e che ha causato il blocco che si verifica a causa di escalation dei blocchi. Questo processo viene sempre completata in meno di un'ora, si potrebbe creare un processo Transact-SQL che contiene il codice riportato di seguito e pianificare il processo di nuovo per avviare alcuni minuti prima dell'ora di inizio del processo batch:
    BEGIN TRANSELECT * FROM mytable (UPDLOCK, HOLDLOCK) WHERE 1=0
    WAITFOR DELAY '1:00:00'
    COMMIT TRAN
    Questa query acquisisce e gestisce un blocco IX su mytable per un'ora, che impedisce l'escalation dei blocchi sulla tabella durante tale periodo. Questo batch non modificano alcun dato o bloccare altre query (a meno che l'altra query impone un blocco di tabella con l'hint TABLOCK o se l'amministratore ha disattivato i blocchi di riga o pagina utilizzando un sp_indexoption stored procedure).
Inoltre, è possibile disabilitare l'escalation abilitando il flag di traccia 1211. Tuttavia, questo flag di traccia disabilita tutte escalation dei blocchi a livello globale nell'istanza di SQL Server. L'escalation svolge una funzione molto utile in SQL Server da aumentarne l'efficienza delle query che in caso contrario rallentato dovuto al sovraccarico di acquisizione e il rilascio di diverse migliaia di blocchi. L'escalation consente inoltre di ridurre al minimo la quantità di memoria richiesta per tenere traccia dei blocchi. La memoria che SQL Server può allocare dinamicamente per strutture di blocco è limitata, in modo che se si disattiva escalation dei blocchi e il blocco di memoria aumenta abbastanza grande, potrebbe non riuscire tenta di allocare ulteriori blocchi per le query e si verifica il seguente errore:

Errore: 1204, gravità: 19, stato: 1
Di SQL Server non può ottenere una risorsa LOCK in questo momento. Rieseguire l'istruzione quando vi sono un numero di utenti attivo o chiedere all'amministratore di sistema per verificare la configurazione di memoria e di blocco di SQL Server.
Nota: Quando si verifica un errore di "1204", interrompe l'elaborazione dell'istruzione corrente e fa sì che un rollback della transazione attiva. Il rollback stesso può impedire agli utenti o comportare un tempo di ripristino di database lunghe se si riavvia il servizio SQL Server.

Utilizzo di un hint di blocco come blocchi di riga, modifica solo il piano di blocco iniziale. Gli hint di blocco non impediscono l'escalation dei blocchi.


Gli altri metodi per prevenire l'escalation dei blocchi che vengono trattati in questo articolo sono migliori opzioni di attivazione del flag di traccia. Inoltre, gli altri metodi generalmente di ottenere prestazioni migliori per la query alla disattivazione di escalation dei blocchi per l'intera istanza. Si consiglia di abilitare questo flag di traccia solo attenuare il blocco gravi causati dall'escalation durante altre opzioni, ad esempio descritti in precedenza in questo articolo vengono analizzati. Per attivare un flag di traccia in modo che sia acceso quando SQL Server viene avviato, aggiungerlo come un parametro di avvio del server.

Per aggiungere un parametro di avvio del server, mouse del server in SQL Enterprise Manager, fare clic su proprietàe quindi nella scheda Generale , fare clic su Parametri di avvioe quindi aggiungere il seguente parametro (esattamente come indicato):
-T1211
È necessario passare il servizio SQL Server per un nuovo parametro di avvio abbiano effetto. Se si esegue la seguente query in Query Analyzer il flag di traccia ha effetto immediato:
DBCC TRACEON (1211, -1)
Tuttavia, se si aggiunge il -T1211 parametro di avvio, l'effetto di un comando traceon viene perduto quando passa il servizio SQL Server. Attivare il flag di traccia impedisce qualsiasi escalation future, ma senza invertire qualsiasi escalation che hanno già una transazione attiva.
Proprietà

ID articolo: 323630 - Ultima revisione: 30 gen 2017 - Revisione: 1

Feedback