• Artículo

Mejorar el rendimiento del flujo de datos

En este tema se proporcionan sugerencias sobre cómo diseñar los paquetes de Integration Services para evitar problemas de rendimiento comunes. También proporciona información sobre las características y las herramientas que puede utilizar para solucionar problemas relacionados con el rendimiento de los paquetes.

Configurar el flujo de datos

Para configurar la tarea Flujo de datos con objeto de mejorar su rendimiento, puede configurar las propiedades de la tarea, ajustar el tamaño de los búferes y configurar el paquete para la ejecución en paralelo.

Configurar las propiedades de la tarea Flujo de datos

Nota

Las propiedades que se analizan en esta sección deben establecerse por separado para cada tarea Flujo de datos de cada paquete.

Puede configurar las siguientes propiedades de la tarea Flujo de datos; todas ellas influyen en el rendimiento:

  • Especifique las ubicaciones para el almacenamiento temporal de datos del búfer (propiedad BufferTempStoragePath) y de columnas que contengan datos de objetos binarios grandes (BLOB) (propiedad BLOBTempStoragePath). De forma predeterminada, estas propiedades contienen los valores de las variables de entorno TMP y TEMP. Es posible que desee especificar otras carpetas para colocar los archivos temporales en otra unidad de disco duro o en una más rápida, o para distribuirlos entre varias unidades. Puede especificar varios directorios delimitando los nombres de los directorios con punto y coma.

  • Defina el tamaño predeterminado del búfer que utiliza la tarea asignando un valor a la propiedad DefaultBufferSize y establezca la cantidad máxima de filas de cada búfer asignando un valor a la propiedad DefaultBufferMaxRows. El tamaño de búfer predeterminado es 10 megabytes, con un tamaño de búfer máximo de 100 megabytes. La cantidad máxima de filas es 10.000.

  • Establezca el número de subprocesos que la tarea puede usar durante la ejecución asignando un valor a la propiedad EngineThreads. Esta propiedad le sugiere al motor de flujo de datos cuántos subprocesos utilizar. El valor predeterminado es 5, con un valor mínimo de 3. Sin embargo, el motor solamente utilizará los subprocesos necesarios, independientemente del valor asignado a esta propiedad. También puede suceder que el motor utilice más subprocesos que los especificados en esta propiedad si así fuera necesario para evitar problemas de simultaneidad.

  • Indique si la tarea Flujo de datos se ejecuta en el modo optimizado (propiedad RunInOptimizedMode). El modo optimizado mejora el rendimiento quitando de flujo de datos las columnas, salidas y componentes que no se utilizan.

    Nota

    Una propiedad con el mismo nombre, RunInOptimizedMode, se puede establecer en el nivel de proyecto en Business Intelligence Development Studio para indicar que la tarea Flujo de datos se ejecuta en el modo optimizado durante la depuración. Esta propiedad del proyecto reemplaza la propiedad RunInOptimizedMode de las tareas Flujo de datos en tiempo de diseño.

Ajustar el cálculo del tamaño de los búferes

El motor de flujo de datos comienza la tarea de ajustar el tamaño de sus búferes calculando el tamaño estimado de una fila de datos. Luego multiplica el tamaño estimado de una fila por el valor de DefaultBufferMaxRows para obtener un valor de trabajo preliminar para el tamaño de los búferes.

  • Si el resultado es superior al valor de DefaultBufferSize, el motor reduce la cantidad de filas.

  • Si el resultado es inferior al tamaño de búfer mínimo calculado internamente, el motor aumenta el número de filas.

  • Si el resultado se encuentra entre el tamaño de búfer mínimo y el valor de DefaultBufferSize, el motor ajusta el tamaño del búfer con la mayor proximidad posible al tamaño estimado de una fila multiplicado por el valor de DefaultBufferMaxRows.

Al comenzar a probar el rendimiento de las tareas de flujo de datos, utilice los valores predeterminados de DefaultBufferSize y DefaultBufferMaxRows. Habilite el registro de la tarea de flujo de datos y seleccione el evento BufferSizeTuning para ver cuántas filas contiene cada búfer.

