Разделение таблиц в выделенном пуле SQL

Статья
2025-01-21

Советы по использованию разделов таблиц в выделенном пуле SQL и соответствующие примеры.

Что такое разделы таблицы?

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

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

Преимущества нагрузок

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

С помощью переключения секций можно быстро удалить или заменить часть таблицы. Например, таблица фактов по продажам может содержать только данные за последние 36 месяцев. В конце каждого месяца данные о продажах за самый давний месяц удаляются из таблицы. Удалить эти данные можно с использованием инструкции DELETE для удаления данных за самый старый месяц.

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

Преимущества запросов

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

Например, если таблица фактов по продажам разделена на 36 месяцев с помощью поля дат продаж, запросы, выполняющие фильтрацию на основе даты продажи, могут пропускать секции, которые не соответствуют условиям фильтра.

Размер раздела

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

Чтобы секционирование было полезным, важно понимать, когда следует использовать секционирование и количество создаваемых секций. Нет жесткого правила, какое количество разделов является чрезмерным, это зависит от ваших данных и от того, сколько разделов вы загружаете одновременно. Правильно составленная схема секционирования обычно содержит от нескольких десятков до нескольких сотен секций (но не тысяч).

При создании секций в кластеризованных таблицах columnstore важно учитывать количество строк, принадлежащих каждой секции. Для оптимального сжатия и производительности кластеризованных таблиц сжатого столбчатого хранилища требуется как минимум 1 миллион строк на одно распределение и один раздел. Еще до создания секций выделенный пул SQL делит каждую таблицу на 60 распределенных частей.

Таким образом, секционирование таблицы выступает дополнением к распределениям, созданным в фоновом режиме. Вернемся к нашему примеру. Если таблица фактов по продажам содержит 36 секций по месяцам, а выделенный пул SQL содержит 60 распределений, то за каждый месяц в таблице должно содержаться 60 миллионов строк, то есть 2,1 миллиарда строк при заполнении данных за все месяцы. Если в таблице намного меньше строк, чем рекомендуемый минимум на одну секцию, уменьшите количество секций, чтобы на каждую из них приходилось больше строк.

Дополнительные сведения см. в статье Индексирование, который включает запросы, позволяющие оценить качество кластеризованных колоннарных индексов.

Отличия синтаксиса от SQL Server

При помощи выделенного пула данных SQL определять секции проще, чем с использованием SQL Server. Функции и схемы секционирования не используются в выделенном пуле SQL, так как они находятся в SQL Server. Вам нужно только определить секционированный столбец и граничные точки.

Хотя синтаксис секционирования может немного отличаться от синтаксиса SQL Server, основные понятия одинаковые. SQL Server и выделенный пул запросов SQL поддерживают наличие одного столбца партиционирования на таблицу, который может быть секционирован по диапазонам. Дополнительные сведения о секционировании см. в статье Секционированные таблицы и индексы.

В приведенном ниже примере таблица секционируется по столбцу FactInternetSales с помощью инструкции OrderDateKey.

CREATE TABLE [dbo].[FactInternetSales]
(
    [ProductKey]            int          NOT NULL
,   [OrderDateKey]          int          NOT NULL
,   [CustomerKey]           int          NOT NULL
,   [PromotionKey]          int          NOT NULL
,   [SalesOrderNumber]      nvarchar(20) NOT NULL
,   [OrderQuantity]         smallint     NOT NULL
,   [UnitPrice]             money        NOT NULL
,   [SalesAmount]           money        NOT NULL
)
WITH
(   CLUSTERED COLUMNSTORE INDEX
,   DISTRIBUTION = HASH([ProductKey])
,   PARTITION   (   [OrderDateKey] RANGE RIGHT FOR VALUES
                    (20000101,20010101,20020101
                    ,20030101,20040101,20050101
                    )
                )
);

Перенос секций из SQL Server

Чтобы перенести определения секций SQL Server в выделенный пул SQL, нужно сделать следующее:

удалить схему секционирования SQL Server;
добавить определение функции секционирования в инструкцию CREATE TABLE.

