Iniciamos hoy una serie de artículos sobre los entresijos del motor de ejecución de SSIS y cómo las diferentes propiedades del mismo afectan de una u otra manera al rendimiento.

Buffers y árboles de ejecución.

Conocer qué son los buffers y cómo lidiar con ellos es importante a la hora de acelerar y optimizar el rendimiento de nuestros paquetes SSIS. En este post vamos a centrarnos en ellos y en los árboles de ejecución.

¿Qué es un buffer?

Un buffer es una zona de memoria que SSIS utiliza para almacenar los datos que manipula durante la ejecución de un paquete. Tienen siempre un metadato invariable, es decir, su estructura no se puede alterar en tiempo de ejecución, pero sí se puede alterar su contenido. Esto es lo que hacen las transformaciones.

Al ser datos que están en posiciones fijas de memoria es muy rápido utilizarlos porque no hay necesidad de copiar información ni mover físicamente los buffers a otras posiciones de memoria. Además, el cálculo de la localización de estos datos se realiza una sola vez.

Uso de buffers

SSIS intenta reutilizar estos buffers tanto como puede desde operaciones previas para ahorrar tiempo de proceso. Los diferentes tipos de transformaciones de SSIS provocan diferentes usos de los buffers.

• Transformaciones fila a fila: Manipulan cada fila a medida que les van llegando, no tienen necesidad de acumular datos. Al poder reutilizar buffers previamente creados (donde se encontraba la fila antes de la transformación, por ejemplo) no necesitan crear nuevos y copiar los datos para ejecutar la transformación. Ejemplos: Conversión de Datos, Búsqueda, Columna Derivada, etc.
• Transformaciones parcialmente bloqueantes: Se utilizan normalmente para combinar conjuntos de datos. Al tener varias entradas de datos, que no tienen por qué tener el mismo número de registros, se suele dar la situación en la que hay muchas filas esperando en memoria a que lleguen filas desde las otras entradas de datos para empezar a operar con ellas. En estos casos, la salida de datos se copia a nuevos buffers y puede que se creen nuevos hilos de ejecución. Ejemplos: Unión, Unión de mezcla y Unir todo.
• Transformaciones bloqueantes: Son aquellas que necesitan todo el conjunto de datos antes de empezar a operar, y por lo tanto, las que más impacto tienen en el rendimiento. También en estos casos se copian los datos a nuevos buffers y se crean nuevos hilos de ejecución. Ejemplos: Agregado, Ordenar.

Las transformaciones fila a fila se conocen como síncronas, ya que por cada fila de entrada tendrán una de salida. En contraposición, tanto las parcialmente bloqueantes como las completamente bloqueantes se conocen como asíncronas, ya que el número de filas de salida no tiene por qué ser el mismo que las de entrada (incluso pueden no tener salida).

Árboles de ejecución

Un árbol de ejecución se define como una sección del flujo de datos empezando desde un componente asíncrono y acabando en destinos o transformaciones que no tienen salida síncrona.

Durante la ejecución, el motor de SSIS divide el flujo de datos en árboles de ejecución. Éstos definen cómo los buffers de memoria e hilos de ejecución se crean a lo largo del paquete. Cada uno de estos árboles fuerza a la creación de al menos un nuevo buffer y puede dar entrada a un nuevo hilo.

Un nuevo buffer requiere memoria extra para lidiar con la transformación; sin embargo, es importante anotar que un nuevo árbol de ejecución NO tiene por qué añadir un nuevo hilo.

En el ejemplo ilustrado por la Figura 1 tenemos:

• Árbol 1: Se crea al leer las filas de “Customer Source” y mantenerlas en memoria hasta cargarlas en “Customer Error Destination”.
• Árbol 2: Igual que el Árbol 0, para “Customer Source 2” y “Customer Error Destination 2”
• Árbol 3: Lee las filas, las copia en los nuevos buffers y las pasa al componente Union All.
• Árbol 4: Igual que en el Árbol 2, para “Customer Source 2” y “Union All”
• Árbol 5: Copia las filas a sus nuevos buffers y ejecuta el componente Union All, la conversión de datos, la columna derivada y el agregado. Todas estas operaciones comparten buffers que pueden ir siendo reutilizados una vez ya no se utilizan en las operaciones previas.
• Árbol 6: Una vez el agregado se ha calculado, las filas resultantes se copian en nuevos buffers de éste árbol y las carga en “Customers Final Destination”

A partir de SSIS 2008, se introdujo una modificación en los árboles de ejecución. Éstos pasan a llamarse rutas (paths) y mantienen el mismo comportamiento. La diferencia es que dentro de cada uno de éstos se pueden crear sub-rutas (subpaths) que intentan aprovechar el paralelismo inherente a las transformaciones que incluye. Cada uno de las sub-rutas puede ser ejecutado en paralelo. Esto facilita mucho la tarea del desarrollador de SSIS al liberarlo en gran parte de la tarea de diseño para ejecución concurrente. Aun así, en casos como los que veremos en el post siguiente de esta serie, es necesario conocer el funcionamiento de este mecanismo para entender cómo distribuye SSIS los hilos y poder aprovecharlo.

