Приветствую всех! Прошу поделиться опытом и своими соображениями.

По данным sp_spaceused в базе несколько таблиц с большим размером unused space (до 2/3 всего размера таблицы). Таблицы большие (20 ГБ), кластеризованные, не кластерных индексов нет, столбцы не удалялись, var* полей нет, записи НЕ удаляются, только добавляются (BULK INSERT'ом) и редко обновляются.

Значение sys.dm_db_index_physical_stats.avg_page_space_used_in_percent близко к 100, то есть страницы заполнены почти полностью, sys.allocation_units показывает большое количество неиспользуемых страниц, а в sys.dm_db_database_page_allocations (DBCC IND) видно, что в большинстве экстентов заняты только 1-2 страницы (из 8).

Почему это может происходить и можно ли это исправить?

rows reserved data index_size unused
147320591 21309072 KB 7742560 KB 33848 KB 13532664 KB
718730165 26862544 KB 14615112 KB 70040 KB 12177392 KB
686030416 27038328 KB 14857064 KB 71584 KB 12109680 KB
284819036 20882944 KB 6692912 KB 31464 KB 14158568 KB

Структура одной из таблиц: [int, int] (PK), tinyint, tinyint

a_voronin,

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

Yasha123,

Видимо при пакетной вставке механизм меняется (как написано по ссылкам приведённым a_voronin).

Версия сервера 2016. Насчёт минимального протоколирования точно не знаю используется ли оно - в разных источниках разные условия для его использования, у меня простая модель восстановления, TABLOCK не используется, таблица класстернаяя, но не пустая, других индексов нет.

Минимальное протоколирование меня не сильно волнует, поскольку записи маленькие и пакеты небольшие. Так может даже лучше в журнал транзакций писать, чем выполнять принудительное сбрасывание страниц при фиксации транзакции.

Главное, что я нашёл по ссылкам, это:
"Bulk Load Batch Size Implications An important consideration in SQL 2016 and later with bulk load context and minimal logging is that each batch allocates new extent(s) (8 contiguous 8K pages, 64K) rather than using existing unused pages in existing extents."

Судя по обилию почти пустых экстентов, в моём случае это и происходит. Покапаю в этом направлении, послежу за выделением места при вставке. Ещё попробую флаг 692 и поговорю с разработчиками софта об увеличении размера пакетов при вставке.

Провёл эксперимент. Создал кластерную таблицу с тестовыми данными на 9 страниц (чтобы перейти на использование однородных экстентов). При выполнении BULK INSERT (маленький пакет 1 КБ), когда требуется добавить страницу, сразу выделяется новый экстент. При включении флага 692 новая страница выделяется в одном из уже выделенных экстентов.