Escalation dei blocchi (Motore di database)
L'escalation dei blocchi è il processo di conversione di un numero elevato di blocchi specifici in un numero inferiore di blocchi generici, in modo da diminuire l'overhead di sistema aumentando, contemporaneamente, la probabilità di contese di concorrenza.
Man mano che in Motore di database di SQL Server vengono acquisiti blocchi di basso livello, vengono inseriti blocchi preventivi negli oggetti contenenti gli oggetti di basso livello:
Quando si bloccano intervalli di chiavi di indice o righe, tramite Motore di database viene inserito un blocco preventivo nelle pagine contenenti le chiavi o le righe.
Quando si bloccano pagine, tramite Motore di database viene inserito un blocco preventivo negli oggetti di livello superiore contenenti le pagine. Oltre a un blocco preventivo sull'oggetto, sono necessari blocchi preventivi a livello di pagina sui seguenti oggetti:
Pagine al livello foglia di indici non cluster
Pagine di dati di indici cluster
Pagine di dati dell'heap
Tramite Motore di database possono venire bloccate sia la riga che la pagina per la stessa istruzione per ridurre al minimo il numero di blocchi e la probabilità che si renda necessaria l'escalation dei blocchi. Tramite Motore di database possono, ad esempio, venire inseriti blocchi di pagina in un indice non cluster, se è selezionato un numero sufficiente di chiavi contigue nel nodo dell'indice per soddisfare la query, e blocchi di riga nei dati.
Per eseguire l'escalation dei blocchi, in Motore di database viene eseguito un tentativo di modifica del blocco preventivo nella tabella nel blocco completo corrispondente, modificando, ad esempio, un blocco preventivo esclusivo (IX) in un blocco esclusivo (X) o un blocco preventivo condiviso (IS) in un blocco condiviso (S). Se il tentativo di escalation dei blocchi ha esito positivo e viene acquisito un blocco completo a livello di tabella, tutti i blocchi a livello di heap o albero B, a livello di pagina (PAGE) o a livello di riga (RID) attivati dalla transazione nell'heap o nell'indice vengono rilasciati. Se non è possibile acquisire un blocco completo, non viene eseguita alcuna escalation dei blocchi e Motore di database continua ad acquisire blocchi a livello di riga, chiave o pagina.
In Motore di database non viene eseguita l'escalation dei blocchi a livello di riga o di intervallo di chiavi in blocchi a livello di pagina, ma direttamente in blocchi a livello di tabella. Analogamente, l'escalation dei blocchi a livello di pagina viene sempre eseguita in blocchi a livello di tabella. In SQL Server 2008 il blocco delle tabelle partizionate può essere alzato al livello HoBT per la partizione associata anziché al blocco della tabella. Un blocco a livello HoBT non blocca necessariamente gli HoBT allineati per la partizione.
[!NOTA]
I blocchi a livello di HoBT di solito aumentano la concorrenza, ma introducono il potenziale per i deadlock quando le transazioni che bloccano partizioni differenti espandono i loro blocchi esclusivi alle altre partizioni. In rare occasioni, la granularità dei blocchi TABLE potrebbe offrire migliori prestazioni.
Se un tentativo di escalation dei blocchi non riesce per la presenza di blocchi in conflitto mantenuti da transazioni simultanee, il Motore di database ritenterà l'escalation dei blocchi ogni 1.250 blocchi aggiuntivi acquisiti dalla transazione.
Ogni evento di escalation si verifica principalmente a livello di una singola istruzione Transact-SQL. All'avvio dell'evento, in Motore di database viene eseguito un tentativo di escalation di tutti i blocchi di proprietà della transazione corrente in tutte le tabelle a cui fa riferimento l'istruzione attiva, a condizione che vengano soddisfatti i requisiti di soglia di escalation. Se l'evento di escalation viene avviato prima dell'accesso dell'istruzione a una tabella, non viene eseguito alcun tentativo di escalation dei blocchi in tale tabella. Se l'escalation dei blocchi viene eseguita correttamente, verrà eseguita l'escalation di eventuali blocchi acquisiti dalla transazione in un'istruzione precedente e ancora presenti al momento dell'avvio dell'evento se l'istruzione corrente fa riferimento alla tabella e se la tabella è inclusa nell'evento di escalation.
Si consideri, ad esempio, una sessione in cui vengono eseguite le operazioni seguenti:
Avvio di una transazione.
Aggiornamento di TableA. In questo modo, vengono generati blocchi di riga esclusivi in TableA che vengono mantenuti fino al completamento della transazione.
Aggiornamento di TableB. In questo modo, vengono generati blocchi di riga esclusivi in TableB che vengono mantenuti fino al completamento della transazione.
Esecuzione di un'istruzione SELECT per unire in join TableA e TableC. Il piano di esecuzione della query richiede il recupero delle righe da TableA prima che le righe vengano recuperate da TableC.
L'istruzione SELECT attiva l'escalation dei blocchi durante il recupero delle righe da TableA e prima dell'accesso a TableC.
Se l'escalation dei blocchi viene eseguita correttamente, verrà eseguita l'escalation solo dei blocchi attivati dalla sessione in TableA, inclusi i blocchi condivisi dovuti all'istruzione SELECT e i blocchi esclusivi dovuti all'istruzione UPDATE precedente. Mentre per determinare se è necessario eseguire l'escalation dei blocchi vengono conteggiati solo i blocchi acquisiti dalla sessione in TableA per l'istruzione SELECT, dopo che l'escalation ha avuto esito positivo viene eseguita l'escalation di tutti i blocchi attivati dalla sessione in TableA in un blocco esclusivo nella tabella e tutti gli altri blocchi con granularità inferiore, inclusi i blocchi preventivi, in TableA vengono rilasciati.
Non viene eseguito alcun tentativo di escalation dei blocchi in TableB in quanto nell'istruzione SELECT non vi erano riferimenti attivi a TableB. Analogamente, non viene eseguito alcun tentativo di escalation dei blocchi in TableC in quanto al momento dell'escalation non era ancora stato eseguito l'accesso alla tabella.
Soglie di escalation dei blocchi
Viene generata l'escalation dei blocchi se tale escalation non è disattivata nella tabella utilizzando l'opzione ALTER TABLE SET LOCK_ESCALATION e se si verifica una delle seguenti condizioni:
Una sola istruzione di Transact-SQL acquisisce almeno 5.000 blocchi su una sola tabella non partizionata o indice.
Una sola istruzione di Transact-SQL acquisisce almeno 5.000 blocchi in una sola partizione di una tabella partizionata e l'opzione ALTER TABLE SET LOCK_ESCALATION è impostata su AUTO.
Il numero di blocchi in un'istanza di Motore di database supera la soglia di memoria o di configurazione.
Se l'escalation dei blocchi non è possibile per la presenza di conflitti di blocchi, il Motore di database attiva periodicamente l'escalation dei blocchi ogni 1.250 nuovi blocchi acquisiti.
Soglia di escalation per un'istruzione Transact-SQL
L'escalation dei blocchi viene attivata quando un'istruzione di Transact-SQL acquisisce almeno 5.000 blocchi su una sola tabella o indice o, se la tabella è partizionata, su un solo riferimento di una partizione di tabella o di indice. L'escalation dei blocchi non viene ad esempio attivata se un'istruzione acquisisce 3000 blocchi in un indice e 3000 blocchi in un altro indice della stessa tabella. Allo stesso modo, l'escalation dei blocchi non viene attivata se un'istruzione presenta un self join su una tabella e ogni riferimento alla tabella acquisisce solo 3.000 blocchi nella tabella.
L'escalation dei blocchi viene eseguita solo per le tabelle a cui è stato effettuato l'accesso al momento dell'attivazione dell'escalation. Si supponga che un'unica istruzione SELECT corrisponda a un join che accede a tre tabelle nell'ordine TableA, TableB e TableC. L'istruzione acquisisce 3.000 blocchi di riga nell'indice cluster per TableA e almeno 5.000 blocchi di riga nell'indice cluster per TableB ma non ha ancora avuto accesso a TableC. Quando il Motore di database rileva che l'istruzione ha acquisito almeno 5.000 blocchi di riga in TableB, tenta l'escalation di tutti i blocchi posti dalla transazione corrente su TableB. Tenta anche l'escalation di tutti i blocchi posti dalla transazione corrente su TableA, ma poiché il numero di blocchi su TableA è < 5.000, l'escalation di tutti i blocchi non riesce. In TableC non viene eseguito alcun tentativo di escalation in quanto al momento dell'escalation non è ancora stato effettuato l'accesso alla tabella.
Soglia di escalation per un'istanza del Motore di database
Ogni volta che il numero di blocchi è maggiore della soglia di memoria per l'escalation dei blocchi, il Motore di database avvia l'escalation dei blocchi. La soglia di memoria dipende dall'impostazione dell'opzione di configurazione locks:
Se l'opzione locks viene impostata sul valore predefinito 0, la soglia di escalation dei blocchi viene raggiunta quando la memoria utilizzata dagli oggetti blocco rappresenta il 24% della memoria utilizzata da Motore di database, esclusa la memoria AWE. La struttura di dati utilizzata per rappresentare un blocco è lunga approssimativamente 100 byte. Questa soglia è dinamica perché nel Motore di database la memoria viene acquisita e liberata dinamicamente per adattarsi ai diversi carichi di lavoro.
Se per l'opzione locks viene impostato un valore diverso da 0, la soglia di escalation dei blocchi corrisponde al 40%, o meno in caso di elevato utilizzo della memoria, del valore dell'opzione.
In Motore di database qualsiasi istruzione attiva può venire scelta da qualsiasi sessione per l'escalation e ogni 1,250 nuovi blocchi vengono scelte istruzioni per l'escalation a condizione che la memoria dei blocchi utilizzata nell'istanza rimanga al di sopra della soglia.
Escalation di tipi di blocco misti
Quando viene eseguita l'escalation, il blocco selezionato per l'heap o l'indice è sufficientemente forte per soddisfare i requisiti del blocco di livello inferiore più restrittivo.
Si consideri, ad esempio, una sessione in cui vengono eseguite le operazioni seguenti:
Avvio di una transazione.
Aggiornamento di una tabella contenente un indice cluster.
Esecuzione di un'istruzione SELECT che fa riferimento alla stessa tabella.
L'istruzione UPDATE acquisisce i blocchi seguenti:
Blocchi esclusivi (X) nelle righe di dati aggiornate.
Blocchi preventivi esclusivi (IX) nelle pagine di indice cluster contenenti queste righe.
Un blocco IX nell'indice cluster e un altro nella tabella.
L'istruzione SELECT acquisisce i blocchi seguenti:
Blocchi condivisi (S) in tutte le righe di dati lette, a meno che la riga sia già protetta da un blocco X dovuto all'istruzione UPDATE.
Blocchi preventivi condivisi in tutte le pagine di indice non cluster contenenti queste righe, a meno che la pagina non sia già protetta da un blocco IX.
Nessun blocco nell'indice cluster o nella tabella in quanto questi oggetti sono già protetti da blocchi IX.
Se l'istruzione SELECT acquisisce blocchi sufficienti per attivare l'escalation dei blocchi e l'escalation viene eseguita correttamente, il blocco IX nella tabella viene convertito in un blocco X e tutti i blocchi a livello di riga, pagina e indice vengono rilasciati. Sia le operazioni di aggiornamento che quelle di lettura sono protette dal blocco X nella tabella.
Riduzione di blocchi ed escalation
Nella maggior parte dei casi, in Motore di database è possibile ottenere le prestazioni migliori utilizzando le impostazioni predefinite per quanto riguarda operazioni di blocco ed escalation dei blocchi. Se tramite un'istanza di Motore di database vengono generati molti blocchi e si verificano escalation dei blocchi frequenti, ridurre la quantità di blocchi eseguendo le operazioni seguenti:
Utilizzare un livello di isolamento che non generi blocchi condivisi per le operazioni di lettura.
Livello di isolamento READ COMMITTED quando l'opzione di database READ_COMMITTED_SNAPSHOT è impostata su ON.
Livello di isolamento SNAPSHOT.
Livello di isolamento READ UNCOMMITTED. Questo livello può essere utilizzato solo per sistemi che possono funzionare con letture dirty.
[!NOTA]
La modifica del livello di isolamento interessa tutte le tabelle sull'istanza di Motore di database.
Utilizzare gli hint di tabella PAGLOCK o TABLOCK affinché in Motore di database vengano utilizzati blocchi a livello di pagina, heap o indice anziché blocchi di riga. Utilizzando questa opzione, tuttavia, si aumentano i problemi legati a utenti che bloccano altri utenti che tentano di accedere agli stessi dati e pertanto non è consigliabile l'utilizzo in sistemi con molti utenti simultanei.
Per le tabelle partizionate utilizzare l'opzione LOCK_ESCALATION di ALTER TABLE per aumentare i blocchi al livello HOBT invece della tabella o disattivare l'escalation dei blocchi.
È inoltre possibile utilizzare i flag di traccia 1211 e 1224 per disattivare alcune o tutte le escalation dei blocchi. Per ulteriori informazioni, vedere Flag di traccia (Transact-SQL). Inoltre, monitorare l'escalation dei blocchi utilizzando l'evento SQL Server Profiler Lock:Escalation e vedere Utilizzo di SQL Server Profiler.
Vedere anche