Columnstore-индексы – это мощный инструмент для хранения аналитических данных прямо в SQL Server. Эта функция улучшалась в каждой версии SQL Server за последние десять лет, и SQL Server 2025 не стал исключением.
Новые улучшения сосредоточены на обеспечении непрерывности бизнеса и повышении производительности. Упорядоченные (ordered) кластерные и некластерные columnstore-индексы, а также операции сжатия базы данных и файлов получили значительные улучшения.
Упорядоченные некластерные Columnstore-индексы
Упорядоченные кластерные columnstore-индексы были представлены в SQL Server 2022. Они позволяли использовать опцию ORDER при создании индекса, чтобы данные предварительно сортировались по одному или нескольким ключевым столбцам. Это давало лучшие возможности для сегментного исключения и повышало производительность и степень сжатия.
SQL Server 2025 добавляет ту же возможность и для некластеризованных columnstore-индексов. Ранее порядок данных в таком индексе наследовался от кластеризованного rowstore индекса. Если порядок совпадал с аналитическими запросами — всё было хорошо. Если нет, приходилось создавать новые таблицы или менять архитектуру.
Пример: создание большой таблицы
USE WideWorldImporters;
SET STATISTICS IO ON;
SELECT TOP 0 *
INTO dbo.SalesOrdersBIG
FROM Sales.Orders;
ALTER TABLE dbo.SalesOrdersBIG
ADD CONSTRAINT PK_SalesOrdersBIG PRIMARY KEY CLUSTERED (OrderID ASC);
GO
INSERT INTO dbo.SalesOrdersBIG
(OrderID, CustomerID, SalespersonPersonID, PickedByPersonID, ContactPersonID,
BackorderOrderID, OrderDate, ExpectedDeliveryDate,
CustomerPurchaseOrderNumber, IsUndersupplyBackordered,
Comments, DeliveryInstructions, InternalComments,
PickingCompletedWhen, LastEditedBy, LastEditedWhen)
SELECT
Orders.OrderID + (ORDERS2.OrderID * 100000) AS OrderID,
Orders.CustomerID, Orders.SalespersonPersonID, Orders.PickedByPersonID,
Orders.ContactPersonID, Orders.BackorderOrderID, Orders.OrderDate, Orders.ExpectedDeliveryDate,
Orders.CustomerPurchaseOrderNumber, Orders.IsUndersupplyBackordered,
Orders.Comments, Orders.DeliveryInstructions, Orders.InternalComments,
Orders.PickingCompletedWhen, Orders.LastEditedBy, Orders.LastEditedWhen
FROM Sales.Orders
LEFT JOIN Sales.Orders ORDERS2 ON ORDERS2.OrderID <= 200;
Эта таблица содержит 14.72 млн строк — достаточно для тестов производительности columnstore-индексов.
Пример аналитического запроса
Примером сценария использования некластерного columnstore-индекса для этих данных может быть оперативная аналитика в реальном времени, основанная на фильтрации по OrderDate. Будь то ежеквартальный отчет о продажах или панель мониторинга продаж в реальном времени, эти данные можно анализировать множеством способов. Рассмотрим аналитический запрос, который используется для создания ежедневной панели мониторинга продаж, включающей текущие и исторические данные:
SELECT
OrderDate,
COUNT(*) AS OrderCount,
AVG(DATEDIFF(HOUR, OrderDate, ExpectedDeliveryDate)) AS AvgOrderDeliveryTimeHours,
SUM(CASE WHEN BackorderOrderID IS NOT NULL THEN 1 ELSE 0 END) AS BackorderCount
FROM dbo.SalesOrdersBIG
WHERE OrderDate >= '1/1/2016'
AND OrderDate < '2/1/2016'
GROUP BY OrderDate
ORDER BY OrderDate;
Результаты представляют собой ежедневный анализ нескольких показателей, связанных с продажами:
Быстрая проверка STATISTICS IO и выполнения запроса показывает, что этот запрос считывает большой объём данных:
Запрос выбирает очень много данных, что создаёт нагрузку на транзакционную таблицу. Частое обслуживание больших транзакционных таблиц в конечном итоге приводит к конфликтам с пользователями, которые пытаются размещать заказы, обновлять их или проверять их статус. Кроме того, если на этом сервере не установлена версия Enterprise, то при обработке этих данных не будет использоваться пакетный режим. Чтобы снизить нагрузку, создадим для таблицы некластерный columnstore-индекс.
Создание Columnstore-индекса
CREATE NONCLUSTERED COLUMNSTORE INDEX NCCI_SalesOrdersBIG
ON dbo.SalesOrdersBIG (OrderDate, ExpectedDeliveryDate, BackorderOrderID);
Производительность значительно улучшается, но все 16 rowgroup были прочитаны, то есть исключение сегментов не сработало.
Это отображается в STATISTICS IO через количество прочитанных сегментов, которое равно нулю. Это означает, что при выполнении запроса не произошло исключения сегментов.
Идеальный способ удалить лишние сегменты и сократить количество групп строк, которые необходимо прочитать — это контролировать порядок данных. В обычных условиях, решением было бы перестроить таблицу с индексом rowstore и заменить его кластерным columnstore-индексом. В качестве альтернативы в SQL Server 2022 и более поздних версиях можно использовать упорядоченный кластерный columnstore-индекс. Поскольку это кластеризованная rowstore таблица, порядок данных напрямую наследуется от этого индекса в некластерном columnstore-индексе.
Чтобы решить эту проблему, будет создан упорядоченный некластерный columnstore-индекс.
Упорядоченный некластеризованный Columnstore-индекс
CREATE NONCLUSTERED COLUMNSTORE INDEX NCCI_SalesOrdersBIG
ON dbo.SalesOrdersBIG (OrderDate, ExpectedDeliveryDate, BackorderOrderID)
ORDER (OrderDate);
Этот индекс упорядочит строки columnstore-индекса по OrderDate перед созданием rowgroups. После этого можно снова запустить аналитический запрос, который выполнялся ранее, и проверить его STATISTICS IO. После создания производительность значительно повышается, а план выполнения теперь включает сканирование columnstore-индекса и значительно меньшее количество операций чтения:
Теперь 5 из 11 rowgroup были пропущены, что снизило число логических чтений примерно на треть. План исполнения при этом не изменился. Изменение заключалось в том, что был использован предикат фильтра по OrderDate для пропуска 5 групп строк, что соответствует примерно 5 миллионам строк или около 1/3 таблицы.
Для оперативной аналитики в реальном времени, где производительность имеет решающее значение, использование упорядоченного некластерного columnstore-индекса поможет исключить сегменты, что обычно невозможно. Это обеспечивает дополнительный прирост производительности по сравнению с неупорядоченным некластерным columnstore-индексом и прирост по сравнению с прямым запросом к таблице rowstore.
Онлайн-перестроение упорядоченных Columnstore-индексов
Ранее перестроение упорядоченных columnstore-индексов выполнялось офлайн. В SQL Server 2025 добавлена поддержка ONLINE REBUILD для кластеризованных и некластерных упорядоченных columnstore-индексов (только в Enterprise и Developer).
Одним из существенных недостатков упорядоченных columnstore-индексов на момент их выпуска было то, что перестроение индекса выполнялось в автономном режиме. Хотя перестроение выполнялось для каждого раздела отдельно, это означало, что во время обслуживания индекса данные будут недоступны.
Для кластерных columnstore-индексов это, как правило, было более приемлемо, поскольку для аналитических данных проще обеспечить безопасность в рамках окна обслуживания, чем для транзакционных данных. Однако для недавно представленных упорядоченных некластерных columnstore-индексов недоступность данных во время перестроения может оказаться неприемлемой.
SQL Server 2025 поддерживает онлайн-перестроение кластерных и некластерных упорядоченных индексов. Это меняет правила игры для данной функции, поскольку обеспечивает непрерывность бизнес-процессов при их создании или перестроении.
Рассмотрим упорядоченный некластерный columnstore-индекс, созданный в ходе предыдущего демонстрации:
CREATE NONCLUSTERED COLUMNSTORE INDEX NCCI_SalesOrdersBIG
ON dbo.SalesOrdersBIG (OrderDate, ExpectedDeliveryDate, BackorderOrderID)
ORDER (OrderDate);
Перестроение этого индекса приведёт к его блокировке на всё время выполнения. Пользователи, пытающиеся прочитать или записать данные, будут вынуждены ждать, что нежелательно для типичного транзакционного приложения. Вот команда T-SQL для перестроения этого индекса:
ALTER INDEX NCCI_SalesOrdersBIG ON dbo.SalesOrdersBIG REBUILD;В отдельном запросе мы проверим dm_tran_locks на наличие блокировок таблицы во время выполнения оператора создания индекса. При создании индекса будут получены следующие результаты:
SELECT
dm_tran_locks.resource_type,
dm_tran_locks.resource_subtype,
dm_tran_locks.request_mode,
dm_tran_locks.request_type,
dm_tran_locks.request_owner_type
FROM sys.dm_tran_locks
WHERE dm_tran_locks.resource_database_id = DB_ID()
AND dm_tran_locks.resource_associated_entity_id = OBJECT_ID(N'dbo.SalesOrder');
Columnstore-индекс заблокирован, поэтому системное представление dm_tran_locks не может вернуть никаких данных. Давайте ещё для проверки используем аналитический запрос, который был представлен ранее:
SELECT
OrderDate,
COUNT(*) AS OrderCount,
AVG(DATEDIFF(HOUR, OrderDate, ExpectedDeliveryDate)) AS AvgOrderDeliveryTimeHours,
SUM(CASE WHEN BackorderOrderID IS NOT NULL THEN 1 ELSE 0 END) AS BackorderCount
FROM dbo.SalesOrdersBIG
WHERE OrderDate >= '1/1/2016'
AND OrderDate < '2/1/2016'
GROUP BY OrderDate
ORDER BY OrderDate;
Как и ожидалось, результатов запроса не было, так как он ждал завершения перестроения:
Как только перестроение индекса завершается, результаты возвращаются в ожидаемом виде:
Небольшое изменение в инструкции по перестройке индекса позволяет выполнять её в режиме реального времени без необходимости блокировать пользователей, пытающихся получить доступ к таблице:
ALTER INDEX NCCI_SalesOrdersBIG ON dbo.SalesOrdersBIG
REBUILD WITH (ONLINE = ON);
Теперь перестроение не блокирует пользователей, аналитические запросы продолжают выполняться. При выполнении аналитического запроса, о котором говорилось ранее, результаты возвращаются немедленно. Аналогичным образом запрос к dm_tran_locks не только выполняется немедленно, но и возвращает результаты:
Это говорит о том что аналитический запрос использовал индекс, который перестраивался в режиме реального времени, а не индекс rowstore.
Для SQL Server с корпоративной версией возможность перестраивать упорядоченные columnstore-индексы в режиме онлайн значительно повышает удобство обслуживания, позволяет не опасаясь, что оно заблокирует пользователей, пытающихся получить доступ к оперативной аналитике в реальном времени.
Последнее (и самое важное) замечание об columnstore индексах это то, операции перестроения не требуют частого выполнения, для columnstore-индексов это позволяет:
- Удалить строки, удаленные программно (фантомов)
- Объединить небольших групп строк
- Преобразовать содержимое дельта-хранилища в сжатые группы строк
Всё это требуется редко и, как правило, приносит наибольшую пользу, когда данные становятся неупорядоченными, а удаление сегментов приводит к снижению производительности для конечных пользователей. Поэтому при обслуживании columnstore-индексов начинайте с регулярных операций реорганизации и планируйте перестроение только в том случае, если необходимо восстановить порядок данных.
Сжатие файлов базы данных
В SQL Server 2025 команда shrink теперь может освобождать место в columnstore-индексах и для LOB-столбцов. Такие индексы оптимизированы для размещения в стиле хранилища данных, поэтому экономия от сжатия строк в Columnstore не так заметна. Одним из самых больших недостатков хранения данных в строковых полях больших объектов было то, что данные, хранящиеся на этих страницах, нельзя было переместить во время операций по уменьшению размера базы данных. Здесь следует обратить внимание на следующие типы LOB-данных:
- VARCHAR(MAX)
- NVARCHAR(MAX)
- VARBINARY(MAX)
Заключение
Columnstore-индексы продолжают развиваться. В SQL Server 2025 улучшения направлены на реальную оперативную аналитику. За прошедшие годы этот функционал значительно расширился и стал более зрелым, представляя собой идеальный способ хранения аналитических данных в SQL Server. Использование упорядоченных columnstore-индексов и онлайн-перестроения значительно повышает их ценность, напрямую влияет на повышение производительности и доступности баз данных и будет полезно для нагрузок, которые в значительной степени зависят от некластерных или кластерных columnstore-индексов.
Упорядоченные columnstore-индексы помогают автоматизировать процесс удаления сегментов. Вместо того чтобы поддерживать порядок данных с помощью специальных процессов или скриптов, можно позволить SQL Server предварительно сортировать данные, чтобы при обслуживании индекса можно было изменить порядок данных, тем самым автоматизировав задачу, которая раньше была более сложной.
Если вы храните данные в SQL Server и ещё не используете columnstore-индексы, сейчас самое время начать! Попробуйте использовать их для аналитических данных, данных журналов/архивов или для быстрой генерации отчётов из транзакционных баз данных.
Комментариев нет:
Отправить комментарий