Carissimi lettori,
Eccoci nuovamente qui per un articolo particolamente intenso.
Con oggi terminiamo questa serie di articoli legati alla problematica detta last page insert contention issue allacciandoci ad un altro arogmento che vi prometto sarà ugualmente se non più interessante.
Come saprete già , è uscita da pochi giorni la versione Release Candidate 1 di SQL Server 2019.
E' stata anticipata da ben 8 Common Technology Preview (CTP) che ci hanno mostrato un assaggio delle nuove feature che la nuova versione ci porterà .
Iniziamo quindi ad analizzare le novità che ci porterà SQL Server 2019.
Una di esse è proprio dedicata alla riduzione del fenomeno che stiamo analizzando in questa ultima serie di articoli.
Questa nuova feature è detta OPTIMIZE FOR SEQUENTIAL KEY ed è in realtà una clausola che è possibile specificare durante la creazione di un indice.
Proviamola!
OPTIMIZE FOR SEQUENTIAL KEY Feature
Impostiamo per prima cosa il livello di compatibilità a 150 ovvero quello nativo di SQL Server 2019.
Poi, prima di attivare la clausola, eseguiamo il nostro benchmark tramite l' RML come abbiamo già visto negli articoli precedenti (https://server.hoit.asia/2019/09/the-last-page-insert-latch-contention_10.html)
Ecco qui i risultati:
Adesso droppiamo l’indice clustered e lo reinseriamo usando questa sintassi:
CREATE CLUSTERED INDEX [IDX_ARTICO_CLUSTERED_ID] ON [dbo].[ARTICO]
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON,
OPTIMIZE_FOR_SEQUENTIAL_KEY = ON) ON [PRIMARY]
Notate che abbiamo specificato la clausola OPTIMIZE FOR SEQUENTIAL KEY=ON
Rieseguiamo adesso il nostro benchmark et voilà !
Bene, ditemi subito che cosa notate.
Il tempo di esecuzione si è ridotto vero? Bravi!
E poi?
Attenzione attenzione: non sono più presenti i wait type LATCH_SH, LATCH_EX e PAGELATCH_EX.
Al loro posto è comparso un nuovo tipo di wait state che si chiama BTREE_INSERT_FLOW_DISPATCHER.
Molto bene! Abbiamo quindi provato che questa nuova clausola sortisce degli effetti.
Effetti che sono però tutti da provare.
Effetti che sono però tutti da provare.
Tenete conto infatti che la stessa Microsoft ne consiglia l'attivazione solamente nel caso in cui esiste veramente un problema di last page insert latch contention.
In caso contrario potrebbe portare addiritura ad una perdita di prestazioni.
Quindi il consiglio è verificate se effettivamente avete questo tipo di problema e poi, solo dopo, effettuate un test con e senza questa clausola attivata!
Adesso dopo ben tre articoli sapete tutto sulla last page insert latch contention issue.
L'articolo di oggi però non termina qui!
Come dicevo in apertura iniziamo a parlare di SQL Server 2019.
Come al solito lo facciamo andando nel dettaglio del funzionamento della nuova clausola OPTIMIZE FOR SEQUENTIAL KEY
Siete pronti?
Via!
Via!
SQL Server 2019 RC1 e la clausola OPTIMIZE FOR SEQUENTIAL KEY
Come dicevamo in apertura SQL Server 2019 è ormai prossimo al rilascio.
E' infatti di qualche giorno fa l'uscita della Release Candidate 1.
Prima di essa Microsoft ha messo a disposizione del pubblico ben 8 Community Tecnology Preview (CTP) svelando così poco alla volta nuove funzionalità .
Una di queste è appunto la clausola OPTIMIZE FOR SEQUENTIAL KEY che abbiamo sfruttato nella prima parte di questo articolo (introdotta in realtà a partire dalla CTP 3.1)
Noi però vogliamo approfondire e quindi la domanda che ci facciamo è sempre la stessa!
Ma come funziona internamente la clausola OPTIMIZE FOR SEQUENTIAL KEY?
Approfondiamo!
Riassumo quello che si siamo detti nei precedenti articoli:
In SQL Server gli indici sono memorizzati come B-TREES ovvero alberi binari ordinati per indice chiave.
Quando inseriamo delle righe in una tabella che ha un indice cluster ed il valore della chiave è un progressivo (l'identità ) tutte queste righe andranno a finire nell'ultima pagina del B-TREE.
L'aumento della concorrenza (più utenti che inseriscono contemporaneamente) fa si che la contesa di questa pagina di memoria aumenti.
Alla fine è proprio questo fattore a limitare la scalabilità .
Diciamo che Microsoft tramite questa clausola non ha voluto apportare cambiamenti significativi al database engine, piuttosto si è concentrata nel capire la logica che sta dietro al meccanismo della contesa delle pagine di memoria.
Prometto, cercherò di essere più chiaro possibile!
Quello che abbiamo già capito è che il fenomeno della contesa dei pagelatch introduce una latenza nella applicazione. Ma ciò è inevitabile dacchè alla pagina di memoria può accedere solamente un thread worker alla volta.
Quello che però in realtà riduce la scalabilità dell'applicazione non è tanto la contesa dei latch bensì un altro fenomeno che Microsoft chiama LATCH CONVOY.
Potremmo tradurre questo termine con "convoglio di latch" nel senso di tanti Latch che si muovono uno dietro l'altro accodati.
Ora immedesimiamoci in questo esempio.
Immaginandoci una fila di Latch che si muovono accodati uno dietro l'altro. Pensiamo che questa coda di Latch come noi a bordo della nostra autovettura.
Come in autostrada trafficata d'agosto tutto fila liscio finchè il traffico (e quindi tutti noi ) ci muoviamo procedendo alla stessa velocità .
Supponiamo però di incontrare dei lavori in corso per cui le nostre due belle corsie diventano improvvisamente una sola.
Accadono due cose:
- Tutte le autovetture saranno costrette a percorrere la corsia rimasta ad una velocità inferiore.
- Prima della strettoia a causa del traffico si formerà una coda che aumenterà finchè la strettoia non sarà rimossa.
Questo è il motivo per cui una strettoia causa un notevole rallentamento.
Per analogia, allo stesso modo, tornando al nostro SQL Server ecco perchè un collo di bottiglia causa un calo vistoso del throughput.
Ma cosa potrebbe essere a rallentare l'esecuzione di uno dei nostri thread?
Tipicamente nel nostro caso il rallentamento è dovuto ad un fenomeno chiamato Page Split.
Il Page Split avviene quando una pagina di memoria arriva a riempirsi totalmente ed una nuova deve essere aggiunta all'indice.
Per di più l'aggiunta di una nuova pagina in memoria richiede un latch di tipo esclusivo (EX) sulla pagina padre e ciò può causare a sua volta una richiesta di Latch a quel livello.
Ed ecco qui l'idea che si cela dietro la creazione della clausola OPTIMIZE_FOR_SEQUENTIAL_KEY:
Per di più l'aggiunta di una nuova pagina in memoria richiede un latch di tipo esclusivo (EX) sulla pagina padre e ciò può causare a sua volta una richiesta di Latch a quel livello.
Ed ecco qui l'idea che si cela dietro la creazione della clausola OPTIMIZE_FOR_SEQUENTIAL_KEY:
- Controllare la velocità alla quale i nuovi thread sono abilitati a richiedere il latch
- Favorire i thread che possono mantenere alto il througput.
Di fatto, limitare l'esecuzione di un thread fa si che, pu avendo questo thread singolamente una latenza più elevata, si possa ridurre o evitare il fenomeno di accodamento dei thread (convoy).
In questo modo viene permesso a tutti gli altri thread di procedere procedere più speditamente.
Allo stesso modo favorire i thread che possono completare la propria esecuzione nel quanto di tempo che lo scheduler assegna loro (4 ms) aumenta il througput.
All'atto pratico come abbiamo visto dal nostro test non vedremo calare le attese di tipo PAGELATCH, bensì vedremo un nuovo tipo di wait type detto BTREE_INSERT_FLOW_CONTROL:
Va detto perè che nel caso non siate soggetti al fenomeno dell'accodamento dei thread (Convoy) non vedrete grossi benefici.
Addirittura potreste avere un piccolo degrado delle prestazioni dovuto alla logica aggiuntiva del controllo del flusso.
Diciamo che possiamo considerare questa clausola come una carta in più da giocarsi nel caso in cui ci dovessimo trovare nelle condizione di esperimentare una latch contention particolamente elevata.
Per oggi è davvero tutto!
Spero vi sia piaciuta questa serie di articoli dedicata alla Last page insert contention come spero vi piacerà la serie di articoli dedicati alle novità che ci porterà il nuovo SQL Server 2019.
Come sempre vi ringrazio per i complimenti e per le numerose visualizzazioni.
Continuate così ... mi raccomando!
Allo stesso modo favorire i thread che possono completare la propria esecuzione nel quanto di tempo che lo scheduler assegna loro (4 ms) aumenta il througput.
All'atto pratico come abbiamo visto dal nostro test non vedremo calare le attese di tipo PAGELATCH, bensì vedremo un nuovo tipo di wait type detto BTREE_INSERT_FLOW_CONTROL:
Va detto perè che nel caso non siate soggetti al fenomeno dell'accodamento dei thread (Convoy) non vedrete grossi benefici.
Addirittura potreste avere un piccolo degrado delle prestazioni dovuto alla logica aggiuntiva del controllo del flusso.
Diciamo che possiamo considerare questa clausola come una carta in più da giocarsi nel caso in cui ci dovessimo trovare nelle condizione di esperimentare una latch contention particolamente elevata.
Per oggi è davvero tutto!
Spero vi sia piaciuta questa serie di articoli dedicata alla Last page insert contention come spero vi piacerà la serie di articoli dedicati alle novità che ci porterà il nuovo SQL Server 2019.
Come sempre vi ringrazio per i complimenti e per le numerose visualizzazioni.
Continuate così ... mi raccomando!
Grazie,
Luca Biondi @ SQLServerPerformance blog!