En este vídeo se demuestra cómo mejorar el rendimiento del flujo de datos en un paquete Integration Services. También aprenderá a ajustar las siguientes fases del flujo de datos:

Extracción
Transformación
Carga

Estas sugerencias se pueden poner en práctica al diseñar, desarrollar y ejecutar el flujo de datos.

Menciones a participantes en el vídeo

Gracias a Thomas Kejser por aportar el material para la serie Serie de vídeos SSIS: diseñar y ajustar el rendimiento de SQL Server. Este vídeo es el segundo de la serie.

Gracias a Carla Sabotta y Douglas Laudenschlager por sus consejos e inestimables comentarios.

Transcripción del vídeo

Marca de tiempo del vídeo	Audio
00:00	Hola, les presento a David Noor, jefe de equipo de SQL Server Integration Services de Microsoft. Este vídeo trata sobre cómo ajustar el flujo de datos de paquetes SSIS en la empresa. Este vídeo es la segunda parte de una serie de cuatro titulada SSIS: diseñar y ajustar el rendimiento. En la parte uno de la serie, Denny repasaba cómo medir y conocer el rendimiento de los paquetes SSIS. En esta entrega, partiremos de ahí y examinaremos cómo mejorar el rendimiento del flujo de datos de los paquetes SSIS. Para empezar, identificaremos los componentes comunes de un flujo de datos y cómo elegir en cuál de sus partes se debe empezar a trabajar si intentamos mejorar el rendimiento. Una vez hayamos entendido dónde están los problemas, se puede hacer mucho en un flujo de datos para conseguir que todo sea más rápido y más eficaz. Examinaremos una serie de sugerencias concretas que se pueden seguir al diseñar, desarrollar e incluso ejecutar un flujo de datos. Vamos a comenzar. La mayor parte de los flujos de datos de paquetes se centran en la extracción, transformación y carga de datos críticos, lo que en ocasiones se conoce como ETL. Ajustar los flujos de datos implica ajustar cada una de estas fases de los mismos: extracción, transformación y carga. En lo que respecta a la extracción, hablaremos sobre cómo ajustar los componentes de los orígenes SSIS y las consultas que ejecutan para conseguir que el rendimiento sea el mejor y la repercusión la mínima en los sistemas de origen. Ajustar las transformaciones de un flujo de datos significa examinar el trabajo que hay que llevar a cabo y elegir el enfoque óptimo que permita realizarlo eliminando pasos innecesarios y, en ocasiones, cambiando las consultas de código originales para hacer las transformaciones en su lugar. Por último, ajustar la carga del flujo de datos significa ajustar el bloqueo, las redes y los componentes de destino de SSIS, así como otros factores que puedan dificultar el progreso del flujo de datos a medida que intenta cargar los datos en su destino. En este vídeo veremos sugerencias prácticas para buscar oportunidades que le permitan ajustar el flujo de datos en las tres fases. Antes de empezar a examinarlas, es importante recordar que normalmente no existe una solución mágica para solventar los problemas de rendimiento. Es probable que ninguna de las sugerencias aquí enumeradas pueda corregir todos los problemas de rendimiento de un flujo de datos. Los consejos prácticos que vamos a mostrar van bien en muchos casos, pero no es aconsejable confiar solo en ellos para resolver el problema. Lo mejor siempre es saber cuál es el propósito de los flujos de datos, asegurarse de que están bien diseñados para cumplir esos objetivos, medir su rendimiento actual y hacer cambios reiteradamente comprobando si el cambio realizado ha mejorado o no el rendimiento. Además, al examinar un problema de rendimiento, es fácil fijarse a una tecnología o componente en particular, en ocasiones debido a la familiaridad con la tecnología o el código. Pero, independientemente del motivo, intente pensar en el flujo de datos en el contexto de un ecosistema completo, que, con mucha probabilidad, incluirá varias bases de datos, una red, sistemas operativos, sistemas de archivos y montones de piezas. Cuanto más sepa acerca de este ecosistema en su totalidad, más completa será la imagen que dispondrá de los desafíos reales a los que una parte se enfrenta en relación al rendimiento, y podrá hacer cambios más efectivos y menos arriesgados.
03:38	Vamos a comenzar por lo que hace el flujo de datos: la extracción. Al usar SQL Server o cualquier otra base de datos con una interfaz masiva como origen, debe experimentar aumentando el tamaño de paquete. Normalmente, el valor predeterminado de SQL Server, 4096, funciona bien, pero dado que la extracción va a mover grandes cantidades de datos, aumentar este valor debería servir de ayuda. Para que esto surta efecto, es aconsejable que le pida al administrador de red que habilite también las “tramas gigantes” en la red. Sin embargo, es conveniente que pruebe el efecto en su paquete. Si usa el mismo administrador de conexiones para las operaciones masivas como un origen OLE DB y para las operaciones de una sola fila (por ejemplo, un comando OLE DB), podría considerar crear un segundo administrador de conexiones para las operaciones con el comando OLE DB y usar en ese caso un tamaño de paquete menor. Según lo dicho, si su ETL ejecuta Windows 2008 y usa un equipo con varios núcleos y tarjetas de interfaz de red, puede lograr un rendimiento de red algo mejor si establece afinidades entre las tarjetas de red y cada uno de los núcleos individuales. Busque la publicación en el blog que trata sobre cómo escalar el tráfico de red abundante con Windows, en el sitio de MSDN para obtener más información. Al ajustar la extracción, una de las cosas más sencillas que puede hacer es ajustar las consultas que está usando. En ocasiones, es tentador seleccionar simplemente la tabla de la que desea extraer datos y permitir que se recuperen todas las columnas, pero obtendrá resultados mucho mejores si únicamente selecciona las que realmente necesita. Esto no solo provocará menos tráfico de red y el uso de menos memoria, sino que el servidor de bases de datos podrá hacer muchas menos operaciones de E/S para satisfacer su consulta. Como se puede ver aquí, con SQL Server, también puede usar una sugerencia para indicar que no se deben emitir bloqueos compartidos mientras se realiza la selección, de modo que su consulta leerá datos potencialmente no confirmados o modificados. Reserve este proceso para los casos en los que deba obtener el máximo rendimiento, y asegúrese de que la lectura de datos modificados siempre será apropiada para su trabajo ETL. Las búsquedas se pueden considerar de extracción o de transformación. Pero, de cualquier modo, debería poner en práctica algunas de las ideas que mostramos en la diapositiva anterior. Aquí, es incluso más importante seleccionar únicamente las columnas que necesita, no solo para la optimización de la red, sino también para la optimización de caché en memoria. Si usa SQL Server 2008, una de las mejores características para el rendimiento es la adición de la nueva memoria caché de búsquedas compartida. Al compartir una memoria caché de búsqueda, puede capturar los datos de referencia una vez y después reutilizarlos en varias operaciones de búsqueda de un paquete, o incluso entre paquetes, si guarda la memoria caché en un archivo. Si tiene varios componentes de búsqueda que hagan referencia a la misma tabla, debe estar atento a esta nueva opción como medio de aumentar en gran medida la velocidad de los paquetes. Su uso puede ser bastante simple. Cree un administrador de conexiones de caché en el paquete, rellene la memoria caché usando la nueva transformación de caché y después cambie las búsquedas para que hagan referencia a la conexión de esta caché con el fin de conseguir los datos de referencia.
06:29	Pasemos a la segunda fase de ETL: la transformación. En la transformación, va a trabajar con datos que ya están cargados en el flujo de datos e intentará darles la forma apropiada antes de que se carguen. En SSIS, cada transformación aparece como un objeto que puede arrastrar en su diseño, pero no todas las transformaciones se hacen del mismo modo. Veamos los tres tipos de transformaciones que existen en SSIS: Las transformaciones sincrónicas, o basadas en filas, operan en los búferes de los datos existentes. No hacen copias de los búferes de datos ni de las filas a medida que fluyen. Solo transforman los datos directamente en el búfer. De este modo, las transformaciones sincrónicas son bastante rápidas. Algunos ejemplos de transformaciones sincrónicas son la conversión de datos, la columna derivada y la búsqueda. Las transformaciones asincrónicas, con bloqueo parcial, son diferentes. La transformación tiene que conservar los datos para hacer su trabajo a medida que los recibe. Para ello, copiará los datos entrantes en búferes internos y usará memoria, mucha en ocasiones. A medida que los datos continúen fluyendo, estas transformaciones pueden darse cuenta de que pueden continuar y escribir los datos de salida. Lo harán y liberarán la memoria interna correspondiente que hayan estado reteniendo. Una vez que el flujo de datos esté completo, liberarán toda la memoria que hayan usado. Pero, hasta entonces, normalmente utilizarán una cantidad apreciable de datos. Algunos ejemplos de estos tipos de transformaciones son Merge, MergeJoin y Union All. Cuando tenga estas transformaciones en su flujo de datos, busque formas para optimizarlas. ¿Hay copias redundantes? ¿Hay transformaciones MergeJoin o Union que pueda “insertar” en el sistema de origen? He visto paquetes en los que en lugar de hacer JOIN en una consulta de origen, se capturaban todos los datos de dos tablas mediante orígenes OLE DB y, después, se usaba una combinación Merge en el paquete para realizar la combinación, incluso aunque pudiera haberse escrito simplemente una combinación SQL en el código, y habría sido mucho más rápida debido a la capacidad de la base de datos de optimizar esa consulta. Investigue si hay alguna oportunidad de consolidar y reducir el número de transformaciones asincrónicas. En SQL Server 2008, hemos intentado mejorar el programador de tareas de flujo de datos para hacer que los flujos de datos complejos sean más eficaces y un mejor uso de las CPU disponibles. Si empezó a usar SSIS en SQL Server 2005, puede que haya introducido una transformación Union All en el flujo de datos para dividir los árboles de ejecución y favorecer la ejecución paralela de forma artificial. Ya no debería tener que hacer esto. Gracias a las mejoras que hemos efectuado en SQL Server 2008, ya no es necesario ni se recomienda. Quite estos componentes Union All artificiales. Debería experimentar un rendimiento mejor. El tercer grupo, las transformaciones asincrónicas de bloqueo, son como versiones extremas del grupo anterior: tienen que esperar a TODOS los datos de entrada para poder escribir los datos de salida. El uso de estas transformaciones en flujos de datos con lotes de datos grandes con frecuencia puede ralentizar el flujo de datos de forma significativa. Si las utiliza en flujos de datos grandes, asegúrese de que no hacerlo de forma redundante. Si tiene dos agregados o dos ordenaciones en los mismos datos del mismo flujo, vuelva a crear el paquete para intentar reducirlos a uno. Dados los fundamentos, examinemos otras cuestiones concretas: Es bastante común que los flujos de datos tengan que transformar el tipo de datos de una columna. Intente hacerlo solo una vez y use los tipos más reducidos que sea posible para mantener compactos los búferes de datos. La conversión de los datos también precisa utilizar la CPU y, si todo el flujo de datos puede usar un tipo para una columna dada, considere convertirla en el origen de datos mediante las funciones CAST o CONVERT de SQL, o la equivalente de su dialecto de bases de datos de SQL. Lo he mencionado un par de diapositivas antes, pero repito que es aconsejable optimizar las transformaciones pensando con inteligencia dónde deben ir y no tener miedo de usar SQL en el código en su provecho. Por ejemplo, si va a hacer ordenaciones en los datos de origen, considere insertarlos en cláusulas ORDER BY en el código SQL. Es posible que la base de datos pueda devolver los datos ordenados de forma mucho más eficiente que en el flujo de datos. Guarde el componente de ordenación aquí para cuando tenga que ordenar los datos que se hayan combinado de varios orígenes. Igualmente, algunas agregaciones se pueden realizar más rápidamente en el código usando una función de agregado SQL y GROUP BY. Si usa SQL Server 2008 y el componente dimensión de variación lenta (SCD), eche un vistazo a la nueva funcionalidad de MERGE en SQL Server. MERGE puede llevar a cabo muchas de las mismas acciones que SCD, pero sin necesidad de los ciclos de ida y vuelta de red que este necesita. No olvide tampoco la utilidad de INSERT INTO de SQL. Si el flujo de datos es simple y tanto el origen como el destino están en la misma instancia de base de datos, es posible que pueda hacer el mismo movimiento de datos pero muchísimo más rápidamente a través de una sola instrucción SQL, de modo que todo el movimiento de datos se produzca por completo en la base de datos. En estos casos, INSERT INTO puede ejecutar una ordenación de magnitud más rápidamente que un flujo de datos, porque los datos nunca tienen que abandonar el servidor. Por último, aunque no por ello menos importante, si está haciendo cargas incrementales, eche un vistazo a la alternativa de la recarga. Se ha comprobado que algunos sistemas emplean mucho tiempo en detectar las diferencias para evitar volver a cargar los datos, pero la E/S y la CPU empleadas para ello terminan haciendo que el trabajo sea a la larga más lento.
11:59	Pasemos a la fase de carga. Al cargar en SQL Server, tiene dos opciones válidas: La primera opción es usar un componente Destino de SQL Server. Este componente usa memoria compartida entre el flujo de datos y el motor de base de datos para cargar los datos rápidamente, pero solo funciona si el flujo de datos se ejecuta en el mismo equipo que el propio SQL Server. Además, el destino de SQL Server tiene algunas limitaciones documentadas respecto al tratamiento de errores. Su otra opción para agilizar la carga en SQL Server es el destino OLE DB, que suele ejecutarse casi tan rápidamente como el destino SQL. En todos estos casos, si se usa un tamaño de confirmación de 0, la carga se produce más rápido. Es una práctica bastante habitual quitar los índices del sistema de destino también cuando se efectúan cargas grandes, pero debería seguir algunas instrucciones para saber cuándo hacerlo. Una recomendación común es elegir quitar los índices según el porcentaje de incremento que se espera que la carga ocasione y los tipos de índices que haya en la tabla: Si tiene un único índice clúster en la tabla, no lo quite. Los datos de la tabla se ordenan por esta clave y el tiempo que se tardará en quitarla, insertarla y volver a crearla prácticamente nunca será menor que si la carga se efectúa con el índice clúster. Si tiene un solo índice no clúster en la tabla, considere quitarlo si la carga representa cerca de un incremento de un cien por cien en el tamaño de los datos. Se trata de una regla general, no exacta, pero es probable que no merezca la pena quitar y volver a generar el índice si no va a duplicar el tamaño de la tabla. Si tiene varios índices en la tabla, es un poco más difícil formular una regla general. Tiendo a pensar en el intervalo de incremento del 10 por ciento. Por ejemplo, las cargas menores del 10% del volumen actual probablemente podrían dejar los índices en su lugar. Pero lo mejor es experimentar y considerar los resultados. Si va a cargar en una tabla que tenga otra actividad, tendrá que idear una estrategia. Las cargas masivas probablemente bloquearán la mayor parte de la tabla de destino, si no toda. Por tanto, es aconsejable que se asegure de que es correcto, o utilice particiones. Si tiene que cargar con una base de datos operativa, es posible que pueda configurar una partición en la que realizar la carga de modo que los datos operativos en ese momento puedan seguir activos. Si la carga va a ser lenta, es conveniente que se asegure de que tiene en cuenta qué otra actividad va a ocurrir en esa tabla o partición, y que nada más va a competir con ellas. Como guía excelente sobre el aumento del rendimiento en las cargas masivas o particiones, busque el artículo de SQLCAT que trata sobre la guía de rendimiento de la carga de datos, disponible en MSDN. Además, al realizar recargas, asegúrese de usar TRUNCATE y no DELETE para borrar los datos, de modo que la eliminación no sea transaccional. Los destinos que usan una conexión de red también está sujetos a los mismos problemas de red que he descrito antes. Busque cómo incrementar el tamaño de paquete y habilitar "tramas gigantes" en la red para conseguir un tiempo de carga óptimo.
15:02	Bien, concluimos la segunda parte de esta serie dedicada al rendimiento. Agradezco especialmente a Thomas toda valiosa información que nos ha proporcionado y que ha servido como base para esta serie de vídeos, y a Carla y a Douglas toda la ayuda prestada en su elaboración. Para obtener más información acerca de estos temas, consulte Diez recomendaciones importantes para SQL Server Integration Services. Gracias por su atención. No olvide ver las otras tres partes de esta serie de vídeos titulada SSIS: diseñar y ajustar el rendimiento. Esperamos que esta información le haya resultado valiosa. Estamos muy interesados en su opinión. En la página principal del vídeo, busque el vínculo para valorar la experiencia y aportar sus comentarios en la esquina superior derecha.

Ajustar el flujo de datos de paquetes SSIS en la empresa (vídeo de SQL Server)

Resumen del vídeo

Menciones a participantes en el vídeo

Transcripción del vídeo

Vea también

Otros recursos

Ayuda e información

Recursos adicionales