▷¿Cómo se investiga el rendimiento de una declaración BULK INSERT? ✔️ Foro Ayuda 【 2024 】

Solución:

Por lo que puedo decir, puede optimizar una inserción masiva de una manera muy similar a la que optimizaría una inserción normal. Por lo general, un plan de consulta para una inserción simple no es muy informativo, así que no se preocupe por no tener el plan. Repasaré algunas formas de optimizar una inserción, pero la mayoría de ellas probablemente no se apliquen a la inserción que especificó en la pregunta. Sin embargo, podrían ser útiles si en el futuro necesita cargar grandes cantidades de datos.

1. Inserte los datos en el orden de las claves de agrupación

SQL Server a menudo clasifica los datos antes de insertarlos en una tabla con un índice agrupado. Para algunas tablas y aplicaciones, puede mejorar el rendimiento clasificando los datos en el archivo plano y haciendo saber a SQL Server que los datos se ordenan a través del ORDER argumento de BULK INSERT:

ORDER ( DESC ] [ ,… n ] )

Especifica cómo se ordenan los datos en el archivo de datos. El rendimiento de la importación masiva mejora si los datos que se están importando se ordenan según el índice agrupado de la tabla, si lo hay.

Dado que está utilizando un IDENTITY columna como clave agrupada, no necesita preocuparse por esto.

2. Utilice TABLOCK si es posible

Si tiene la garantía de tener solo una sesión insertando datos en su tabla, puede especificar el TABLOCK argumento a favor BULK INSERT. Esto puede reducir la contención de bloqueo y puede llevar a un registro mínimo en algunos escenarios. Sin embargo, está insertando en una tabla con un índice agrupado que ya contiene datos, por lo que no obtendrá un registro mínimo sin el indicador de seguimiento 610 que se menciona más adelante en esta respuesta.

Si TABLOCK no es posible, porque no se puede cambiar el código, no todas las esperanzas están perdidas. Considere usar sp_table_option:

EXEC [sys].[sp_tableoption]
    @TableNamePattern = N'dbo.BulkLoadTable' ,
    @OptionName = 'table lock on bulk load' , 
    @OptionValue = 'ON'

Otra opción es habilitar el indicador de seguimiento 715.

3. Utilice un tamaño de lote adecuado

A veces, podrá ajustar las inserciones cambiando el tamaño del lote.

ROWS_PER_BATCH = filas_por_ lote

Indica el número aproximado de filas de datos en el archivo de datos.

De forma predeterminada, todos los datos del archivo de datos se envían al servidor como una única transacción y el optimizador de consultas desconoce el número de filas del lote. Si especifica ROWS_PER_BATCH (con un valor> 0), el servidor usa este valor para optimizar la operación de importación masiva. El valor especificado para ROWS_PER_BATCH debe ser aproximadamente el mismo que el número real de filas. Para obtener información sobre las consideraciones de rendimiento, consulte “Comentarios”, más adelante en este tema.

Aquí está la cita de más adelante en el artículo:

Si el número de páginas que se van a vaciar en un solo lote excede un umbral interno, puede ocurrir un escaneo completo del grupo de búfer para identificar qué páginas vaciar cuando el lote se confirma. Este análisis completo puede afectar el rendimiento de la importación masiva. Un caso probable de exceder el umbral interno ocurre cuando un grupo de búfer grande se combina con un subsistema de E / S lento. Para evitar desbordamientos de búfer en máquinas grandes, no use la sugerencia TABLOCK (que eliminará las optimizaciones masivas) o use un tamaño de lote más pequeño (que conserva las optimizaciones masivas).

Debido a que las computadoras varían, le recomendamos que pruebe varios tamaños de lotes con su carga de datos para descubrir cuál funciona mejor para usted.

Personalmente, solo insertaría las 695 filas en un solo lote. Sin embargo, ajustar el tamaño del lote puede marcar una gran diferencia al insertar muchos datos.

4.Asegúrese de que necesita el IDENTITY columna

