Solución:
El plan de ejecución muestra que está leyendo filas de un índice no agrupado en algún orden y luego realiza búsquedas para cada fila exterior leída para evaluar la NOT EXISTS
Estás eliminando el 7,2% de la tabla. 16.000.000 filas en 3.556 lotes de 4.500
Suponiendo que las filas que califican se distribuyen eventualmente por todo el índice, esto significa que eliminará aproximadamente 1 fila cada 13,8 filas.
Entonces, la iteración 1 leerá 62,156 filas y realizará muchas búsquedas de índice antes de encontrar 4,500 para eliminar.
la iteración 2 leerá 57.656 (62.156 – 4.500) filas que definitivamente no calificarán ignorando las actualizaciones simultáneas (ya que ya se han procesado) y luego otras 62.156 filas para obtener 4.500 para eliminar.
la iteración 3 leerá (2 * 57.656) + 62.156 filas y así sucesivamente hasta que finalmente la iteración 3.556 leerá (3555 * 57.656) + 62.156 filas y realizará tantas búsquedas.
Por tanto, el número de búsquedas de índices realizadas en todos los lotes es SUM(1, 2, ..., 3554, 3555) * 57,656 + (3556 * 62156)
Cual es ((3555 * 3556 / 2) * 57656) + (3556 * 62156) – o 364,652,494,976
Le sugiero que materialice las filas para eliminar en una tabla temporal primero
INSERT INTO #MyTempTable
SELECT MySourceTable.PK,
1 + ( ROW_NUMBER() OVER (ORDER BY MySourceTable.PK) / 4500 ) AS BatchNumber
FROM MySourceTable
WHERE NOT EXISTS (SELECT *
FROM dbo.vendor AS v
WHERE VendorId = v.Id)
Y cambia el DELETE borrar WHERE PK IN (SELECT PK FROM #MyTempTable WHERE BatchNumber = @BatchNumber) Es posible que aún deba incluir un NOT EXISTS en el DELETE consulta en sí misma para atender las actualizaciones ya que la tabla temporal se completó, pero esto debería ser mucho más eficiente ya que solo necesitará realizar 4500 búsquedas por lote.
El plan de ejecución sugiere que cada ciclo sucesivo hará más trabajo que el ciclo anterior. Suponiendo que las filas para eliminar se distribuyan uniformemente a lo largo de la tabla, el primer ciclo deberá escanear alrededor de 4500 * 221000000/16000000 = 62156 filas para encontrar 4500 filas para eliminar. También hará el mismo número de búsquedas de índices agrupados contra el vendor mesa. Sin embargo, el segundo ciclo deberá leer más allá de las mismas 62156 – 4500 = 57656 filas que no eliminó la primera vez. Podríamos esperar que el segundo bucle escanee 120000 filas de MySourceTable y hacer 120000 búsquedas contra el vendor mesa. La cantidad de trabajo necesario por bucle aumenta a un ritmo lineal. Como aproximación, podemos decir que el ciclo promedio necesitará leer 102516868 filas de MySourceTable y para hacer 102516868 búsquedas contra el vendor mesa. Para eliminar 16 millones de filas con un tamaño de lote de 4500, su código debe realizar 16000000/4500 = 3556 bucles, por lo que la cantidad total de trabajo que debe completar su código es de alrededor de 364,5 mil millones de filas leídas. MySourceTable y 364,5 mil millones de búsquedas de índices.
Un problema menor es que usa una variable local @BATCHSIZE en una expresión TOP sin un RECOMPILE o alguna otra pista. El optimizador de consultas no conocerá el valor de esa variable local al crear un plan. Asumirá que es igual a 100. En realidad, está eliminando 4500 filas en lugar de 100, y posiblemente podría terminar con un plan menos eficiente debido a esa discrepancia. La estimación de cardinalidad baja al insertar en una tabla también puede causar un impacto en el rendimiento. SQL Server puede elegir una API interna diferente para hacer inserciones si cree que necesita insertar 100 filas en lugar de 4500 filas.
Una alternativa es simplemente insertar las claves primarias / claves agrupadas de las filas que desea eliminar en una tabla temporal. Dependiendo del tamaño de sus columnas clave, esto podría caber fácilmente en tempdb. Puede obtener un registro mínimo en ese caso, lo que significa que el registro de transacciones no explotará. También puede obtener un registro mínimo en cualquier base de datos con un modelo de recuperación de SIMPLE. Consulte el enlace para obtener más información sobre los requisitos.
Si esa no es una opción, debe cambiar su código para poder aprovechar el índice agrupado en MySourceTable. La clave es escribir su código para que haga aproximadamente la misma cantidad de trabajo por ciclo. Puede hacerlo aprovechando el índice en lugar de simplemente escanear la tabla desde el principio cada vez. Escribí una publicación de blog que repasa algunos métodos diferentes de bucle. Los ejemplos en esa publicación se insertan en una tabla en lugar de eliminarlos, pero debería poder adaptar el código.
En el código de ejemplo a continuación, supongo que la clave principal y la clave agrupada de su MySourceTable. Escribí este código bastante rápido y no puedo probarlo:
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
DECLARE @BATCHSIZE INT,
@ITERATION INT,
@TOTALROWS INT,
@MSG VARCHAR(500)
@STARTID BIGINT,
@NEXTID BIGINT;
SET DEADLOCK_PRIORITY LOW;
SET @BATCHSIZE = 4500;
SET @ITERATION = 0;
SET @TOTALROWS = 0;
SELECT @STARTID = ID
FROM MySourceTable
ORDER BY ID
OFFSET 0 ROWS
FETCH FIRST 1 ROW ONLY;
SELECT @NEXTID = ID
FROM MySourceTable
WHERE ID >= @STARTID
ORDER BY ID
OFFSET (60000) ROWS
FETCH FIRST 1 ROW ONLY;
BEGIN TRY
BEGIN TRANSACTION;
WHILE @STARTID IS NOT NULL
BEGIN
WITH MySourceTable_DELCTE AS (
SELECT TOP (60000) *
FROM MySourceTable
WHERE ID >= @STARTID
ORDER BY ID
)
DELETE FROM MySourceTable_DELCTE
OUTPUT DELETED.*
INTO MyBackupTable
WHERE NOT EXISTS (
SELECT NULL AS Empty
FROM dbo.vendor AS v
WHERE VendorId = v.Id
);
SET @BATCHSIZE = @@ROWCOUNT;
SET @ITERATION = @ITERATION + 1;
SET @TOTALROWS = @TOTALROWS + @BATCHSIZE;
SET @MSG = CAST(GETDATE() AS VARCHAR) + ' Iteration: ' + CAST(@ITERATION AS VARCHAR) + ' Total deletes:' + CAST(@TOTALROWS AS VARCHAR) + ' Next Batch size:' + CAST(@BATCHSIZE AS VARCHAR);
PRINT @MSG;
COMMIT TRANSACTION;
CHECKPOINT;
SET @STARTID = @NEXTID;
SET @NEXTID = NULL;
SELECT @NEXTID = ID
FROM MySourceTable
WHERE ID >= @STARTID
ORDER BY ID
OFFSET (60000) ROWS
FETCH FIRST 1 ROW ONLY;
END;
END TRY
BEGIN CATCH
IF @@ERROR <> 0
AND @@TRANCOUNT > 0
BEGIN
PRINT 'There is an error occured. The database update failed.';
ROLLBACK TRANSACTION;
END;
END CATCH;
GO
La parte clave está aquí:
WITH MySourceTable_DELCTE AS (
SELECT TOP (60000) *
FROM MySourceTable
WHERE ID >= @STARTID
ORDER BY ID
)
Cada bucle solo leerá 60000 filas de MySourceTable. Eso debería resultar en un tamaño de eliminación promedio de 4500 filas por transacción y un tamaño de eliminación máximo de 60000 filas por transacción. Si desea ser más conservador con un tamaño de lote más pequeño, también está bien. los @STARTID La variable avanza después de cada bucle para evitar leer la misma fila más de una vez en la tabla de origen.
Me vienen a la mente dos pensamientos:
El retraso probablemente se deba a la indexación con ese volumen de datos. Intente eliminar los índices, eliminarlos y reconstruirlos.
O..
Puede ser más rápido copiar las filas que desea mantener en una tabla temporal, eliminar la tabla con los 16 millones de filas y cambiar el nombre de la tabla temporal (o copiar a una nueva instancia de la tabla fuente).