Compartir por

Optimización de transacciones en un grupo de SQL dedicado en Azure Synapse Analytics

Aprenda a optimizar el rendimiento de su código transaccional en un pool de SQL dedicado, al mismo tiempo que minimiza el riesgo de reversiones prolongadas.

Transacciones y registro

Las transacciones son un componente importante de un motor de grupo de SQL relacional. Las transacciones se usan durante la modificación de datos. Estas transacciones pueden ser explícitas o implícitas. Las instrucciones INSERT, UPDATE y DELETE únicas son ejemplos de transacciones implícitas. Las transacciones explícitas usan BEGIN TRAN, COMMIT TRAN o ROLLBACK TRAN. Normalmente, las transacciones explícitas se usan cuando es necesario vincular varias instrucciones de modificación en una sola unidad atómica.

Se realiza un seguimiento de los cambios en el grupo de SQL mediante registros de transacciones. Cada distribución tiene su propio registro de transacciones. Las escrituras del registro de transacciones son automáticas. No se requiere ninguna configuración. Sin embargo, aunque este proceso garantiza la escritura, introduce una sobrecarga en el sistema. Puede minimizar este impacto escribiendo código transaccionalmente eficaz. El código eficaz transaccionalmente se divide ampliamente en dos categorías.

  • Uso de construcciones de registro mínimas siempre que sea posible
  • Procesar datos mediante lotes delimitados para evitar transacciones únicas de larga duración
  • Adopción de un patrón de cambio de partición para realizar modificaciones grandes en una partición determinada

Registro mínimo vs. registro completo

A diferencia de las operaciones totalmente registradas, que usan el registro de transacciones para realizar un seguimiento de cada cambio de fila, las operaciones con registro mínimo realizan un seguimiento de las asignaciones de extensiones y los cambios de metadatos solo. Por lo tanto, el registro mínimo implica registrar solo la información necesaria para revertir la transacción después de un error o para una solicitud explícita (ROLLBACK TRAN). Dado que se hace un seguimiento de mucha menos información en el log de transacciones, una operación mínimamente registrada funciona mejor que una operación totalmente registrada de tamaño similar. Además, dado que menos escrituras van al registro de transacciones, se genera una cantidad mucho menor de datos de registro y, por tanto, es más eficaz la E/S.

Los límites de seguridad de las transacciones solo se aplican a las operaciones totalmente registradas.

Nota:

Las operaciones registradas mínimamente pueden participar en transacciones explícitas. A medida que se realiza un seguimiento de todos los cambios en las estructuras de asignación, es posible revertir las operaciones con registro mínimo.

Operaciones registradas mínimamente

Las siguientes operaciones son capaces de registrarse mínimamente:

  • CREAR TABLA COMO SELECT (CTAS)
  • INSERT..SELECT
  • CREAR ÍNDICE
  • ALTER INDEX REBUILD
  • DROP INDEX
  • TRUNCATE TABLE
  • DROP TABLE
  • ALTER TABLE SWITCH PARTITION

Nota:

Las operaciones de movimiento de datos internos (como BROADCAST y SHUFFLE) no se ven afectadas por el límite de seguridad de las transacciones.

Registro mínimo con carga masiva

CTAS e INSERT... SELECT son operaciones de carga masiva. Sin embargo, ambas se ven afectadas por la definición de la tabla de destino y dependen del escenario de carga. En la tabla siguiente se explica cuándo se registran completamente o mínimamente las operaciones masivas:

Índice principal Escenario de carga Modo de registro
Heap Cualquiera Mínimo
Índice agrupado Tabla de destino vacía Mínimo
Índice agrupado Las filas cargadas no se superponen con las páginas existentes en el destino especificado. Mínimo
Índice agrupado Las filas cargadas se superponen a páginas existentes en el destino Completo
Índice de almacén de columnas agrupado Tamaño del lote >= 102.400 por cada partición en distribución alineada Mínimo
Índice de almacén de columnas agrupado Tamaño de lote < 102,400 por partición en distribución alineada Completo

Vale la pena tener en cuenta que las escrituras para actualizar índices secundarios o no agrupados siempre serán operaciones totalmente registradas.

Importante

Un grupo de SQL dedicado tiene 60 distribuciones. Por lo tanto, suponiendo que todas las filas tengan una distribución uniforme y una sola partición como destino, el lote deberá contener 6 144 000 filas o más para un registro mínimo cuando se escribe en un índice de almacén de columnas agrupado. Si la tabla tiene particiones y las filas que se insertan traspasan los límites de partición, necesitará 6 144 000 filas por límite de partición, suponiendo una distribución uniforme de los datos. Cada partición de cada distribución debe superar de forma independiente el umbral de 102 400 filas para que la inserción se registre mínimamente en la distribución.

