Un viaje por tempdb: tablas temporales, variables de tipo tabla, tipos tabla definidos por usuario y escrituras asíncronas en el log de transacciones

Por todos es bien conocida la base de datos tempdb así como su criticidad de cara al correcto funcionamiento de SQL Server. Con la llegada de las versiones SQL Server 2005 y 2008 su uso se ha incrementado considerablemente debido a múltiples funcionalidades internas que se apoyan sobre ésta (versionado de filas, reconstrucción de índices online, etc.) En este post vamos a analizar el comportamiento de algunos elementos que residen en tempdb así como una propiedad característica de la base de datos tempdb: escrituras asíncronas en el log de transacciones.

Vamos a comenzar creando algunos de los elementos típicos de tempdb (tabla temporal, variable de tipo tabla y variable de tipo tabla definido por usuario) para analizar cómo se comportan ante pequeñas inserciones. Este es el escenario típico que podríamos tener cuando tratamos de utilizar estos elementos como interfaz entre procedimientos almacenados. Esta es una nueva funcionalidad de SQL Server 2008 (TVP) que nos permite pasar como parámetros tablas directamente, evitando así tener que convertir tablas en XML, CSV, etc. El siguiente script crea estos elementos y realiza una inserción de 5 registros en cada uno de ellos:

USE tempdb

— Creamos una tabla temporal, una variable de tipo tabla y un tipo tabla

CREATE TYPE TipoTabla AS TABLE (id int, data char(3000))

CREATE TABLE #TEMP (id int, data char(3000))

DECLARE @tabla AS TipoTabla

DECLARE @tabla2 AS TABLE (id int, data char(3000))

— Obtenemos el último LSN actual

DECLARE @lastTempdbLSN VARCHAR(MAX) =

(SELECT MAX([CURRENT LSN])

FROM fn_dblog(NULL, NULL))

— Realizamos inserciones en cada elemento

INSERT INTO #TEMP

VALUES (1,‘data1’),(2,‘data2’),(3,‘data3’),(4,‘data4’),(5,‘data5’)

INSERT INTO @tabla

VALUES (1,‘data1’),(2,‘data2’),(3,‘data3’),(4,‘data4’),(5,‘data5’)

INSERT INTO @tabla2

VALUES (1,‘data1’),(2,‘data2’),(3,‘data3’),(4,‘data4’),(5,‘data5’)

— Mostramos los registros desde el anterior LSN

SELECT AllocUnitName, *

FROM fn_dblog(NULL, NULL)

WHERE [CURRENT LSN] > @lastTempdbLSN

ORDER BY [CURRENT LSN] ASC

SELECT allocation_unit_id, type_desc, total_pages, used_pages, data_pages, container_id, hobt_id,rows,data_compression FROM sys.allocation_units

JOIN sys.partitions ON container_id = sys.partitions.hobt_id

DROP TABLE #TEMP

Antes de ejecutar el script, vamos a analizar el plan de ejecución previsto para las inserciones:

Como podemos ver el plan de ejecución para todas las operaciones es idéntico. Si nos fijamos a más bajo nivel vemos que el coste estimado es el mismo para todos los casos:

A continuación vamos a ejecutar el script y analizaremos las entradas en el log de transacciones. Tras ejecutarlo, vemos que las tres operaciones generan 92 entradas en el log de transacciones. La primera de ellas (sobre la tabla temporal) genera 32 entradas, la segunda (sobre la variable de TipoTabla) 28 y la tercera (sobre la variable de tipo tabla) genera 32 entradas.

Si comparamos las 32 operaciones de la tabla temporal y de la variable de tipo tabla vemos que las diferencias son mínimas pues el tipo de las operaciones realizadas coincide en orden y número. Los cambios se centran en los nombres de los objetos, pequeños cambios en la longitud de los registros, los LSN (obviamente) y en la descripción de la operación. La descripción de la operación en el log es importante pues nos indica que en el caso de la tabla temporal está considerada una operación INSERT real mientras que en las variables son TVQUERY. Las TVQUERY no participan en la transacción por lo que no se verían afectadas por un rollback de ésta. Las operaciones realizadas, en pseudocódigo, quedarían así:

Por simplicidad hemos obviado algunas entradas referentes a los cambios en sys.sysallocunits que podréis ver si lanzáis el script en vuestra máquina. Si comparamos estas inserciones con la inserción en la variable de tipo TipoTabla podemos encontrar que una de las pocas diferencias es que en este caso NO existen cuatro entradas que sí aparecen tanto en el caso de tabla temporal como en el caso de variable de tipo tabla. De nuevo en pseudocódigo:

Por tanto, la inserción en una variable de un tipo de tabla aunque genera asignaciones de espacio, no modifica la SGAM ni la GAM. Podemos ver cómo se asigna el espacio obteniendo los allocation_unit_id del log de transacciones consultando la tabla sys.allocation_units:

select allocation_unit_id, type_desc, total_pages, used_pages, data_pages, container_id, hobt_id,rows,data_compression from sys.allocation_units

join sys.partitions on container_id = sys.partitions.hobt_id

Los allocation unit mostrados corresponden con la variable de tipo TipoTabla, con la variable de tipo tabla y con la tabla temporal respectivamente. En general podemos esperar un rendimiento muy similar para pequeñas tablas utilicemos la alternativa que utilicemos pues vemos que la actividad real en el log de transacciones es prácticamente idéntica. Desde el punto de vista del rendimiento la actividad del log de transacciones es vital. Por tanto consideramos importante destacar que la escritura en el log de transacciones ocurre de forma asíncrona en tempdb. Esto incrementa notablemente el rendimiento de las operaciones que requieren de uso intensivo del log de transacciones respecto a una base de datos de usuario. Para comprobar este punto vamos a realizar 10000 veces la misma inserción de los 5 registros sobre una tabla temporal y sobre una tabla no temporal para comparar tiempos. Indicar que todos los ficheros de datos y de log residen sobre el mismo disco por lo que no se esperan diferencias debido a dicho motivo.

USE test

CREATE TABLE #TEMP (id int, data char(3000))

CREATE TABLE NOTEMP (id int, data char(3000))

SELECT GETDATE();

DECLARE @i int = 0

WHILE @i<10000

BEGIN

    INSERT INTO #TEMP

    VALUES (1,‘data1’),(2,‘data2’),(3,‘data3’),(4,‘data4’),(5,‘data5’)

    set @i=@i+1

END

SELECT GETDATE();

SET @i=0

WHILE @i<10000

BEGIN

    INSERT INTO NOTEMP

        VALUES (1,‘data1’),(2,‘data2’),(3,‘data3’),(4,‘data4’),(5,‘data5’)

    set @i=@i+1

END

SELECT GETDATE();

Como podemos ver la diferencia de tiempo necesario para realizar las mismas operaciones sobre una tabla temporal o sobre una tabla no temporal es considerable (14 segundos vs 34 segundos). Obviamente estos resultados tan dispares se producen debido a la no existencia de una caché de escritura suficientemente grande en mi máquina. En un sistema de almacenamiento empresarial con varios gigabytes de caché de escritura las diferencias serían mínimas o no existentes. Deberemos en todo caso hacer mediciones en nuestro sistema particular para determinar el impacto real. No olvidemos que tempdb es un recurso compartido por todas las bases de datos. Por tanto, lo que ganamos con la asincronía lo podemos perder debido a congestión en los discos que soporten tempdb.

Si analizamos la actividad generada en el log de tempdb veremos se caracteriza por un tamaño de registros de log reducido pues únicamente tenemos que almacenar los valores “before” y no los valores “after” pues nunca deberemos echar manos de la “recuperabilidad” en un caso de desastre.

USE test

SELECT TOP 10 AllocUnitName, *

FROM fn_dblog(NULL, NULL)

WHERE AllocUnitName=‘dbo.NOTEMP’ and Operation=‘LOP_INSERT_ROWS’

ORDER BY [CURRENT LSN] DESC

USE tempdb

SELECT TOP 10 AllocUnitName, *

FROM fn_dblog(NULL, NULL)

WHERE AllocUnitName like ‘dbo.#temp%’ and Operation=‘LOP_INSERT_ROWS’

ORDER BY [CURRENT LSN] DESC

Pensemos que cada vez que se inicia SQL Server la base de datos tempdb se inicializa vacía por lo cual no tienen sentido los valores “after” al no tener que hacer nunca un REDO de la operación. Si simultáneamente al script lanzamos una herramienta de monitorización como “Process Monitor” de Sysinternals podemos ver como las escrituras sobre el log de tempdb se realizan de forma asíncrona respecto a las escrituras. Esto lo podemos ver cuando durante el script anterior se intercalan operaciones de escritura del log en tempdb con la escritura en el log de la base de datos test:

En conclusión, vemos que tanto el uso de variables de tipo tabla, como variables creadas con un tipo tabla como tablas temporales implican actividad en el log de transacciones y, por tanto, operaciones de entrada/salida a disco. Sin embargo para su uso como estructuras temporales de pequeño tamaño todas son válidas. Por otra parte, tempdb opera como una base de datos temporal, en modo de recuperación simple, con logging reducido y con escritura asíncrona en el log. Por tanto el uso de tempdb puede traernos ventajas de rendimiento pero debemos tener en cuenta que es una base de datos especialmente sensible a congestión por lo que requiere un análisis caso por caso para evitar empeorar el rendimiento global.