No sé nada sobre su modelo de datos o requisitos, pero no caiga en la trampa de agregar un IDENTITY columna a cada tabla. Aaron Bertrand tiene un artículo sobre esto llamado Malos hábitos para patear: poner una columna de IDENTIDAD en cada mesa. Para ser claros, no estoy diciendo que debas quitar el IDENTITY columna de esta tabla. Sin embargo, si determina que el IDENTITY columna no es necesaria y elimínela para mejorar el rendimiento de la inserción.

5. Deshabilitar índices o restricciones

Si está cargando una gran cantidad de datos en una tabla en comparación con lo que ya tiene, entonces puede ser más rápido deshabilitar índices o restricciones antes de la carga y habilitarlos después de la carga. Para grandes cantidades de datos, normalmente es más ineficiente que SQL Server cree un índice de una vez en lugar de cuando los datos se cargan en la tabla. Parece que insertó 695 filas en una tabla con 11500 filas, por lo que no recomendaría esta técnica.

6. Considere TF 610

Trace Flag 610 permite un registro mínimo en algunos escenarios adicionales. Para tu mesa con un IDENTITY clave agrupada, obtendría un registro mínimo para cualquier página de datos nueva siempre que su modelo de recuperación sea simple o con registro masivo. Creo que esta función no está activada de forma predeterminada porque puede degradar el rendimiento en algunos sistemas. Debería realizar una prueba con cuidado antes de habilitar esta marca de seguimiento. La referencia recomendada de Microsoft todavía parece ser la Guía de rendimiento de carga de datos

Impacto de E / S del registro mínimo bajo el indicador de seguimiento 610

Cuando confirma una transacción de carga masiva que se registró mínimamente, todas las páginas cargadas deben vaciarse en el disco antes de que se complete la confirmación. Cualquier página vacía que no haya sido capturada por una operación de punto de control anterior puede crear una gran cantidad de E / S aleatorias. Compare esto con una operación de registro completo, que crea E / S secuencial en las escrituras de registro y no requiere que las páginas cargadas se descarguen en el disco en el momento de la confirmación.

Si su escenario de carga son pequeñas operaciones de inserción en árboles b que no cruzan los límites de los puntos de control y tiene un sistema de E / S lento, el uso de un registro mínimo puede reducir la velocidad de inserción.

Por lo que puedo decir, esto no tiene nada que ver con el indicador de seguimiento 610, sino con un registro mínimo en sí. Creo que la cita anterior sobre ROWS_PER_BATCH tuning estaba llegando a este mismo concepto.

En conclusión, probablemente no haya mucho que pueda hacer para ajustar su BULK INSERT. No me preocuparía el recuento de lecturas que observó con su inserto. SQL Server informará las lecturas cada vez que inserte datos. Considere lo siguiente muy simple INSERT:

DROP TABLE IF EXISTS X_TABLE;

CREATE TABLE X_TABLE (
VAL VARCHAR(1000) NOT NULL
);

SET STATISTICS IO, TIME ON;

INSERT INTO X_TABLE WITH (TABLOCK)
SELECT REPLICATE('Z', 1000)
FROM dbo.GetNums(10000); -- generate 10000 rows

Salida de SET STATISTICS IO, TIME ON:

Tabla ‘X_TABLE’. Cuenta de escaneo 0, lecturas lógicas 11428

Tengo 11428 lecturas informadas, pero esa no es información procesable. A veces, el número de lecturas informadas se puede reducir mediante un registro mínimo, pero, por supuesto, la diferencia no se puede traducir directamente en una ganancia de rendimiento.

Comenzaré a responder esta pregunta, con la intención de actualizar continuamente esta respuesta a medida que construyo una base de conocimientos de trucos. Ojalá otros se encuentren con esto y me ayuden a mejorar mi propio conocimiento en el proceso.

