SqlBulkCopy es lento, no utiliza velocidad de red completa

database networking smo sqlbulkcopy sql-server

Pregunta

Durante las últimas semanas, he estado creando scripts genéricos que pueden copiar bases de datos. El objetivo es poder especificar cualquier base de datos en algún servidor y copiarla en otra ubicación, y solo debe copiar el contenido especificado. El contenido exacto que se copiará se especifica en un archivo de configuración. Este script se utilizará en unas 10 bases de datos diferentes y se ejecutará semanalmente. Y al final, estamos copiando solo alrededor del 3% -20% de las bases de datos que son tan grandes como 500 GB. He estado usando los ensamblajes SMO para lograr esto. Esta es la primera vez que trabajo con SMO y me llevó un tiempo crear una forma genérica de copiar los objetos de esquema, los grupos de archivos ... etc. (En realidad ayudó a encontrar algunos procs mal almacenados).

En general, tengo un guión de trabajo que carece de rendimiento (y en ocasiones se ha agotado) y esperaba que ustedes pudieran ayudar. Al ejecutar el comando WriteToServer para copiar una gran cantidad de datos (> 6GB), alcanza mi tiempo de espera de 1 hora. Aquí está el código central para copiar datos de la tabla. El script está escrito en PowerShell.

$query = ("SELECT * FROM $selectedTable " + $global:selectiveTables.Get_Item($selectedTable)).Trim()
Write-LogOutput "Copying $selectedTable : '$query'"            
$cmd = New-Object Data.SqlClient.SqlCommand -argumentList $query, $source
$cmd.CommandTimeout = 120;
$bulkData = ([Data.SqlClient.SqlBulkCopy]$destination)
$bulkData.DestinationTableName = $selectedTable;
$bulkData.BulkCopyTimeout = $global:tableCopyDataTimeout # = 3600
$reader = $cmd.ExecuteReader();
$bulkData.WriteToServer($reader); # Takes forever here on large tables

Las bases de datos de origen y destino están ubicadas en servidores diferentes, así que también mantuve un registro de la velocidad de la red. La utilización de la red nunca superó el 1%, lo que me sorprendió bastante. Pero cuando solo transfiero algunos archivos grandes entre los servidores, la utilización de la red aumenta hasta un 10%. He intentado establecer $ bulkData.BatchSize en 5000 pero nada realmente cambió. Aumentar el BulkCopyTimeout a una cantidad aún mayor solo resolvería el tiempo de espera. Realmente me gustaría saber por qué la red no se está utilizando por completo.

¿Alguien más ha tenido este problema? Cualquier sugerencia sobre redes o copia masiva será apreciada. Y por favor déjame saber si necesitas más información.

Gracias.

ACTUALIZAR

He ajustado varias opciones que aumentan el rendimiento de SqlBulkCopy, como configurar el registro de transacciones a simple y proporcionar un bloqueo de tabla a SqlBulkCopy en lugar del bloqueo de fila predeterminado. También algunas tablas están mejor optimizadas para ciertos tamaños de lote. En general, la duración de la copia se redujo en un 15%. Y lo que haremos es ejecutar la copia de cada base de datos simultáneamente en diferentes servidores. Pero todavía estoy teniendo un problema de tiempo de espera al copiar una de las bases de datos.

Cuando copio una de las bases de datos más grandes, hay una tabla para la que recibo constantemente la siguiente excepción:

System.Data.SqlClient.SqlException: Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding. 

Se lanza unos 16 minutos después de que comienza a copiar la tabla, que no se encuentra cerca de mi BulkCopyTimeout. A pesar de que tengo la excepción de que la tabla se copia completamente al final. Además, si trunco ​​esa tabla y reinicio mi proceso solo para esa tabla, las tablas se copian sin ningún problema. Pero pasar por el proceso de copiar toda la base de datos falla siempre para esa tabla.

He intentado ejecutar todo el proceso y restablecer la conexión antes de copiar esa tabla defectuosa, pero aún se ha producido un error. Mi SqlBulkCopy y Reader se cierran después de cada mesa. ¿Alguna sugerencia sobre qué otra cosa podría estar causando que el script falle en el punto cada vez?

CREATE TABLE [dbo].[badTable](
[someGUID] [uniqueidentifier] NOT NULL,
[xxx] [uniqueidentifier] NULL,
[xxx] [int] NULL,
[xxx] [tinyint] NOT NULL,
[xxx] [datetime] NOT NULL,
[xxx] [datetime] NOT NULL,
[xxx] [datetime] NOT NULL,
[xxx] [datetime] NULL,
[xxx] [uniqueidentifier] NOT NULL,
[xxx] [uniqueidentifier] NULL,
CONSTRAINT [PK_badTable] PRIMARY KEY NONCLUSTERED 
(
[someGUID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

No existen índices para esta tabla en la base de datos de destino.

Respuesta aceptada

¿Ha considerado eliminar los índices, hacer la inserción y luego volver a indexar?


Respuesta popular

SqlBulk Copy es, con mucho, la forma más rápida de copiar datos en tablas SQL.
Deberías estar obteniendo velocidades de más de 10,000 filas por segundo.
Para probar la funcionalidad de copia masiva, intente DBSourceTools. ( http://dbsourcetools.codeplex.com )
Esta utilidad está diseñada para crear un script de bases de datos en el disco y luego volver a crearlas en un servidor de destino.
Al copiar datos, DBSourceTools primero exportará todos los datos a un archivo .xml local y luego realizará una Copia masiva a la base de datos de destino.
Esto ayudará a identificar mejor dónde se encuentra su cuello de botella, dividiendo el proceso en dos pases: uno para leer y otro para escribir.



Licencia bajo: CC-BY-SA with attribution
No afiliado con Stack Overflow
¿Es esto KB legal? Sí, aprende por qué
Licencia bajo: CC-BY-SA with attribution
No afiliado con Stack Overflow
¿Es esto KB legal? Sí, aprende por qué