Desde tiempos inmemoriales, hay un dicho que dice: “Usa siempre que puedas tablas temporales porque esas tablas viven en RAM”. A veces lo he oido con variables de tabla, pero la “esencia” del dicho es que puedes mejorar el rendimiento de lógica de negocio en por ejemplo procedimientos almacenados, creando objetos “temporales”. Nada mas lejos de la realidad 🙂
La realidad es que una tabla temporal o una variable de tabla, finalmente acaba escribiendose en disco…pero en los discos donde residen los ficheros de tempdb. Es decir, que si, que una tabla temporal o variable de tabla SI genera presión de E/S. ¡Sorpresa!
La buena noticia es que ese famoso “dicho” en SQL Server 2014 ya no es mentira del todo :). Si, ahora en SQL Serve 2014 existen situaciones por las que una determinada operación no tiene por qué acabar escrita en tempdb. Pero la razón no es que ahora las tablas temporales acaben como objetos SCHEMA_ONLY del motor In-Memory OLTP, sino que se ha mejorado el rendimiento de ciertas operaciones con el fin de minimizar las escrituras a base de retrasar el momento de empezar a escribir ante determinadas situaciones. Dicho de otra forma…el mecanismo sigue necesitando que se escriba en disco todo…peeero, si tu objeto temporal es creado, usado y destruido antes de que “le toque” ser escrito en disco…pues realmente no será escrito en disco y por tanto no generará E/S.
Eager Writes
En SQL Server existe el concepto “eager writes”, por el cual, cada vez que ocurre una carga masiva, el propio SQL Server envía en bloques de 32 páginas sucias contiguas (las nuevas generalmente) a disco, para minimizar el impacto sobre el buffer pool, checkpoints y sobre lazy writer. Esto está muy bien para escenarios de carga masiva digamos “normales” pero no está tan bien en determinado tipo de operaciones, que ademas son bastante más frecuentes. Imagina un procedimiento almacenado en el que realizas una operación del estilo select …. INTO #tablatemporal … para luego en dicho stored procedure hacer consultas, filtros,…con los resultados parciales que existan en dicha #tablatemporal. Pues bien, lo que ocurre es que quieras o no, lo que metas en el objeto #tablatemporal acabará si o si en disco, sobre tempdb.mdf (o el .mdf que sea de tempdb), con lo que estas impactando (sin saberlo a lo mejor) en E/S.
Mejoras en SQL Server 2014
En SQL Server 2014 lo que se ha introducido es una relajación del comienzo de volcado de esas páginas sucias a tempdb. Las implicaciones de esto son que ahora, vas a tener situaciones donde la vida del objeto temporal sea mas corta que el momento en el que le toque ser “volcado” a disco, con lo que efectivamente tu objeto temporal solo habrá vivido en RAM realmente, sin haber tocado para nada la E/S.
Esto quiere decir que por el hecho de migrar a SQL Server 2014, tienes posibilidades de que ciertas operaciones se realicen mucho mas rápido puesto que diréctamente estas eliminando la E/S de la operación 🙂
¿Qué operaciones se verán beneficiadas?
- Bulk Inserts
- select … into #tablatemporal
- Creación o mantenimiento de índices con el flag SORT_IN_TEMPDB activado
Resultados
Viendo esto con datos
La query utilizada para la prueba
SELECT TOP(500)
t1.name, replicate('X',5000) as col2
into #tablatemporal
FROM master.sys.objects t1
CROSS JOIN master.sys.objects t2
Cada prueba consta de una ejecución de 4 hilos y 200 hiteraciones por hilo (800 ejecuciones)
SQL Server 2012 SP1
SQL Server 2014
Como vemos, así de entrada y con pocas filas (lo normal) el aumento de rendimiento es de prácticamente 2x. Este aumento de rendimiento obviamente va a ser mucho mas acentuado, cuanto peor sistema de E/S tengas o cuanto más saturado lo tengas.
¿Qué pasa con SQL Server 2012?
En SQL Server 2012 tenemos liberada esa mejora de rendimiento en el parche acumulativo 10 de SP1, pero ojo porque no viene con el SP2 de SQL Server 2012
Descarga el SQL Server 2012 CU 10: http://support.microsoft.com/kb/2958012/en-us?sd=rss&spid=1044
