Оптимизация транзакций в выделенном пуле SQL в Azure Synapse Analytics

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

Транзакции и ведение журнала

Транзакции являются важным компонентом подсистемы реляционного пула SQL. Транзакции используются во время изменения данных. Эти транзакции могут быть явными или неявными. Операторы INSERT, UPDATE и DELETE являются примерами неявных транзакций. Явные транзакции используют BEGIN TRAN, COMMIT TRAN или ROLLBACK TRAN. Явные транзакции обычно используются, когда необходимо связать несколько инструкций изменения в одной атомарной единице.

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

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

Минимальное ведение журнала против полного

В отличие от полностью регистрируемых операций, которые используют журнал транзакций для отслеживания каждого изменения строки, минимально регистрируемые операции фиксируют только выделение экстентов и изменения в метаданных. Поэтому минимальное ведение журнала включает запись только тех сведений, которые необходимы для отката транзакции в случае сбоя или по явному запросу (ROLLBACK TRAN). Так как в журнале транзакций отслеживается гораздо меньше сведений, минимально зарегистрированная операция выполняется лучше, чем операция полного ведения журнала. Кроме того, поскольку меньше операций записи отправляются в журнал транзакций, создается гораздо меньше данных журнала, что делает его более эффективным с точки зрения ввода-вывода.

Ограничения безопасности транзакций применяются только к полностью зарегистрированным операциям.

Минимально зарегистрированные операции

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

• СОЗДАТЬ ТАБЛИЦУ КАК SELECT (CTAS)
• INSERT..SELECT
• СОЗДАТЬ ИНДЕКС
• ALTER INDEX REBUILD (изменить индекс и перестроить его)
• DROP INDEX
• TRUNCATE TABLE
• УДАЛИТЬ ТАБЛИЦУ
• ALTER TABLE SWITCH PARTITION (переключение раздела)

Минимальное ведение журнала с массовой загрузкой

CTAS и INSERT... SELECT — это обе операции массовой загрузки. Однако оба влияют на определение целевой таблицы и зависят от сценария загрузки. В следующей таблице объясняется, когда массовые операции полностью или минимально регистрируются:

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

Загрузка данных в непустую таблицу с кластеризованным индексом часто может содержать смесь полностью зарегистрированных и минимально записанных строк. Кластеризованный индекс — это сбалансированное дерево (b-дерево) страниц. Если страница, в которую вносятся изменения, уже содержит строки из другой транзакции, то эти записи будут полностью зафиксированы в журнале. Однако если страница пуста, запись на нее будет минимально зафиксирована.

Оптимизация удаления

DELETE — это полностью зарегистрированная операция. Если требуется удалить большое количество данных в таблице или секции, часто имеет смысл SELECT оставить те данные, которые вы хотите сохранить, что можно выполнить как минимально зарегистрированную операцию. Чтобы выбрать данные, создайте новую таблицу с помощью CTAS. После создания используйте ПЕРЕИМЕНОВАТЬ, чтобы заменить старую таблицу на вновь созданную.

-- Delete all sales transactions for Promotions except PromotionKey 2.