Cargar datos en una tabla no vacía con un índice agrupado a menudo puede contener una combinación de filas totalmente registradas y mínimamente registradas. Un índice agrupado es un árbol equilibrado (árbol b) de páginas. Si la página que se escribe ya contiene filas de otra transacción, estas escrituras se registrarán completamente. Sin embargo, si la página está vacía, la escritura en esa página se registrará mínimamente.

Optimización de borrados

DELETE es una operación totalmente registrada. Si necesita eliminar una gran cantidad de datos de una tabla o una partición, suele tener más sentido realizar una instrucción SELECT en los datos que desea conservar, que pueden ejecutarse como operación con registro completo. Para seleccionar los datos, cree una tabla con CTAS. Una vez creado, use RENAME para intercambiar la tabla anterior con la tabla recién creada.

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

Optimización de actualizaciones

UPDATE es una operación totalmente registrada. Si necesita actualizar un gran número de filas en una tabla o una partición, a menudo puede ser mucho más eficaz usar una operación con registro mínimo, como CTAS , para hacerlo.

En el ejemplo siguiente, se ha convertido la actualización de una tabla completa en una operación CTAS para que sea posible el registro mínimo.

En este caso, estamos agregando de forma retrospectiva una cantidad de descuento a las ventas de la tabla:

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

Nota:

El uso de las características de administración de cargas de trabajo del grupo de SQL dedicado puede ser una ventaja a la hora de volver a crear tablas de gran tamaño. Para más información, consulte Clases de recursos para la administración de cargas de trabajo.

Optimización con la conmutación de particiones

Si se enfrenta a modificaciones a gran escala dentro de una partición de tabla, entonces es racional emplear un patrón de intercambio de particiones. Si la modificación de datos es significativa y abarca varias particiones, la iteración sobre las particiones logra el mismo resultado.

Los pasos para realizar un modificador de partición son los siguientes:

  1. Creación de una partición de salida vacía
  2. Realizar la actualización como una operación CTAS
  3. Desactivar los datos existentes en la tabla de salida
  4. Introduce los nuevos datos
  5. Limpieza de los datos

Sin embargo, para ayudar a identificar las particiones que se van a cambiar, cree el siguiente procedimiento 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 procedimiento maximiza la reutilización del código y mantiene el ejemplo de cambio de partición más compacto.

En el código siguiente se muestran los pasos mencionados anteriormente para lograr una rutina de cambio de partición completa.

--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 el registro de datos con lotes pequeños

En el caso de las operaciones de modificación de datos de gran tamaño, puede tener sentido dividir la operación en fragmentos o lotes para definir el ámbito de la unidad de trabajo.

Un código siguiente es un ejemplo de trabajo. El tamaño del lote se ha establecido en un número trivial para resaltar la técnica. En realidad, el tamaño del lote sería significativamente mayor.

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

Guía de pausa y escalado

El grupo de SQL dedicado le permite pausar, reanudar y escalar el grupo de SQL dedicado a petición. Al pausar o escalar el grupo de SQL dedicado, es importante entender que las transacciones en curso se terminan inmediatamente, lo que hace que las que están abiertas se reviertan. Si la carga de trabajo había emitido una modificación de datos incompleta y de larga ejecución antes de la operación de pausa o escalado, será necesario deshacer este trabajo. Esta acción puede afectar al tiempo que se tarda en pausar o escalar el grupo de SQL dedicado.

Importante

Tanto UPDATE como DELETE son operaciones totalmente registradas, por lo que estas operaciones de deshacer o rehacer pueden tardar significativamente más tiempo que las operaciones mínimamente registradas equivalentes.

Lo mejor es dejar que las transacciones de modificación de datos en curso se completen antes de pausar o escalar el grupo de SQL dedicado. Sin embargo, este escenario podría no ser siempre práctico. Para mitigar el riesgo de una reversión prolongada, considere una de las siguientes opciones:

  • Reescritura de operaciones de larga duración mediante CTAS
  • Divida la operación en fragmentos para operar sobre un subconjunto de las filas

Pasos siguientes

Consulte Transacciones en un grupo de SQL dedicado para obtener más información sobre los niveles de aislamiento y los límites transaccionales. Para obtener información general sobre otros procedimientos recomendados, consulte Procedimientos recomendados del grupo de SQL dedicado.

  • Last updated on