Если вы переносите секционированную таблицу из экземпляра SQL Server, следующий SQL-запрос поможет вам определить количество строк в каждой партиции. Обратите внимание: если в выделенном пуле SQL используется такая же степень детализации секционирования, то количество строк в секции уменьшится в 60 раз.

-- Partition information for a SQL Server Database
SELECT      s.[name]                        AS      [schema_name]
,           t.[name]                        AS      [table_name]
,           i.[name]                        AS      [index_name]
,           p.[partition_number]            AS      [partition_number]
,           SUM(a.[used_pages]*8.0)         AS      [partition_size_kb]
,           SUM(a.[used_pages]*8.0)/1024    AS      [partition_size_mb]
,           SUM(a.[used_pages]*8.0)/1048576 AS      [partition_size_gb]
,           p.[rows]                        AS      [partition_row_count]
,           rv.[value]                      AS      [partition_boundary_value]
,           p.[data_compression_desc]       AS      [partition_compression_desc]
FROM        sys.schemas s
JOIN        sys.tables t                    ON      t.[schema_id]         = s.[schema_id]
JOIN        sys.partitions p                ON      p.[object_id]         = t.[object_id]
JOIN        sys.allocation_units a          ON      a.[container_id]      = p.[partition_id]
JOIN        sys.indexes i                   ON      i.[object_id]         = p.[object_id]
                                            AND     i.[index_id]          = p.[index_id]
JOIN        sys.data_spaces ds              ON      ds.[data_space_id]    = i.[data_space_id]
LEFT JOIN   sys.partition_schemes ps        ON      ps.[data_space_id]    = ds.[data_space_id]
LEFT JOIN   sys.partition_functions pf      ON      pf.[function_id]      = ps.[function_id]
LEFT JOIN   sys.partition_range_values rv   ON      rv.[function_id]      = pf.[function_id]
                                            AND     rv.[boundary_id]      = p.[partition_number]
WHERE       p.[index_id] <=1
GROUP BY    s.[name]
,           t.[name]
,           i.[name]
,           p.[partition_number]
,           p.[rows]
,           rv.[value]
,           p.[data_compression_desc];

Переключение секций

Выделенный пул SQL поддерживает разбиение, слияние и переключение секций. Для каждой из этих функций используется инструкция ALTER TABLE.

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

Для разделения секций требуется, чтобы соответствующая секция (необязательно вся таблица) была пустой, если у таблицы есть кластеризованный индекс columnstore (CCI). Другие секции в той же таблице могут содержать данные. Разделы, содержащие данные, не могут быть разделены, это приведёт к ошибке: ALTER PARTITION statement failed because the partition is not empty. Only empty partitions can be split in when a columnstore index exists on the table. Consider disabling the columnstore index before issuing the ALTER PARTITION statement, then rebuilding the columnstore index after ALTER PARTITION is complete. В качестве обходного решения для разделения раздела, содержащего данные, см. Как разделить раздел, содержащий данные.

Как разделить секцию, которая содержит данные

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

В приведенном ниже примере создается секционированная таблица columnstore. Затем в каждый раздел вставляется одна строка.

CREATE TABLE [dbo].[FactInternetSales]
(
        [ProductKey]            int          NOT NULL
    ,   [OrderDateKey]          int          NOT NULL
    ,   [CustomerKey]           int          NOT NULL
    ,   [PromotionKey]          int          NOT NULL
    ,   [SalesOrderNumber]      nvarchar(20) NOT NULL
    ,   [OrderQuantity]         smallint     NOT NULL
    ,   [UnitPrice]             money        NOT NULL
    ,   [SalesAmount]           money        NOT NULL
)
WITH
(   CLUSTERED COLUMNSTORE INDEX
,   DISTRIBUTION = HASH([ProductKey])
,   PARTITION   (   [OrderDateKey] RANGE RIGHT FOR VALUES
                    (20000101
                    )
                )
);

INSERT INTO dbo.FactInternetSales
VALUES (1,19990101,1,1,1,1,1,1);

INSERT INTO dbo.FactInternetSales
VALUES (1,20000101,1,1,1,1,1,1);

Следующий запрос позволяет найти количество строк с помощью представления каталога sys.partitions.

