En esta entrada, hablaremos de la nueva propiedad que aparece en la versión de SQL 2016. Este atributo es propio del componente “DataFlow” y puede tomar los valores ‘True’ o ‘False’ (predeterminada). Además propondremos una solución aproximada para versiones anteriores.

SQL Server 2016 ¿Qué hace esta propiedad?

Para ello debemos de hablar primero un poco de las características “DefaultBufferMaxRows” y “DefaultBufferSize”. Estas dos propiedades definen el tamaño del bloque y número de filas de este en el flujo de datos de SSIS, con lo que, cuanto más grande sea el bloque, mayor será la cantidad de datos que se manipulará a la vez en memoria.

Por defecto estas propiedades mueven 10.000 filas y 10MB de tamaño de ‘Buffer’. Estos tamaños pueden llegar a ser pequeños en comparación con las cargas que pueden soportar los servidores donde se ejecutan. Por eso, a la hora de desarrollar puede ser conveniente jugar con estas propiedades para mejorar el rendimiento de nuestros procesos de ETL.

Cuando se establece a ‘True’ la propiedad en cuestión, se ignora la propiedad “DefaultBufferSize” ajustándose automáticamente en tiempo de ejecución con la propiedad “DefaultBufferMaxRows” y el tamaño de la fila que se procesa en el flujo. Con esto conseguimos una ayuda a la hora de ‘tunear’ el paquete.

Probando la propiedad

A continuación, se muestra una comparación en la que se exponen los tiempos medios de carga de una tabla, moviendo 30 millones de filas en un ETL mediante distintas parametrizaciones.

Independientemente de que la propiedad esté activa o no, según aumentamos las filas que procesamos mejor es el resultado en términos de tiempo, por consiguiente, estamos aprovechándonos más de los recursos de los que disponemos. Una vez que activamos la propiedad, la mejoría es mucho más notable por los ajustes automáticos que realiza.

¿Cómo podemos hacer esto en otras versiones?

Esta propiedad es una novedad en 2016, por lo que, si queremos hacer esto mismo en versiones anteriores podemos implementar una aproximación en nuestros paquetes.

Si bien SSIS sabe cuál es el ancho de la fila que trata en el momento en ejecución, nosotros podemos obtener el ancho de la fila de la tabla destino y a partir de ahí calcular el tamaño del ‘Buffer’ de forma sencilla. No es una solución perfecta, el tamaño de la fila destino puede no ser el mismo del de la que hay en el “DataFlow”, pero para cargas de tablas sencillas puede llegar a ser una solución muy válida.

Para obtener el tamaño de la fila o directamente, el valor que queremos asignarle a la propiedad “DefaultBufferSize”, podemos emplear la consulta de a continuación:

DECLARE @TABLE VARCHAR(50) = ?
DECLARE @ROWS INT = ?

SELECT SUM (MAX_LENGTH) * @ROWS AS BUFFER_SIZE 
FROM  SYS.TABLES T
JOIN  SYS.COLUMNS C ON T.OBJECT_ID = C.OBJECT_ID
JOIN  SYS.SCHEMAS S ON T.SCHEMA_ID = S.SCHEMA_ID
WHERE T.NAME = @TABLE

Esto lo insertamos en un “SQL Task” y le pasamos como parámetros el nombre de la tabla destino y las filas que irán a la propiedad “DefaultBufferMaxRows”. Puede ir en unas variables, por ejemplo “BufferRows” y “TableName”.

En la variable “BufferSize” parametrizamos el resultado del “SQL Task”. Finalmente, las variables “BufferRows” y “BufferSize” las asignamos a las propiedades “DefaultBufferMaxRows” y “DefaultBufferSize” del “DataFlow” respectivamente mediante unas expresiones.

Como resultado, realizando las mismas mediciones cambiando el valor de la variable “BufferRows” obtenemos los siguientes tiempos:

Poniendo todos los rendimientos en común vemos como la solución calculada por nosotros se aproxima a la aportada por SSIS.

Sin embargo, desde el inicio los cálculos que realiza la opción “AutoAdjunstBufferSize” devuelven un mejor resultado desde el inicio.