Comprobación intestinal: ¿Su cortafuegos realiza una inspección profunda de paquetes con estado? No encontrará mucho en Internet sobre esto, pero si sus inserciones masivas son aproximadamente 10 veces más lentas de lo que deberían ser, es probable que tenga un dispositivo de seguridad que realice una inspección profunda de paquetes de nivel 3-7 y verifique la “Prevención de inyección de SQL genérico “.
Mida el tamaño de los datos que planea insertar de forma masiva, en bytes, por lote. Y compruebe si está almacenando datos LOB, ya que se trata de una operación de búsqueda y escritura de página separada.

Varias razones por las que debes hacerlo de esta manera:

una. En AWS, las IOPS de Elastic Block Storage se dividen en bytes, no en filas.
1. Consulte Rendimiento del volumen de Amazon EBS en instancias de Linux »Monitoreo y características de E / S para obtener una explicación de qué es una unidad de IOPS de EBS.
2. Específicamente, los volúmenes SSD de uso general (gp2) tienen el concepto de “Créditos de E / S y rendimiento de ráfagas” y es común que el procesamiento ETL pesado agote los créditos de saldo de ráfagas. La duración de la ráfaga se mide en bytes, no en filas de SQL Server 🙂
B. Si bien la mayoría de las bibliotecas o documentos técnicos prueban en función del número de filas, en realidad es el número de páginas que se pueden escribir en ese asunto y, para calcularlo, necesita saber cuántos bytes por fila y el tamaño de su página (generalmente 8KB , pero siempre verifique si ha heredado el sistema de otra persona).
```
SELECT *
FROM 
sys.dm_db_index_physical_stats(DB_ID(),OBJECT_ID(N'YourTable'), NULL, NULL, 'DETAILED')
```
Preste atención a avg_record_size_in_bytes y page_count.

C. Como explica Paul White en https://sqlperformance.com/2019/05/sql-performance/minimal-logging-insert-select-heap, “Para habilitar un registro mínimo con INSERT...SELECT, SQL Server debe esperar más de 250 filas con un tamaño total de al menos una extensión (8 páginas) “.
Si tiene índices con restricciones de verificación o restricciones únicas, use SET STATISTICS IO ON y SET STATISTICS TIME ON (o SQL Server Profiler o SQL Server Extended Events) para capturar información como si su inserción masiva tiene operaciones de lectura. Las operaciones de lectura se deben al motor de la base de datos de SQL Server que se asegura de que se cumplan las restricciones de integridad.
Intente crear una base de datos de prueba donde el PRIMARIO FILEGROUP está montado en una unidad RAM. Esto debería ser un poco más rápido que SSD, pero también eliminará cualquier pregunta sobre si su controlador RAID podría estar agregando sobrecarga. En 2018, no debería, pero al crear múltiples líneas base diferenciales como esta, puede tener una idea general de cuánta sobrecarga está agregando su hardware.
También coloque el archivo fuente en una unidad RAM.

Poner el archivo de origen en una unidad RAM descartará cualquier problema de contención si está leyendo el archivo de origen desde la misma unidad en la que se encuentra el FILEGROUP de su servidor de base de datos.
Verifique que haya formateado su disco duro con extensiones de 64 KB.
Utilice UserBenchmark.com y compare su SSD. Esta voluntad:
1. Agregue más conocimientos a otros aficionados al rendimiento sobre qué rendimiento esperar de un dispositivo
2. Ayudarle a determinar si el rendimiento de su disco es inferior al de sus pares con el mismo disco exacto
3. Ayudarle a determinar si el rendimiento de su unidad tiene un rendimiento inferior al de otras unidades de la misma categoría (SSD, HDD, etc.)
Si está llamando a “INSERT BULK” desde C # a través de Entity Framework Extensions, asegúrese de “calentar” el JIT primero y “desechar” los primeros resultados.
Intente crear contadores de rendimiento para su programa. Con .NET, puede usar benchmark.NET y automáticamente perfilará un montón de métricas básicas. Luego, puede COMPARTIR sus intentos de generador de perfiles con la comunidad de código abierto y ver si las personas que ejecutan hardware diferente informan las mismas métricas (es decir, de mi punto anterior sobre el uso de UserBenchmark.com para comparar).
Intente usar canalizaciones con nombre y ejecutarlo como localhost.
Si se dirige a SQL Server y utiliza .NET Core, considere la posibilidad de hacer girar Linux con SQL Server Std Edition; esto cuesta menos de un dólar por hora incluso para hardware serio. La principal ventaja de probar el mismo código con el mismo hardware con un sistema operativo diferente es ver si la pila TCP / IP del núcleo del sistema operativo está causando problemas.
Utilice las consultas de diagnóstico de SQL Server de Glen Barry para medir la latencia de la unidad que almacena el FILEGROUP de la tabla de su base de datos.

