Ben ritrovati!
Oggi continuiamo il discorso relativo alla problematica del “Last page insert contention” di cui abbiamo discusso qui: The last page insert latch contention issue. Cos'è?
Nello scorso articolo avevamo visto nel dettaglio di che cosa di tratta e ci siamo lasciati con la seguente domanda: è possibile evitare questo fenomeno o almeno ridurne l’entità?
Come dicevamo che tale problematica nasce dalla concomitanza dei due fattori: L’indice Clustered su una colonna di una tabella e la presenza della identità sulla stessa.
Ma come fare per risolvere oppure attenuare questo fenomeno?
Ci sono vari metodi prima però lasciatemi dire due parole sui test che eseguiremo.
Ottenere una baseline…
Al fine di ottenere dei valori di riferimento eseguiremo tramite il tool RML parallelamente la seguente stored procedure:
CREATE PROCEDURE SP_INSART AS
BEGIN
DECLARE @VALID INT = DATEPART(ms,getdate())
INSERT INTO ART(HASHVAL, CODICE,COLORE,POSIZIONE)
SELECT HASHVAL,CODICE,COLORE,POSIZIONE FROM TEST_TEMPDB..ARTICOLI
WHERE ID = @VALID
END
Ho aggiunto una condizione di WHERE in modo tale che ad ogni esecuzione venga letta una riga diversa della tabella ART.
Tramite la funzione DATEPART estraggo dall’orario corrente i millisecondi e quindi otterrò sempre un valore compreso tra 0 e 999.
La tabella ART deve avere più di 1000 righe.
Cancelliamo le statistiche ed il piano di esecuzione con i comandi:
DBCC FREEPROCCACHE WITH NO_INFOMSGS;
DBCC SQLPERF("sys.dm_os_wait_stats",CLEAR);
Infine eseguiamo il tool RML con questi parametri:
OStress.exe -iC:\Scambio\Test_Tabelle_notemp.SQL -Usa -Pxxxxx -SDESKTOP-JJP3TOU\MSSQLSERVER01 -dTEST_TEMPDB -oc:\scambio -n400 -r200
Otteniamo questi valori:
Ma ora partiamo! ...iniziamo a testare qualche metodo.
Un indice clustered composto da più colonne
Diciamo che con la struttura a cui facciamo riferimento togliere l’indice Clustered non è sicuramente proficuo, possiamo però creare un indice clustered su più di una colonna!
Nel nostro esempio dove abbiamo un indice Clustered sul campo ID (identità)
Proviamo ora di crearlo sui campi ID + CODICE
Per farlo lo eliminiamo e lo ricreiamo tramite questa sintassi:
CREATE CLUSTERED INDEX [IDX_ARTICOLI_ID] ON [dbo].[ARTICOLI] ( [ID] ASC, [CODICE] ASC )
Poi nel campo CODICE ci mettiamo un valore UNIVOCO GUID che generiamo tramite il comando T-SQL NEWID()
Il campo ha questo aspetto:
Così facendo dovremmo limitare il problema dell’inserimento dati in ultima pagina.
Fiduciosi, eseguiamo poi il nostro carico di lavoro:
Cosa notate?
Il tempo di esecuzione è inferiore anche se in misura non significativa.
Globalmente la somma del campo Wait_Sec per i tre tipi di latch è diminuita anche se Il wait type PAGELATCH_EX è cresciuto.
Ma perché questa prima soluzione non porta benefici consistenti?
Occorre considerare che l’indice clustered è diventato di dimensioni maggiori e quindi più oneroso da gestire.
Nella nostra tabella il campo ID è intero ed occupa quindi 4 Byte, il campo CODICE è invece un VARCHAR da ben 80 caratteri.
Benissimo direte voi,
Ma se il problema è che l’indice Cluster è troppo grande proviamo allora di sostituirlo con qualcosa che abbia dimensioni minori.
Supponiamo ad esempio di aggiungere una ulteriore colonna che chiamiamo HASHVAL che riempiremo con valori generati da una funzione HASH.
In questo modo diffondiamo le operazioni di inserimento in modo uniforme.
Scriviamo quindi:
ALTER TABLE ARTICOLI ADD HASHVAL INT
UPDATE ARTICOLI SET HASHVAL = CAST( ID % 8 AS INTEGER)
Adottiamo adesso un indice Clustered sui campi ID + HASHVAL:
Testiamo…
Il tempo di esecuzione è ulteriormente calato seppure in misura minima così come sono diminuiti i tre wait type LATCH.
Probabilmente però l’overhead introdotto dal fatto di aver costruito un indice clustered su più colonne è il fattore limitante!
Occorrerà quindi seguire un'altra strada.
Al posto dell’identità!
Se non modifichiamo l’indice clustered possiamo agire sulla identità.
Potremmo ad esempio eliminarla.
Così facendo sarà la nostra applicazione a dover generare un valore univoco per il campo ID.
L’obbiettivo oltre a generare un campo univoco è quello di generare un valore che si distribuisca in modo uniforme.
Potremmo ad esempio usare una funzione di HASH come la GUID ma abbiamo visto che un indice clustered deve essere di dimensioni il più ridotte possibili.
Potremmo invece generare noi il campo ID tramite una funzione “INVERTI BIT”
L’idea è quella di generare un numero, non progressivo per evitare di dover inserire i dati alla pagina della pagina e come farlo?
Prendendo un numero ed invertendo l’ordine dei bit di cui è composto.
Ad esempio con un numero a 8 bit:
Numero Valore binario Valore con bit invertiti Valore ottenuto
1 0000001
2 0000010
3 0000011
4 0000100
5 0000101
La funzione che inverte l’ordine è questa:
CREATE FUNCTION INVERTI_BIT (@Input int)
RETURNS INT
AS
BEGIN
DECLARE @WorkValue int=@Input
DECLARE @Result int=0;
DECLARE @Counter int=0;
WHILE @Counter < 31
BEGIN
SET @Result=@Result*2
IF (@WorkValue&1)=1
BEGIN
SET @Result=@Result+1
SET @WorkValue=@WorkValue-1
END
SET @WorkValue=@WorkValue/2
SET @Counter=@Counter+1
END
RETURN @Result
ENDModifichiamo infine la stored procedure:
ALTER PROCEDURE SP_INSART AS
BEGIN
DECLARE @VALID INT = DATEPART(ms,getdate())
INSERT INTO ARTICO(ID,HASHVAL, CODICE,COLORE,POSIZIONE)
SELECT dbo.INVERTI_BIT(COLORE),HASHVAL,CODICE,COLORE,POSIZIONE FROM TEST_TEMPDB..ARTICOLI WHERE ID = @VALID
END
Ed ecco i risultato:
Quello che possiamo notare in modo evidente è che i wait type di page LATCH sono ridotti in modo sensibile.
Tuttavia il tempo di esecuzione non si è ridotto anzi si è incrementato di più del 10%.
Per oggi è tutto!
Seguitemi però nel prossimo articolo dove continueremo questa analisi.
Non abbiamo terminato anzi, abbiamo ancora a disposizione altre “carte” da giocarci.
Infatti, proprio questa serie di articoli che state leggendo serve a fare da ponte per introdurre alcune novità che la nuova versione di SQL Server 2019 ormai allo stadio di Release Candidate mette a disposizione!
A presto!
Luca Biondi @ SQLServerPerformance blog!
Previous post: The last page insert latch contention issue. Cos'è?