SSIS: Optimizando inserciones en SQL

El post anterior ya lo tenía preparado, y publicado en http://blogs.gsc.es, y mientras lo reproduje en este blog me vinieron a la mente algunos otros intentos de ganarle tiempo al tiempo. Alguna vez os habrá pasado, lanzas un proceso ETL que sabes es pesado de narices, y si tienes tiempo para prestarle atención a cómo pasan los segundos lo aprovechas para pensar cómo mejorarlo.

La idea inicial era hablar de cómo trabajar con destinos SQL y configurar los índices de una tabla de forma adecuada para usar BULK Insert, o Fast Load según el componente que se utilice, pero voy a poneros en situación.

El escenario es el siguiente:

• Servidor Origen SQL2008r2 con BD relacional como origen de datos, REL.
  
• Servidor Destino SQL2005Sp3 con BD relacional como destino de datos, DW. Mismo servidor para SSAS.
  
• Red interna de 100Mbps
  

Solicitan la modificación un datamart de un sistema de BI, recientemente migrado a SQL 2008R2, la inclusión de una nueva dimensión. Una vez realizada la consultoría pertinente sobre los requisitos de este nuevo objeto en la BD, se crea la tabla de dimensión en el datamart y se construye el paquete ETL necesario para mantenerla. Modificamos el diseño de la base de datos de Analysis Services creando la dimensión. Y hasta aquí todo va como la seda...

En tabla de hechos que se relaciona con la dimensión es necesario introducir un nuevo campo con valor por defecto... y aquí hubo que pararse pensar un poco más.
¿Introducir un campo en la tabla existente? Con más de 200 millones de registros (unos 200Gb) el proceso podía tomarse su tiempo y cómo siempre, no lo había. Además hay que decir que el campo existía en el modelo relacional, en los datos de origen, desde el principio de los tiempos así que el siguiente paso hubiera sido actualizar el campo mediante una consulta de update. No es factible.

¿Cuál es la forma más rápida de tener una tabla de 200M. de registros con un campo nuevo y actualizado?

Comando OLEDB? A no ser que sean 20 filas las que vayais a actualizar, no recomiendo la utilización de este componente que actualiza n campos fila a fila. 1 a 1.

Inserción en nueva tabla stage con el id de fila y el nuevo campo. Posterior update mediante consulta SQL utilizando un join por el id de fila (unique non-clustered).

No actualizar la tabla, sino crear una nueva con el nuevo campo y eliminar. Aqui volvimos a mirar si se había realizado el backup de la BD Destino.

Pues está claro. Nos quedamos con la tercera opción... Hay que recrear la tabla con el nuevo campo e insertar con la opción Fast-Load funcionando en las mejores condiciones. Se diseñó un paquete ETL que leyera el origen y el destino como fuentes, combinando los datos del modelo relacional y dimensional en memoria para, desde el campo de origen obtener mediante un lookup la clave surrograda de la dimensión correspondiente e insertando en la nueva tabla. Listo!
Pero.... las inserciones no se realizaban tan rápido como cabía esperar, dichosas filas perezosas!!! La tabla destino se había recreado tal cual, con índices clustered y non-clustered.

¿Cómo configurar la tabla para un rendimiento óptimo de un Bulk-Insert?
En la documentación del componente OLEDB Destination indican la utilización del Hit Order de las opciones de configuración, Alberto Ferrari tiene un buen post sobre el uso de estas opciones.
http://sqlblog.com/blogs/alberto_ferrari/archive/2007/04/08/making-fast-load-really-fast-on-clustered-indexed-tables-with-ssis.aspx
. Es cierto que si la tabla contiene índices agrupados (clustered) utilizar los hits de Fast-Load optimiza enormemente el rendimiento del componente.
¿Pero que hace el componente? Pues ni más ni menos que un bulk-insert desde memoria.

Siguiendo la documentación de Microsoft para optimizar operaciones de Bulk Insert (http://msdn.microsoft.com/en-us/library/ms177445.aspx) y observando los tiempos que publico Alberto en su blog, llegué a la conclusión de que la configuración optima para este caso era mantener el índice clustered de la tabla y eliminar el resto, utilizando el Hint ORDER del componente Destino OLEDB

El proceso diseñado ETL para hacer la carga se tomó poco más de 2 horas para llenar la nueva tabla.

Una vez lo sabes... a optimizar destinos!

Saludos.