SELECT  QUOTENAME(s.[name])+'.'+QUOTENAME(t.[name]) as Table_name
,       i.[name] as Index_name
,       p.partition_number as Partition_nmbr
,       p.[rows] as Row_count
,       p.[data_compression_desc] as Data_Compression_desc
FROM    sys.partitions p
JOIN    sys.tables     t    ON    p.[object_id]   = t.[object_id]
JOIN    sys.schemas    s    ON    t.[schema_id]   = s.[schema_id]
JOIN    sys.indexes    i    ON    p.[object_id]   = i.[object_Id]
                            AND   p.[index_Id]    = i.[index_Id]
WHERE t.[name] = 'FactInternetSales';

Следующая команда разделения вызывает сообщение об ошибке:

ALTER TABLE FactInternetSales SPLIT RANGE (20010101);

Msg 35346, Level 15, State 1, Line 44
SPLIT clause of ALTER PARTITION statement failed because the partition is not empty. Only empty partitions can be split in when a columnstore index exists on the table. Consider disabling the columnstore index before issuing the ALTER PARTITION statement, then rebuilding the columnstore index after ALTER PARTITION is complete.

Можно создать новую таблицу для хранения данных при помощи CTAS.

CREATE TABLE dbo.FactInternetSales_20000101
    WITH    (   DISTRIBUTION = HASH(ProductKey)
            ,   CLUSTERED COLUMNSTORE INDEX              
            ,   PARTITION   (   [OrderDateKey] RANGE RIGHT FOR VALUES
                                (20000101
                                )
                            )
)
AS
SELECT *
FROM    FactInternetSales
WHERE   1=2;

Так как границы этих секций выровнены, переключение допускается. Так вы получите пустую секцию в исходной таблице, чтобы впоследствии разделить ее.

ALTER TABLE FactInternetSales SWITCH PARTITION 2 TO FactInternetSales_20000101 PARTITION 2;

ALTER TABLE FactInternetSales SPLIT RANGE (20010101);

Теперь нам осталось только выровнять данные по новым границам разделов, используя CTAS, и затем возвратить данные в основную таблицу.

CREATE TABLE [dbo].[FactInternetSales_20000101_20010101]
    WITH    (   DISTRIBUTION = HASH([ProductKey])
            ,   CLUSTERED COLUMNSTORE INDEX
            ,   PARTITION   (   [OrderDateKey] RANGE RIGHT FOR VALUES
                                (20000101,20010101
                                )
                            )
            )
AS
SELECT  *
FROM    [dbo].[FactInternetSales_20000101]
WHERE   [OrderDateKey] >= 20000101
AND     [OrderDateKey] <  20010101;

ALTER TABLE dbo.FactInternetSales_20000101_20010101 SWITCH PARTITION 2 TO dbo.FactInternetSales PARTITION 2;

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

UPDATE STATISTICS [dbo].[FactInternetSales];

Наконец, в случае одноразового переключения секции для перемещения данных можно удалить таблицы, созданные для переключения секций — FactInternetSales_20000101_20010101 и FactInternetSales_20000101. Кроме того, может потребоваться сохранить пустые таблицы для обычных автоматических переключений секций.

Загрузите новые данные в секции, содержащие данные, за один шаг

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

Ранее, чтобы очистить существующие данные в разделе, требовалось использовать ALTER TABLE для замены данных. Затем для переключения новых данных требовался ещё один ALTER TABLE.

В выделенном пуле SQL параметр TRUNCATE_TARGET поддерживается в команде ALTER TABLE. При помощи TRUNCATE_TARGET команда ALTER TABLE перезаписывает существующие данные в секции, изменяя их на новые. Ниже приведен пример использования CTAS для создания новой таблицы с существующими данными, вставки новых данных и переноса всех данных обратно в целевую таблицу с перезаписью существующих данных.

CREATE TABLE [dbo].[FactInternetSales_NewSales]
    WITH    (   DISTRIBUTION = HASH([ProductKey])
            ,   CLUSTERED COLUMNSTORE INDEX
            ,   PARTITION   (   [OrderDateKey] RANGE RIGHT FOR VALUES
                                (20000101,20010101
                                )
                            )
            )
