Почему последовательные удаления занимают больше времени?

У меня есть таблица (скажем, tableB) с примерно 40 миллионами строк (всегда увеличивающихся). В настоящее время архивирование осуществляется DELETE .. OUTPUT .. INTO .. FROM .. способом. Для архивирования 1000 строк изначально требовалось 3~5 секунд. Но чем больше строк удаляется, тем больше времени требуется. Например, после удаления 10 миллионов строк теперь требуется 35–40 секунд, чтобы удалить 1000 строк.

Чем это вызвано? Как я могу улучшить ситуацию (мне нужно заархивировать не менее 30 миллионов строк)? Если разделение — единственный способ, как я могу сделать это с минимальным временем простоя?

Другая информация:

tableB имеет 2 столбца внешнего ключа (tableAId, tableCId)
Архивирование выполняется на основе поля даты и времени в tableA (в операторе DELETE используется inner-join)
План запроса показывает 81 % затрат на внутреннее соединение.
Если я использую подсказку запроса with (index=ix_time) для tableA, план показывает 100% затрат на поиск по индексу для индекса в столбце внешнего ключа tableB
И tableA, и tableB имеют автоинкремент bigint в качестве первичного ключа.
tableB имеет 4 индекса
tableA имеет 5 индексов
tableA содержит более 30 миллионов строк.
Индексы/статистика перестраиваются/реорганизуются/обновляются каждый день
SQL Server 2008R2
Windows Server 2008R2, 16 ядер, 32 ГБ ОЗУ.
Я случайный банкомат

Выдержка из плана запроса:

Сценарий:

DECLARE @older_than datetime2(0) = '2015-10-01';
DECLARE @i int = 1;
DECLARE @j int = 0;
DECLARE @imax int = 1000;
DECLARE @jmax int = 50;
DECLARE @total int = 0;
DECLARE @t1 DATETIME2(3);
DECLARE @t2 DATETIME2(3);
DECLARE @timetook int;

WHILE @i > 0 AND @j < @jmax
BEGIN
    SET @t1 = GETDATE();
    DELETE TOP (@imax) ss   
        OUTPUT  deleted.[Id]    
          ,deleted.[columnA]
          ,deleted.[columnB]          

        INTO [MyArchive_Data].dbo.tableB([Id]
          ,columnA
          ,columnB)

    FROM [MyLive_Data].dbo.tableB ss
    INNER JOIN [MyLive_Data].dbo.tableA s ON s.Id = ss.tableAID

    WHERE s.Time < @older_than;
    SET @i = @@rowcount;
    SET @j = @j + 1;
    SET @total = @total + @i;
    SET @t2 = GETDATE();
    SET @timetook = datediff(second,@t1,@t2);
    RAISERROR('LOOP %d COMPLETE [%d rows][%d sec]',10,1,@j, @total, @timetook) with nowait;
    WAITFOR DELAY '00:00:03';
END

Обновить

Кажется, что если я исключаю конструкцию цикла (WHILE @i > 0 AND @j < @jmax) и запускаю только оператор DELETE отдельно, это занимает 10-12 секунд. Я наблюдал планы запросов. Это были другие. С включенной конструкцией цикла она использовала index-seek, но без нее использовалась index-scan. Почему?

database sql-server database-administration

mshsayem 01.12.2015 источник

comment

Можете ли вы опубликовать свое DELETE заявление? - Felix Pamittan 01.12.2015

comment

Попробуйте отключить индексы при выполнении операции удаления, так как индексы могут снизить производительность удаления, поскольку SQL-сервер должен обновлять индексы после каждого удаления. - Harsh 01.12.2015

comment

@Felix Pamittan: сценарий опубликован - mshsayem 01.12.2015

comment

@Harsh: не вариант - mshsayem 01.12.2015

comment

Вы используете TOP без ORDER BY. Это предназначено? - Felix Pamittan 01.12.2015

comment

@FelixPamittan: Да. - mshsayem 01.12.2015

comment

Вместо удаления с помощью TOP рассмотрите возможность удаления по диапазонам идентификаторов в дополнение к вашим временным критериям. Я ожидаю, что это обеспечит поиск вместо сканирования. - Dan Guzman 01.12.2015

comment

Что такое размер таблицы А? - Eric 01.12.2015

comment

@DanGuzman: сканирование/поиск составляет всего 4% от стоимости. 81% затрат приходится на слияние-соединение. Если я использую подсказку запроса, это поиск; но тогда 100% стоимость для поиска. - mshsayem 01.12.2015

comment

@Eric: Размер таблицы A превышает 30M. - mshsayem 01.12.2015

comment

Ваш скриншот обрезан, поэтому я не вижу строк Actual. Поскольку стрелки, идущие в Merge Join, довольно тонкие, я думаю, что проблема может быть в другом (да, планы выполнения могут лгать!). Найдите толстые стрелки, предупреждения о ключевом поиске или что-то еще, что показывает огромное несоответствие между ожидаемым и фактически. - Code Different 01.12.2015

comment

Сюрприз: если я исключаю ограничение цикла WHILE @i > 0 AND @j < @jmax, оно сокращается до 10~12 секунд (вместо 40). Я наблюдал за планом запроса. К моему удивлению, планы запросов отличаются. При включенной конструкции цикла используется поиск по индексу. Но без него используется индексное сканирование (быстрее). Вопрос в том, ПОЧЕМУ? - mshsayem 01.12.2015

Ответы (2)

arrow_upward
0
arrow_downward

Не видя полной схемы таблицы с индексами, я бы сказал, что оптимизатор запросов SQL Server считает более эффективным сканирование кластеризованного индекса, поскольку статистика в таблице показывает корреляцию между значениями ID и Time, чтобы знать, что если он запустится в конце кластеризованного индекса и обрабатывает его в обратном порядке, ему придется прочитать меньше строк, чтобы найти строки TOP(x), удовлетворяющие запросу. публикация Брента Озара о сканировании, поиске и статистике< /а>

Также у вас есть оператор WAITFOR в цикле, который, вероятно, вызывает эскалацию блокировки в таблице, поскольку удаление выполняется в неявной транзакции, поэтому удаление будет зафиксировано только после завершения цикла. Попробуйте добавить BEGIN TRANSACTION перед оператором удаления и COMMIT TRANSACTION сразу после него. И, если возможно, удалите оператор WAITFOR, так как он вызывает задержки в обработке.

DECLARE @older_than datetime2(0) = '2015-10-01';
DECLARE @i int = 1;
DECLARE @j int = 0;
DECLARE @imax int = 1000;
DECLARE @jmax int = 50;
DECLARE @total int = 0;
DECLARE @t1 DATETIME2(3);
DECLARE @t2 DATETIME2(3);
DECLARE @timetook int;

WHILE @i > 0 AND @j < @jmax
BEGIN
SET @t1 = GETDATE();
BEGIN TRANSACTION 
DELETE TOP (@imax) ss   
    OUTPUT  deleted.[Id]    
      ,deleted.[columnA]
      ,deleted.[columnB]          

    INTO [MyArchive_Data].dbo.tableB([Id]
      ,columnA
      ,columnB)

FROM [MyLive_Data].dbo.tableB ss
INNER JOIN [MyLive_Data].dbo.tableA s ON s.Id = ss.tableAID

WHERE s.Time < @older_than;
COMMIT TRANSACTION 
SET @i = @@rowcount;
SET @j = @j + 1;
SET @total = @total + @i;
SET @t2 = GETDATE();
SET @timetook = datediff(second,@t1,@t2);
RAISERROR('LOOP %d COMPLETE [%d rows][%d sec]',10,1,@j, @total, @timetook) with nowait;
WAITFOR DELAY '00:00:03';
END

Aaron 01.12.2015

arrow_upward
0
arrow_downward

Ниже приведен пример, который, как я полагаю, будет более эффективным для обработки очень больших архивов. Этот метод удаляет пакетами по диапазону идентификаторов, а не по началу. Вы можете настроить размер пакета в соответствии с вашими потребностями в производительности и параллелизме.

DECLARE
      @older_than datetime2(0) = '2015-10-01'
    , @i int = 1
    , @j int = 0
    , @total int = 0
    , @t1 DATETIME2(3)
    , @t2 DATETIME2(3)
    , @timetook int
    , @MinID int
    , @MaxId int
    , @BatchFirstId int
    , @BatchLastId int
    , @BatchSize int =100000;
SELECT @MinID = MIN(Id), @MaxID = MAX(Id) FROM dbo.TableA;
SET @BatchFirstId = @MinID;
WHILE @BatchFirstId <=  @MaxId
BEGIN

    SET @BatchLastID = @BatchFirstId + @BatchSize - 1;
    SET @t1 = GETDATE();
    DELETE ss   
        OUTPUT  deleted.[Id]    
          ,deleted.[columnA]
          ,deleted.[columnB]          

        INTO [MyArchive_Data].dbo.tableB([Id]
          ,columnA
          ,columnB)

    FROM [MyLive_Data].dbo.tableB ss
    INNER JOIN [MyLive_Data].dbo.tableA s ON s.Id = ss.tableAID
    WHERE s.Time < @older_than
        AND s.Id BETWEEN @BatchFirstID AND @BatchLastID;

    SET @i = @@ROWCOUNT;
    SET @BatchFirstId += @BatchSize;
    SET @j = @j + 1;
    SET @total = @total + @i;
    SET @t2 = GETDATE();
    SET @timetook = datediff(second,@t1,@t2);
    RAISERROR('LOOP %d COMPLETE [%d rows][%d sec]',10,1,@j, @total, @timetook) with nowait;
    WAITFOR DELAY '00:00:03';

END;

Dan Guzman 01.12.2015

Почему последовательные удаления занимают больше времени?

Ответы (2)

Похожие вопросы