Estas sub-rutas son creadas por SSIS de manera automática después de analizar si las transformaciones que hay dentro de una ruta son susceptibles de ser paralelizadas.

En este ejemplo, tenemos un flujo de datos que implicaría dos Execution Trees en SSIS 2005 pero que en SSIS 2008 supone 2 rutas y 2 sub-rutas. Las rutas serían equivalentes a los ExecutionTrees que ya conocemos:

• Ruta 0: Desde “OLEDB Source” hasta Union All
• Ruta 1: Desde Union All hasta “OLEDB Destination”

Pero para la Ruta 0 tenemos 2 sub-rutas que se generan:

• Sub-ruta 0: De Multicast hasta Union All pasando por “Derived column 1”
• Sub-ruta 1: De Multicast hasta Union All pasando por “Derived column”

Para comprobar esta estructura podemos utilizar PipelineExecutionTrees en el log de eventos de SSIS.

Si analizamos los mensajes que tenemos durante la ejecución de este flujo de datos vemos como dentro de la ruta de acceso 0 tenemos un primer paso siguiendo el camino principal hasta el Multicast y después se divide en dos sub-rutas hasta que acaba la ruta principal donde están contenidas. Finalmente, se ejecuta la última ruta principal (ruta 1).

Tamaño de buffers

El tamaño de los buffers es variable y es el motor de SSIS quien lo establece intentando optimizarlo dependiendo de los valores de las variables que lo controlan y sus límites internos.

Las variables y parámetros son los siguientes:

• Estimated Row Size: No es una variable propia de SSIS, sino que es un valor que SSIS calcula en función del metadato que recoge desde los orígenes de datos en tiempo de diseño. Reduciendo la anchura de las filas y configurando los tipos de datos para ajustarlos lo más posible a las necesidades podemos reducir el resultado de éste cálculo.
• DefaultMaxBufferRows: Se trata de una de las propiedades del flujo de datos. Es configurable, aunque SSIS la establece por defecto a 10.000. SSIS multiplica Estimated Row Size por DefaultMaxBufferRows para hacer una aproximación estimada de cuánto ocupará cada buffer.
• DefaultMaxBufferSize: Otra propiedad de SSIS a nivel de flujo de datos. Se establece a 10MB por defecto. Se puede ampliar para intentar optimizar el rendimiento pero tienen como cota superior los 100MB de la variable interna MaxBufferSize, que NO puede ser cambiada.
• MinBufferSize: Como MaxBufferSize, se trata de la cota que SSIS establece para sus buffers, en este caso inferior. Está definida por la granularidad del sistema de reserva de memoria virtual del sistema operativo. Normalmente, está definida a 65.536 bytes, pero puede cambiar entre equipos.

SSIS ajusta los tamaños de buffer utilizando estos parámetros. Así:

• Si la estimación de EstimatedRowSize * DefaultMaxBufferRows es mayor que MaxBufferSize, SSIS reducirá el número de filas de cada buffer (ya que no puede reducir la anchura de la fila, esa es nuestra tarea) para ajustar cada buffer al tamaño máximo.
• Si la estimación de de EstimatedRowSize * DefaultMaxBufferRows es menor que MinBufferSize, SSIS establecerá el tamaño del buffer a MinBufferSize, acomodándolo a un tamaño óptimo para su utilización.
• Si la estimación de EstimatedRowSize * DefaultMaxBufferRows está entre MinBufferSize y DefaultMaxBufferSize, SSIS establecerá el tamaño del buffer al valor más próximo a EstimatedRowSize * DefaultMaxBufferRows utilizando un múltiplo de MinBufferSize para optimizar el rendimiento en el direccionamiento de memoria.

Jugar con estos parámetros y variables puede incrementar el rendimiento o modificar el comportamiento de nuestros paquetes SSIS de diversas maneras. Un ejemplo claro de esto es el caso en una operación semi-bloqueante tenemos un origen muy rápido y el otro más lento. Ajustar los tamaños de los buffers y el número de filas de cada uno puede ahorrarnos problemas de sobrecargas de memoria RAM.

Utilizando BufferSizeTuning en el registro de eventos podremos observar cómo SSIS ajusta los tamaños de nuestro buffers dependiendo del origen de datos y de los parámetros mencionados anteriormente. Además podremos ver las razones por las que ajusta los tamaños (límite superior excedido, tamaño inferior a MinBufferSize, etc.)

Además de todas las variables y procesos de los que hemos hablado, en el rendimiento de un paquete SSIS influyen muchas otras cosas (estado de la red si leemos de orígenes remotos, optimización de los equipos con los que trabajemos, rendimiento de los discos, etc.). Por lo tanto, un testeo completo habría de incluir una visión más global del sistema.

Como en la gran mayoría de las situaciones cuando trabajamos con rendimientos, es clave cuánto probemos y analicemos nuestros casos concretos.