В этой статье мы погрузимся в концепцию кластеризованного индекса столбцов в SQL Server и исследуем разницу в производительности, которую он может обеспечить. Мы будем использовать базу данных AdventureworksDW2016CTP3 и таблицу FactResellerSalesXL, которая содержит 11,6 миллиона строк.

Давайте начнем с выполнения простого запроса к таблице без какого-либо существующего индекса столбцов. Этот запрос выбирает ProductKey и возвращает некоторые агрегации, сгруппированные по разным ключам продуктов. Анализируя статистику с помощью SET STATISTICS IO и TIME, мы можем лучше понять улучшения, предоставляемые индексом столбцов.

USE [AdventureworksDW2016CTP3]
GO
SET STATISTICS IO ON
GO
SET STATISTICS TIME ON
GO

SELECT ProductKey, sum(SalesAmount) SalesAmount, sum(OrderQuantity) ct
FROM dbo.FactResellerSalesXL
GROUP BY ProductKey

Просмотрев результаты, мы видим, что запрос завершил пять сканирований, 318 076 логических чтений, два физических чтения и 291 341 предварительное чтение. Он занял время CPU 8233 миллисекунды (мс) и время ожидания 5008 мс. Оптимизатор выбрал сканирование существующего кластеризованного индекса строкового хранилища с затратами 91% и просканировал все 11,6 миллиона записей, чтобы вернуть набор результатов из 395 записей.

Теперь давайте создадим кластеризованный индекс столбцов для этой таблицы. Мы можем сделать это, используя графический интерфейс и выбрав “Новый индекс”, а затем “Кластеризованный индекс столбцов”. Мы также можем создать скрипт с использованием следующего оператора T-SQL:

USE [AdventureworksDW2016CTP3]
GO
CREATE CLUSTERED COLUMNSTORE INDEX [CS_IDX_FactResellerSalesXL] 
ON [dbo].[FactResellerSalesXL] WITH (DROP_EXISTING = OFF, COMPRESSION_DELAY = 0)
GO

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

В качестве альтернативы мы можем использовать существующую таблицу “dbo.FactResellerSalesXL_CCI”, в которой уже создан кластеризованный индекс столбцов. Создав скрипт, мы можем увидеть, что он выглядит точно так же, как тот, который мы пытались создать. Давайте используем эту таблицу для нашего примера:

USE [AdventureworksDW2016CTP3]
GO
CREATE CLUSTERED COLUMNSTORE INDEX [IndFactResellerSalesXL_CCI] 
ON [dbo].[FactResellerSalesXL_CCI] WITH (DROP_EXISTING = OFF, COMPRESSION_DELAY = 0) ON [PRIMARY]
GO

Теперь давайте повторно выполним тот же запрос на таблице с индексом столбцов:

USE AdventureWorksDW2016CTP3;
GO
SET STATISTICS IO ON;
GO 
SET STATISTICS TIME ON;
GO

SELECT ProductKey, sum(SalesAmount) SalesAmount, sum(OrderQuantity) ct
FROM dbo.FactResellerSalesXL_CCI
GROUP BY ProductKey

Просмотрев план выполнения, мы видим, что оптимизатор перешел от восьми операторов к всего пяти операторам для завершения транзакции. Он выполнил сканирование индекса кластеризованного столбцового хранилища с затратами всего 36% и прочитал ноль из 11,6 миллиона записей.

Сравнив результаты, мы видим, что индекс столбцов значительно улучшил производительность. Результаты столбцового хранилища имели ноль логических чтений, ноль физических чтений, ноль предварительных чтений и выполнились менее чем за секунду. Время CPU сократилось с 8233 мс до 391 мс, а время ожидания сократилось с 5008 мс до 442 мс.

Важно отметить, что индекс столбцов использовал режим пакетной обработки для выполнения, что позволяет движку обрабатывать несколько строк одновременно. Это может привести к увеличению производительности выполнения запроса в два-четыре раза.

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

Это завершает наше исследование кластеризованного индекса столбцов в SQL Server. Следите за новыми статьями о концепциях и идеях SQL Server!