Blog di Mauro Munzi

… when You can’t use InMemory

We read more and more on temporary tables and table variables, and the pro/cons of one over the other, and also some myths have been explained ( …both are on the tempdb ) ; to make it short, there are a couple of pro in favor of temp tables:

1. Have statistics, while for table variables (also InMemory) SQL Server always estimates 1 row.
2. Are accessible from stored procedures called inside the one that created the temp table.

Point 1 become negligible in case of objects with few rows, while in case of a great number of rows we can use:

• OPTION (RECOMPILE), but particular attention may be needed in case of several calls (also every second), because You may spend more CPU resources in continuously recompiling the statements instead of run them.
• TRACE FLAG 2453, but the setting is server wide, so the risk is to fall back on the situation mentioned above.

Given this let’s look at a more “practical” approach to this comparison, which basically take into account two fundamental key points for the TempDb performance when dealing with “High Workload Scenarios”:

• PAGELATCH contention, EX and SH, on PFS pages ( 1 and every 8088 pages, 64Mb ), GAM and SGAM ( 2 and 3, every 511230 pages, 4Gb).
• TempDb Tlog traffic, created by Log records, which translates into MB/s.

To show the differences I’ll use two stored procedures, one which creates a temporary table and the other which creates a table variable, inserting 5 rows each; their structure and average rows is very similar to that of our environments. An Extended Events session and a query over the TempDb Tlog will show the differences.

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, 'Test')

INSERT INTO #Table1 VALUES (10,100000,200000,35,9.20,9.20, 3, 'Test')

INSERT INTO #Table1 VALUES (100,100000,200000,35,9.20,9.20, 3, 'Test')

INSERT INTO #Table1 VALUES (1000,100000,200000,35,9.20,9.20, 3, 'Test')

INSERT INTO #Table1 VALUES (1000,100000,200000,35,9.20,9.20, 3, 'Test')

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, 'Test')

INSERT INTO @Table1 VALUES (10,100000,200000,35,9.20,9.20, 3, 'Test')

INSERT INTO @Table1 VALUES (100,100000,200000,35,9.20,9.20, 3, 'Test')

INSERT INTO @Table1 VALUES (1000,100000,200000,35,9.20,9.20, 3, 'Test')

INSERT INTO @Table1 VALUES (1000,100000,200000,35,9.20,9.20, 3, 'Test')

END

GO

The Extended Event session uses the sqlserver.latch_acquired event filtered for dbid 2 and the session from which I’m running the stored procedures.

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

While the Tlog query is something like

SELECT

    fd.[Current LSN],

    fd.Operation,

    fd.AllocUnitName,

    fd.[Transaction Name],

    fd.[Transaction ID]

FROM sys.fn_dblog(NULL, NULL) AS fd

Putting all together and issuing a checkpoint before starting let’s check the results with the Temp Table…

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

The Tlog query tells that there are 38 log records (138 at the first execution)

The Extended Events session show a total of 51 acquired Latches

Let’s remove the just created files and repeat the same test with the Table Variable.

This time the query over the Tlog is telling that the number of records is 23 (240 at the first execution, meaning that caching a table variable creates more records)

The session shows now a total of 11 acquired Latches.

Basically we have that Table Variables require much less Latch and Tlog records than Temp Tables, a condition that in case of thousands of calls per minute could make a great difference. If we change the Tlog query to extract the SUM ([Log Record Lenght]) we find that the sp using the Temp Table writes 5552 bytes, while the other 2632 bytes.

Let’s check now what happens on the Performance Counters “Log Flushes/sec” and “Log Bytes Flushed/sec” of the “Databases” object, tempdb instance; for the purpose I used the SQL Load Generator to generate (only) around 130 Batch/sec with the two stored procedure.

This is the result with the Temp Tables

16 Log Flushes per second and around 650Kb/sec of Tlog traffic.

On the contrary with the Table Variables we have

Almost half the  Log Flushes/sec ( 8 ) and the  Log Bytes Flushed /sec more than halved at 300Kb/sec.

So, from a practical point of view, when we can’t enable the InMemory feature, it is more useful to start playing with the table variables, particularly when we work with a small number of rows and several calls per minute (or second); and when the performance favors Temp Tables, and the rate is not so high, a RECOMPILE in the statement should eliminate any difference.

… 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.