Antes de empezar a ajustar el tamaño de los búferes, la mejora más importante que se puede hacer consiste en reducir el tamaño de cada fila quitando las columnas innecesarias y configurando los tipos de datos de manera adecuada.

Cuando haya suficiente memoria disponible, deberá usar una menor cantidad de búferes grandes, en lugar de una mayor cantidad de búferes pequeños. Es decir, se puede mejorar el rendimiento reduciendo el número total de búferes necesarios para contener los datos, e incluyendo en un búfer tantas filas de datos como sea posible. Para determinar la cantidad óptima de búferes y sus tamaños, realice pruebas con los valores de DefaultBufferSize y DefaultBufferMaxRows mientras supervisa el rendimiento y la información que proporciona el evento BufferSizeTuning.

No aumente el tamaño de los búferes hasta un punto en el que se produzca la paginación del disco. La paginación del disco afecta al rendimiento más que al hecho de no optimizar el tamaño de los búferes. Para determinar si se está produciendo la paginación, supervise el contador de rendimiento "Búferes puestos en cola" en el complemento Rendimiento de Microsoft Management Console (MMC). 

Configurar el paquete para la ejecución en paralelo

La ejecución en paralelo mejora el rendimiento en los equipos que tienen varios procesadores físicos o lógicos. Para admitir la ejecución en paralelo de tareas diferentes del paquete, Integration Services utiliza dos propiedades: MaxConcurrentExecutables y EngineThreads.

La propiedad MaxConcurrentExcecutables

La propiedad MaxConcurrentExecutables es una propiedad del propio paquete. Esta propiedad define cuántas tareas se pueden ejecutar simultáneamente. El valor predeterminado es -1, que significa el número de procesadores físicos o lógicos más 2.

Para entender cómo funciona esta propiedad, considere un paquete de ejemplo que tiene tres tareas Flujo de datos. Si establece MaxConcurrentExecutables en 3, las tres tareas Flujo de datos se pueden ejecutar simultáneamente. Sin embargo, suponga que cada tarea Flujo de datos tiene 10 árboles de ejecución de origen a destino. El hecho de establecer MaxConcurrentExecutables en 3 no garantiza que los árboles de ejecución de cada tarea Flujo de datos se ejecuten en paralelo.

La propiedad EngineThreads

La propiedad EngineThreads es una propiedad de cada tarea Flujo de datos. Esta propiedad define cuántos subprocesos puede crear y ejecutar en paralelo el motor de flujo de datos. La propiedad EngineThreads se aplica por igual tanto a los subprocesos de origen que crea el motor de flujo de datos para los orígenes como a los subprocesos de trabajo que crea el motor para las transformaciones y los destinos. Por consiguiente, establecer EngineThreads en 10 significa que el motor puede crear hasta diez subprocesos de origen y hasta diez subprocesos de trabajo.

Para entender cómo funciona esta propiedad, considere el paquete de ejemplo, que tiene tres tareas Flujo de datos. Cada una de las tareas Flujo de datos contiene diez árboles de ejecución de origen a destino. Si establece EngineThreads en 10 en cada tarea Flujo de datos, es posible que los 30 árboles de ejecución se ejecuten simultáneamente.

Nota

Una discusión sobre los subprocesos queda fuera del ámbito de este tema. Sin embargo, la regla general consiste en no ejecutar en paralelo un número de subprocesos superior al número de procesadores disponibles. Ejecutar en paralelo un número de subprocesos superior al número de procesadores disponibles puede afectar al rendimiento debido a los continuos cambios de contexto entre los subprocesos.

Configurar cada uno de los componentes de flujo de datos

Hay algunas directrices generales que permiten configurar cada uno de los componentes de flujo de datos con objeto de mejorar el rendimiento. También hay instrucciones específicas para cada tipo de componente de flujo de datos: origen, transformación y destino.

Directrices generales

