SQL Server 2012 RC и ColumnStore: как аналитический запрос на 40 секунд укладывается в три

Microsoft в феврале выкатил Release Candidate SQL Server 2012, и мы наконец добрались до функции, которая интересовала нас ещё с момента анонса на TechEd: ColumnStore Index. Название говорящее - индекс, в котором данные хранятся по столбцам, а не по строкам. Для OLTP это звучит странно, но для аналитических запросов, которые читают пару колонок из миллиона строк, идея имеет смысл.

Поставили RC на тестовую машину, взяли реальную задачу и посмотрели, что получится.

Что за задача

У одного из клиентов есть таблица продаж - строки за несколько лет, несколько десятков миллионов записей. Над ней живёт отчёт: продажи по продуктовым группам и регионам за выбранный период с разбивкой по месяцам. Классический управленческий срез, ничего экзотического.

Запрос агрегирует суммы по нескольким измерениям, джойнит справочники, группирует. На боевом сервере он уходил в 40 секунд при средней загрузке. Для ночного ETL это терпимо, но отчёт открывается и днём - и пользователи периодически жаловались, что «всё висит». Стандартные индексы по дате и группе товара уже стоят, дальнейшая оптимизация классическим способом упиралась в то, что движку всё равно надо прочитать огромный объём строк.

Как устроен ColumnStore

Обычный индекс B-tree хранит строки целиком - все колонки вместе. При запросе, который читает только три из двадцати колонок, движок тащит остальные семнадцать вхолостую. ColumnStore переворачивает это: каждая колонка хранится отдельно и сжимается по-своему. При агрегации по двум-трём столбцам читается только то, что нужно. Плюс компрессия на числовых данных получается очень хорошей - повторяющиеся значения вроде кодов групп товаров складываются эффективно.

В SQL Server 2012 это называется xVelocity - технология, которую Microsoft купил вместе с компанией Vertipaq (та самая, что стоит за Analysis Services в PowerPivot). Теперь она приходит прямо в реляционный движок.

Создать индекс просто:

CREATE NONCLUSTERED COLUMNSTORE INDEX ix_sales_columnstore
ON dbo.Sales (SaleDate, ProductGroupID, RegionID, Amount, Quantity);

Указываешь только те колонки, которые нужны для аналитических запросов. Всё остальное остаётся в обычной строковой структуре.

Что получилось

Создали индекс на тестовой копии таблицы, запустили тот же запрос. Время выполнения упало примерно с 40 секунд до 3. Без каких-либо изменений в самом запросе - оптимизатор сам выбрал ColumnStore, когда увидел, что он подходит.

Это не случайное везение: именно такой сценарий - агрегация по нескольким колонкам большой таблицы - это то, для чего индекс проектировался. Оптимизатор перешёл в batch mode вместо row mode, и это само по себе даёт заметный выигрыш за счёт обработки данных блоками по несколько тысяч строк вместо поштучной.

Важная оговорка: создание индекса заняло несколько минут и потребовало заметного дискового места. Не катастрофа, но нужно планировать.

Где это работает, а где нет

Несколько наблюдений по итогам тестирования, которые стоит держать в голове.

Сценарии, где ColumnStore реально помогает. Большие таблицы фактов в хранилище данных - продажи, транзакции, события. Запросы с агрегацией (SUM, COUNT, AVG) по нескольким измерениям. Аналитические отчёты, где читается небольшая доля колонок от общего числа. По сути, это классическая star schema или flat-таблица с деналормализованными измерениями.

Где не поможет или помешает. Здесь главный сюрприз: таблица с ColumnStore Index становится read-only. Никаких INSERT, UPDATE, DELETE - получите ошибку. Это не баг, это архитектурное решение. Microsoft пишет об этом прямо в документации RC, но в порыве энтузиазма легко упустить. Для хранилища данных, где данные загружаются пакетами ночью (drop index - load - recreate index), это терпимо. Для OLTP-таблиц с постоянными записями - непригодно.

OLTP-запросы по первичному ключу тоже не выиграют: ColumnStore не заменяет B-tree индекс для точечного поиска. Это два разных инструмента для двух разных задач.

Ограничение по типам колонок. Не все типы поддерживаются: varchar(max), nvarchar(max), xml и ряд других не входят в ColumnStore. Если в аналитических запросах есть такие поля - придётся думать, как обойти.

Что из этого следует для практики

Схема, которая вырисовывается: ColumnStore Index имеет смысл на таблицах фактов в хранилище данных, где данные загружаются пакетно и аналитические запросы тяжёлые. Обычная картина - ночной ETL заливает данные, индекс пересоздаётся или обновляется, днём отчёты работают быстро.

Для клиента с тем самым отчётом по продажам это выглядит так: добавить ColumnStore на таблицу фактов, перевести загрузку на схему с пересозданием индекса после batch-загрузки. Технически несложно, выигрыш по времени отчёта очевиден.

Но RC - это RC. До GA ещё несколько месяцев, и в продакшен мы ничего ставить не будем. Сейчас это тест и подготовка: понять, какие таблицы являются кандидатами, как выглядит сценарий загрузки с пересозданием индекса, где ограничение read-only создаёт проблему, а где нет. Когда выйдет финальная версия - будем готовы, а не начинать с нуля.

Один нюанс, который пока открытый: насколько стабильно ведёт себя оптимизатор при смешанных запросах, часть которых выигрывает от ColumnStore, а часть - нет. На тестовой базе мы видели несколько случаев, когда оптимизатор выбирал ColumnStore там, где это не давало выигрыша. Возможно, это поведение RC, которое подправят к GA. Смотрим.