SQLServerAsyncExecProcedures

Introducción

En muchas ocasiones los procesos o sentencias programadas en T-SQL no requieren de una ejecución secuencial, es decir, que una sentencia no necesite de los resultados de la sentencia anterior. Es el caso de la creación de índices, o del traspaso de información desde una base de datos a otra.

Insert Bulk Bestia ofrece, bajo determinados tipos de licencia, una librería con un conjunto de procedimientos almacenados que permiten definir los trabajos que se desean realizar en paralelo y cómo realizar dichos trabajos.

Dicho esto, se ha de tener en cuenta que la ejecución de procesos en paralelo dentro de un Dominio de SQL Server no es sencillo. Si a ello se le añade la posibilidad de utilizar transacciones resulta ser una tarea ardua y compleja, dado que las transacciones de SQL Server no permiten la ejecución de procesos en paralelo. Para ello, se ha montado un artificio que puede ser útil en la mayoría de los casos, pero que no cubre todas las posibilidades de error.

Sin embargo, como se verá en el capítulo siguiente, con tesón, paciencia, y mucho ensayo y error, se puede llegar a definir procesos que se recuperan ante fallos. Sin embargo, hoy en día, con las políticas de sustitución de personal inteligente y caro por personal barato, máquinas más potentes y herramientas más tontas que inteligentes, es muy difícil llegar a este tipo de soluciones. Así que, para aquellos que tienen una sana curiosidad e ilimitada capacidad de aprendizaje, se ofrece este capítulo y el siguiente. Solamente pedir disculpas a los lectores por mis pobres explicaciones, e incitarles a que ayuden con sus comentarios a mejorarlas e incluso a mejorar el proceso. No duden en utilizar la página de Contacto.

Ayuda en Base de Datos

En primer lugar, indicar que todos los procedimientos almacenados pertenecientes a esta librería tienen su réplica correspondiente terminada con el prefijo "_Help" que muestran una escueta ayuda en panel de "Mensajes" en la ventana de Resultados de SSMS y en la tabla 'ibb.Log_Errores'.

ibbclr.Parallel_Help

ibbclr.Parallel_Help - 1

ibbclr.Parallel_Help - 2

Estrategia de 'SQLServerAsyncExecProcedures'

En la figura siguiente se esquematiza la organización que 'SQLServerAsyncExecProcedures' hace de los trabajos a ejecutar.

Estructura Paralelo - 1

Estructura Paralelo - 1

Paralelo

Un "Paralelo' es un conjunto independiente de 'Trabajos' a ejecutar. 'SQLServerAsyncExecProcedures' puede almacenar una lista de 'Paralelos' y el usuario decidirá cuando se ejecuta cada 'Paralelo'.

Un 'Paralelo' se identifica por un nombre único de 256 caracteres. Este nombre será el que aparezca en el campo 'Clasificacion_Log_Error' de la tabla 'ibb.Log_Errores' cuando se inserte un registro que afecte al 'Paralelo'. Durante la ejecución, es en esta tabla donde se irán insertando las trazas y los errores que se pudiesen producir en la librería. Esto no quiere decir que si se producen errores en las sentencias que se ejecutan, estos aparezcan en dicha tabla. Eso dependerá de si en dichas sentencias se hace un control de errores y estos se vuelcan a dicha tabla.

Añadir que es absurdo intentar ejecutar dos o más 'Paralelos' en paralelo, ya que se supone que un 'Paralelo' está exprimiendo al máximo los recursos de la instancia de SQL Server.

Cuando ha finalizado la ejecución de un 'Paralelo', éste desaparecerá de la lista de paralelos. Si el 'Paralelo' se ha ejecutado correctamente, éste devolverá un 0, en caso contrario un valor distinto de 0.

Por último, indicar que, si aparece un problema de SQLCLR, como que la ejecución se quede 'colgada' o entre en un bucle infinito, es posible descargar y volver a cargar el AppDomain ejecutando las siguientes instrucciones:

ALTER ASSEMBLY[SQLServerAsyncExecProcedures] WITH PERMISSION_SET = EXTERNAL_ACCESS
ALTER ASSEMBLY[SQLServerAsyncExecProcedures] WITH PERMISSION_SET = UNSAFE

Opciones de Ejecución para un Paralelo

Estas opciones se establecen con el procedimiento almacenado 'ibbclr.Parallel_SetOptions'. Las Transacciones serán tratadas más adelante, aunque aquí se describen los parámetros asociados a las Transacciones para un 'Paralelo'.

Los parámetros que admite el procedimiento almacenado son:

  • parallelName: NVARCHAR(256). Obligatorio. Identificador del 'Paralelo'. Cadena de 256 caracteres como máximo.
  • transactionLevel: NVARCHAR(20). Obligatorio. Identificador del Nivel de Aislamiento de las Transacciones. Cadena de 20 caracteres como máximo. Los valores posibles son:
    • 'Serializable' Volatile data can be read but not modified, and no new data can be added during the transaction.
    • 'RepeatableRead' Volatile data can be read but not modified during the transaction. New data can be added during the transaction.
    • 'ReadCommitted' Volatile data cannot be read during the transaction, but can be modified.
    • 'ReadUncommitted' (Default) Volatile data can be read and modified during the transaction.
    • 'Snapshot' Volatile data can be read. Before a transaction modifies data, it verifies if another transaction has changed the data after it was initially read.
                        If the data has been updated, an error is raised. This allows a transaction to get to the previously committed value of the data.
                        Transactions with IsolationLevel Snapshot cannot be promoted.
    • 'Chaos' The pending changes from more highly isolated transactions cannot be overwritten.
    • 'Unspecified' A different isolation level than the one specified is being used, but the level cannot be determined. An exception is thrown if this value is set.
  • maxTimeOutTransaction: INT. Obligatorio. Tiempo en minutos. Al ejecutar un Trabajo en una Transacción, ésta se abre y permanece abierta hasta que finalizan todos los Trabajos. Se deberá definir un valor tal, que sea superior al tiempo estimado en que tardará el proceso desde que se abre la primera Transacción hasta que termina el Paralelo.
  • user: NVARCHAR(512). Usuario. Opcional. Usuario con el que se abrirán las conexiones a la Base de Datos. Puede ser necesario para usar Servidores Vinculados.
  • pwd: NVARCHAR(512). Contraseña. Opcional. Contraseña para el Usuario.
  • srv: NVARCHAR(512). Servidor. Opcional. Servidor o dirección IP en el caso de que haya problemas con la cadena de conexión.
  • bd: NVARCHAR(512). Base de Datos. Opcional. Base de Datos en el caso de que haya problemas con la cadena de conexión.
  • connectTimeout: INT. Opcional. Tiempo de espera en segundos antes de saltar un error al establecer la conexión. Por defecto se establece a 60 segundos.

La utilidad de estos últimos parámetros se verá en el capítulo siguiente, donde se detalla un ejemplo de trasvase de información entre servidores vinculados.

Lote de Trabajos

Un lote de trabajos es simplemente una agrupación de trabajos que se ejecuta con el mismo grado de paralelismo. Los lotes se identifican con un número y cuando se ejecuta el 'Paralelo' los lotes se ejecutan secuencialmente en orden ascendente.

Con el procedimiento almacenado 'ibbclr.Parallel_SetOption_MaxTaskBatch' se define el grado de paralelismo de un determinado lote. Los parámetros son:

  • parallelName: NVARCHAR(256). Obligatorio. Identificador del 'Paralelo'. Cadena de 256 caracteres como máximo.
  • batch: SMALLINT. Obligatorio. Identificador del Lote de Trabajos.
  • maxCurrentTask: TINYINT. Obligatorio. Valor entre 1 y 64. Nº máximo de Trabajos en Paralelo a ejecutar para el Lote.

Se recomienda un 'maxCurrentTask' semejante al número de procesadores asignados a SQL Server.

Se recomienda especificar OPTION(MAXDOP 1) en las Querys de cada Trabajo que se ejecuten en Paralelo. Como son procesos que se van a ejecutar en paralelo, cada núcleo va a estar muy ocupado con un determinado proceso. De esta forma, cada procesador tiene asignado su Trabajo, evitando los bloqueos de CPU.

OPTION(MAXDOP 1) es prácticamente imprescindible en sentencias MERGE, ya que la paralelización de este tipo de Querys bloquearán la CPU.

Grupos de Trabajos

Cada Lote agrupa los Trabajos a realizar en tres grupos:

  • Iniciales: Opcional. Grupo de Trabajos que se ejecutarán al inicio de Lote. La secuencia seguida es el orden en que se hayan definido.
  • Paralelo: Opcional. Grupo de Trabajos que se ejecutarán en paralelo. La secuencia de arranque de los trabajos sigue el orden en que se han definido, por lo que se recomienda poner en primer lugar los trabajos de mayor duración, ya que así, en paralelo, se ejecutarán los trabajos de menos duración. Habrá tantos trabajos ejecutándose como se haya indicado en 'maxCurrentTask' para el Lote al que pertenecen.
  • Finales: Opcional. Grupo de Trabajos que se ejecutarán al inicio de Lote. La secuencia seguida es el orden en que se hayan definido.

Trabajo

Un trabajo es el conjunto de sentencias que se desean ejecutar. Se define mediante el procedimiento almacenado 'ibbclr.Parallel_AddJob', cuyos parámetros de entrada son:

  • parallelName: NVARCHAR(256). Obligatorio. Identificador del 'Paralelo'. Cadena de 256 caracteres como máximo.
  • batch: SMALLINT. Obligatorio. Identificador del Lote de Trabajos.
  • grupo: NVARCHAR(15). Obligatorio. Grupo dentro del Lote al que se quiere inscribir el trabajo. Los valores permitidos son: 'Iniciales', 'Paralelo' o 'Finales'.
  • jobName: NVARCHAR(1024). Obligatorio. Cadena de 1024 caracteres como máximo. Identificador del Trabajo. Debe ser un nombre único dentro del Paralelo. Este nombre será el que aparezca en el campo 'Clasificacion_Log_Error' de la tabla 'ibb.Log_Errores' cuando se inserte un registro que afecte al 'Trabajo', ya sea una traza o un mensaje de error.
  • withTransaction: BIT. Obligatorio. 1 El trabajo tiene su propia Transacción. 0 El Trabajo no tiene Transacción.
  • jobTSQL: NVARCHAR(MAX). Obligatorio. Script T-SQL que se desea ejecutar. El Resultado de la ejecución se debe devolver en un parámetro llamado @error de tipo INT y de salida.
    • = 0 Debe indicar que el Trabajo se ha realizado correctamente. Al final de la ejecución del Paralelo, si todos los Trabajos se han ejecutado correctamente, se hará un Commit de las Transacciones abiertas.
    • != 0 Debe indicar que se han producido Errores en el Trabajo. Hará que se cancelen los Trabajos que se encuentren en ejecución, se suspenda la ejecución del resto de trabajos y se hará un Rollback de las Transacciones abiertas.

Cada Trabajo se ejecuta en una nueva conexión a la Base de Datos. Es la forma en que Insert Bulk Bestia paraleliza los 'Trabajos'. Para todas las conexiones se establecen las siguientes opciones:

    SET ANSI_DEFAULTS OFF;
    SET ANSI_WARNINGS, ANSI_PADDING, ANSI_NULLS, ARITHABORT, QUOTED_IDENTIFIER, CONCAT_NULL_YIELDS_NULL ON;
    SET NUMERIC_ROUNDABORT, CURSOR_CLOSE_ON_COMMIT, IMPLICIT_TRANSACTIONS OFF;
    SET REMOTE_PROC_TRANSACTIONS, XACT_ABORT ON;

Las dos últimas opciones, SET REMOTE_PROC_TRANSACTIONS, XACT_ABORT ON, son prácticamente imprescindibles cuando se usan Servidores Vinculados, ya que, por un lado, las transacciones locales a SQL Server pasarán a ser transacciones distribuidas, y por otro, se necesitará que si se produce un error en el código T-SQL se revierta la transacción.

Aunque estas opciones se añaden por defecto al abrir la conexión, son muy dependientes del trabajo a realizar, del comportamiento deseado, de las Transacciones (locales y/o distribuidas) y de los errores. La opción SET REMOTE_PROC_TRANSACTIONS ON dependerá de cómo desee que se comporten las Transacciones Distribuidas con MS DTC. Así, Se recomienda realizar diferentes pruebas (con y sin errores provocados) hasta encontrar la configuración adecuada.

Parallel Remove

'ibbclr.Parallel_Remove' simplemente elimina de la lista de 'Paralelos' el 'Paralelo' indicado en su parámetro de entrada.

Transacciones

Como ya se ha comentado anteriormente, se ha montado un artificio para manejar las Transacciones, dado que SQL Server no permite la ejecución de procesos en paralelo dentro de una transacción. Este artificio puede ser útil en determinadas ocasiones, pero lo que realmente es útil es la definición de un algoritmo que permita que la base de datos se pueda recuperar ante fallos. En el capítulo siguiente se muestra un ejemplo que usa dicha técnica.

