Mejorar Variables Tabla con Table Deferred Compilation

Desde que tengo memoria usando los tipos de datos «table«, he recibido la pregunta respecto a si era lo mismo utilizarlos en lugar de una tabla temporal. La respuesta puedes verla en el post Variables Tabla vs Tablas Temporales. No quiero arruinarte la respuesta así que primero visita ese enlace. Es este artículo veremos cómo mejorar variables tabla.

Para empezar

Las Variables Tabla y las Tablas Temporales tienen comportamientos diferentes y debemos comprender el origen de aquello. (No olvides ver el enlace de arriba)

SQL Server 2019 ha traído con su lanzamiento, un concepto súper interesante dentro de lo que se ha llamado el Intelligent Query Processing; este es el Table Variable Deferred Compilation.

¿Cómo funciona TVDC?

Al generar un plan de ejecución, se utilizarán las estimaciones de cardinalidad basadas en la cantidad de filas actuales que tiene la variable tabla.

Así nos olvidaríamos de la «adivinanza» que hacía el motor diciendo que las variables tabla tenían siempre una sola fila de datos.

El plan conseguido podrá ser reutilizado hasta que sea descartado o se genere una recompilación.

OK. Vamos a generar una variable tabla y llenarla de datos para ver esta magia.

USE WideWorldImportersDW 
GO

--   Be sure you're on previuos version
ALTER DATABASE WideWorldImportersDW 
SET COMPATIBILITY_LEVEL = 140;
GO

DECLARE @BestBands TABLE ​
(BandName varchar(50))​
​
INSERT INTO @BestBands VALUES​
('Led Zeppelin'),​
('Deep Purple'),​
('The Beatles'),​
('Metallica'),​
('Iron Maiden'),​
('System of a Down'),​
('Pink Floyd'),​
('Guns n Roses'),​
('Tool')​,
('Pearl Jam')​,
('Dream Theater')​
​
SELECT * FROM @BestBands​

Mira sobre todo el segundo bloque del plan de ejecución que se genera aquí.

La cantidad actual de filas vs el estimado tienen una notoria diferencia que puede causarnos problemas en la generación de un Plan de Ejecución adecuado.

Ahora bien, vamos a cambiar el modo de compatibilidad para realizar el mismo trabajo.

-- LET'S CHANGE COMPAT LEVEL 15
ALTER DATABASE [WideWorldImportersDW] 
SET COMPATIBILITY_LEVEL = 150;
GO

DECLARE @BestBands TABLE ​
(BandName varchar(50))​
​
INSERT INTO @BestBands VALUES​
('Led Zeppelin'),​
('Deep Purple'),​
('The Beatles'),​
('Metallica'),​
('Iron Maiden'),​
('System of a Down'),​
('Guns n Roses'),​
('Tool')​

SELECT * FROM @BestBands​

Ahora veamos el nuevo Plan de Ejecución

¡Exactamente! Es la magia de estar en Modo de Compatibilidad 150 (SQL Server 2019) Ahora sí puedes ver que la cantidad actual de filas es idéntica a la cantidad estimada.

Mejorar variables tabla

Solo basta con hacer este cambio en el Modo de Compatibilidad.

Una de las premisas de las mejoras de rendimiento de SQL Server 2019 es que no tengas que realizar ajustes en el código. Solo usa la nueva versión para tener estas mejoras.

No te olvides la importancia de las estadísticas del motor. Que las estimaciones sean siempre precisas para mejores resultados.

¿Quieres ver más de esta nueva versión? Puedes ver Qué trae de nuevo SQL Server 2019 y no olvides registrarte aquí para recibir las invitaciones a las próximas Webinars y ver las demostraciones en vivo.