Hay dos directrices generales que no dependen del componente de flujo de datos y que debería seguir para mejorar el rendimiento: optimizar las consultas y evitar las cadenas innecesarias.

Optimizar las consultas

Varios componentes de flujo de datos utilizan consultas, ya sea al extraer datos de los orígenes o en operaciones de búsqueda para crear tablas de referencia. La consulta predeterminada utiliza la sintaxis SELECT * FROM <nombreDeTabla>. Este tipo de consulta devuelve todas las columnas de la tabla de origen. Disponer de todas las columnas en tiempo de diseño permite elegir cualquier columna como columna de búsqueda, de paso a través o de origen. Sin embargo, después de seleccionar las columnas que se deben utilizar, debe revisar la consulta para que incluya únicamente las columnas seleccionadas. El hecho de quitar las columnas superfluas aumenta la eficacia de flujo de datos de un paquete, ya que al haber menos columnas se crea una fila más pequeña. Una fila más pequeña significa que caben más filas en un búfer y que cuesta menos trabajo procesar todas las filas del conjunto de datos.

Para crear una consulta, puede escribirla o utilizar el Generador de consultas.

Nota

Al ejecutar un paquete en Business Intelligence Development Studio, la pestaña Progreso del Diseñador SSIS muestra una lista de las advertencias. Entre estas advertencias se incluye la identificación de cualquier columna de datos que un origen pone a disposición de flujo de datos, pero que no utilizan posteriormente los componentes de flujo de datos de nivel inferior. Se puede utilizar la propiedad RunInOptimizedMode para eliminar esas columnas automáticamente.

Evitar ordenaciones innecesarias

La ordenación es una operación inherentemente lenta, y evitar la ordenación innecesaria puede mejorar el rendimiento de flujo de datos del paquete.

A veces, los datos de origen se ordenan antes de que los utilice un componente de nivel inferior. Tal ordenación puede producirse cuando la consulta SELECT utiliza una cláusula ORDER BY o cuando los datos se insertan ordenados en el origen. Para tales datos de origen preordenados, puede proporcionar una sugerencia indicando que los datos están ordenados, con lo que evitará el uso de una transformación Ordenar para satisfacer los requisitos de ordenación de ciertas transformaciones de nivel inferior. Por ejemplo, las transformaciones Mezclar y Combinación de mezcla requieren entradas ordenadas. Para proporcionar una sugerencia indicando que los datos están ordenados, deberá realizar las tareas siguientes:

  • Establecer la propiedad IsSorted en la salida de un componente de flujo de datos de nivel superior en True.

  • Especificar las columnas de criterio de ordenación en las que se basa el orden de los datos.

Para obtener más información, vea Cómo ordenar datos para las transformaciones Mezclar y Combinación de mezcla.

Si es necesario ordenar los datos en el flujo de datos, puede mejorar el rendimiento diseñando el flujo de datos de forma que utilice el menor número posible de operaciones de ordenación. Por ejemplo, si el flujo de datos utiliza una transformación Multidifusión para copiar el conjunto de datos, ordene el conjunto de datos una vez antes de que se ejecute la transformación Multidifusión, en lugar de ordenar varias salidas después de la transformación.

Para obtener más información, vea Transformación Ordenar, Transformación Mezclar, Transformación Combinación de mezcla y Transformación Multidifusión.

Orígenes

Origen de OLE DB

Cuando utilice un origen de OLE DB para recuperar datos de una vista, seleccione "comando SQL" como modo de acceso a los datos y escriba una instrucción SELECT. El hecho de tener acceso a los datos mediante una instrucción SELECT presenta un rendimiento mejor que seleccionar "Tabla o vista" como modo de acceso a los datos.

Transformaciones

Utilice las sugerencias de esta sección para mejorar el rendimiento de las transformaciones Agregado, Búsqueda aproximada, Agrupación aproximada, Búsqueda, Combinación de mezcla y Dimensión de variación lenta.

Transformación Agregado

La transformación Agregado incluye las propiedades Keys, KeysScale, CountDistinctKeys y CountDistinctScale. Estas propiedades mejoran el rendimiento habilitando la transformación para asignar previamente la cantidad de memoria que necesita la transformación para los datos que almacena en caché. Si conoce el número exacto o aproximado de grupos que se esperan como resultado de una operación Agrupar por, debe establecer las propiedades KeysScale y Keys, respectivamente. Si conoce el número exacto o aproximado de valores distintos que se esperan como resultado de una operación Recuento distinto, debe establecer las propiedades CountDistinctScale y CountDistinctKeys, respectivamente.

Si tiene que crear varias agregaciones en un flujo de datos, considere la posibilidad de crear varias agregaciones que utilicen una sola transformación Agregado, en lugar de crear varias transformaciones. Esto mejora el rendimiento cuando una agregación es un subconjunto de otra agregación, ya que la transformación puede optimizar el almacenamiento interno y examinar los datos entrantes una sola vez. Por ejemplo, si una agregación utiliza una cláusula GROUP BY y una agregación AVG, puede mejorar el rendimiento combinándolas en una transformación. No obstante, al realizar varias agregaciones dentro de una transformación Agregado se serializan las operaciones de agregación y, por consiguiente, el rendimiento podría no mejorar cuando haya que calcular varias agregaciones por separado.

Para obtener más información, vea Transformación Agregado.

Transformaciones Búsqueda aproximada y Agrupación aproximada

Para obtener información sobre cómo optimizar el rendimiento de las transformaciones Agrupación aproximada y Búsqueda aproximada, vea las notas del producto Fuzzy Lookup and Fuzzy Grouping in SQL Server Integration Services 2005.

Transformación Búsqueda

Puede minimizar el tamaño de los datos de referencia en memoria si escribe una instrucción SELECT que busque solo las columnas necesarias. Esta opción presenta un rendimiento mejor que seleccionar una tabla o una vista completa, lo que devuelve una gran cantidad de datos innecesarios.

Transformación Combinación de mezcla

La transformación Combinación de mezcla incluye la propiedad MaxBuffersPerInput, que especifica el número máximo de búferes que pueden estar activos al mismo tiempo para cada entrada. Puede utilizar esta propiedad para optimizar la cantidad de memoria que consumen los búferes y, en consecuencia, el rendimiento de la transformación. Un mayor número de búferes da como resultado una mayor cantidad de memoria utilizada por la transformación y un mejor rendimiento. El valor predeterminado de MaxBuffersPerInput es 5, que es el número de búferes que mejor funciona en la mayoría de los escenarios. Para optimizar el rendimiento, conviene utilizar un número ligeramente diferente de búferes, como 4 o 6. Si es posible, debería evitar utilizar un número de búferes demasiado pequeño. Por ejemplo, el establecimiento de MaxBuffersPerInput en 1 en lugar de 5 tiene un impacto considerable en el rendimiento. Asimismo, no debería establecer MaxBuffersPerInput en 0 o un valor menor. Este intervalo de valores significa que no se produce ninguna limitación y, en función de la carga de datos y la cantidad de memoria disponible, es posible que no se complete el paquete.

Para evitar un interbloqueo, la transformación Combinación de mezcla puede incrementar temporalmente el número de búferes que utiliza por encima del valor de MaxBuffersPerInput. Una vez que se resuelve la situación de interbloqueo, MaxBuffersPerInput regresa a su valor configurado.

Para obtener más información, vea Transformación Combinación de mezcla.

Transformación Dimensión de variación lenta

El Asistente para dimensiones variables y la transformación Dimensión de variación lenta son herramientas de uso general que satisfacen las necesidades de la mayoría de los usuarios. Sin embargo, el flujo de datos que genera el asistente no está optimizado en cuanto a rendimiento.

