Directrices para optimizar la importación masiva
En este tema se describen las pautas para optimizar el rendimiento en varios escenarios de importación masiva de datos:
Importar datos desde un solo cliente (o secuencia) a una tabla vacía.
Importar datos desde un solo cliente (o secuencia) a una tabla parcialmente llena (no vacía).
[!NOTA]
La importación de datos a una tabla que no está vacía se conoce como importación masiva incremental. La información clave antes de realizar una importación masiva incremental consiste en saber de antemano si los índices se eliminarán.
Importar datos en paralelo con bloqueo de tablas desde varios clientes (o secuencias).
Copiar datos entre instancias de MicrosoftSQL Server.
En este tema también se proporciona un resumen del bloqueo de tablas y del registro durante las operaciones de importación masiva.
Importar datos de un solo cliente (o secuencia) a una tabla vacía
Cuando se importan datos desde un solo cliente (o secuencia) a una tabla vacía, Microsoft recomienda realizar lo siguiente
Especificar el calificador TABLOCK (que está disponible como sugerencia u opción en los tres métodos de importación masiva). El uso de TABLOCK provoca que se aplique un bloqueo de las tablas mientras se realiza la operación masiva y elimina la carga que supone bloquear las filas una a una. Para obtener más información, vea Controlar el comportamiento de bloqueo para la importación masiva.
Minimizar la creación de registros. Para obtener más información, vea Requisitos previos para el registro mínimo durante la importación masiva.
Utilizar los índices de la siguiente manera.
Cuando esté utilizando bcp, BULK INSERT o INSERT ... SELECT * FROM OPENROWSET(BULK...), si la tabla está vacía y tiene un índice agrupado, y los datos del archivo de datos están ordenados para que coincidan con las columnas de clave del índice agrupado, realice también las siguientes acciones:
Importe los datos de forma masiva incluido el índice agrupado.
Especifique la sugerencia ORDER y la sugerencia TABLOCK. Para obtener más información, vea Controlar el criterio de ordenación en las importaciones masivas de datos.
De esta manera, para una tabla vacía es significativamente más rápido que crear un índice agrupado después de importar los datos, puesto que elimina la necesidad de realizar la ordenación.
[!NOTA]
Si una tabla que no está vacía contiene índices, se crean registros completos para las importaciones masivas, incluso con el modelo de recuperación optimizado para cargas masivas de registros. Para determinar si es necesario quitar los índices, debe tener en cuenta si la ventaja que supone la importación masiva a una tabla sin índices es mayor que el costo de eliminar y volver a crear los índices.
Si importa datos de forma masiva a una tabla vacía que contiene índices y especifica el tamaño del lote, la tabla se convierte en una tabla no vacía después del primer lote. A partir del segundo lote, se crean registros completos para los datos. Para las tablas indizadas vacías, considere la posibilidad de realizar la importación masiva en un solo lote.
[!NOTA]
Cuando no se especifica un tamaño de lote, el optimizador de consultas de SQL Server presupone un tamaño predeterminado para el archivo de datos. Para mejorar el rendimiento, puede utilizar el calificador ROWS_PER_BATCH o KILOBYTES_PER_BATCH como una sugerencia para que el optimizador sepa el número de filas aproximado que contiene el archivo de datos. Para obtener más información, vea Administrar tamaños de lote de copia masiva.
Por lo general, es más rápido importar de forma masiva a una tabla sin índices que a una tabla con índices. Así pues, si una tabla vacía contiene algún índice, elimínelos antes de importar los datos a la tabla y vuelva a crearlos después. Si los datos no están ordenados en una columna de claves agrupadas y la tabla está vacía, elimine todos los índices, importe los datos y, a continuación, cree índices nuevos.
Importar datos desde un solo cliente (o secuencia) a una tabla que no está vacía
La importación de datos a una tabla que ya contiene datos (una tabla que no está vacía) se denomina importación masiva incremental. La información clave antes de realizar una importación masiva incremental consiste en saber de antemano si los índices se eliminarán. Tiene dos opciones. Puede mantener los índices, o bien quitarlos y volver a crearlos posteriormente.
Cuando importa datos desde un solo cliente (o secuencia) a una tabla que no está vacía, la decisión de mantener o no los índices depende de la cantidad de datos nuevos importados en relación con los datos existentes en la tabla:
Si importa una pequeña cantidad de datos en comparación con los datos existentes, la eliminación y recreación de los índices puede ser contraproducente. Es posible que el tiempo necesario para volver a crear los índices sea superior al tiempo que se ha ahorrado durante la operación de importación masiva.
En cambio, si va a importar una cantidad relativamente grande de datos nuevos, la eliminación de los índices de la tabla antes de realizar la importación masiva puede mejorar el rendimiento sin que el tiempo necesario para crear los índices aumente sustancialmente.
La siguiente tabla enumera la cantidad mínima de datos nuevos que debería contener una tabla antes de eliminar los índices. La cantidad mínima es una proporción del total de datos de la tabla. La cantidad varía en función de los tipos y combinaciones de índices. Si los datos nuevos superan el porcentaje sugerido para un determinado tipo de índice o grupo de índices, considere la posibilidad de eliminar los índices antes de la operación masiva y crearlos de nuevo después. Estos números son sensibles al patrón de datos de los datos existentes y de los que se van a cargar. Por consiguiente, los números se proporcionan únicamente como directriz general.
Índices |
Cantidad relativa de nuevos datos |
|---|---|
Sólo un índice agrupado |
30 por cien |
Un índice agrupado y un índice no agrupado |
25 por cien |
Un índice agrupado y dos índices no agrupados |
25 por cien |
Sólo un índice no agrupado |
100 por cien |
Dos índices no agrupados |
60 por cien |
Importar datos en paralelo con bloqueo de tablas desde varios clientes (o secuencias)
Si SQL Server se está ejecutando en un sistema que tiene más de un procesador y los datos que se importarán de forma masiva a una tabla pueden dividirse en archivos de datos separados, puede mejorar el rendimiento importando los datos a la tabla desde varios clientes en paralelo. Al importar de forma masiva desde varios clientes a una tabla, cada cliente debe tener un solo archivo de datos de entrada.
Cuando importe datos a una tabla desde varios clientes, tenga en cuenta que:
Varias secuencias de importaciones masivas pueden bloquearse entre sí.
Para evitarlo, SQL Server proporciona un bloqueo interno especial denominado bloqueo de actualización masiva (BU). Para conseguir un bloqueo BU, es preciso especificar la opción TABLOCK con cada secuencia de importación masiva sin bloquear otras secuencias de importación masiva. De este modo, se evitan conflictos de acceso a las tablas entre los clientes. Sin embargo, un bloqueo BU sólo está disponible en las tablas (vacías o no) que no tienen índice. Si especifica TABLOCK en una tabla con índices, no será posible la importación masiva de datos en paralelo. Para obtener más información, vea Controlar el comportamiento de bloqueo para la importación masiva.
Si la tabla tiene índices, puede beneficiarse del bloqueo BU eliminando todos los índices antes de importar los datos de forma masiva. A continuación, puede importar los datos masivamente en paralelo mediante TABLOCK y después volver a crear el índice o los índices. Asimismo, debe tener en cuenta que si una tabla que no está vacía contiene índices, se crean registros completos para las importaciones masivas, incluso con el modelo de recuperación optimizado para cargas masivas de registros. Para determinar si es necesario quitar los índices, debe tener en cuenta si la ventaja que supone la importación masiva a una tabla sin índices es mayor que el costo de eliminar y volver a crear los índices.
[!NOTA]
Si elimina índices secundarios, considere la posibilidad de volver a crearlos en paralelo creando cada índice secundario desde un cliente diferente.
Para no tener que eliminar y volver a crear los índices, puede realizar una importación en paralelo sin especificar la sugerencia TABLOCK. Sin embargo, en este caso, varias secuencias de importaciones masivas pueden bloquearse entre sí y, además, las optimizaciones de registros masivos no están disponibles. Para minimizar el bloqueo, puede especificar un tamaño de lote más pequeño y utilizar la sugerencia ORDER para eliminar la ordenación durante la operación de importación masiva.
Es necesario dividir los datos en varios archivos de entrada, un archivo por cada cliente. Para utilizar la CPU con más eficacia, los archivos de datos deberían tener un tamaño parecido.
Para obtener más información, vea Importar datos en paralelo con bloqueo de tabla.
Bloqueo de tablas y registro durante la importación masiva
En la tabla siguiente se resume cómo determina el esquema de tabla los tipos de bloqueo durante una operación de importación masiva. En la tabla siguiente también se indica si la tabla está vacía, si se establece TABLOCK para la operación y el tipo de registro que se lleva a cabo si la base de datos utiliza el modelo de recuperación optimizado para cargas masivas de registros.
[!NOTA]
Después del primer lote correcto, la tabla deja de estar vacía.
Tabla de destino de la importación masiva |
¿Está vacía la tabla? |
¿Se ha establecido TABLOCK? |
Bloqueos |
Registro en los modelos de recuperación simple y por medio de registros de operaciones masivas |
|---|---|---|---|---|
Montón |
Sí |
Sí |
BU-Tab |
Registro masivo |
Montón |
Sí |
No |
IX-Tab |
Registro completo |
Montón |
No |
Sí |
BU-tab |
Registro masivo |
Montón |
No |
No |
IX-Tab |
Registro completo |
Montón con un índice no agrupado |
Sí |
Sí |
SCH-M |
Registro masivo |
Montón con un índice no agrupado |
Sí |
No |
IX-Tab |
Registro completo |
Montón con un índice no agrupado |
No |
Sí |
SCH-M |
|
Montón con un índice no agrupado |
No |
No |
IX-Tab |
Registro completo |
Índice agrupado |
Sí |
Sí |
SCH-M |
Registro masivo |
Índice agrupado |
Sí |
No |
IX-Tab |
Registro completo |
Índice agrupado |
No |
Sí |
X-TAB |
Registro completo |
Índice agrupado |
No |
No |
IX-Tab |
Registro completo |
Copiar datos entre instancias de SQL Server
Para copiar datos de forma masiva desde una instancia de SQL Server a otra, utilice bcp para exportar los datos de la tabla a un archivo de datos. A continuación, utilice uno de los métodos de importación masiva para importar los datos desde el archivo a la tabla. Realice tanto las operaciones de exportación como de importación masiva en formato nativo o en formato nativo Unicode.
[!NOTA]
Para obtener más información acerca de estos formatos, vea Usar el formato nativo para importar o exportar datos y Usar el formato nativo Unicode para importar o exportar datos.
Si la tabla de origen contiene un índice agrupado o si piensa realizar una importación masiva de datos en una tabla con un índice agrupado:
Exporte masivamente los datos de la tabla origen mediante bcp con la opción query en una instrucción SELECT y una cláusula ORDER BY adecuada para crear un archivo de datos ordenado. Para obtener más información, vea bcp (utilidad).
Cuando importe los datos de forma masiva a SQL Server, utilice el calificador ORDER, admitido únicamente por bcp y BULK INSERT. Para obtener más información, vea Controlar el criterio de ordenación en las importaciones masivas de datos.
Para obtener más información, vea Copiar datos entre servidores.