Otimizar transações no conjunto de SQL dedicado no Azure Synapse Analytics

Saiba como otimizar o desempenho do seu código transacional no conjunto de SQL dedicado, minimizando o risco de reversões longas.

Transações e registos

As transações são um componente importante de um motor de conjunto de SQL relacional. As transações são utilizadas durante a modificação de dados. Estas transações podem ser explícitas ou implícitas. As instruções INSERT, UPDATE e DELETE são exemplos de transações implícitas. As transações explícitas utilizam BEGIN TRAN, COMMIT TRAN ou ROLLBACK TRAN. Normalmente, as transações explícitas são utilizadas quando várias instruções de modificação precisam de ser associadas numa única unidade atómica.

As alterações ao conjunto de SQL são registadas através de registos de transações. Cada distribuição tem o seu próprio registo de transações. As escritas do registo de transações são automáticas. Não é necessária nenhuma configuração. No entanto, embora este processo garanta a escrita, introduz uma sobrecarga no sistema. Pode minimizar este impacto ao escrever código transacionalmente eficiente. O código transacionalmente eficiente enquadra-se em duas categorias.

• Utilizar construções de registo mínimas sempre que possível
• Processar dados com lotes de âmbito para evitar transações de execução prolongada singulares
• Adotar um padrão de mudança de partição para grandes modificações a uma determinada partição

Registo mínimo vs. completo

Ao contrário das operações totalmente registadas, que utilizam o registo de transações para controlar todas as alterações de linhas, as operações registadas mínimamente controlam apenas as alocações de extensão e as alterações de metadados. Portanto, o registo mínimo envolve registar apenas as informações necessárias para reverter a transação após uma falha ou para um pedido explícito (ROLLBACK TRAN). Como muito menos informações são registadas no registo de transações, uma operação mínima registada tem um desempenho melhor do que uma operação totalmente registada de tamanho semelhante. Além disso, como menos escritas vão para o registo de transações, é gerada uma quantidade muito menor de dados de registo, pelo que é mais eficiente em termos de E/S.

Os limites de segurança da transação aplicam-se apenas a operações totalmente registadas.

Operações mínimas registadas

As seguintes operações são capazes de ser registadas minimamente:

• CRIAR TABELA COMO SELECT (CTAS)
• INSERT.. SELECIONAR
• CREATE INDEX
• ALTER INDEX REBUILD
• DROP INDEX
• TRUNCATE TABLE
• DROP TABLE
• ALTERAR PARTIÇÃO DO COMUTADOR DE TABELA

Registo mínimo com carga em massa

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 seguinte explica quando as operações em massa são registadas de forma total ou mínima:

Vale a pena notar que quaisquer escritas para atualizar índices secundários ou não agrupados serão sempre operações totalmente registadas.

Carregar dados para uma tabela não vazia com um índice agrupado pode, muitas vezes, conter uma mistura de linhas totalmente registadas e mínimamente registadas. Um índice agrupado é uma árvore equilibrada (b-tree) de páginas. Se a página que está a ser escrita já contiver linhas de outra transação, estas escritas serão totalmente registadas. No entanto, se a página estiver vazia, a escrita nessa página será mínimamente registada.

Otimizar eliminações

DELETE é uma operação totalmente registada. Se precisar de eliminar uma grande quantidade de dados numa tabela ou partição, muitas vezes faz mais sentido para SELECT os dados que pretende manter, que podem ser executados como uma operação mínima registada. Para selecionar os dados, crie uma nova tabela com CTAS. Depois de criada, utilize o RENAME para trocar a sua tabela antiga pela tabela criada recentemente.

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

Otimizar atualizações

UPDATE é uma operação totalmente registada. Se precisar de atualizar um grande número de linhas numa tabela ou partição, muitas vezes pode ser muito mais eficiente utilizar uma operação mínimamente registada, como o CTAS , para o fazer.

No exemplo abaixo, uma atualização de tabela completa foi convertida numa CTAS para que seja possível um registo mínimo.

Neste caso, estamos a adicionar retrospectivamente 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]

Otimizar com a mudança de partições

Se for confrontado com modificações em grande escala dentro de uma partição de tabela, um padrão de mudança de partição faz sentido. Se a modificação de dados for significativa e abranger várias partições, iterando sobre as partições obtém o mesmo resultado.

Os passos para executar um comutador de partição são os seguintes:

1. Criar uma partição vazia
2. Executar a "atualização" como um CTAS
3. Mudar os dados existentes para a tabela de saída
4. Mudar para os novos dados
5. Limpar os dados

No entanto, para ajudar a identificar as partições a mudar, 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 do código e mantém o exemplo de mudança de partição mais compacto.

O código seguinte demonstra os passos mencionados anteriormente para obter uma rotina completa de mudança 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

Minimizar o registo com pequenos lotes

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

Um código seguinte é um exemplo funcional. O tamanho do lote foi definido como um número trivial para realçar 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

Documentação de orientação para colocar em pausa e dimensionar

O Conjunto de SQL dedicado permite-lhe colocar em pausa, retomar e dimensionar o conjunto de SQL dedicado a pedido. Quando colocar em pausa ou dimensionar o conjunto de SQL dedicado, é importante compreender que quaisquer transações em voo são terminadas imediatamente; o que faz com que quaisquer transações abertas sejam revertidas. Se a carga de trabalho tiver emitido uma modificação de dados de execução prolongada e incompleta antes da operação de pausa ou dimensionamento, este trabalho terá de ser anulado. Essa anulação pode afetar o tempo que demora a colocar em pausa ou dimensionar o conjunto de SQL dedicado.

O melhor cenário é permitir que as transações de modificação de dados de voo sejam concluídas antes de colocar em pausa ou dimensionar um conjunto de SQL dedicado. No entanto, este cenário pode nem sempre ser prático. Para mitigar o risco de uma reversão longa, considere uma das seguintes opções:

• Reescrever operações de execução prolongada com o CTAS
• Divida a operação em segmentos; a operar num subconjunto das linhas

Passos seguintes

Veja Transações no conjunto de SQL dedicado para saber mais sobre os níveis de isolamento e os limites transacionais. Para obter uma descrição geral de outras Melhores Práticas, veja Melhores práticas do conjunto de SQL dedicado.