Blog di Mauro Munzi

… quando non si possono usare le InMemory

Sulle tabelle temporanee e sulle variabili di tipo Table, e dei vantaggi e/o svantaggi delle prime rispetto alle altre, si è già scritto di tutto e di più, ed anche sfatati “strani” miti ( ma entrambe sono nel TempDb ); alla fine per farla breve rimangono a favore delle temporanee un paio di aspetti:

1. Hanno le statistiche mentre per le variabili ti tipo table (anche InMemory) SQL Server stima sempre 1 riga
2. Sono visibili da stored procedure richiamate da quella che ha creato la temporanea

Il punto 1 diventa trascurabile nel caso di oggetti con poche righe, mentre gli svantaggi con molte righe possono essere tranquillamente superati con:

• OPTION (RECOMPILE), ma prestate attenzione al caso di numerose chiamate (anche ogni secondo), altrimenti si spreca più CPU per ricompilare gli statement che non eseguirli.
• TRACE FLAG 2453, ma poi l’impostazione è per tutto il server, e per quanto riguarda la CPU si ricade nel punto precedente per tutte le stored procedure che usano variabili di tipo table.

Fatte queste premesse vediamo ora un approccio molto più pratico del confronto, che riguarda 2 aspetti fondamentali nelle performance del TempDb quando si ha a che fare con “High Workload Scenarios”;

• PAGELATCH contention, EX e SH, nelle pagine PFS ( 1 e ogni 8088, 64Mb ), GAM e SGAM ( 2 e 3, e ogni 511230 pagine, 4Gb)
• Traffico nel Tlog, generato dai record scritti, che alla fine si traduce in MB/s.

Per dimostrare le differenze userò due stored procedure che creano una tabella temporanea e una variabile di tipo table, inserendo 5 righe per ognuna; la loro struttura ed il numero di righe medie rispecchia una situazione tipica dei nostri ambienti; una sessione Extended Events e una query sul Tlog del Tempdb mostreranno le differenze.

CREATE PROCEDURE [dbo].[proc_TestTemp]
AS
BEGIN
SET NOCOUNT ON
CREATE TABLE #Table1(
[Fld1] [bigint] NULL,
[Fld2] [int] NULL,
[Fld3] [int] NULL,
[Fld4] [tinyint] NULL,
[Fld5] [decimal](9, 2) NULL,
[Fld6] [decimal](9, 2) NULL,
[Fld7] [tinyint] NULL,
[Fld8] [varchar](15) ,
INDEX [IX_Fld1] CLUSTERED ([Fld1] ASC))       

INSERT INTO #Table1 VALUES (1,100000,200000,35,9.20,9.20, 3, 'Prova')
INSERT INTO #Table1 VALUES (10,100000,200000,35,9.20,9.20, 3, 'Prova')
INSERT INTO #Table1 VALUES (100,100000,200000,35,9.20,9.20, 3, 'Prova')
INSERT INTO #Table1 VALUES (1000,100000,200000,35,9.20,9.20, 3, 'Prova')
INSERT INTO #Table1 VALUES (1000,100000,200000,35,9.20,9.20, 3, 'Prova')
END
GO       

CREATE PROCEDURE [dbo].[proc_TestVar]
AS
BEGIN
SET NOCOUNT ON
DECLARE @Table1 TABLE (
[Fld1] [bigint] NULL,
[Fld2] [int] NULL,
[Fld3] [int] NULL,
[Fld4] [tinyint] NULL,
[Fld5] [decimal](9, 2) NULL,
[Fld6] [decimal](9, 2) NULL,
[Fld7] [tinyint] NULL,
[Fld8] [varchar](15),
INDEX [IX_Fld1] CLUSTERED ([Fld1] ASC))

INSERT INTO @Table1 VALUES (1,100000,200000,35,9.20,9.20, 3, 'Prova')
INSERT INTO @Table1 VALUES (10,100000,200000,35,9.20,9.20, 3, 'Prova')
INSERT INTO @Table1 VALUES (100,100000,200000,35,9.20,9.20, 3, 'Prova')
INSERT INTO @Table1 VALUES (1000,100000,200000,35,9.20,9.20, 3, 'Prova')
INSERT INTO @Table1 VALUES (1000,100000,200000,35,9.20,9.20, 3, 'Prova')
END
GO