una. Asegúrese de medir antes de su prueba y después de su prueba. El “antes de su prueba” solo le dice si tiene características de IO horribles como base.

B. Para medir “durante su prueba”, realmente necesita usar PerfMon Performance Counters.

¿Por qué? Porque la mayoría de los servidores de bases de datos utilizan algún tipo de almacenamiento conectado a la red (NAS). En la nube, en AWS, Elastic Block Storage es solo eso. Podría estar sujeto a las IOPS de su solución NAS / volumen EBS.
Utilice alguna herramienta para medir las estadísticas de espera. Red Gate SQL Monitor, SolarWinds Database Performance Analyzer, o incluso las consultas de diagnóstico de SQL Server de Glen Barry, o la consulta de estadísticas de espera de Paul Randal.

una. Los tipos de espera más comunes probablemente serán Memory / CPU, WRITELOG, PAGEIOLATCH_EX y ASYNC_NETWORK_IO.

B. Puede incurrir en tipos de espera adicionales si está ejecutando grupos de disponibilidad.
Mida los efectos de múltiples y simultáneos INSERT BULK comandos con TABLOCK desactivado (TABLOCK probablemente forzará la serialización de los comandos INSERT BULK). Tu cuello de botella podría estar esperando INSERT BULK completar; debe intentar poner en cola tantas de estas tareas como pueda manejar el modelo de datos físicos de su servidor de base de datos.
Considere dividir su mesa. Como ejemplo particular: si la tabla de su base de datos es solo para agregar, Andrew Novick sugirió crear un “HOY” FILEGROUP y particionando en al menos dos grupos de archivos, HOY y BEFORE_TODAY. De esta manera, si tu INSERT BULK Los datos son solo datos de hoy, puede filtrar en un campo CreatedOn para forzar que todas las inserciones alcancen un solo FILEGROUPy, por lo tanto, reducir el bloqueo al usar TABLOCK. Esta técnica se describe con más detalle en un documento técnico de Microsoft: Estrategias de índices y tablas particionadas con SQL Server 2008
Si usa índices de almacén de columnas, desactive TABLOCK y cargar datos en 102,400 filas Tamaño de lote. A continuación, puede cargar todos sus datos en paralelo directamente en grupos de filas de almacén de columnas. Esta sugerencia (y racional documentada) proviene de los índices Columnstore de Microsoft – Guía de carga de datos:

La carga masiva tiene estas optimizaciones de rendimiento integradas:

Cargas paralelas: Puede tener varias cargas masivas simultáneas (bcp o inserción masiva) que cargan cada una un archivo de datos separado. A diferencia de las cargas masivas del almacén de filas en SQL Server, no es necesario especificar TABLOCK porque cada subproceso de importación masiva cargará datos exclusivamente en grupos de filas separados (grupos de filas comprimidos o delta) con bloqueo exclusivo. Utilizando TABLOCK forzará un bloqueo exclusivo en la tabla y no podrá importar datos en paralelo.

Registro mínimo: Una carga masiva utiliza un registro mínimo de datos que van directamente a grupos de filas comprimidos. Todos los datos que van a un grupo de filas delta se registran por completo. Esto incluye cualquier tamaño de lote que tenga menos de 102.400 filas. Sin embargo, con la carga masiva, el objetivo es que la mayoría de los datos omitan los grupos de filas delta.

