Rendimiento

El uso de tablas temporales en SQL Server es común para el almacenamiento de datos temporales durante el procesamiento de consultas. Aunque su uso es sencillo, la eliminación de estas tablas debe gestionarse adecuadamente. Seguro que estás acostumbrado a ver algo parecido a esto “DROP TABLE #TEMPTABLE;” al final de las consultas que usan tablas temporales. Yo también, sin embargo, el otro día encontré un artículo que lo desaconsejaba.
El resumen es simple, con decenas de sesiones creando y eliminando tablas temporales en bucle, al final nos encontramos con tiempos de espera de PAGELATHes pero si no borramos las tablas y cerramos la sesión esto no pasa. El artículo en cuestión no solo captó mi curiosidad, David, un seguidor del blog, también lo vio y me lo mandó para que opinase al respecto. Yo me debo a vosotros así que aquí van mis pruebas.  En este artículo, veremos como no siempre es la mejor idea y cómo puede llegar a generar problemas de contención en tempdb.

Introducción a las tablas temporales

Las tablas temporales, identificadas con el prefijo # (locales) o ## (globales), son útiles para almacenar datos que solo necesitamos de manera transitoria. Son empleadas comúnmente en escenarios como el almacenamiento de resultados intermedios, la manipulación de datos en procesos ETL, o la mejora del rendimiento de consultas mediante la reducción del número de accesos a disco.

Estas tablas residen en la base de datos tempdb, lo que las convierte en una solución conveniente para manipular grandes volúmenes de datos sin afectar el esquema de las tablas principales. Sin embargo, tempdb es un recurso compartido, lo que significa que un uso incorrecto de las tablas temporales puede afectar el rendimiento global del servidor SQL.

Impacto de la eliminación de tablas temporales en tempdb

Eliminar tablas temporales manualmente usando DROP TABLE puede parecer una buena práctica para liberar recursos, pero en ciertos contextos, como sistemas de alta concurrencia, esto puede exacerbar los problemas de contención en tempdb. Cada vez que una tabla temporal se crea o elimina, tempdb necesita actualizar sus páginas de asignación, lo que incrementa la posibilidad de bloqueos de tipo PAGELATCH.

El uso del DROP TABLE, por tanto, desencadena este problema y si abusamos de ello, podemos llegar a notarlo.Por el contrario si dejamos que las tablas temporales se eliminen solas al cerrar la sesión esto no pasa. La clave está en cómo se elimina la tabla mediante DROP TABLE y en alguna optimización (no documentada, por supuesto) que tiene SQL Server para liberar las tablas temporales al cerrar la sesión. En teoría, cada operación que implique la creación, modificación o eliminación de tablas temporales añade carga a las páginas de asignación, lo que puede desencadenar los problemas de contención pero, lo cierto es que esto no pasa si cerramos la sesión y dejamos que las tablas temporales se eliminen solas.

Probando el problema de las tablas temporales

Como sé que es lo que la mayoría estáis esperando, no me voy a enrollar más, aquí va mi prueba. Lo primero que debéis saber es que estas pruebas las he realizado en mi máquina de pruebas más potente (16 procesadores lógicos a 3,8GHz y 32Gb de RAM), con un SQL Server 2022 completamente actualizado y la tempDB en un disco SSD.

Para la prueba he usado la herramienta Ostress de la suite RML Utilities de Microsoft. Esta herramienta está desarrollada para hacer pruebas de estrés contra servidores SQL y, si os parece, otro día hablaremos de ella. 

Primera prueba: Borrar las tablas temporales

Para esta primera prueba he creado este procedimiento almacenado que veis a continuación. Simplemente crea una tabla temporal, inserta un valor y borra la tabla temporal.

CREATE PROC [dbo].[DropTempTable]
AS
BEGIN
CREATE TABLE #TempTable (ID INT null)
INSERT INTO #TempTable values(1)
DROP TABLE #TempTable 
END

A continuación, haciendo uso de Ostress he ejecutado el procedimiento 1000 veces en 50 sesiones paralelas. Es decir, he abierto 50 conexiones a la vez a la base de datos y en cada una de ellas he ejecutado 20 veces el procedimiento.

El resultado, como podéis ver a continuación han sido los comentados tiempos de espera de PAGELATACH

Siguientes pruebas

Bien, lo comentado por Haripriya Naidu en su artículo parece que es cierto pero, ¿será cierto que si no borramos la tabla temporal los problemas desaparecen?

Para esta segunda prueba simplemente he creado un procedimiento almacenado como el anterior pero sin la parte del borrado de la tabla temporal. Lo he llamado NoDropTempTable y de la misma manera que antes lo he ejecutado con Ostress y, ¿sabéis qué? No ha ocurrido nada. Cero contención. Ni un solo problema. He repetido la prueba con 1000 ejecuciones pero esta vez en 100 sesiones simultáneas y nada, mismo resultado, todo bien. Va a resultar que es cierto, alguna optimización tiene el motor de base de datos que no genera estos tiempos de espera.

En este punto, tenía que probar otra cosa, pero para ello, antes tenemos que comprender por qué nos hemos encontrado con este problema. Vamos a ello y luego os sigo contando.