La sessione Extended Events usa l’evento sqlserver.latch_acquired filtrato per il dbid 2 e la sessione dalla quale si eseguono le due sp.

CREATE EVENT SESSION [Latch]
ON SERVER
ADD EVENT sqlserver.latch_acquired(
ACTION(sqlserver.session_id,sqlserver.sql_text)
WHERE ([package0].[equal_uint64]([database_id],(2))
AND [sqlserver].[session_id]=(57)))
ADD TARGET package0.event_file(SET filename=N'Latch'),
ADD TARGET package0.ring_buffer(SET max_memory=(40960))
WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,
MAX_DISPATCH_LATENCY=1 SECONDS, MAX_EVENT_SIZE=0 KB,
MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=OFF)
GO

Mentre la query sul Tlog è una cosa del tipo

SELECT
    fd.[Current LSN],
    fd.Operation,
    fd.AllocUnitName,
    fd.[Transaction Name],
    fd.[Transaction ID]
FROM sys.fn_dblog(NULL, NULL) AS fd

Mettendo tutto insieme ed eseguendo un checkpoint prima di iniziare vediamo cosa succede con la tabella temporanea…

USE tempdb
GO
CHECKPOINT
GO
ALTER EVENT SESSION Latch ON SERVER STATE = start
GO
exec [TestDb].[dbo].[proc_TestTemp]
GO
ALTER EVENT SESSION Latch ON SERVER STATE = stop
GO
SELECT
    fd.[Current LSN],
    fd.Operation,
    fd.AllocUnitName,
    fd.[Transaction Name],
    fd.[Transaction ID]
FROM sys.fn_dblog(NULL, NULL) AS fd

La query sul transaction log ci dice che sono stati scritti 38 log record ( 138 alla prima esecuzione )

Mentre per la sessione Extended Events sono stati acquisiti un totale di 51 Latch

Cancelliamo adesso i file della sessione Extended Events e ripetiamo la stessa cosa per la sp che usa la table variabile

Questa volta la query sul Tlog dice che i record scritti sono 23 ( 240 alla prima esecuzione, quindi mettere in cache una Table Variable genera più record)

Mentre la sessione Extended Events dice che i Latch acquisiti in totale sono solo 11

Quindi in sostanza abbiamo che le variabili di tipo Table richiedono molti meno LATCH e record nel Tlog, condizione che nel caso di migliaia di chiamate al minuto può fare una differenza notevole. Se la query sul Transaction Log viene modificata per prendere una SUM ([Log Record Lenght]) abbiamo che la stored procedure che usa la tabella temporanea usa 5552 bytes, mentre l’altra 2632 bytes.

Vediamo adesso cosa succede prendendo come riferimento i contatori di Performance “Log Flushes/sec” e “Log Bytes Flushed/sec” dell’oggetto “Databases, istanza tempdb: a tal proposito ho configurato il tool SQL Load Generator per generare (solo) circa 130 Batch al secondo con le due stored appena viste.

Nel caso della stored procedure con tabella temporanea abbiamo il seguente risultato

16 Log Flushes al secondo e circa 650Kb/sec scritti nel tlog.
Passando alla varsione con variabile di tipo table otteniamo invece

I Log Flushes/sec sono dimezzati a 8 e i Log Bytes Flushed /sec più che dimezzati a 300Kb/sec.

Dal punto di vista pratico quindi,nel momento in cui non si possa abilitare l’ InMemory, è più conveniente affidarsi in prima istanza alle variabili ti tipo table, soprattutto quando si ha a che fare con poche righe e molte chiamate al minuto, e nei casi in cui la differenza di prestazioni volga a favore delle tabelle temporanee provare una RECOMPILE che molto spesso livella le prestazioni dei due oggetti.