Partilhar via

Optimizing transactions in dedicated SQL pool in Azure Synapse Analytics

  • Artigo

Saiba como otimizar o desempenho do seu código de transações num pool SQL dedicado, minimizando o risco de longos processos de reversão.

Transações e registo de eventos

Transactions are an important component of a relational SQL pool engine. Transactions are used during data modification. Estas transações podem ser explícitas ou implícitas. Instruções únicas INSERT, UPDATE e DELETE são exemplos de transações implícitas. As transações explícitas usam BEGIN TRAN, COMMIT TRAN ou ROLLBACK TRAN. Explicit transactions are typically used when multiple modification statements need to be tied together in a single atomic unit.

Changes to the SQL pool are tracked using transaction logs. Cada distribuição tem seu próprio log de transações. As gravações do log de transações são automáticas. Não é necessária nenhuma configuração. However, whilst this process guarantees the write it does introduce an overhead in the system. Você pode minimizar esse impacto escrevendo código transacionalmente eficiente. O código transacionalmente eficiente se enquadra amplamente em duas categorias.

  • Utilize construções mínimas de registo sempre que possível
  • Process data using scoped batches to avoid singular long running transactions
  • Adotar um padrão de comutação de partição para grandes modificações em uma determinada partição

Minimal vs. full logging

Ao contrário das operações totalmente registradas, que usam o log de transações para acompanhar todas as alterações de linha, as operações minimamente registradas controlam apenas as alocações de extensão e as alterações de metadados. Portanto, o log mínimo envolve registrar apenas as informações necessárias para reverter a transação após uma falha ou para uma solicitação explícita (ROLLBACK TRAN). Como muito menos informações são rastreadas no log de transações, uma operação minimamente registrada tem um desempenho melhor do que uma operação totalmente registrada de tamanho semelhante. Furthermore, because fewer writes go the transaction log, a much smaller amount of log data is generated and so is more I/O efficient.

Os limites de segurança da transação só se aplicam a operações totalmente registradas.

Observação

Operações minimamente registradas podem participar de transações explícitas. Como todas as alterações nas estruturas de alocação são rastreadas, é possível reverter operações minimamente registradas.

Operações minimamente registadas

As seguintes operações podem ser minimamente registradas:

  • CREATE TABLE AS SELECT (CTAS)
  • INSERIR.. SELECIONAR
  • CRIAR ÍNDICE
  • ALTER INDEX REBUILD
  • DROP INDEX
  • TRUNCATE TABLE
  • DROP TABLE
  • ALTER TABLE SWITCH PARTITION

Observação

As operações internas de movimentação de dados (como BROADCAST e SHUFFLE) não são afetadas pelo limite de segurança da transação.

Minimal logging with bulk load

CTAS e INSERT... SELECT são ambas operações de carga em massa. No entanto, ambos são influenciados pela definição da tabela de destino e dependem do cenário de carga. A tabela a seguir explica quando as operações em massa são total ou minimamente registradas:

Índice primário Cenário de carga Modo de registo
Heap Any Mínimo
Índice agrupado Tabela de destino vazia Mínimo
Índice agrupado Loaded rows do not overlap with existing pages in target Mínimo
Índice agrupado Loaded rows overlap with existing pages in target Full
Clustered Columnstore Index Batch size >= 102,400 per partition aligned distribution Mínimo
Clustered Columnstore Index Batch size < 102,400 per partition aligned distribution Full

Vale a pena notar que todas as gravações para atualizar índices secundários ou não clusterizados sempre serão operações totalmente registradas.

Important

A dedicated SQL pool has 60 distributions. Therefore, assuming all rows are evenly distributed and landing in a single partition, your batch will need to contain 6,144,000 rows or larger to be minimally logged when writing to a Clustered Columnstore Index. Se a tabela for particionada e as linhas que estão sendo inseridas ampliarem os limites da partição, você precisará de 6.144.000 linhas por limite de partição, assumindo uma distribuição de dados uniforme. Each partition in each distribution must independently exceed the 102,400 row threshold for the insert to be minimally logged into the distribution.

