Может ли удаление строк сделать таблицу...больше?
Пересказ статьи Brent Ozar. Can deleting rows make a table…bigger?
Michael J. Swart задал интересный вопрос: его таблица содержала 7,5 миллиардов строк и 5 индексов. При удалении 10 миллионов строк он обратил внимание, что индексы стали больше, а не меньше.
Вот один способ, когда удаление может вызвать рост таблицы:
Для демонстрации роста я взял базу данных Stack Overflow (в которой не включен RCSI) и создал набор индексов на таблице Posts. Я проверил размеры индексов с помощью sp_BlitzIndex @Mode = 2 (скопировал результаты в электронную таблицу и немного почистил, чтобы максимизировать плотность информации):
sp_BlitzIndex до
Затем я удалил почти половину строк:
Смешно, что в процессе удаления файл данных увеличивался, чтобы также разместить метки времени! Отчет SSMS использования диска показал рост - здесь только верхушка для демонстрации:
Отчет использования диска в SSMS
Пока выполнялось удаление я снова запустил sp_BlitzIndex. Заметьте, что кластеризованный индекс имеет меньше строк, но его размер уже вырос примерно на 1,5 Гб. Некластеризованные индексы на AcceptedAnswerId выросли существенно - это индексы на малых значениях, которые по большей части NULL, поэтому их размеры почти удвоились!
Я не собирался дожидаться окончания удаления, чтобы проверить это, поэтому остановился на этом. Итак: когда вы выполняете объемное удаление в таблице, которая была реализована до включения RCSI, SI или AG, индексы (включая кластеризованный) могут реально вырасти, чтобы включить добавление временной метки хранилища версии.
Выводы, которые из этого следует сделать:
- Строки были изначально записаны, когда база данных не имела разрешенных Read Committed Snapshot (RCSI), Snapshot Isolation (SI) или Availability Groups (AGs).
- RCSI или SI были разрешены, или база данных была добавлена в AG.
- При удалении 14-байтная временная метка была добавлена к каждой уделенной строке для поддержки чтений RCSI/SI/AG.
Для демонстрации роста я взял базу данных Stack Overflow (в которой не включен RCSI) и создал набор индексов на таблице Posts. Я проверил размеры индексов с помощью sp_BlitzIndex @Mode = 2 (скопировал результаты в электронную таблицу и немного почистил, чтобы максимизировать плотность информации):
sp_BlitzIndex до
Затем я удалил почти половину строк:
BEGIN TRAN;
DELETE dbo.Posts WHERE Id % 2 = 0;
GO
Смешно, что в процессе удаления файл данных увеличивался, чтобы также разместить метки времени! Отчет SSMS использования диска показал рост - здесь только верхушка для демонстрации:
Отчет использования диска в SSMS
Пока выполнялось удаление я снова запустил sp_BlitzIndex. Заметьте, что кластеризованный индекс имеет меньше строк, но его размер уже вырос примерно на 1,5 Гб. Некластеризованные индексы на AcceptedAnswerId выросли существенно - это индексы на малых значениях, которые по большей части NULL, поэтому их размеры почти удвоились!
Я не собирался дожидаться окончания удаления, чтобы проверить это, поэтому остановился на этом. Итак: когда вы выполняете объемное удаление в таблице, которая была реализована до включения RCSI, SI или AG, индексы (включая кластеризованный) могут реально вырасти, чтобы включить добавление временной метки хранилища версии.
Выводы, которые из этого следует сделать:
- RCSI, SI и AG имеют накладные расходы, которые вы могли не принять в расчет.
- После включения RCSI, SI или AG перестройте ваши индексы - не то, чтобы это сделало вашу жизнь легче, но просто чтобы вы могли контролировать расщепление страниц, рост объектов, фрагментацию и журнализацию, а не ждать, пока грянет гром, когда вы заняты совсем другими делами.
- Офлайновые перестройки индексов, похоже, также влияют - Сварт отметил в комментарии, что при тестировании они были перестроены без 14-байтовой отметки версии, а это означает, что последующие обновления и удаления вызвали расщепление страницы и увеличение объекта.
Обратные ссылки
Автор не разрешил комментировать эту запись
Комментарии
Показывать комментарии Как список | Древовидной структурой