Le Table Variables // Le novità introdotte con SQL Server 2019. (Parte 2)

Cari lettori,

Eccoci di nuovo assieme per continuare il discorso inziato nel precedente articolo che trovate Qui

In precedenza vi avevo raccontato la storia delle Table Variables ma anche delle loro limitazioni.
Ora però è il momento di raccontarvi le novità che accompagnano l'uscita della versione 2019 di SQL Server.

Siete pronti? Mettetevi comodi che si parte!

 

Table Variables deferred compilation 

Diamo subito un nome alla novità introdotta da SQL Server 2019 che riguarda le Table Variables: la Table Variables deferred compilation

Come spiega il nome la compilazione delle Table Variables diventa Differita.
Differita nel senso che avviene non al momento in cui la tabella viene creata bensì viene posticipata (differita) al momento in cui la tabella viene utilizzata nella nostra Query.

Più in dettaglio, la stima della cardinalità avviene al primo utilizzo dopodichè non avverrà nessuna altra ricompilazione.

Di fatto è quindi un compromesso tra il ricompilare la tabella tutte le volte per avere una stima della cardinalità precisa e non ricompilarla mai ed avere una stima della cardinalità pari a 1.

Il "trucco" è, come chiarisce Microsoft, "proprio e solo questo" i risultati che tuttavia si ottegono non sono per niente secondari.

Ovviamente occorre aver installato la Versione di SQL Server 2019 con il livello di compatibilità nativo impostato a 150.

Ora verifichiamo cosa accade facendo un confronto parallelo con la versione di SQL Server 2017.

 

Confronto SQL2017 vs. SQL 2019

Apriamo il nostro SMSS ed impostiamo il livello di compatibilità a 140 

Creiamo una tabella in memoria @T e la riempiamo con 1000 righe.

DECLARE @T TABLE(ID INT PRIMARY KEY, NAME SYSNAME NOT NULL UNIQUE);

INSERT @T SELECT TOP (1000) [OBJECT_ID], NAME FROM SYS.ALL_OBJECTS;

SELECT T.ID, T.NAME
FROM SYS.ALL_OBJECTS O
JOIN @T AS T ON O.[OBJECT_ID] = T.ID
WHERE T.NAME like '%EXEC%'

 

Dopo l'esecuzione vediamo che sono state lette 10 pagine di memoria.

       
(32 righe interessate)
Tabella 'sysschobjs'. Conteggio analisi 0, letture logiche 64, letture fisiche 0, letture read-ahead 0, letture logiche LOB 0, letture fisiche LOB 0, letture read-ahead LOB 0.
Tabella '#B197CBB3'. Conteggio analisi 1, letture logiche 10, letture fisiche 0, letture read-ahead 0, letture logiche LOB 0, letture fisiche LOB 0, letture read-ahead LOB 0.

Il piano di esecuzione è questo:

 

Se notate nella immagine sopra il riguadro in verde potetenotate che l'optimizer di SQL Server stima che la tabella contenga una (1) sola riga.
Adotta quindi per eseguire fisicamente la JOIN l'algoritmo detto di NESTED JOIN (in italiano CICLO ANNIDATI)


Adesso impostiamo il livello di compatibilità nativo a 150 e rieseguiamo la Query.

Guardate cosa accade!

Il numero di letture rimane lo stesso:

(31 righe interessate)
Tabella 'sysschobjs'. Conteggio analisi 1, letture logiche 16, letture fisiche 0, letture server di pagine 0, letture read-ahead 0, letture read-ahead server di pagine 0, letture logiche LOB 0, letture fisiche LOB 0, letture LOB server di pagine 0, letture LOB read-ahead 0, letture read-ahead LOB server di pagine 0.
Tabella '#A146BBBC'. Conteggio analisi 1, letture logiche 10, letture fisiche 0, letture server di pagine 0, letture read-ahead 0, letture read-ahead server di pagine 0, letture logiche LOB 0, letture fisiche LOB 0, letture LOB server di pagine 0, letture LOB read-ahead 0, letture read-ahead LOB server di pagine 0.

Ma il piano di esecuzione cambia:

Il numero stimato di righe contenuto nella tabella è ora 90
L'optimizer utilizza per risolvere la JOIN il più veloce algoritmo MERGE JOIN

Ad oggi possiamo utilizzare le Table Variables fiduciosi del fatto che la stima della cardinalità si basa su valori reali. L'unico limite insorge qualora il numero di righe contenute nella tabella vari nel corso della esecuzione della nostra procedura.

Tutto questo avviene però senza specificare nessuna opzione di RECOMPILE e senza utilizzare nessun trace flag come avevamo visto nello scorso articolo.

Adesso sapete tutto sulle Table Variables! 

Quindi non mi resta che darvi appuntamento al prossimo articolo.
Ricordandovi di iscrivervi per ricevere il tempo reale ogni aggiornamento.

Ciao a tutti! A presto!
Luca

Luca Biondi @ SQLServerPerformance blog!

Next post: SQL Server 2019 e l'hint QUERY_OPTIMIZER_COMPATIBILITY_LEVEL_n

Previous post: Le Table Variables // Tutta la loro storia, come ottenere maggiori prestazioni attraverso il trace flag 2543. (Parte 1)