¿Cómo puedo hacer que mi proceso SSIS consuma más recursos y se ejecute más rápido?

Question

Tengo un proceso ETL diario en SSIS que construye mi almacén para que podamos proporcionar informes día tras día.

Tengo dos servidores: uno para SSIS y otro para la base de datos de SQL Server. El servidor SSIS (SSIS-Server01) es una caja de 8 CPU y 32 GB de RAM. La base de datos de SQL Server (DB-Server) es otra caja de 8 CPU y 32 GB de RAM. Ambas son máquinas virtuales VMWare.

En su forma demasiado simplificada, SSIS lee 17 millones de filas (aproximadamente 9 GB) de una sola tabla en el servidor DB, las desvincula en 408 millones de filas, realiza algunas búsquedas y un montón de cálculos, y luego las agrega nuevamente en aproximadamente 8 millones de filas. que se escriben en una tabla nueva en el mismo servidor DB cada vez (esta tabla luego se moverá a una partición para proporcionar informes diarios).

Tengo un bucle que procesa 18 meses de datos a la vez, un total de 10 años de datos. Elegí 18 meses según mi observación del uso de RAM en el servidor SSIS: a los 18 meses consume 27 GB de RAM. Si es superior a eso, SSIS comienza a almacenar en búfer en el disco y el rendimiento cae en picada.

Aquí está mi flujo de datos.http://img207.imageshack.us/img207/4105/dataflow.jpg

estoy usandoDistribuidor de datos equilibrados de Microsoftpara enviar datos por 8 rutas paralelas para maximizar el uso de recursos. Hago un sindicato antes de empezar a trabajar en mis agregaciones.

Aquí está el gráfico del administrador de tareas del servidor SSIS.

Aquí hay otro gráfico que muestra las 8 CPU individuales.

Como puede ver en estas imágenes, el uso de la memoria aumenta lentamente hasta aproximadamente 27 G a medida que se leen y procesan más y más filas. Sin embargo, el uso de la CPU es constante alrededor del 40%.

El segundo gráfico muestra que solo utilizamos 4 (a veces 5) CPU de 8.

Estoy tratando de hacer que el proceso se ejecute más rápido (solo usa el 40% de la CPU disponible).

¿Cómo hago para que este proceso se ejecute de manera más eficiente (menos tiempo, más recursos)?

Answer 1

Después de las buenas sugerencias hechas por bilinkc, y sin saber dónde está el cuello de botella, probaría algunas cosas más.

Como ya señaló, debe trabajar en el paralelismo, no procesar más datos (meses) en el mismo flujo de datos. Ya ha realizado transformaciones que se ejecutan en paralelo, pero el origen y el destino (así como la agregación) no se ejecutan en paralelo. Así que lea hasta el final y tenga en cuenta que también debe hacer que se ejecuten en paralelo para utilizar la potencia de su CPU. Y no olvides que tú eresmemoria limitada (no se puede agregar un número infinito de meses en un lote), por lo que el camino a seguir ("ampliación horizontal") es obtener una gran cantidad de datos, procesarlos y colocarlos en la base de datos de destino lo antes posible.. Esto requiere eliminar los componentes comunes (una fuente, una unión Todo) porque cada fragmento de datos está limitado a la velocidad de esos componentes comunes.

Optimización relacionada con la fuente:

• pruebe con múltiples fuentes (y destinos) en el mismo flujo de datos en lugar de Balanced Data Distributor: utiliza un índice agrupado en la columna de fecha para que su servidor de base de datos sea capaz de recuperar rápidamente datos en rangos basados ​​en fechas; Si ejecuta el paquete en un servidor diferente al que reside la base de datos, aumentará la utilización de la red.

Optimización relacionada con transformaciones:

• ¿Realmente necesitas hacer Union All antes de Aggregate? De lo contrario, eche un vistazo a la optimización relacionada con el destino con respecto a múltiples destinos.
• ColocarClaves, KeyScale y AutoExtendFactorpara que el componente Agregado evite volver a aplicar hash: si estas propiedades están configuradas incorrectamente, verá una advertencia durante la ejecución del paquete; tenga en cuenta que predecir valores óptimos es más fácil para lotes de un número fijo de meses que para un número infinito (como en su caso 18 y mayores)
• Considere agregar y (des)pivotar en SQL Server en lugar de hacerlo en el paquete SSIS: SQL Server supera a Integration Services en estas tareas; Por supuesto, la lógica de transformaciones puede ser tal que prohíba agregar antes de realizar algunas transformaciones en el paquete.
• Si puede agregar (y pivotar/desvincular) (por ejemplo) datos mensuales en la base de datos, intente hacerlo en la consulta de origen o en la base de datos de destino con SQL; dependiendo de su entorno, escribir en una tabla separada en la base de datos de destino, crear un índice, SELECCIONAR EN y agregar con SQL puede ser más rápido que hacerlo en un paquete; tenga en cuenta que paralelizar dichas actividades ejercerá mucha presión sobre su almacenamiento
• Tienes una multidifusión al final; No sé cuántas filas llegan allí, pero considere lo siguiente: escriba en el destino a la derecha (en la captura de pantalla) y luego complete los registros en el destino a la izquierda en la consulta SQL (para eliminar la segunda agregación y liberar recursos - SQL Server probablemente lo hará mucho más rápido)

Optimización relacionada con el destino:

