Всем добрый день!

Есть служебная таблица, которую нужно периодически чистить. Таблица «горячая», много добавлений-изменений от бизнес-логики, поэтому хочется, чтобы сборщик мусора максимально не отсвечивал.

Получилась конструкция из скрипта ниже: сначала создаем временную таблицу со списком id на удаление, потом удаляем в цикле с отбором по этой времяшке – так получается стабильное время транзакций и минимальное число логических чтений.

В целом, меня тут все устраивает. Единственное, за что цепляется глаз: сортировка перед Merge Join. (полный план - во вложении)




Понятно, почему так получается: сиквел не знает, что num и id растут «в одну сторону», он ожидает примерно такой ситуации:

NUM ID10 515 2
ну и перестраховывается дополнительной сортировкой.

Собственно, вопрос: может быть я что-то пропустил и от этой сортировки можно избавиться? Как-то все-таки подсказать движку, что значения постоянно равномерно растут и выбрав растущие num, он выберет так же растущие id? Сразу добавлю: индекс по num, id или id, num не помогает

Код:

aleks222,

нет, к сожалению, получается хуже:
куча перечитываний ids на каждой итерации для того, чтобы сформировать очередную #to_del
И после этого - практически тот же план, что я привел в начале.

Спасибо всем, перепробовал еще несколько вариантов.

Можно сделать индекс во временной таблице по obj, id, num – доп. сортировка, естественно, пропадает. Но тогда для получения очередной пачки на удаление нужно делать сканирование всей временной таблицы. И это то, от чего я изначально бежал: на большой временной таблице у меня может быть много итераций. И чем ближе к концу цикла, тем дальше в таблице будут нужные значения num – то есть, удаление со временем будет деградировать. С тем же успехом можно просто сделать DELETE TOP (N) в цикле и не городить весь этот огород.

Можно сделать кластерный индекс по obj, id и некластерный по num. Тогда сиквел отбирает записи по индексу с num и тут понимает, что ключи кластерного индекса у него же уже считаны – и опять уходит в сортировку (не говорю, что это хуже, чем key lookup, но это и не лучше исходного варианта)

Ну и изначальный вариант, по сути мало отличающийся от предыдущего.

В волшебном мире розовых пони я бы вообще выкинул поле num и фильтровал пачки по id (ну и сделал бы соответствующий индекс на таблице ids). Но в реальности id распределён неравномерно, в распределении возможны дыры, так что надёжнее все-таки принудительно пронумеровать времяшку.

В целом, сортировка на стабильно коротком объёме видится меньшим злом, чем замедляющееся на каждой итерации сканирование таблиц. Всем еще раз спасибо, думаю, можно закрывать.

Зачем делать просто, когда можно делать сложно? Тем более, что простые пути ведут к неожиданным последствиям: https://dba.stackexchange.com/questions/246833/deleting-millions-of-records-on-sql-server-14-0

aleks2221. Выбираем ВСЕ, что НЕ нужно удалять.
2. Тупо удаляем все, что не входит в набор 1.
3. Пачками не более 5000 шт.


Ну то есть у нас на каждой итерации удаления будет какой-нибудь Nested Loops для проверки на вхождение в "белый список" (из шага 1). А если этот список окажется большим? Как-то рискованно, я уж лучше с моим Merge join останусь

Сразу в сторону: пока проблемы с очисткой мусора выстреливают хоть и метко, но редко. Так что про партиционирование пока думать рано, но когда (и если) этот скрипт перестанет справляться, разбитие на секции будет следующим шагом.

Подумал над вашим вариантом и понял, что исходный план примерно так и работает:
1. из #to_del выбирается очередная пачка с отбором по num (номер по порядку)
2. выполняется та самая сортировка, из-за которой я написал пост. Находится минимальное и максимальное значение ключей
3. Дальше merge по найденным границам.



--
Как ни странно, последний вариант aleks222 оказался немножко лучше всех предыдущих попыток. Если сделать "белый список" и создать по нему индекс по obj, id - в плане выбирается Merge join. По суммарному числу чтений получается то же самое, что и мой исходный вариант, но нет сортировки, а число чтений из "белого списка" постепенно увеличивается от итерации к итерации - то есть, этот вариант действительно выгоден, когда в таблице удаляется на порядки больше строк, чем остается.

msLex, invm,

Да, спасибо. Я сначала думал, что merge передаст границы в нижнюю таблицу, поэтому и не заподозрил разницы. Проверил вживую - действительно, у обращения к ids появляются предикаты, он превращается в Seek и читает гораздо меньше строк.

Ну и к слову, на какой-то итерации merge все-таки превращается в hash match - но это уже детали. Посмотрю подробнее, если будет мешать - можно действительно хинт добавить.