AS
SELECT  *
FROM    [dbo].[FactInternetSales]
WHERE   [OrderDateKey] >= 20000101
AND     [OrderDateKey] <  20010101
;

INSERT INTO dbo.FactInternetSales_NewSales
VALUES (1,20000101,2,2,2,2,2,2);

ALTER TABLE dbo.FactInternetSales_NewSales SWITCH PARTITION 2 TO dbo.FactInternetSales PARTITION 2 WITH (TRUNCATE_TARGET = ON);

Контроль исходного кода для секционирования таблиц

Примечание.

Если средство управления версиями не настроено на игнорирование схем разделов, изменение схемы таблицы для обновления разделов может привести к удалению и повторному воссозданию таблицы в процессе развертывания, что может быть неосуществимо. Для реализации такого изменения может потребоваться индивидуальное решение, как описано ниже. Убедитесь, что такое решение возможно для вашего средства непрерывной интеграции и непрерывного развертывания (CI/CD). В SQL Server Data Tools (SSDT) найдите параметры расширенной публикации "Игнорировать схемы секционирования", чтобы избежать созданного скрипта, который приводит к тому, что таблица будет удалена и воссоздана.

Этот пример полезен при обновлении схем секционирования пустой таблицы. Для непрерывного внесения изменений в секционирование таблицы с данными следуйте шагам в Как разделить раздел, содержащий данные, чтобы временно переместить данные из каждого раздела перед применением команды разделения SPLIT RANGE. Это необходимо, так как средству CI/CD неизвестно, какие именно секции содержат данные.

Чтобы избежать застаревания определения таблицы в системе управления версиями, попробуйте применить следующий подход:

Создайте таблицу как секционированную, но без значений секций.

CREATE TABLE [dbo].[FactInternetSales]
(
    [ProductKey]            int          NOT NULL
,   [OrderDateKey]          int          NOT NULL
,   [CustomerKey]           int          NOT NULL
,   [PromotionKey]          int          NOT NULL
,   [SalesOrderNumber]      nvarchar(20) NOT NULL
,   [OrderQuantity]         smallint     NOT NULL
,   [UnitPrice]             money        NOT NULL
,   [SalesAmount]           money        NOT NULL
)
WITH
(   CLUSTERED COLUMNSTORE INDEX
,   DISTRIBUTION = HASH([ProductKey])
,   PARTITION   (   [OrderDateKey] RANGE RIGHT FOR VALUES () )
);

SPLIT таблица в составе процесса развертывания:

 -- Create a table containing the partition boundaries

CREATE TABLE #partitions
WITH
(
    LOCATION = USER_DB
,   DISTRIBUTION = HASH(ptn_no)
)
AS
SELECT  ptn_no
,       ROW_NUMBER() OVER (ORDER BY (ptn_no)) as seq_no
FROM    (
    SELECT CAST(20000101 AS INT) ptn_no
    UNION ALL
    SELECT CAST(20010101 AS INT)
    UNION ALL
    SELECT CAST(20020101 AS INT)
    UNION ALL
    SELECT CAST(20030101 AS INT)
    UNION ALL
    SELECT CAST(20040101 AS INT)
) a;

 -- Iterate over the partition boundaries and split the table

DECLARE @c INT = (SELECT COUNT(*) FROM #partitions)
,       @i INT = 1                                 --iterator for while loop
,       @q NVARCHAR(4000)                          --query
,       @p NVARCHAR(20)     = N''                  --partition_number
,       @s NVARCHAR(128)    = N'dbo'               --schema
,       @t NVARCHAR(128)    = N'FactInternetSales' --table;

WHILE @i <= @c
BEGIN
    SET @p = (SELECT ptn_no FROM #partitions WHERE seq_no = @i);
    SET @q = (SELECT N'ALTER TABLE '+@s+N'.'+@t+N' SPLIT RANGE ('+@p+N');');

    -- PRINT @q;
    EXECUTE sp_executesql @q;
    SET @i+=1;
END

 -- Code clean-up

DROP TABLE #partitions;

При таком подходе код в системе управления версиями остается статичным, а значения границ секционирования могут быть динамическими и со временем развиваться вместе с пулом SQL.

Дополнительные сведения о разработке таблиц см. в разделе " Общие сведения о таблице".