• usarDestino del servidor SQLsi es posible (el paquete debe ejecutarse en el mismo servidor que la base de datos y la base de datos de destino debe ser SQL Server); tenga en cuenta que requiere una coincidencia exacta del tipo de datos de las columnas (canalización -> columnas de la tabla)
• considere establecerModelo de recuperacióna Simple en su base de datos de destino (almacén de datos)
• paralelizar destinos: en lugar de unir todos + agregado + destino, use agregados separados y destinos separados (en la misma tabla); Aquí deberías considerarfraccionamientosu tabla de destino y colocar particiones en grupos de archivos separados; si procesa datos mes a mes, haga particiones por mes y use conmutación de partición

Parece que no tenía claro qué camino tomar con el paralelismo. Puedes probar:

• poner múltiples fuentes en un solo flujo de datos requiere que usted copie y pegue la lógica de transformación y el destino para cada fuente
• ejecutar múltiples flujos de datos en paralelo donde cada flujo de datos procesa solo un mes
• ejecutar múltiples paquetes en paralelo donde cada paquete tiene un flujo de datos que procesa solo un mes; y un paquete maestro para controlar la ejecución de cada paquete (mes); esta es la forma preferida porque probablemente ejecutará el paquete solo durante un mes una vez que entre en producción.
• o igual que el anterior pero con Distribuidor de Datos Balanceados y Unión Todo y Agregado

Antes de hacer cualquier otra cosa, es posible que desee hacer una prueba rápida: obtenga su paquete original, cámbielo para que use 1 mes, haga una copia exacta que procese otro mes y ejecute esos paquetes en paralelo. Compárelo con el procesamiento de su paquete original de 2 meses. Haga lo mismo con 2 paquetes separados de 6 meses y un paquete único de 12 meses a la vez. Debería ejecutar su servidor con el uso total de la CPU.

Trate de no paralelizar demasiado porque tendrá varias escrituras en el destino, por lo que no desea iniciar 18 paquetes mensuales paralelos, sino 3 o 4 para comenzar.

Y, por último, creo firmemente que las presiones de E/S de memoria y destino son las que deben eliminarse.

Por favor infórmenos sobre su progreso.

Answer 2

UsarExplorador de procesospara revelar más uso de recursos (memoria e IO). Tenga en cuenta que el gráfico Disk-IO puede ser un poco engañoso ya que los picos en el gráfico a menudo se deben a las capacidades de almacenamiento en caché de los discos duros, por lo que cuando el disco IO es el cuello de botella, no siempre se revela inmediatamente en el gráfico.

En algunos casos, puede beneficiarse instalando una unidad RAM y colocando los directorios temporales allí. He utilizado con éxitoÉstepara reducir el tiempo que nuestra máquina de compilación solía realizar pruebas de compilación y ejecución nocturnas completas. Sin embargo, no estoy seguro de si SSIS se beneficiaría.

Answer 3

(volviendo a publicar mi respuesta inicial, no he tenido en cuenta BDD)

Al final del día, todo el procesamiento está sujeto a uno de cuatro factores

• Memoria
• UPC
• Disco
• Red

El primer paso es identificar cuál es el factor limitante y luego determinar si se puede influir en él (adquirir más o reducir su uso).

Opciones de componentes

La razón por la que la memoria de su servidor se agota cuando pasa más de 18 meses está relacionada con la razón por la que tarda tanto en procesarse. ElTransformaciones de pivote y agregadoson componentes asincrónicos. Cada fila que proviene del componente fuente tiene N bytes de memoria asignados. Ese mismo depósito de datos visita todas las transformaciones, aplica sus operaciones y se vacía en el destino. Ese depósito de memoria se reutiliza una y otra vez.

Cuando un componente asíncrono entra en escena, la canalización se divide. El depósito que transportaba esa fila de datos ahora debe vaciarse en un depósito nuevo para completar la canalización. Que copiar datos entre árboles de ejecución es una operación costosa en términos de tiempo de ejecución y memoria (podría duplicarla). Esto también reduce la oportunidad de que el motor ponga en paralelo algunas de las oportunidades de ejecución mientras espera que se completen las operaciones asíncronas. Una mayor ralentización de las operaciones se debe a la naturaleza de las transformaciones. El Agregado es un componente de bloqueo total, por lo quetodolos datos deben llegar y procesarse antes de que la transformación libere una sola fila para las transformaciones posteriores.

Si es posible, ¿puedes insertar el pivote y/o el agregado en el servidor? Eso debería disminuir el tiempo dedicado al flujo de datos, así como los recursos consumidos.

Puede intentar aumentar la cantidad de operaciones paralelas que puede elegir el motor.El artículo de Jaime.,Artículo de SQL CAT

Si realmente desea saber dónde se invierte su tiempo en el flujo de datos, registre OnPipelineRowsSent para una ejecución. Entonces puedes usar estoconsultapara destrozarlo (después de sustituir sysssislog por sysdtslog90)

Transferencia de red

Según sus gráficos, no parece que la CPU o la memoria estén gravadas en ninguna de las casillas. Creo que ha indicado que el servidor de origen y de destino están en un solo cuadro, pero el paquete SSIS está alojado y procesado en otro cuadro. Está pagando un costo no insignificante por transferir esos datos por cable y viceversa. ¿Es posible procesar los datos en el servidor de origen? Necesitarías asignar más recursos a esa caja y estoy cruzando los dedos para que sea una máquina virtual grande y robusta y no sea un problema.

Si esa no es una opción, intente configurar elTamaño del paquetepropiedad del administrador de conexión al 32767 y hable con operadores de red sobre si las tramas gigantes son adecuadas para usted. Ambos consejos se encuentran en la sección Sintonice su red.

Soy un desastre con los contadores de disco, pero deberías poder ver si los tipos de espera están relacionados con el disco.