SQL Server 2016 CTP 2.4: Stretch Database и columnstore индексы смотрим вживую

На прошлой неделе Microsoft выкатила CTP 2.4 SQL Server 2016, и мы наконец нормально посмотрели на две вещи, которые в предыдущих сборках были либо сырыми, либо неполными: Stretch Database и обновлённые columnstore индексы. Оба - в контексте реальных задач, которые к нам приходят.

Stretch Database: архивирование без переезда

Идея простая и давно напрашивалась. У клиентов с большими транзакционными системами типичная история: таблица аудита за восемь лет весит несколько сотен гигабайт, запросы к ней делаются только ради compliance и редких разборов инцидентов, но она лежит рядом с горячими данными и давит на I/O и резервное копирование.

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

Stretch Database делает это прозрачно. Таблица остаётся одна, запросы к ней не меняются, но «холодные» строки по заданному предикату автоматически переезжают в Azure SQL. SQL Server держит маппинг и на запрос SELECT отвечает сам, если данные горячие, или идёт в Azure, если нет. Для приложения - одна таблица, один коннекшн, полный T-SQL.

Настраивается на уровне таблицы:

ALTER TABLE dbo.AuditLog
    SET ( REMOTE_DATA_ARCHIVE = ON (
        FILTER_PREDICATE = dbo.fn_stretchpredicate(EventDate),
        MIGRATION_STATE = OUTBOUND
    ) );

Функция-предикат задаёт, что считать «холодным» - обычно по дате. Миграция идёт в фоне, не блокирует таблицу.

Мы потестировали на стенде с таблицей в несколько миллионов строк. Миграция уходит в фон и работает батчами - не вываливает всё разом в сеть. Интересный момент: данные в Azure шифруются при передаче (TLS) и при хранении (TDE). Microsoft сами к данным доступа не имеют - это важный аргумент для клиентов с параноей по поводу облака.

Есть ограничения, которые пока сдерживают: не работает с таблицами, у которых есть FILESTREAM, не поддерживаются некоторые типы данных, и нет поддержки сжатия строк - то есть если таблица уже с PAGE COMPRESSION, придётся думать. В CTP это всё ещё шероховато.

Columnstore: что изменилось в 2016

Columnstore индексы в SQL Server появились в 2012, в 2014 стали updateable. В 2016 CTP 2.4 два заметных улучшения.

Первое - операционная аналитика в реальном времени. Можно создать некластерный columnstore индекс поверх обычной rowstore таблицы, и OLAP-запросы пойдут через columnstore, а OLTP-вставки - через rowstore. Раньше нужно было выбирать: либо быстрые вставки, либо быстрое сканирование. Теперь - оба режима на одной таблице.

-- Обычная транзакционная таблица
CREATE TABLE dbo.Sales (
    SaleId      bigint NOT NULL PRIMARY KEY,
    CustomerId  int NOT NULL,
    Amount      decimal(18,2) NOT NULL,
    SaleDate    date NOT NULL
);

-- Columnstore для аналитики поверх
CREATE NONCLUSTERED COLUMNSTORE INDEX ix_cs_sales
    ON dbo.Sales (CustomerId, Amount, SaleDate);

Запрос с GROUP BY по Amount и SaleDate уйдёт в columnstore и получит batch mode execution. INSERT - в rowstore без блокировки columnstore индекса.

Второе - batch mode для агрегатов. В 2014 batch mode работал только для hash join и scan. В 2016 добавили batch mode aggregate - GROUP BY на больших объёмах теперь тоже выполняется батчами по 900 строк через vectorized операции. На наших тестах с аналитическими запросами по нескольким десяткам миллионов строк разница в execution time ощутимая.

Что это значит для DWH-клиентов

У нас есть несколько клиентов, где хранилище данных живёт на SQL Server, и типичная боль - таблицы фактов за несколько лет, которые растут быстрее, чем железо. Columnstore там уже используем с 2014, но обновляемые columnstore на аналитических запросах давали компромисс: либо блокировки при загрузке, либо rebuild индекса после ETL.

Новый режим с некластерным columnstore поверх rowstore выглядит как нормальный ответ на задачу: ETL пишет в heap/rowstore, аналитические запросы идут в columnstore, конфликтов нет. Для intraday-аналитики, где нужно видеть данные за последние часы в тех же срезах, что и исторические - это рабочий вариант.

Stretch Database интересен для аудит-таблиц и таблиц событий. Клиенты с compliance-требованиями хранят логи годами, но запросы к старью - единицы в месяц. Гонять эти данные через стандартный бэкап и платить за SAN-место не обязательно, если прозрачная миграция в Azure SQL решает вопрос. Единственный вопрос - latency запросов к холодным данным: Azure SQL это не локальная база, и если кто-то вдруг полезет в логи трёхлетней давности, он подождёт. Для compliance это, как правило, окей.

До GA ещё далеко - SQL Server 2016 пока в CTP, и мы не советуем ни один из клиентских проектов переводить на это прямо сейчас. Но смотреть нужно: когда релиз выйдет, клиенты с большими таблицами аудита придут с вопросом «можно ли так», и хочется иметь готовый ответ, а не разбираться на живом.

Работы с хранилищами данных на SQL Server ведём в рамках DWH и бизнес-аналитики.