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

Статья
06/01/2023

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

Что такое секции таблицы

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

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

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

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

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

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

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

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

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

Изменение размера секции

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

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

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

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

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

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

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

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

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

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.

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

Для разделения секций требуется, чтобы соответствующая секция (необязательно вся таблица) была пустой, если у таблицы есть кластеризованный индекс 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, то разделять в такой таблице можно только пустые секции.

В приведенном ниже примере создается секционированная таблица 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.

Дальнейшие действия

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

Поделиться через