Optimización de bloqueo: Al cargar en un grupo de filas comprimido, se adquiere el bloqueo X en el grupo de filas. Sin embargo, cuando se realiza una carga masiva en un grupo de filas delta, se adquiere un bloqueo X en el grupo de filas, pero SQL Server aún bloquea los bloqueos PAGE / EXTENT porque el bloqueo del grupo de filas X no forma parte de la jerarquía de bloqueo.
A partir de SQL Server 2016, ya no es necesario habilitar el indicador de seguimiento 610 para un inicio de sesión mínimo en la tabla indexada. Citando al ingeniero de Microsoft Parikshit Savjani (énfasis mío):

Uno de los objetivos de diseño de SQL Server 2016 era mejorar el rendimiento y la escalabilidad del motor desde el primer momento para que se ejecute más rápido sin necesidad de perillas o marcas de seguimiento para los clientes. Como parte de estas mejoras, Una de las mejoras realizadas en el código del motor de SQL Server fue activar el contexto de carga masiva (también conocido como inserciones rápidas o contexto de carga rápida) y el registro mínimo de forma predeterminada al realizar operaciones de carga masiva en la base de datos con un modelo de recuperación de registro simple o masivo. Si no está familiarizado con el registro mínimo, le recomiendo encarecidamente leer esta publicación de blog de Sunil Agrawal, donde explica cómo funciona el registro mínimo en SQL Server. Para que las inserciones a granel se registren mínimamente, aún debe cumplir con las condiciones previas que se documentan aquí.

Como parte de estas mejoras en SQL Server 2016, ya no es necesario habilitar el indicador de seguimiento 610 para un inicio de sesión mínimo en la tabla indexada y se une a algunas de las otras banderas traza (1118, 1117, 1236, 8048) para pasar a formar parte de la historia. En SQL Server 2016, cuando la operación de carga masiva hace que se asigne una nueva página, todas las filas que llenan secuencialmente esa nueva página se registran mínimamente si se cumplen todos los demás requisitos previos para el registro mínimo discutidos anteriormente. Las filas insertadas en páginas existentes (sin asignación de página nueva) para mantener el orden del índice todavía se registran por completo, al igual que las filas que se mueven como resultado de las divisiones de página durante la carga. También es importante tener ALLOW_PAGE_LOCKS activado para los índices (que está activado de forma predeterminada) para que la operación de registro mínima funcione, ya que los bloqueos de página se adquieren durante la asignación y, por lo tanto, solo se registran las asignaciones de página o extensión.
Si está usando SqlBulkCopy en C # o EntityFramework.Extensions (que usa SqlBulkCopy bajo el capó), verifique su configuración de compilación. ¿Está ejecutando sus pruebas en modo de lanzamiento? ¿La arquitectura de destino está configurada en cualquier CPU / x64 / x86?
Considere usar sp_who2 para ver si la transacción INSERT BULK está SUSPENDIDA. Podría estar SUSPENDIDO porque está bloqueado por otro spid. Considere leer Cómo minimizar el bloqueo de SQL Server. También puede usar sp_WhoIsActive de Adam Machanic, pero sp_who2 le dará la información básica que necesita.
Es posible que tenga una E / S de disco defectuosa. Si está haciendo una inserción masiva y la utilización de su disco no alcanza el 100% y se atasca en alrededor del 2%, entonces probablemente tenga un firmware defectuoso o un dispositivo de E / S defectuoso. (Esto le sucedió a un compañero de trabajo). [SSD UserBenchmark] para comparar con otros para el rendimiento del hardware, especialmente si puede replicar la lentitud en su máquina de desarrollo local. (Pongo este último en la lista porque la mayoría de las empresas no permiten que los desarrolladores ejecuten bases de datos en su máquina local debido al riesgo de IP).
Si su tabla usa compresión, puede intentar ejecutar múltiples sesiones, y en cada sesión, comience usando una transacción existente y ejecútela antes del comando SqlBulkCopy:

MODIFICAR LA CONFIGURACIÓN DEL SERVIDOR AJUSTAR LA AFINIDAD DEL PROCESO CPU = AUTO;
Para la carga continua, una serie de ideas, descritas por primera vez en un documento técnico de Microsoft, tablas particionadas y estrategias de índice con SQL Server 2008:

Carga continua

En un escenario OLTP, es posible que entren nuevos datos de forma continua. Si los usuarios también consultan la partición más nueva, la inserción de datos de forma continua puede provocar un bloqueo: las consultas del usuario pueden bloquear las inserciones y, de manera similar, las inserciones pueden bloquear las consultas del usuario.

La contención en la tabla de carga o la partición se puede reducir utilizando el aislamiento de instantáneas, en particular, el READ COMMITTED SNAPSHOT nivel de aislamiento. Debajo READ COMMITTED SNAPSHOT aislamiento, las inserciones en una tabla no provocan actividad en el tempdb tienda de versiones, por lo que tempdb la sobrecarga es mínima para las inserciones, pero las consultas de los usuarios no tomarán bloqueos compartidos en la misma partición.

En otros casos, cuando los datos se insertan en una tabla particionada continuamente a una velocidad alta, es posible que aún pueda organizar los datos durante cortos períodos de tiempo en las tablas de preparación y luego insertar esos datos en la partición más nueva repetidamente hasta que aparezca la ventana para la partición actual pasa y los datos se insertan en la siguiente partición. Por ejemplo, suponga que tiene dos tablas de preparación que reciben 30 segundos de datos cada una, de forma alternativa: una tabla durante la primera mitad de un minuto, la segunda tabla durante la segunda mitad de un minuto. Un procedimiento almacenado de inserción determina en qué mitad del minuto se encuentra la inserción actual y, a continuación, se inserta en la primera tabla de preparación. Cuando transcurren 30 segundos, el procedimiento de inserción determina que debe insertarse en la segunda mesa de preparación. A continuación, otro procedimiento almacenado carga los datos de la primera tabla de ensayo en la partición más nueva de la tabla y luego trunca la primera tabla de ensayo. Después de otros 30 segundos, el mismo procedimiento almacenado inserta los datos del segundo procedimiento almacenado y los coloca en la partición actual, y luego trunca la segunda tabla provisional.
La guía de rendimiento de carga de datos del equipo de Microsoft CAT
Asegúrese de que sus estadísticas estén actualizadas. Utilice FULLSCAN si puede después de cada generación de índice.
SAN Performance Tuning con SQLIO y también asegúrese de que, si está utilizando discos mecánicos, las particiones de su disco estén alineadas. Consulte las prácticas recomendadas de alineación de particiones de disco de Microsoft.
COLUMNSTOREINSERT/UPDATE rendimiento

Es probable que las lecturas sean las restricciones únicas y FK que se verifican durante la inserción; puede obtener una mejora de la velocidad si puede deshabilitarlas / soltarlas durante la inserción y habilitarlas / recrearlas después. Deberá probar si esto lo hace más lento en general en comparación con mantenerlos activos. Esto también puede no ser una buena idea si otros procesos están escribiendo en la misma tabla al mismo tiempo. – Gareth Lyons

Según las preguntas y respuestas, las claves externas dejan de ser de confianza después de la inserción masiva, las restricciones de FK dejan de ser de confianza después de una BULK INSERT sin CHECK_CONSTRAINTS opción (mi caso, ya que terminé con restricciones que no son de confianza). No está claro, pero no tendría sentido revisarlos y aun así hacer que no sean de confianza. Sin embargo, PK y UNIQUE seguirán estando marcados (consulte BULK INSERT (Transact-SQL)). – Alexei

valoraciones y reseñas

¡Haz clic para puntuar esta entrada!

(Votos: 0 Promedio: 0)

¿Cómo se investiga el rendimiento de una declaración BULK INSERT?

Solución:

Carga continua

valoraciones y reseñas

Utiliza Nuestro Buscador

Preguntas Relacionadas: