Descripción del funcionamiento de la carga masiva
Son varios los factores que afectan al rendimiento de las operaciones de carga masiva (por ejemplo, la instrucción BULK INSERT y la utilidad bcp). En las siguientes secciones se describen estos factores y se sugieren formas de mejorar el rendimiento. Los parámetros descritos se aplican a la instrucción BULK INSERT. Existen también parámetros similares para otros métodos de carga masiva.
Método de carga masiva
SQL Server admite tres métodos de carga masiva:
La inserción masiva como un comando de Transact-SQL
La utilidad bcp
Por paquete de Integration Services
La instrucción BULK INSERT de Transact-SQL se ejecuta en el mismo proceso que SQL Server, por lo que comparte el mismo espacio de direcciones de memoria. Como el que abre los archivos de datos es un proceso de SQL Server, no es posible la copia de datos entre procesos.
Los ejecutables de BCP o de paquetes de Integration Servicies, se ejecutan en un proceso diferente y, por tanto, requieren la copia de datos entre procesos y el cálculo de referencia de parámetros para mover los datos entre los distintos espacios de memoria que usan los diferentes procesos. El cálculo de referencia de datos entre procesos es el proceso de convertir parámetros de una llamada a un método en una secuencia de bytes y puede agregar una sobrecarga considerable a la CPU. Con Bulk Insert, puede omitir estos pasos y utilizar directamente OLE-DB. Sin embargo, a diferencia de Bulk Insert, BCP analiza los datos y los convierte en formato de almacenamiento nativo en el proceso del cliente. Esto puede constituir una ventaja en cuanto al rendimiento frente a Bulk Insert, si SQL Server se ejecuta en un equipo con un único procesador, ya que el proceso de SQL Server se sobrecarga con el análisis y la conversión de los datos cuando se ejecuta la instrucción BULK INSERT. Según el tipo de procesamiento y de copia, puede observar un aumento del 20 por ciento o una merma de hasta el 100 por cien (por ejemplo, cuando se realiza una carga masiva de datos LOB) en el rendimiento de carga masiva con BCP frente a Bulk Insert.
Para este caso práctico, hemos elegido el comando Bulk Insert para las operaciones de carga masiva.
Modo de recuperación de la base de datos
Las operaciones de carga masiva pueden realizar un registro optimizado (es decir, sólo se registran las asignaciones de páginas de registro y no los datos reales ni las filas de índice) en determinadas condiciones. Si la base de datos está configurada en modo de recuperación completa, el registro especializado para las operaciones en bloque y de ordenación no está habilitado. Si es posible, debe establecer el modo de recuperación de la base de datos en Recuperación de registro masivo (configuración preferida) o Recuperación simple durante las operaciones de carga masiva.
Nota la configuración del modo de recuperación de la base de datos es sólo una de las condiciones para habilitar el registro masivo optimizado. Éste depende también de otros factores descritos más adelante en este documento.
Esquema de la tabla de destino
El esquema de la tabla de destino afecta a los planes de consultas de las operaciones de carga masiva, al registro optimizado y a la carga masiva simultánea y, en última instancia, repercute en el rendimiento de la operación de carga masiva. En las siguientes secciones se explican los elementos del esquema de la tabla de destino que afectan al plan de consultas.
Índices
La mayoría de las tablas contienen índices; la tabla de destino de la operación de carga masiva no es una excepción. Sin embargo, la inclusión de índices en la tabla de destino afecta al rendimiento de la carga masiva. Hay dos opciones para los índices de la tabla de destino: borrar uno de ellos o varios, realizar una carga masiva de los datos y volver a crear después los índices borrados, o no borrar ningún índice. Tenga en cuenta las siguientes consideraciones cuando la tabla de destino:
No contenga índices Cuando la tabla de destino no contenga índices y se haya especificado TABLOCK, puede ejecutar varios comandos de carga masiva simultáneamente. Estas cargas masivas simultáneas utilizan un bloqueo de actualización masiva en el nivel de tabla, que inserta los datos sin bloquear otras sesiones. Si utiliza TABLOCK sin índices, también se pueden realizar optimizaciones de registro masivo. No importa si la tabla está vacía o llena. Ésta es la forma más rápida de realizar una carga masiva de datos. Sin embargo, el bloqueo de actualización masiva entra en conflicto con los bloqueos de recursos normales y los bloqueos exclusivos.
Si no se ha especificado TABLOCK, también puede ejecutar simultáneamente varios comandos de carga masiva, pero cada uno de estos comandos utiliza un bloqueo normal, como el que emplea la instrucción Insert de Transact-SQL, y no se pueden realizar optimizaciones de registro masivo.
Contenga un único índice no agrupado o agrupado Cuando la tabla de destino contiene un índice, la carga masiva simultánea con el bloqueo de actualización masiva no se puede realizar con TABLOCK.
Nota aún es posible ejecutar cargas masivas simultáneas sin especificar TABLOCK (como se ha descrito en el escenario sin índices) y para ello se utiliza el bloqueo normal. Sin embargo, puede que las sesiones de carga masiva simultánea se bloqueen. En ese caso, las optimizaciones de registro masivo sólo están disponibles cuando la tabla está vacía inicialmente y la carga masiva se realiza en un único lote.
Nota si realiza la carga masiva de datos en varios lotes en esta situación, la optimización de registro masivo no estará disponible a partir del segundo lote, ya que la tabla no estará vacía una vez ejecutado correctamente el primer lote.
Un plan de consultas típico en una tabla con un índice agrupado es
(archivoDatos-Examinar) --> (Ordenar por clave agrupada) --> (Insertar en índice agrupado).
Para evitar la ordenación si los datos del archivo de datos ya están ordenados por columnas de clave de índice agrupado, especifique una indicación ORDER.
De igual forma, un plan de consultas típico en una tabla con un único índice no agrupado es
(archivoDatos-Examinar) --> (Insertar datos en la tabla) --> (Ordenar por clave no agrupada) --> (Insertar en índice no agrupado).
Nota aunque los datos de entrada estén ordenados por columnas de clave no agrupada, no se puede eliminar la ordenación. La indicación ORDER sólo se puede aplicar al insertar datos en la tabla base (con índice agrupado). No se tiene en cuenta en los demás casos.
Contenga un índice agrupado y varios índices no agrupados Cuando la tabla de destino contiene un índice agrupado y varios índices no agrupados, la carga masiva simultánea con el bloqueo de actualización masiva no se puede realizar con TABLOCK.
Nota aún es posible ejecutar cargas masivas simultáneas sin especificar TABLOCK (como en el escenario sin índices) y para ello se utiliza el bloqueo normal. Sin embargo, puede que las sesiones de carga masiva simultánea se bloqueen. En ese caso, las optimizaciones de registro masivo sólo están disponibles cuando la tabla está vacía inicialmente y la carga masiva se realiza en un único lote.
Nota si realiza la carga masiva de datos en varios lotes en esta situación, la optimización de registro masivo no estará disponible a partir del segundo lote, ya que la tabla no estará vacía una vez ejecutado correctamente el primer lote.
Un plan de consultas típico en este caso es más complejo, pero la parte inicial es la misma que en el caso de un único índice no agrupado o de un índice agrupado.
--> Poner en cola --> Ordenar por clave no agrupada --> insertar en índice 1 no agrupado
(plan anterior) -->| .......
--> Poner en cola --> Ordenar por clave no agrupada --> insertar en índice 2 no agrupado
Una vez creado el índice agrupado, todos los atributos necesarios para crear índices no agrupados se ponen en cola. Estos atributos incluyen todas las columnas de clave de los índices no agrupados y las columnas de clave agrupada. Las columnas de la clave de índice agrupado se utilizan para apuntar a la fila de datos de las páginas secundarias del índice no agrupado. En el plan de consultas anterior que representa una carga masiva, los datos de cada índice no agrupado se insertan en paralelo.
Cuando la tabla de destino está vacía, es lógico crear índices después de que haya terminado la carga masiva por dos motivos. En primer lugar, puede realizar una carga masiva simultánea con el bloqueo de actualización masiva y el registro masivo optimizado. En segundo lugar, puede crear cada índice en paralelo.
Nota en este documento, "en paralelo" hace referencia a un único comando ejecutado por varios subprocesos. Por ejemplo, varios subprocesos pueden ejecutar una única instrucción CREATE INDEX de Transact-SQL. En este caso, la instrucción BULK INSERT no se puede ejecutar en paralelo. Debe llamar a varias instrucciones BULK INSERT para cargar los datos en paralelo.
Restricciones
Las restricciones se comprueban para cada fila insertada. Si procede, se recomienda deshabilitar la comprobación de restricciones. La única restricción que se puede deshabilitar es la restricción de comprobación. No se puede deshabilitar la restricción de unicidad, la restricción de clave principal/clave externa ni la restricción NOT NULL. Cuando la opción de restricción de comprobación se vuelva a habilitar, SQL Server deberá comprobar toda la tabla para volver a validar las restricciones. Por este motivo, no se recomienda deshabilitar restricciones durante la carga masiva incremental, ya que es más laborioso volver a validar la restricción para toda la tabla que aplicar restricciones a los datos incrementales.
Una situación en la que puede ser conveniente deshabilitar restricciones es cuando los datos de entrada contienen filas que infringen las restricciones. Al deshabilitar las restricciones, puede cargar los datos y utilizar después instrucciones de Transact-SQL para limpiarlos.
Desencadenadores
Si se han definido desencadenadores para las operaciones de inserción en la tabla de destino, se activarán para cada lote finalizado. Si procede, deshabilite la ejecución de desencadenadores durante la operación de carga masiva.
TABLOCK
La instrucción BULK INSERT acepta la indicación TABLOCK, que permite al usuario especificar el comportamiento de bloqueo que se va a utilizar.
En carga masiva con Bulk Isert, TABLOCK especifica que se utilice el bloqueo de nivel de tabla de actualización masiva durante la carga masiva. Esto permite mejorar el rendimiento de la operación de carga masiva, ya que se reduce el conflicto de bloqueo en la tabla. Asimismo, TABLOCK es un parámetro obligatorio para el registro masivo cuando el modo de recuperación de la base de datos está configurado en REGISTRO MASIVO o SIMPLE.
Cuando la carga masiva se realiza en una tabla con uno o varios índices, TABLOCK impone un bloqueo de tabla X a las operaciones de carga masiva de forma que no sea posible la carga masiva simultánea.
Si no especifica TABLOCK, la carga masiva no adquiere el bloqueo de tabla, sino el bloqueo en filas o páginas. Sin embargo, los bloqueos de filas o páginas se pueden trasladar al bloqueo X en función del tamaño del lote y de la actividad simultánea en la tabla de destino. SQL Server intenta trasladar los bloqueos de filas o páginas al nivel de tabla si el número de bloqueos es mayor que 5.000.
En la Tabla a continuación se describe el comportamiento de registro y bloqueo cuando la tabla de destino no está vacía (es decir, cuando la carga masiva es incremental).
Comportamiento de registro y bloqueo en una tabla de destino que no está vacía:
|
Esquema de tabla |
TABLOCK |
Tipo de registro |
Carga masiva simultánea |
|
Tabla sin índices |
Sí |
Registro masivo |
Sí (bloqueo de actualización masiva) |
|
Tabla sin índices |
No |
Registro completo |
Sí (bloqueo X normal en filas o páginas) |
|
Tabla con índices |
Sí |
Registro completo |
No |
|
Tabla con índices |
No |
Registro completo |
Sí (bloqueo X normal en filas o páginas) |
Nota en el esquema de tabla, el término montón hace referencia a una tabla sin índices agrupados o no agrupados. Se pueden utilizar datos LOB aunque estén representados internamente como un índice por motivos de asignación.
Nota la carga masiva simultánea hace referencia a varios comandos de carga masiva, cada uno con su propia secuencia de datos. Si se especifica TABLOCK, el comando de carga masiva espera a adquirir el bloqueo de tabla. Sin embargo, si TABLOCK no se ha especificado, cada comando de carga masiva adquiere un bloqueo de filas o páginas (siempre que no se haya producido el traslado de bloqueo), según cómo se haya definido el nivel de bloqueo en la tabla.
Carga masiva simultánea
La carga masiva suele estar limitada por la CPU. Por tanto, si SQL Server se ejecuta en un equipo con varios procesadores, observará un mayor rendimiento de la carga masiva cuando se invoquen simultáneamente varios comandos de carga masiva, siempre que no haya bloqueos. Asimismo, durante la carga masiva, existe un bloqueo especial de nivel de tabla (bloqueo de actualización masiva) cuando se especifica TABLOCK. Este bloqueo funciona como un bloqueo exclusivo porque bloquea el acceso a la tabla a través de instrucciones normales de Transact-SQL, pero los subprocesos de carga masiva simultánea no se bloquean. El bloqueo de actualización masiva sólo está disponible en el montón (es decir, en una tabla sin índices) y proporciona el rendimiento de carga simultánea óptimo para esta situación. En la Tabla 1 se describen las condiciones para la carga masiva simultánea.
Nota no es necesario que la base de datos tenga el modo de recuperación SIMPLE o REGISTRO MASIVO para la carga masiva simultánea.
Para ejecutar la carga masiva simultáneamente, necesita emitir varios comandos de carga masiva de forma que cada uno de ellos lea sus propios datos. Los comandos de carga masiva múltiples no pueden leer los mismos datos.
Orden de los datos de entrada
Si los datos de entrada está ordenados por la columna de clave agrupada, se elimina la ordenación, como se ha descrito en el plan de consultas para un índice no agrupado o un índice agrupado en la sección "Índices", anteriormente en este documento.
BATCHSIZE
La configuración predeterminada de BATCHSIZE es la longitud del archivo de entrada para una instrucción BULK INSERT. Cuando se utiliza otro valor distinto al predeterminado, la carga masiva se divide en una o varias transacciones. Cada transacción inserta hasta BATCHSIZE número de filas. Un lote de tamaño más pequeño proporciona las siguientes ventajas:
- Cuando existen índices, un lote pequeño reduce la memoria necesaria para la ordenación. Durante la carga masiva simultánea, puede reducir también el bloqueo en función del modelo de datos y de la distribución en los archivos de datos de entrada.
- En caso de que se produzca un error, tendrá que volver a cargar sólo los datos a partir del último lote que no pudo ejecutarse. Por ejemplo, si la carga masiva dura tres horas y el error se produce casi al final, sólo tendrá que volver a cargar el último lote, en lugar de iniciar de nuevo toda la carga masiva.
Modo de datos
Los datos del archivo de datos pueden tener formato de carácter o nativo (es decir, representación binaria). La carga de datos almacenados en formato de carácter requiere el análisis y su posterior conversión al formato de almacenamiento nativo basado en el tipo de columna de la tabla de destino, lo que requiere recursos del servidor. El formato de carácter es el formato más común para los datos, pero es más eficaz cargar datos que estén disponibles en el formato nativo.
Tamaño de los datos de entrada
El tamaño de los datos de entrada es importante en caso de que haya índices. Cuando se crea un índice agrupado, SQL Server ordena los datos y aumenta su tamaño. Cuanto mayor sea el tamaño de los datos de entrada, mayor será la memoria necesaria (es posible que la ordenación se tenga que ejecutar en varios pasos). Lo mismo ocurre con los índices no agrupados, salvo que en este caso el tamaño de los datos que se van a ordenar depende del tamaño de la clave de índice y del número de filas. Puede controlar el tamaño de los datos especificando un tamaño de lote más pequeño. Asimismo, en función del tamaño de los datos que desee cargar, la información estadística sobre la tabla de destino puede cambiar considerablemente. Es recomendable que vuelva a crear las estadísticas después de la operación de carga masiva, especialmente si ha deshabilitado la regeneración automática de estadísticas.
Apunte y recopilación por Norman M. Pardell. Usando fuentes de: Microsoft
Puedes consultarme, si deseas cualquier aclaración, pregunta o sugerencia en: Contacto, contestaré tan pronto como me sea posible.