O carregamento de dados em uma tabela não vazia com um índice clusterizado geralmente pode conter uma mistura de linhas totalmente registradas e minimamente registradas. A clustered index is a balanced tree (b-tree) of pages. If the page being written to already contains rows from another transaction, then these writes will be fully logged. However, if the page is empty then the write to that page will be minimally logged.

Optimizing deletes

DELETE é uma operação totalmente registrada. If you need to delete a large amount of data in a table or a partition, it often makes more sense to SELECT the data you wish to keep, which can be run as a minimally logged operation. Para selecionar os dados, crie uma nova tabela com CTAS. Uma vez criada, use RENAME para trocar sua tabela antiga pela tabela recém-criada.

-- 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];

Optimizing updates

UPDATE é uma operação totalmente registrada. Se você precisar atualizar um grande número de linhas em uma tabela ou partição, muitas vezes pode ser muito mais eficiente usar uma operação minimamente registrada, como CTAS , para fazer isso.

In the example below a full table update has been converted to a CTAS so that minimal logging is possible.

Neste caso, estamos adicionando retrospetivamente um valor de desconto às vendas na tabela:

--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]

Observação

A recriação de tabelas grandes pode se beneficiar do uso de recursos dedicados de gerenciamento de carga de trabalho do pool SQL. Para obter mais informações, consulte Classes de recursos para gerenciamento de carga de trabalho.

Optimizing with partition switching

If faced with large-scale modifications inside a table partition, then a partition switching pattern makes sense. Se a modificação de dados for significativa e abranger várias partições, a iteração sobre as partições alcançará o mesmo resultado.

As etapas para executar uma opção de partição são as seguintes:

  1. Criar uma partição vazia
  2. Perform the 'update' as a CTAS
  3. Switch out the existing data to the out table
  4. Switch in the new data
  5. Limpar os dados

No entanto, para ajudar a identificar as partições a serem alternadas, crie o seguinte procedimento auxiliar.

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

Este procedimento maximiza a reutilização de código e mantém o exemplo de comutação de partição mais compacto.

O código a seguir demonstra as etapas mencionadas anteriormente para alcançar uma rotina completa de comutação de partição.

--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

Minimize logging with small batches

Para operações de modificação de dados grandes, pode fazer sentido dividir a operação em partes ou lotes para definir o escopo da unidade de trabalho.

Um código a seguir é um exemplo de trabalho. O tamanho do lote foi definido para um número trivial para destacar a técnica. Na realidade, o tamanho do lote seria significativamente maior.

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

Diretrizes de pausa e escalonamento

Dedicated SQL pool lets you pause, resume, and scale your dedicated SQL pool on demand. Quando você pausa ou dimensiona seu pool SQL dedicado, é importante entender que todas as transações a bordo são encerradas imediatamente; fazendo com que quaisquer transações abertas sejam revertidas. If your workload had issued a long running and incomplete data modification prior to the pause or scale operation, then this work will need to be undone. This undoing might impact the time it takes to pause or scale your dedicated SQL pool.

Important

Both UPDATE and DELETE are fully logged operations and so these undo/redo operations can take significantly longer than equivalent minimally logged operations.

The best scenario is to let in flight data modification transactions complete prior to pausing or scaling a dedicated SQL pool. No entanto, este cenário pode nem sempre ser prático. Para reduzir o risco de uma reversão longa, considere uma das seguintes opções:

  • Reescrever operações de longa execução usando CTAS
  • Break the operation into chunks; operating on a subset of the rows

Próximos passos

Consulte Transações no pool SQL dedicado para saber mais sobre níveis de isolamento e limites transacionais. For an overview of other Best Practices, see Dedicated SQL pool best practices.