¿Qué es PAGELATCH y cómo afecta a tempdb?

Un PAGELATCH es una forma de sincronización de acceso en memoria que SQL utiliza para garantizar la coherencia cuando múltiples procesos intentan acceder a las mismas páginas de datos. En el contexto de tempdb, estos bloqueos ocurren cuando múltiples sesiones intentan crear, modificar o eliminar tablas temporales simultáneamente, ya que todas residen en tempdb, un recurso compartido entre todas las sesiones.

En concreto, el problema que acabamos de ver surge porque muchas sesiones intentan acceder a las mismas páginas de administración en tempdb, en particular las páginas PFS (Page Free Space), GAM (Global Allocation Map) y SGAM (Shared Global Allocation Map), que gestionan la asignación de espacio en disco. Esto provoca contención de recursos y puede causar que las operaciones que dependen de estas páginas se ralenticen significativamente.

La última prueba, borrar temporales pero en memoria

Ahora que ya sabemos que los PAGELATCH que estabamos viendo son causados por el acceso a las páginas donde se almacenan los metadatos de las tablas, ¿que pasará si habilitamos la opción de Memory-Optimized TempDB Metadata que vimos en el pasado artículo? Pues bien, según mis pruebas con esta característica que se lleva a memoria las páginas de metadatos de TempDB el problema desaparece. La velocidad de la memoria es capaz de asumir las operaciones de lectura y escritura sin generar contención.

Estrategias para mitigar los problemas de contención en tempdb

Para reducir el riesgo de bloqueos PAGELATCH en tempdb, especialmente cuando trabajamos con tablas temporales en entornos altamente concurrentes, podemos implementar las siguientes estrategias:

• Habilitar Memory-Optimized TempDB Metadata como acabamos de ver. El problema es que esto, en entornos cloud de Azure no es compatible, como vimos ayer.
• Aumentar el número de archivos de tempdb: Una de las estrategias más efectivas es aumentar el número de archivos de datos de tempdb para distribuir la carga entre varias páginas de asignación. Se recomienda tener al menos un archivo de datos por cada núcleo lógico, hasta un máximo de ocho archivos, para aliviar la contención en las páginas PFS. Aunque en mi caso con esos 8 archivos no ha sido suficiente, lo cierto es que la prueba con 50 sesiones simultaneas sobre TempDB es un poco exagerada.
• Optimizar el uso de tablas temporales: Limitar el uso de tablas temporales a los casos en los que sean estrictamente necesarias. Cuando sea posible, se deben utilizar alternativas como variables de tabla, que no generan la misma carga sobre tempdb.
• Reutilización de tablas temporales: En lugar de crear y eliminar tablas temporales repetidamente en la misma sesión, es recomendable reutilizarlas dentro de la misma transacción o procedimiento almacenado, reduciendo así el número de operaciones de creación y eliminación en tempdb.

Otras ventajas de no borrar las tablas temporales

Además de todo lo que hemos visto hasta ahora, existe otra ventaja de la que no hemos hablado para no borrar las tablas temporales antes de tiempo. Cuando creamos y utilizamos tablas temporales en SQL Server, el optimizador de consultas puede reutilizarlas para generar y mantener planes de ejecución eficientes. Estos planes son cruciales, ya que permiten al servidor recordar la mejor manera de ejecutar consultas similares en el futuro, reduciendo la sobrecarga computacional.

Si eliminamos manualmente las tablas temporales mediante DROP TABLE y luego las recreamos, el optimizador pierde la capacidad de aprovechar estos planes previamente generados. Esto significa que, en consultas repetitivas, SQL Server tendría que volver a calcular el plan de ejecución desde cero, lo que incrementa el tiempo de procesamiento y reduce el rendimiento global. En casos donde las mismas tablas temporales son necesarias en múltiples ocasiones dentro de la transacción, dejar que SQL Server gestione automáticamente su ciclo de vida puede mejorar significativamente la eficiencia, ya que el sistema tiene más oportunidades de reutilizar los planes de ejecución optimizados y reducir la carga sobre tempdb.

Conclusión

El uso de tablas temporales en SQL Server puede ser una herramienta extremadamente útil, pero en entornos de alta concurrencia puede generar problemas de rendimiento si no se gestiona adecuadamente. El riesgo principal no reside en el DROP TABLE manual en sí, sino en la concurrencia y la carga sobre tempdb, pero evitando el borrado evitaremos el problema. Otras estrategias para mitigar los bloqueos PAGELATCH incluyen aumentar el número de archivos de tempdb, limitar el uso excesivo de tablas temporales y aprovechar las mejoras de las versiones más recientes de SQL Server. Con estas precauciones, podemos garantizar un uso más eficiente de las tablas temporales, evitando bloqueos y manteniendo un rendimiento óptimo del sistema.

Si tenéis alguna duda o sugerencia, podéis dejarla en Twitter, por mail o dejarnos un mensaje en los comentarios. Y recuerda que también tenemos un grupo de Telegram y un canal de YouTube a los que te puede unir. ¡Hasta la próxima!