Le Table Variables // Tutta la loro storia, come ottenere maggiori prestazioni attraverso il trace flag 2543. (Parte 1)
Carissimi lettori,
Oggi volevo tornare a parlarvi delle table variables.
Ne avevamo già discusso delle Table variables ad inizio mese Qui quando le avevamo confrontate con le Temp Tables.
Questa volta però volevo parlarvi delle loro storia, raccontarvi della loro evoluzione nel corso del tempo e delle versioni di SQL Server.
Parlaremo poi anche del trace flag 2453 che ci permetterà di avere in alcuni casi prestazioni maggiori.
Ma adesso partiamo!
Table variables
Le tabelle di tipo Table variables furono introdotte addirittura ai tempi di SQL Server 2000.
Create per gestire i dati temporanei in modo più veloce rispetto alle Temp Tables, fu scelto di limitare le ricompilazioni e di non dotarle di statistiche.
Di fatto l'idea si rivelò vincente ...a metà!
Se infatti memorizzate in una Table Variables solamente poche righe avrete prestazioni ottime.
Se però avete necessità di memorizzarne di più vedrete che le prestazioni caleranno.
Caleranno proprio a causa della mancanza delle statistiche.
Così con il passare del tempo le Table Variables si fecero una brutta nomea!
Senza statistiche per SQL Server una Table Variables contiene sempre una sola riga!
Questo porta l'optimizer di SQL Server a scegliere un piano di esecuzione non corretto quando le righe contenute nella tabella sono molte di più!
Ma vediamolo in pratica!
Creaiamoci quindi la nostra tabella in memoria con queste stringhe T-SQL:
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 @T AS T;
Eseguendole visualizzando il piano di esecuzione effettivo.
Vediamo subito che il numero stimato di righe è 1 anche se la tabella di righe nel contiene molte di più.
Adesso facciamo un passo avanti nella nostra analisi.
Vediamo dapprima che SQL Server effettivamente non adotta il piano di esecuzione corretto.
Poi introduciamo il workaround che viene comunemente utilizzato per risolvere.
Per questo prendiamo la nostra tabella temporanea ed andiamo in JOIN con se stessa:
DECLARE @T TABLE(ID INT PRIMARY KEY, NAME SYSNAME NOT NULL UNIQUE);
INSERT @T SELECT TOP (2000) [OBJECT_ID], NAME FROM SYS.ALL_OBJECTS;
SELECT T.ID, T.NAME
FROM @T AS T
JOIN @T AS T2 ON T.NAME = T2.NAME
Cosa accade?
L'optimizer è convinto che la tabella @T contenga una sola riga e proprio per questo motivo utilizza come operatore di JOIN il nested join (cicli annidati).
Il nested JOIN è l'operatore di JOIN più lento dei tre che SQL Server può utilizzare.
Per ognuna delle 2000 righe presenti nella tabella Master andrà a cercare sulla tabella detail i dato cercato.
In questo modo vengono lette 4018 pagine di memoria e viene impiegato un tempo pari a 342 ms.
La situazione in questo caso la si risolve forzando la ricompilazione della Query tramite l'opzione RECOMPILE. (E' questo il workaround che si adotta tipicamente per risolvere!)
Proviamolo!
Scriviamo:
DECLARE @T TABLE(ID INT PRIMARY KEY, NAME SYSNAME NOT NULL UNIQUE);
INSERT @T SELECT TOP (2000) [OBJECT_ID], NAME FROM SYS.ALL_OBJECTS;
SELECT T.ID, T.NAME
FROM @T AS T
JOIN @T AS T2 ON T.NAME = T2.NAME
OPTION (RECOMPILE)
Quando la eseguiamo otteniamo:
Cosa notate?
Il numero stimato di righe contenute nella nostra tabella è ora di 2000 righe.
L'operatore di JOIN utilizzato è ora il Merge Join che è l'operatore di JOIN più veloce.
Gli aspetti negativi derivati dell'aggiungere l'opzione RECOMPILE alla nostra Query sono:
- Occorre modificare il sorgente della nostra applicazione, cosa non sempre possibile.
- La ripetuta forzatura della ricompilazione della query comporta un consumo non necessario di risorse.
Il trace flag 2453
Entrambi gli aggiornamenti infatti contenevano la patch 2952444 dal titolo originale "Poor performance when you use table variables in SQL Server 2012 or SQL Server 2014"
Tramite questa patch venne introdotto il trace flag 2453.
Abilitandolo l'optimizer di SQL Server sarà in grado di ottenere una migliore stima della cardinalità.
Verifichiamolo!
DBCC TRACEON(2453)
DECLARE @T TABLE(ID INT PRIMARY KEY, NAME SYSNAME NOT NULL UNIQUE);
INSERT @T SELECT TOP (2000) [OBJECT_ID], NAME FROM SYS.ALL_OBJECTS;
SELECT T.ID, T.NAME
FROM @T AS T
JOIN @T AS T2 ON T.NAME = T2.NAME
DBCC TRACEOFF(2453)
Se guardiamo il piano di esecuzione vediamo che anche questa volta il numero di righe stimate non è 1 bensì il numero effettivo di righe contenute nella tabella.
Come operatore di JOIN abbiamo il Merge Join e le pagine lette sono solamente 36 esattamente come quando abbiamo applicato l'opzione RECOMPILE.
Vi chiederete quindi, il nostro trace flag ed il ricompile sono in tutto e per tutto la stessa cosa?
beh no! Ci sono due differenze.
Mentre con il recompile la Query viene ricompilata tutte le volte e quindi SQL Server torna a valutare nuovamente il piano di esecuzione scegliendo il migliore, con il trace flag questo non accade. La Query viene ricompilata solamente al cambio di numero di righe oltre la soglia predefinita ed i parametri non vengono valutati nuovamente.
Il mio consiglio è quindi quello di attivare questo flag mai globalmente all'avvio dell'istanza bensì di utilizzarlo sono in alcune Query mirate e solo dopo aver valutato che effettivamente porti beneficio. In alternativa valutate che a portare beneficio sia invece l'opzione ricompile!
E così arrivamo all'attuale SQL Server 2019, ma delle novità che ci porta sulle table variables parleremo nella seconda parte di questo articolo.
Per oggi è tutto, non vi resta che aspettare la prossima pubblicazione.
Luca Biondi @ SQLServerPerformance blog!
Next post: Le Table Variables // Le novità introdotte con SQL Server 2019. (Parte 2)
Previous post: https://sqlserverperformace.blogspot.com/2019/09/la-clausola-nolock-approfondiamo-e.html
Comments
Post a Comment