Normalmente, los componentes más lentos de la transformación Dimensión de variación lenta son las transformaciones Comando de OLE DB que ejecutan cláusulas UPDATE sobre las filas de una en una. Por consiguiente, la manera más efectiva de mejorar el rendimiento de la transformación Dimensión de variación lenta consiste en reemplazar las transformaciones Comando de OLE DB. Puede reemplazar estas transformaciones por componentes de destino que guarden todas las filas que hay que actualizar en una tabla de ensayo. A continuación, puede agregar una tarea Ejecutar SQL que ejecute una cláusula Transact-SQL UPDATE basada en un solo conjunto sobre todas las filas al mismo tiempo.

Los usuarios avanzados pueden diseñar un flujo de datos personalizado para el procesamiento de dimensiones de variación lenta que esté optimizado para las dimensiones de gran tamaño. Para obtener una descripción y un ejemplo de este método, consulte la sección "Unique dimension scenario" en las notas del producto Project REAL: Business Intelligence ETL Design Practices.

Destinos

Para lograr un mejor rendimiento con los destinos, considere la posibilidad de utilizar un destino de SQL Server y de probar el rendimiento del destino.

Destino de SQL Server

Cuando un paquete cargue datos en una instancia de SQL Server en el mismo equipo, utilice un destino de SQL Server. Este destino está optimizado para cargas masivas de alta velocidad.

Probar el rendimiento de los destinos

Es posible que guardar datos en los destinos lleve más tiempo del esperado. Para identificar si esto se debe a que el destino no es capaz de procesar los datos con suficiente rapidez, puede sustituir el destino por una transformación Recuento de filas temporalmente. Si el rendimiento mejora de forma significativa, es probable que el destino que carga los datos sea la causa de la tardanza.

Supervisar el rendimiento del paquete

Integration Services incluye herramientas y características que se pueden utilizar para supervisar el rendimiento de un paquete. Por ejemplo, el registro captura información de un paquete en tiempo de ejecución y los contadores de rendimiento permiten supervisar el motor de flujo de datos. Aproveche las siguientes sugerencias para determinar qué partes del paquete afectan en mayor medida al rendimiento. 

Revisar la información de la pestaña Progreso

El Diseñador SSIS proporciona información sobre el flujo de control y sobre el flujo de datos al ejecutar un paquete en Business Intelligence Development Studio. En la pestaña Progreso se muestran las tareas y los contenedores en orden de ejecución; incluye las horas de inicio y finalización, las advertencias y los mensajes de error de cada tarea y contenedor, incluido el paquete en sí. También se muestran los componentes de flujo de datos en el orden de ejecución, y se incluye información sobre su progreso, mostrado como porcentaje finalizado, y el número de filas procesadas.

Para habilitar o deshabilitar la presentación de mensajes en la pestaña Progreso, active o desactive la opción Informe de progreso de depuración del menú SSIS. La deshabilitación de los informes de progreso puede ayudar a mejorar el rendimiento al ejecutar un paquete complejo en BI Development Studio.

Configurar el registro en el paquete

Integration Services incluye varios proveedores de registro que permiten a los paquetes registrar información en tiempo de ejecución en diferentes tipos de archivos o en SQL Server. Puede habilitar entradas del registro para los paquetes y objetos de paquete individuales, como las tareas y los contenedores. Integration Services incluye una amplia variedad de tareas y contenedores, y cada uno de ellos tiene su propio conjunto de entradas descriptivas del registro. Por ejemplo, un paquete que incluya una tarea Ejecutar SQL puede escribir una entrada del registro que muestre la instrucción SQL ejecutada por la tarea, incluidos los valores de los parámetros para la instrucción.

Las entradas del registro incluyen información, como la hora de inicio y fin de los paquetes y objetos de paquete, que permite identificar las tareas y contenedores que se ejecutan lentamente. Para obtener más información, vea Registrar la ejecución de paquetes, Implementar inicios de sesión en paquetes y Mensajes personalizados para registro.

Configurar el registro para las tareas Flujo de datos

