Columnstore-индексы в SQL Server: когда они реально работают на ваших больших таблицах

Короткая историческая справка

Columnstore появился в SQL Server 2012 — тогда ещё read-only nonclustered. В 2014 сделали clustered и updatable, в 2016 — добавили operational analytics (nonclustered columnstore поверх обычной OLTP-таблицы, фильтруемый). В 2017 расширили поддержку computed columns, в 2019 подтянули batch mode и для rowstore тоже. Сейчас это уже зрелая фича, которой можно пользоваться на проде без оглядки — вопрос только в том, куда её прикладывать.

Как устроен columnstore — коротко и по делу

Классический rowstore — это B-tree из страниц по 8 КБ. На странице лежат строки целиком. Чтобы прочитать одну строку, SQL Server достаёт одну страницу — быстро, если есть нужный индекс, и долго, если таблица широкая, а запросу нужны всего две колонки: всё равно приходится поднимать страницы с тридцатью лишними полями.

Columnstore хранит данные колонками. Значения одной колонки лежат подряд, сжимаются собственным алгоритмом (dictionary, run-length, bit-packing — SQL сам выбирает) и не перемешиваются с другими колонками. Степень сжатия зависит от данных: колонка «Дата» жмётся в 30-50 раз (повторяющиеся значения + dictionary), колонка «Сумма» с разбросанными decimal — в 2-3 раза.

Единицы хранения:

• Rowgroup — горизонтальный срез примерно на 1 048 576 строк (2^20). Таблица на 500 млн строк — это около 480 rowgroups.
• Segment — вертикальный кусок: одна колонка внутри одного rowgroup. То есть rowgroup + колонка = сегмент. Сегмент сжимается независимо.
• Delta store — маленькая rowstore-табличка на каждой секции, куда попадают только что вставленные строки. Когда delta набирает ~1 млн, она закрывается и превращается в COMPRESSED rowgroup (tuple mover делает это фоном или явно по REORGANIZE).

Выигрыш — в двух вещах.

Первое: segment elimination. У каждого сегмента в метаданных хранятся min/max. Запрос WHERE [Дата] >= '2025-01-01' пройдётся по метаданным и тронет только те rowgroups, у которых max(Дата) ≥ '2025-01-01'. Остальные 300 rowgroups просто пропускаются. Эффект особенно сильный, если данные вставляются в таблицу по возрастанию даты (а в DWH так и есть).

Второе: batch mode execution. Обычный executor обрабатывает строки по одной: взял, посчитал, положил. Для миллиарда строк это миллиард вызовов итератора. Batch mode обрабатывает пачками по 900 строк, которые целиком влезают в CPU-кэш. Агрегаты (SUM, COUNT, AVG) и hash join ускоряются в разы просто за счёт того, что процессор перестаёт ждать память.

Какие бывают columnstore

Для классической задачи «храним фактовую таблицу в DWH и читаем её из Tabular-модели» в 99% случаев подходит Clustered columnstore. Дальше по тексту, если не оговорено отдельно, имеется в виду он.

Минимальный рабочий пример

Создаём таблицу сразу как columnstore — без PK, без обычных B-tree:

CREATE TABLE [dbo].[tf Продажи_columnstore] (
    [SK_Дата]           INT           NOT NULL,
    [SK_Номенклатура]   BIGINT        NOT NULL,
    [SK_Контрагент]     BIGINT        NOT NULL,
    [SK_Подразделение]  INT           NOT NULL,
    [Количество]        DECIMAL(18,3) NOT NULL,
    [Сумма]             DECIMAL(18,2) NOT NULL,
    [Себестоимость]     DECIMAL(18,2) NULL,
    [Маржа]             DECIMAL(18,2) NULL
);

CREATE CLUSTERED COLUMNSTORE INDEX CCI_tf_Продажи
    ON [dbo].[tf Продажи_columnstore];

Если таблица уже существует и живёт с кластерным PK, его придётся снести — кластерным индексом таблица может быть только одним:

ALTER TABLE [dbo].[tf Продажи]
    DROP CONSTRAINT [PK_tf_Продажи];

CREATE CLUSTERED COLUMNSTORE INDEX CCI_tf_Продажи
    ON [dbo].[tf Продажи]
    WITH (DROP_EXISTING = OFF, MAXDOP = 4);

MAXDOP = 4 здесь — не каприз, а важная деталь. При билде columnstore параллельные потоки могут размазать данные по rowgroups неоптимально: вместо 500 полных rowgroups вы получите 2000 полупустых. Для первоначального билда часто лучше MAXDOP = 1 — медленнее, зато rowgroups выйдут ровные.

Четыре сценария, где columnstore выигрывает кратно

А. Агрегаты по большой фактовой таблице за период

SELECT YEAR([Дата]) AS Год, SUM([Сумма]) AS Оборот
FROM [dbo].[tf Продажи]
WHERE [Дата] BETWEEN '2023-01-01' AND '2025-12-31'
GROUP BY YEAR([Дата]);

Rowstore-вариант таблицы из 30 колонок и 500 млн строк будет читать все страницы, хотя для ответа нужны только две колонки (Дата и Сумма). Columnstore читает ровно два сегмента на каждый rowgroup и выбрасывает rowgroups, не попадающие в диапазон дат, через segment elimination. Типичное ускорение — 20-50×. Плюс дисковое место: то, что было на 350 ГБ, становится 45-60 ГБ. Снижается нагрузка на SSD, а не только время запроса.

Б. Сканирование широкого диапазона с фильтром по дате

SELECT SK_Номенклатура, SK_Контрагент, Количество, Сумма
FROM [dbo].[tf Продажи]
WHERE [Дата] >= '2025-01-01';

Классический rowstore-подход — индекс по дате + key lookup на каждую строку за остальными колонками. Если подходит 50 млн строк, будет 50 млн lookup'ов — проще сделать table scan. Columnstore пропустит все rowgroups, где max(Дата) < 2025-01-01, прочитает только нужные колонки из оставшихся rowgroups и отдаст результат за секунды даже на миллиардной таблице.

В. Join большой fact со справочником

SELECT d.Категория, SUM(f.[Сумма]) AS Оборот
FROM  [dbo].[tf Продажи]          AS f
JOIN  [dbo].[tr Номенклатура]     AS d
      ON f.SK_Номенклатура = d.SK_Номенклатура
WHERE d.Категория = N'Вода'
GROUP BY d.Категория;

Batch mode + hash join на маленькой dim-таблице — то, ради чего columnstore и придумывался. Запрос на миллиардной таблице фактов и справочнике в 50 тыс. строк отрабатывает за 2-3 секунды. На аналогичном rowstore — минуты.

Г. Tabular / Power BI import с инкрементом

Когда SSAS Tabular обновляет партицию из таблицы фактов, он читает только те колонки, которые есть в модели (обычно 8-15 из 25-30). Из columnstore это буквально чтение нужных сегментов, остальное не трогается. Full refresh модели на 500 млн строк с rowstore — час; с columnstore — 20-25 минут. Инкрементальные обновления — аналогично пропорционально.

Четыре сценария, где columnstore хуже rowstore

А. Маленькие таблицы (< 1-2 млн строк)

Минимальный размер rowgroup — около миллиона строк. На таблице в 50 тыс. строк у вас будет ровно один OPEN rowgroup, который никогда не закроется естественно. Компрессии нет, segment elimination не работает (нечего elimin-ить), batch mode не окупается. Зато есть оверхед на delta store и сложности с updatе. Вывод: справочники (tr Контрагенты, tr Номенклатура) — всегда rowstore.

Б. Точечные поиски одной-двух строк по ключу

SELECT * FROM [dbo].[tf Продажи]
WHERE SK_Продажи = 123456789;

Rowstore с PK вернёт эту строку за миллисекунду: три прыжка по B-tree и одна страница. Columnstore вынужден поднимать минимум целый rowgroup (~1 млн строк) и применять предикат через bitmap — это десятки миллисекунд, а на холодном кэше и больше. Если у вас такие запросы есть и они критичны — добавьте NCI rowstore поверх CCI по нужному ключу.

В. Высокочастотные UPDATE отдельных строк

UPDATE в columnstore работает так: старая версия строки помечается как deleted (в deleted bitmap), новая версия уходит в delta store. После некоторого объёма deleted_rows и наполнения delta сегмент приходится переупаковывать. Пока delta растёт, запросы к этому rowgroup работают частично через rowstore-ядро и теряют скорость. Если у вас тысячи UPDATE в минуту — columnstore будет постоянно «ломаться», а ночные REORGANIZE не успевать всё приводить в порядок.

Г. Таблицы с 1-3 колонками

Главное преимущество columnstore — не читать лишние колонки. Если в таблице всего 3 колонки и типичный запрос читает все три — экономить нечего. Остаются компрессия и batch mode, но на узких таблицах выигрыш от batch mode маленький, а rowstore-компрессия страниц (PAGE compression) иногда и близко подходит к columnstore. Оверхед того не стоит.

Эксплуатация: rowgroups, deleted rows, регламент

Дальше речь про то, что надо периодически делать с уже работающим columnstore, иначе он деградирует.

Смотрим состояние rowgroups

SELECT
    OBJECT_NAME(object_id)              AS TableName,
    row_group_id,
    state_desc,
    total_rows,
    deleted_rows,
    size_in_bytes,
    CAST(size_in_bytes * 1.0
         / NULLIF(total_rows, 0) AS DECIMAL(10,2))  AS bytes_per_row
FROM sys.dm_db_column_store_row_group_physical_stats
WHERE object_id = OBJECT_ID(N'dbo.[tf Продажи]')
ORDER BY row_group_id DESC;

Что считать нормой, а что поводом лезть с REORGANIZE:

• state_desc = 'COMPRESSED' — зрелый rowgroup, всё ок.
• 'OPEN' — rowgroup принимает строки через delta store, ещё не заполнен до миллиона.
• 'CLOSED' — заполнен, но tuple mover ещё не дошёл. REORGANIZE закроет принудительно.
• deleted_rows / total_rows > 10% — пора REORGANIZE: мёртвый груз замедляет сканы.
• total_rows < 100 000 на COMPRESSED — это признак плохого билда (например, делали INSERT мелкими порциями). Fragmented rowgroups — кандидаты на merge через REORGANIZE или полный REBUILD.

Регламент обслуживания

-- Ночная задача после загрузки DWH:
-- собирает мелкие rowgroups, гонит CLOSED в COMPRESSED,
-- удаляет deleted_rows.
ALTER INDEX CCI_tf_Продажи
    ON [dbo].[tf Продажи]
    REORGANIZE WITH (COMPRESS_ALL_ROW_GROUPS = ON);

-- Редко (раз в месяц, по секции) — полная перестройка:
ALTER INDEX CCI_tf_Продажи
    ON [dbo].[tf Продажи]
    REBUILD PARTITION = 2025
    WITH (MAXDOP = 1, DATA_COMPRESSION = COLUMNSTORE);

REORGANIZE — онлайн, блокировок почти нет, можно гонять каждую ночь. REBUILD — тяжёлая операция, на большой таблице занимает часы и требует эксклюзивной блокировки на секцию. Поэтому REBUILD всегда только на одну секцию за раз и только если deleted_rows и fragmentation сильно зашкаливают.

Подводные камни

• OLTP-нагрузка и columnstore несовместимы в лоб. Если в таблицу идут тысячи UPDATE/DELETE в минуту — либо не ставьте CCI, либо используйте NCCI с фильтром по «холодному» диапазону (например, данные старше 30 дней).
• LOB-типы off-row. NVARCHAR(MAX), VARBINARY(MAX), XML — хранятся отдельно, в columnstore почти не жмутся. Если у вас в факте поле «Комментарий NVARCHAR(MAX)» — вынесите его в отдельную rowstore-таблицу, связанную по SK. Иначе CCI по этой колонке будет читаться в rowstore-режиме, теряя все бонусы.
• Всегда секционируйте большие таблицы. На 500 млн+ строк partitioning по дате обязателен — иначе REBUILD превратится в недельную операцию. Plus — partition switching для быстрой загрузки новых данных: грузите в staging-таблицу с той же схемой, потом ALTER TABLE ... SWITCH PARTITION — миллисекунды.
• Computed columns. В SQL Server 2016 и старше persistent computed columns в columnstore не индексировались — приходилось материализовать физически. С 2017+ поддерживаются, но non-persistent по-прежнему считаются на каждый запрос.
• CDC и триггеры — с оглядкой. CDC на CCI-таблице формально работает, но добавляет нагрузку на каждый INSERT/UPDATE. Триггеры на таблицу с CCI лучше не ставить вообще — они ломают batch-вставку и превращают её в построчную.
• Бэкапы. Columnstore сжат на уровне страниц, и бэкап остаётся компактным. Но при RESTORE на другой сервер столько же места на диске потребуется — не забывайте при планировании тестовой/DR-площадки.
• Memory Grant. Batch mode обожает память. Запросы с большими hash join могут просить десятки ГБ grant'а — если Max Server Memory прижат, sort/hash улетает в tempdb, и скорость падает. Для аналитического сервера с columnstore закладывайте не меньше 128 ГБ RAM.

Что получаем на реальных проектах

Усреднённые цифры с нескольких DWH-проектов, где columnstore приложен к правильным таблицам (большие факты, не справочники):

Самое важное в этой таблице — последняя строка с точечным SELECT. Если ваш бизнес-кейс включает такие запросы, columnstore в лоб не подойдёт — нужен или NCI rowstore поверх CCI, или отдельная rowstore-копия для таких сценариев.

Проект всегда начинаем с rowstore. Таблицу фактов переводим на columnstore в тот момент, когда её размер переваливает за 50-100 млн строк или Tabular-рефреш начинает идти дольше SLA. Меньше этого порога — columnstore добавляет обслуживания больше, чем даёт выигрыша.

-- 1. Партиционная функция по дате (ежемесячные секции, 5 лет истории)
CREATE PARTITION FUNCTION PF_tf_Продажи_Date (DATE)
AS RANGE RIGHT FOR VALUES (
    '2021-01-01','2021-02-01','2021-03-01', -- ...полный список...
    '2026-04-01','2026-05-01','2026-06-01'
);

-- 2. Партиционная схема — все секции в одну FG, либо разнести по tier'ам
CREATE PARTITION SCHEME PS_tf_Продажи_Date
AS PARTITION PF_tf_Продажи_Date
ALL TO ([FG_DWH_HOT]);

-- 3. Копия таблицы со схемой секционирования
CREATE TABLE [dbo].[tf Продажи_v2] (
    [SK_Продажи]        BIGINT        NOT NULL,
    [Дата]              DATE          NOT NULL,
    [SK_Номенклатура]   BIGINT        NOT NULL,
    [SK_Контрагент]     BIGINT        NOT NULL,
    [Количество]        DECIMAL(18,3) NOT NULL,
    [Сумма]             DECIMAL(18,2) NOT NULL,
    [Себестоимость]     DECIMAL(18,2) NULL
) ON PS_tf_Продажи_Date ([Дата]);

-- 4. Clustered columnstore по той же схеме
CREATE CLUSTERED COLUMNSTORE INDEX CCI_tf_Продажи_v2
    ON [dbo].[tf Продажи_v2]
    WITH (MAXDOP = 1, DATA_COMPRESSION = COLUMNSTORE)
    ON PS_tf_Продажи_Date ([Дата]);

-- 5. Перенос данных порциями (по секциям)
INSERT INTO [dbo].[tf Продажи_v2] WITH (TABLOCK)
SELECT *
FROM [dbo].[tf Продажи]
WHERE [Дата] >= '2021-01-01' AND [Дата] < '2022-01-01';
-- повторить для остальных лет

-- 6. Переключение: переименовываем таблицы
EXEC sp_rename N'[dbo].[tf Продажи]',    N'tf Продажи_old';
EXEC sp_rename N'[dbo].[tf Продажи_v2]', N'tf Продажи';

-- 7. Старые секции переводим в COLUMNSTORE_ARCHIVE
ALTER INDEX CCI_tf_Продажи
    ON [dbo].[tf Продажи]
    REBUILD PARTITION = 1
    WITH (DATA_COMPRESSION = COLUMNSTORE_ARCHIVE);

Чек-лист: пойдёт ли columnstore на вашу таблицу

Пробегите по пунктам. Пять и больше «да» — таблица реальный кандидат на CCI.

• Это таблица фактов (tf), а не справочник (tr).
• Текущий размер — больше 50 млн строк и продолжает расти.
• Основная нагрузка — агрегаты и сканы по диапазонам дат, а не точечные SELECT по PK.
• UPDATE / DELETE на отдельных строках — редкие (< 5% строк в сутки).
• Таблица участвует в Tabular-модели или читается Power BI import'ом.
• Секционирование по дате есть или его можно ввести без перелома процессов.
• В таблице нет или мало LOB-колонок (NVARCHAR(MAX), VARBINARY(MAX)).
• На таблице нет триггеров, а CDC выключен или его overhead приемлем.

Если «да» — 4 и меньше, columnstore, скорее всего, не окупится: обслуживания добавит, выигрыш будет копеечный. Оставайтесь на rowstore с PAGE compression и хорошими покрывающими индексами.

Итог

Columnstore — инструмент точечного применения. На больших таблицах фактов он даёт кратное ускорение и сильную экономию диска. На справочниках и OLTP-таблицах с частыми UPDATE — вредит. Не накатывайте CCI на всё подряд «для аналитики» — выберите 2-3 самые большие таблицы фактов, перенесите на columnstore, настройте ночной REORGANIZE, и получите именно тот эффект, ради которого эта фича и делалась.

Связанные материалы

• Как построить корпоративное DWH поэтапно — где в архитектуре живут фактовые таблицы, на которые стоит класть CCI
• Как ускорить отчёты Power BI — columnstore на стороне SQL как один из рычагов ускорения import-моделей
• Рефакторинг Power BI: как ускорить модель в 10 раз — связка «быстрый источник + правильная модель»
• Наша методология BI-проекта — на каком этапе мы принимаем решение о переводе таблиц на columnstore