SQL Server 2019 ed il Memory-Optimized TempDB Metadata

Ciao a tutti!

Oggi torniamo a parlare del database TEMPDB.
Ne abbiamo già parlato altre volte in una serie di articoli che potete trovare qui:

• Confronto tra "Temp Tables" (#) e "Table Variables" (@) 
• Il TEMPDB e la sua configurazione.. pronti per le Ferie? 
• SQL Server & come spostare il database TEMPDB
• Cos’è il database TEMPDB? ...e quindi perché è importante.

Questa volta però vi racconterò delle novità introdotte più di recente.
Già perchè SQL Server con l'edizione 2019 introduce alcuni miglioramenti che sono davvero significativi in questo caso.

 

Memory-Optmized TempDB Metadata

La feature Memory-Optmized TempDB Metadata di cui parlamiano oggi fa parte della famiglia di tecnologie dette In-Memory Database.

Ma cosa significa Memory-Optmized TempDB Metadata?

Cercherò di spiegarlo nel modo più semplice possibile!

Dopo i numerosi articoli saprete sicuramente cos'è ed a che cosa serve il database TempDB. Sapete che è una risorsa condivisa e comune a tutti gli altri database che può diventare il collo di bottiglia di tutte le operazioni.
Abbiamo visto anche che in caso di carichi pesanti sul tempdb si possono generare concorrenze ed attese a livello dei Metadati.

Con la Release 2019 di SQL Server per eliminare questo tipo di problema si è pensato di utilizzare la tecnologia In Memory per gestire appunto i Metadati del TempDb che diventano così privi dei Latch.

Questa modifica dovrebbe permettere ridurre i colli di bottiglia e di rendere maggiormente scalabili le procedure.

Per abilitare questa funzionalità è molto semplice ed è sufficiente eseguire questo comando:

ALTER SERVER CONFIGURATION SET MEMORY_OPTIMIZED TEMPDB_METADATA = ON 

E riavviare l'istanza di SQL Server.

And now ...some benchmark

Noi come al solito non ci fidiamo e siamo subito pronti per verificare se le affermazioni della casa di Redmond sono veritiere!

Per prima cosa creaimoci una stored procedure che andremo ad eseguire in modo parallelo utilizzando le RML utilities con e senza la nuova funzionalità abilitata:

       
CREATE PROCEDURE SP_TEMPART_# AS
BEGIN
  CREATE TABLE #ARTICO (CODICE VARCHAR(80) ,COLORE VARCHAR(80) , POSIZIONE INTEGER)

  INSERT INTO #ARTICO(CODICE,COLORE, POSIZIONE)
    VALUES ('00001','rosso',1),
           ('00002','rosso',1),
           ('00003','rosso',1),
           ('00004','rosso',1),
           ('00005','rosso',1),
           ('00006','rosso',1),
           ('00007','rosso',1),
           ('00008','rosso',1),
           ('00009','rosso',1),
           ('000010','rosso',1),
           ('000011','rosso',1),
           ('000012','rosso',1)
END

Adesso eseguiamole utilizzando il nostro OStrees con questi parametri:

OStress.exe -iTest_tempdb_2019_a.SQL -Usa -Pxxxx -Syyyyy -dzzzzz-oc:\bin\ostress -n200 -r20

Come riferimento otteniamo un tempo di esecuzione di 7.561 secondi.

In realtà le informazioni che però più cerchiamo sono i Wait States

Se il osserviamo subito dopo l'esecuzione della procedura vediamo che il 3% è rappresentato da PAGELATCH_EX e l' 1% è rappresentato da LATCH_SH:



Ora Attiviamo l'opzione tramite il comando:

ALTER SERVER CONFIGURATION SET MEMORY_OPTIMIZED TEMPDB_METADATA = ON 

Riavviamo l'istanza di SQL Server e rieseguiamo i nostri test.

Via!





Primo risultato: il tempo di esecuzione è sceso a 5 secondi. 

Adesso interroghiamo nuovamente i Wait states. 

Avete visto cosa è accaduto?

....Sono sicuro di si! Ormai siete pratici!

LATCH ed i PAGELATCH sono scomparsi.

Da questi primi test questa nuova feature sembra essere efficace.

Naturalmente come sempre attivatela misurandone le differenza prima e dopo.

In ogni caso se avete dubbi volentieri possiamo approfondire.

Per oggi è tutto,

Buon inizio di settimana!

Luca

Luca Biondi @ SQLServerPerformance blog!

 

Next post:SQL Server 2019 e le inline scalar function

Previous post: SQL Server 2019 e l'hint QUERY_OPTIMIZER_COMPATIBILITY_LEVEL_n