La tarea Flujo de datos proporciona varias entradas del registro personalizadas que pueden utilizarse para supervisar y ajustar el rendimiento. Por ejemplo, puede supervisar componentes que puedan causar pérdidas de memoria o realizar un seguimiento del tiempo que lleva ejecutar un componente determinado. Para obtener una lista de las entradas del registro personalizadas y ejemplos de la salida del registro, vea Tarea Flujo de datos.

Usar el evento PipelineComponentTime

Quizás la entrada de registro personalizada más útil es el evento PipelineComponentTime. Esta entrada de registro notifica el número de milisegundos que cada componente del flujo de datos emplea en cada uno de los cinco pasos de procesamiento principales. En la tabla siguiente se describen estos pasos de procesamiento. Los programadores de Integration Services reconocerán estos pasos como los métodos principales de PipelineComponent.

Paso

Descripción

Validate

El componente comprueba los valores de configuración y los valores de propiedad válidos.

PreExecute

El componente realiza el procesamiento único antes de empezar a procesar filas de datos.

PostExecute

El componente realiza el procesamiento único después de haber procesado todas las filas de datos.

ProcessInput

El componente de transformación o de destino procesa las filas de datos entrantes que un origen o una transformación de nivel superior le han pasado.

PrimeOutput

El componente de origen o de transformación llena los búferes con los datos que se van a pasar a un componente transformación o de destino de nivel inferior.

Cuando habilita el evento PipelineComponentTime, Integration Services registra un mensaje para cada paso de procesamiento realizado por cada componente. Las entradas de registro siguientes muestran un subconjunto de los mensajes que el ejemplo de paquete CalculatedColumns de Integration Services registra:

The component "Calculate LineItemTotalCost" (3522) spent 356 milliseconds in ProcessInput.

The component "Sum Quantity and LineItemTotalCost" (3619) spent 79 milliseconds in ProcessInput.

The component "Calculate Average Cost" (3662) spent 16 milliseconds in ProcessInput.

The component "Sort by ProductID" (3717) spent 125 milliseconds in ProcessInput.

The component "Load Data" (3773) spent 0 milliseconds in ProcessInput.

The component "Extract Data" (3869) spent 688 milliseconds in PrimeOutput filling buffers on output "OLE DB Source Output" (3879).

The component "Sum Quantity and LineItemTotalCost" (3619) spent 141 milliseconds in PrimeOutput filling buffers on output "Aggregate Output 1" (3621).

The component "Sort by ProductID" (3717) spent 16 milliseconds in PrimeOutput filling buffers on output "Sort Output" (3719).

Estas entradas de registro muestran que la tarea de flujo de datos empleó la mayor parte del tiempo en los pasos siguientes, presentados en orden descendente:

  • El origen de OLE DB que se denomina "Extract Data" empleó 688 ms en cargar datos.

  • La transformación Columna derivada que se denomina "Calculate LineItemTotalCost" empleó 356 ms en la realización de cálculos en las filas entrantes.

  • La transformación Agregado que se denomina "Sum Quantity and LineItemTotalCost" empleó un tiempo combinado de 220 ms (141 ms en PrimeOutput y 79 ms en ProcessInput) en las operaciones de realizar cálculos y pasar los datos a la transformación siguiente.

Supervisar el rendimiento del motor de flujo de datos

Integration Services incluye un conjunto de contadores de rendimiento para supervisar el rendimiento del motor de flujo de datos. Por ejemplo, puede realizar un seguimiento de la cantidad total de memoria, en bytes, que utilizan todos los búferes y comprobar si la memoria de los componentes es insuficiente. Un búfer es un bloque de memoria que un componente utiliza para almacenar datos. Para obtener más información, vea Supervisar el rendimiento del motor de flujo de datos.

Recursos externos

Icono de Integration Services (pequeño) Manténgase al día con Integration Services

Para obtener las descargas, los artículos, los ejemplos y los vídeos más recientes de Microsoft, así como una selección de soluciones de la comunidad, visite la página de Integration Services en MSDN:


Para recibir notificaciones automáticas de estas actualizaciones, suscríbase a las fuentes RSS disponibles en la página.