Si además de las restricciones impuestas por SQL Server para las Transacciones, se añade que éstas pueden "promocionar" de transacciones locales a transacciones distribuidas sin que el programador lo pueda controlar, la complejidad de su tratamiento, programación y pruebas crece exponencialmente.

En este apartado se intentará explicar de forma sencilla el tratamiento que Insert Bulk Bestia hace con las transacciones.

Apertura de las Conexiones a la Base de Datos

Como ya se ha indicado, cada trabajo se ejecuta en una conexión independiente. Cada conexión nueva posee unas determinadas características, de las cuales, a continuación, se enumeran las principales:

  • Base de Datos y Servidor: Los indicados en las opciones del 'Paralelo' o bien, si no se indica nada, la Base de Datos y Servidor donde se ejecuta el 'Paralelo'.
  • Usuario y Contraseña: Los indicados en las opciones del 'Paralelo' o bien, si no se indica nada, se utiliza la opción 'IntegratedSecurity' = true.
  • Pooling: False. No se usa el pooling de conexiones debido a un bug de Microsoft: Cuando se libera una conexión y pasa al Pool de conexiones, se queda con el IsolationLevel establecido, de tal forma que, si se desea cambiar el nivel de aislamiento para una nueva conexión, al coger la conexión del Pool, no se cambia dicho nivel de aislamiento, sino que se queda con el que tenía.
  • Enlist: False. Si se ha definido que un trabajo se ha de ejecutar dentro de una Transacción, en primer lugar, se crea la transacción usando la librería "System.Transactions" con las características deseadas de nivel de aislamiento y Timeout. Posteriormente se crea y se abre la conexión a la base de datos y por último se da de alta en la transacción previamente creada.
  • MultipleActiveResultSets: True
  • ConnectRetryCount: 10
  • MultiSubnetFailover: True
  • ConnectTimeout: Valor del parámetro 'connectTimeout' de las opciones del 'Paralelo'

Dado que "System.Data.SqlClient" sólo permite trabajar con transacciones locales a la instancia donde se ejecuta el 'Paralelo', se ha optado por usar la librería "System.Transactions" para poder trabajar con Transacciones Distribuidas.

Con "System.Transactions" se crea un ámbito ('TransactionScope') que permita la ejecución asíncrona ('TransactionScopeAsyncFlowOption.Enabled'). Dicho ámbito lleva asociada una Transacción, que es la que se utiliza para ejecutar el trabajo. De esta forma, se consigue la ejecución de los trabajos en paralelo.

Tratamiento de las Transacciones

Cuando se ejecuta el 'Paralelo', se ejecutan todos los lotes en orden ascendente según su identificador. En cada lote se ejecutan los trabajos 'Iniciales', 'Paralelo' y 'Finales' en dicho orden. Las Transacciones de aquellos trabajos marcados a ejecutar dentro de un transacción, se quedan almacendas en una lista a la espera de hacer un commit o rollback.

Si todos los Trabajos se ejecutaron satisfactoriamente, se hará un commit de todas las Transacciones abiertas. Mientras que, si un trabajo falla, se cancelarán todos los trabajos que se encuentren en ejecución, no se ejecutarán más trabajos, y se hará un rollback de las transacciones que se encuentren abiertas.

Por ello es de vital importancia que el parámetro 'maxTimeOutTransaction' cubra todo el tiempo que dure el 'Paralelo', ya que, en caso contrario, el administrador de transacciones comenzará a anularlas a medida que las Transacciones vayan superando este valor.

Uno de los problemas que suelen tener las Transacciones Distribuidas es que "fallan más que una escopeta de feria". Así, es imprescindible un correcto diseño del algoritmo deseado para que el tiempo que duren abiertas las transacciones sea el mínimo posible, al mismo tiempo que, también se recomienda un uso mínimo de las mismas debido a su complejidad y poca fiabilidad.

Por último, recalcar que, dependiendo del Nivel de Aislamiento, los Trabajos podrán ver/modificar los 'Volatile data' de los otros Trabajos.

RECOMENDACIONES: Pensar, codificar y probar. Volver a pensar, recodificar y volver a probar. Y así sucesivamente hasta obtener los resultados deseados. Utilizar la tabla 'ibb.Log_Errores' y el procedimiento almacenado 'ibb.Log_Errores_Inserta' dentro de las sentencias que se desean ejecutar, ya que facilitarán de manera extraordinaria la depuración del proceso.