Для понимания сути фрагментации таблиц СУБД необходимо понимать, каким образом устроена структура хранения данных в СУБД.Вся информация в СУБД хранится не в одном большой файле, а делится на блоки (pages). Само явления фрагментации делится на 2 типа: внутренняя и внешняя.
Внутренняя фрагментация индексов
Пример 1
Если мы записываем большие данные, например, по 5 Кб, то в каждом блоке будет по 1 записи.
Часть из выделенной области останется пустой.
Пример 2
При изменении данных, строка удаляется, и на ее место добавляется новая (измененная). Если места на странице не хватает, то строка будет добавлена на другую (в конец), а в текущем блоке будет ссылка на эту строку. При этом останется свободная, но уже выделенная область.
Пример 3
При добавлении новой информации в индекс, СУБД анализирует, может ли она вставить данные туда, где должна быть строка согласно индексу. Если места не достаточно, то блок разбивается пополам (page split), и получается 2 блока, в нужный добавляется строка. При этом в обеих страницах остается незанятое пространство.
Такое явление называется внутренней фрагментацией.
Вследствие внутренней фрагментации, при работе с таблицами, повышается количество операций ввода/вывода, потому что записи находятся не компактно и pages имеют «пустые» места. А так же увеличивается нагрузка на память, т.к. СУБД кэширует лишнюю информацию, которая занимает больше места.
Внешняя фрагментация индексов
Когда мы добавляем данные в СУБД (в разные таблицы), при необходимости СУБД создает новые блоки (pages) для хранения данных. Таким образом, на жестком диске сами страницы могут идти непоследовательно (для конкретной отдельной таблицы), потому что могут добавляться блоки из другой таблицы. Т.е. на самом физическом диске, если считывать данные из таблицы, блоки не идут последовательно а разбросаны по секторам жесткого диска.
Такое явление называется внешней фрагментацией.
Вследствие внешней фрагментации, при чтении информации из таблицы, физически данные читаются из разных секторов диска непоследовательно. Т.е. OS приходится перескакивать через секторы жесткого диска, что приводит к увеличению времени операций ввода/вывода ( I/O).
Команда СУБД «Shrink»
Команда СУБД shrink переносит данные из крайних страниц в пустые места первых страниц. Тем самым уменьшается кол-во страниц и объем СУБД.
При этом в случае с кучей, мы не можем гарантировать, что записи будут выбираться запросом в том порядке, в котором они добавлялись, т.к. нет сортировки. Так же на порядок выбранных запросом данных влияет и распараллеливание чтения данных (механизм оптимизации в СУБД).
Реструктуризация и реиндексация таблиц
“Прыгающие” ключи, которые приводят к фрагментации таблиц – это плохое явление, которое приводит к деградации производительности. Чтобы бороться с фрагментацией, существуют специальные операции в СУБД, которые называются реорганизация (REORGANIZE) индексов и перестроение (REBUILD) индексов.
AVERAGE FRAGMENTATION PERCENT
Перед проведением операций реорганизации и перестроения, требуется определить, что нам требуется сделать, для этого необходимо понять степень фрагментации. Это можно сделать командой SQL:
SELECT indexes.Name, object_name(ph_stats.object_id), indexes.fill_factor, ph_stats.page_count, ph_stats.fragment_count, ph_stats.avg_fragmentation_in_percent FROM sys.dm_db_index_physical_stats (DB_ID (N'ИмяБазыДанных') , NULL , NULL , NULL , NULL) AS ph_stats INNER JOIN sys.indexes AS indexes ON ph_stats.object_id = indexes.object_id AND ph_stats.index_id = indexes.index_id WHERE ph_stats.avg_fragmentation_in_percent > 0 order by ph_stats.avg_fragmentation_in_percent DESC; GO
Если процент фрагментации от 5% до 30%, то следует делать REORGANIZE, в случае > 30% следует делать REBUILD.
REORGANIZE
Команда СУБД, которая вызывает реорганизацию таблицы:
ALTER INDEX PK___Referen__AC8ED0C4178F715E ON _Reference31 REORGANIZE; REBUILD
Команда СУБД, которая вызывает полное перестроение таблицы:
ALTER INDEX PK___Referen__AC8ED0C4178F715E ON _Reference31 REBUILD WITH (FILLFACTOR = 80) FILLFACTOR
FILLFACTOR – параметр, который задает процент заполненности блоков (pages), при их формировании. Т.е. другими словами, если fillfactor = 80, то страницы будут заполнены на 80%.
Таким образом, в случае добавления новых строк в страницах есть место для их добавления (20% зарезервировано) или для изменения старых строк, которые будут содержать больше информации.
FILLFACTOR < 100 помогает избегать фрагментации, но использовать его следует осторожно.
Данная тема подробнее рассматривается в пакете видео-курса "Секреты 1С: Эксперта" Шаг 2. Занятие 04-02 Хранение данных в СУБД.