--Step 01. Create a new table select only the records we want to kep (PromotionKey 2)
CREATE TABLE [dbo].[FactInternetSales_d]
WITH
(    CLUSTERED COLUMNSTORE INDEX
,    DISTRIBUTION = HASH([ProductKey])
,     PARTITION     (    [OrderDateKey] RANGE RIGHT
                                    FOR VALUES    (    20000101, 20010101, 20020101, 20030101, 20040101, 20050101
                                                ,    20060101, 20070101, 20080101, 20090101, 20100101, 20110101
                                                ,    20120101, 20130101, 20140101, 20150101, 20160101, 20170101
                                                ,    20180101, 20190101, 20200101, 20210101, 20220101, 20230101
                                                ,    20240101, 20250101, 20260101, 20270101, 20280101, 20290101
                                                )
)
AS
SELECT     *
FROM     [dbo].[FactInternetSales]
WHERE    [PromotionKey] = 2
OPTION (LABEL = 'CTAS : Delete')
;

--Step 02. Rename the Tables to replace the
RENAME OBJECT [dbo].[FactInternetSales]   TO [FactInternetSales_old];
RENAME OBJECT [dbo].[FactInternetSales_d] TO [FactInternetSales];

Оптимизация обновлений

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

В приведенном ниже примере полное обновление таблицы было преобразовано в CTAS, чтобы можно было выполнить минимальное ведение журнала.

В этом случае мы ретроспективно добавляем скидку к продажам в таблице:

--Step 01. Create a new table containing the "Update".
CREATE TABLE [dbo].[FactInternetSales_u]
WITH
(    CLUSTERED INDEX
,    DISTRIBUTION = HASH([ProductKey])
,     PARTITION     (    [OrderDateKey] RANGE RIGHT
                                    FOR VALUES    (    20000101, 20010101, 20020101, 20030101, 20040101, 20050101
                                                ,    20060101, 20070101, 20080101, 20090101, 20100101, 20110101
                                                ,    20120101, 20130101, 20140101, 20150101, 20160101, 20170101
                                                ,    20180101, 20190101, 20200101, 20210101, 20220101, 20230101
                                                ,    20240101, 20250101, 20260101, 20270101, 20280101, 20290101
                                                )
                )
)
AS
SELECT
    [ProductKey]  
,    [OrderDateKey]
,    [DueDateKey]  
,    [ShipDateKey]
,    [CustomerKey]
,    [PromotionKey]
,    [CurrencyKey]
,    [SalesTerritoryKey]
,    [SalesOrderNumber]
,    [SalesOrderLineNumber]
,    [RevisionNumber]
,    [OrderQuantity]
,    [UnitPrice]
,    [ExtendedAmount]
,    [UnitPriceDiscountPct]
,    ISNULL(CAST(5 as float),0) AS [DiscountAmount]
,    [ProductStandardCost]
,    [TotalProductCost]
,    ISNULL(CAST(CASE WHEN [SalesAmount] <=5 THEN 0
         ELSE [SalesAmount] - 5
         END AS MONEY),0) AS [SalesAmount]
,    [TaxAmt]
,    [Freight]
,    [CarrierTrackingNumber]
,    [CustomerPONumber]
FROM    [dbo].[FactInternetSales]
OPTION (LABEL = 'CTAS : Update')
;

--Step 02. Rename the tables
RENAME OBJECT [dbo].[FactInternetSales]   TO [FactInternetSales_old];
RENAME OBJECT [dbo].[FactInternetSales_u] TO [FactInternetSales];

--Step 03. Drop the old table
DROP TABLE [dbo].[FactInternetSales_old]

Оптимизация при переключении разделов

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

Действия, необходимые для выполнения переключения разделов, следующие:

1. Создайте пустой раздел
2. Выполните "обновление" как CTAS
3. Замените существующие данные на таблицу вывода
4. Введите новые данные
5. Очистка данных

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

CREATE PROCEDURE dbo.partition_data_get
    @schema_name           NVARCHAR(128)
,    @table_name               NVARCHAR(128)
,    @boundary_value           INT
AS
IF OBJECT_ID('tempdb..#ptn_data') IS NOT NULL
BEGIN
    DROP TABLE #ptn_data
END
CREATE TABLE #ptn_data
WITH    (    DISTRIBUTION = ROUND_ROBIN
        ,    HEAP
        )
AS
WITH CTE
AS
(
SELECT     s.name                            AS [schema_name]
,        t.name                            AS [table_name]
,         p.partition_number                AS [ptn_nmbr]
,        p.[rows]                        AS [ptn_rows]
,        CAST(r.[value] AS INT)            AS [boundary_value]
FROM        sys.schemas                    AS s
JOIN        sys.tables                    AS t    ON  s.[schema_id]        = t.[schema_id]
JOIN        sys.indexes                    AS i    ON     t.[object_id]        = i.[object_id]
JOIN        sys.partitions                AS p    ON     i.[object_id]        = p.[object_id]
                                                AND i.[index_id]        = p.[index_id]
JOIN        sys.partition_schemes        AS h    ON     i.[data_space_id]    = h.[data_space_id]
JOIN        sys.partition_functions        AS f    ON     h.[function_id]        = f.[function_id]
LEFT JOIN    sys.partition_range_values    AS r     ON     f.[function_id]        = r.[function_id]
                                                AND r.[boundary_id]        = p.[partition_number]
WHERE i.[index_id] <= 1
)
SELECT    *
FROM    CTE
WHERE    [schema_name]        = @schema_name
AND        [table_name]        = @table_name
AND        [boundary_value]    = @boundary_value
OPTION (LABEL = 'dbo.partition_data_get : CTAS : #ptn_data')
;
GO

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

В следующем коде показаны шаги, упомянутые ранее для достижения полной процедуры переключения секций.

--Create a partitioned aligned empty table to switch out the data
IF OBJECT_ID('[dbo].[FactInternetSales_out]') IS NOT NULL
BEGIN
    DROP TABLE [dbo].[FactInternetSales_out]
END

CREATE TABLE [dbo].[FactInternetSales_out]
WITH
(    DISTRIBUTION = HASH([ProductKey])
,    CLUSTERED COLUMNSTORE INDEX
,     PARTITION     (    [OrderDateKey] RANGE RIGHT
                                    FOR VALUES    (    20020101, 20030101
                                                )
                )
)
AS
SELECT *
FROM    [dbo].[FactInternetSales]
WHERE 1=2
OPTION (LABEL = 'CTAS : Partition Switch IN : UPDATE')
;

--Create a partitioned aligned table and update the data in the select portion of the CTAS
IF OBJECT_ID('[dbo].[FactInternetSales_in]') IS NOT NULL
BEGIN
    DROP TABLE [dbo].[FactInternetSales_in]
END

CREATE TABLE [dbo].[FactInternetSales_in]
WITH
(    DISTRIBUTION = HASH([ProductKey])
,    CLUSTERED COLUMNSTORE INDEX
,     PARTITION     (    [OrderDateKey] RANGE RIGHT
                                    FOR VALUES    (    20020101, 20030101
                                                )
                )
)
AS
SELECT
    [ProductKey]  
,    [OrderDateKey]
,    [DueDateKey]  
,    [ShipDateKey]
,    [CustomerKey]
,    [PromotionKey]
,    [CurrencyKey]
,    [SalesTerritoryKey]
,    [SalesOrderNumber]
,    [SalesOrderLineNumber]
,    [RevisionNumber]
,    [OrderQuantity]
,    [UnitPrice]
,    [ExtendedAmount]
,    [UnitPriceDiscountPct]
,    ISNULL(CAST(5 as float),0) AS [DiscountAmount]
,    [ProductStandardCost]
,    [TotalProductCost]
,    ISNULL(CAST(CASE WHEN [SalesAmount] <=5 THEN 0
         ELSE [SalesAmount] - 5
         END AS MONEY),0) AS [SalesAmount]
,    [TaxAmt]
,    [Freight]
,    [CarrierTrackingNumber]
,    [CustomerPONumber]
FROM    [dbo].[FactInternetSales]
WHERE    OrderDateKey BETWEEN 20020101 AND 20021231
OPTION (LABEL = 'CTAS : Partition Switch IN : UPDATE')
;

--Use the helper procedure to identify the partitions
--The source table
EXEC dbo.partition_data_get 'dbo','FactInternetSales',20030101
DECLARE @ptn_nmbr_src INT = (SELECT ptn_nmbr FROM #ptn_data)
SELECT @ptn_nmbr_src

--The "in" table
EXEC dbo.partition_data_get 'dbo','FactInternetSales_in',20030101
DECLARE @ptn_nmbr_in INT = (SELECT ptn_nmbr FROM #ptn_data)
SELECT @ptn_nmbr_in

--The "out" table
EXEC dbo.partition_data_get 'dbo','FactInternetSales_out',20030101
DECLARE @ptn_nmbr_out INT = (SELECT ptn_nmbr FROM #ptn_data)
SELECT @ptn_nmbr_out

--Switch the partitions over
DECLARE @SQL NVARCHAR(4000) = '
ALTER TABLE [dbo].[FactInternetSales]    SWITCH PARTITION '+CAST(@ptn_nmbr_src AS VARCHAR(20))    +' TO [dbo].[FactInternetSales_out] PARTITION '    +CAST(@ptn_nmbr_out AS VARCHAR(20))+';
ALTER TABLE [dbo].[FactInternetSales_in] SWITCH PARTITION '+CAST(@ptn_nmbr_in AS VARCHAR(20))    +' TO [dbo].[FactInternetSales] PARTITION '        +CAST(@ptn_nmbr_src AS VARCHAR(20))+';'
EXEC sp_executesql @SQL

--Perform the clean-up
TRUNCATE TABLE dbo.FactInternetSales_out;
TRUNCATE TABLE dbo.FactInternetSales_in;

DROP TABLE dbo.FactInternetSales_out
DROP TABLE dbo.FactInternetSales_in
DROP TABLE #ptn_data

Сократите объем ведения журнала при работе с небольшими пакетами

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

Следующий код — это рабочий пример. Размер пакета установлен на тривиальное число, чтобы выделить метод. В действительности размер пакета будет значительно больше.

SET NO_COUNT ON;
IF OBJECT_ID('tempdb..#t') IS NOT NULL
BEGIN
    DROP TABLE #t;
    PRINT '#t dropped';
END

CREATE TABLE #t
WITH    (    DISTRIBUTION = ROUND_ROBIN
        ,    HEAP
        )
AS
SELECT    ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS seq_nmbr
,        SalesOrderNumber
,        SalesOrderLineNumber
FROM    dbo.FactInternetSales
WHERE    [OrderDateKey] BETWEEN 20010101 and 20011231
;

DECLARE    @seq_start        INT = 1
,        @batch_iterator    INT = 1
,        @batch_size        INT = 50
,        @max_seq_nmbr    INT = (SELECT MAX(seq_nmbr) FROM dbo.#t)
;

DECLARE    @batch_count    INT = (SELECT CEILING((@max_seq_nmbr*1.0)/@batch_size))
,        @seq_end        INT = @batch_size
;

SELECT COUNT(*)
FROM    dbo.FactInternetSales f

PRINT 'MAX_seq_nmbr '+CAST(@max_seq_nmbr AS VARCHAR(20))
PRINT 'MAX_Batch_count '+CAST(@batch_count AS VARCHAR(20))

WHILE    @batch_iterator <= @batch_count
BEGIN
    DELETE
    FROM    dbo.FactInternetSales
    WHERE EXISTS
    (
            SELECT    1
            FROM    #t t
            WHERE    seq_nmbr BETWEEN  @seq_start AND @seq_end
            AND        FactInternetSales.SalesOrderNumber        = t.SalesOrderNumber
            AND        FactInternetSales.SalesOrderLineNumber    = t.SalesOrderLineNumber
    )
    ;

    SET @seq_start = @seq_end
    SET @seq_end = (@seq_start+@batch_size);
    SET @batch_iterator +=1;
END

Руководство по приостановке и масштабированию

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

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

• Перезапись длительных операций с помощью CTAS
• Разорвать операцию на блоки; работа с подмножеством строк

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

См. Транзакции в выделенном пуле SQL, чтобы узнать больше об уровнях изоляции и ограничениях транзакций. Чтобы ознакомиться с другими лучшими практиками, см. раздел Лучшие практики выделенного пула SQL.