你当前正在访问 Microsoft Azure Global Edition 技术文档网站。如果需要访问由世纪互联运营的 Microsoft Azure 中国技术文档网站，请访问 https://docs.azure.cn。

在 Azure Synapse Analytics 中优化专用 SQL 池中的事务

了解如何优化专用 SQL 池中事务代码的性能，同时最大程度地降低长时间回滚的风险。

事务和日志记录

事务是关系型 SQL 池引擎的重要组成部分。事务在数据修改期间使用。这些事务可以是显式的或隐式的。单个 INSERT、UPDATE 和 DELETE 语句都是隐式事务的示例。显式事务使用 BEGIN TRAN、COMMIT TRAN 或 ROLLBACK TRAN。显式事务通常用于多个修改语句需要绑定在单个原子单元的时候。

使用事务日志跟踪对 SQL 池的更改。每个分发都有其自己的事务日志。事务日志写入是自动的。无需配置。虽然此过程能确保写入，但它确实会在系统中引入开销。可以通过编写事务高效的代码来最大程度地减少这种影响。事务高效的代码大致分为两类。

尽可能使用最小日志记录构造
使用限定范围的批来处理数据，避免单数形式的长时运行事务
采用分区切换模式，对给定分区进行大规模修改

最小日志记录与完整日志记录

与使用事务日志跟踪每一行更改的完全记录操作不同，最少记录的操作仅跟踪盘区分配和元数据更改。因此，最少日志记录涉及只记录失败或显式请求 (ROLLBACK TRAN) 后回退事务所需的信息。由于事务日志中跟踪的信息要少得多，最小记录的操作的性能优于类似大小的完全记录操作。此外，由于写入事务日志的次数减少，因此生成的事务日志数据更少，从而提高了 I/O 效率。

事务安全限制仅适用于完全记录的操作。

注释

最少记录的操作可以参与显式事务。分配结构中的所有更改都被跟踪，因此实现回滚最少记录的操作变得可能。

最少记录的操作

可以以最小化日志记录进行以下操作：

创建表作为选择（CTAS）
插入。。选择
创建索引 (CREATE INDEX)
ALTER INDEX REBUILD
DROP INDEX
TRUNCATE TABLE
DROP TABLE
ALTER TABLE SWITCH PARTITION

注释

内部数据移动操作（如 BROADCAST 和 SHUFFLE）不受事务安全限制的影响。

使用大容量加载进行最小日志记录

CTAS 和 INSERT...SELECT 都是大容量加载操作。但是，这两者都受目标表定义的影响，并取决于负载方案。下表说明了何时完全或最少地记录批量操作：

主索引	加载场景	日志记录模式
堆	任意	最小
聚集索引	空目标表	最小
聚集索引	加载的行不会与目标中的现有页面重叠	最小
聚集索引	加载的行与目标中的现有页面重叠	完整
聚集列存储索引	批大小 >= 102,400/每分区对齐的分布区	最小
聚集列存储索引	批大小 < 102,400/每分区对齐的分布区	完整

值得注意的是，更新辅助索引或非聚集索引的任何写入始终都是完全记录的操作。

重要

专用 SQL 池有 60 个分发版。因此，假设所有行均匀分布且处于单个分区中，批在写入到聚集列存储索引时会需有 6,144,000 行（或更多）要按最少记录的方式记入日志。如果表已分区，并且要插入的行跨越分区边界，则每个分区边界需要 6,144,000 行，假设数据分布也是如此。每个分布区的每个分区各自必须超过 102,400 行的阈值，从而使插入以最少记录的方式记录到分布区中。

将数据加载到含聚集索引的非空表通常可以包含完整记录和最少记录的行的组合。聚集索引是页面的平衡树 (b-tree)。如果要写入的页面已包含另一个事务中的行，则将完全记录这些写入。但是，如果页面为空，则对该页的写入将进行最少的日志记录。

优化删除

DELETE 是一个完全记录的操作。如果需要删除表或分区中的大量数据，SELECT 要保留的数据通常更有意义，其可作为最少记录的操作来运行。若要选择数据，请使用 CTAS 创建新表。创建后，使用 RENAME 将旧表与新创建的表交换。

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

注释

重新创建大型表可以受益于使用专用 SQL 池工作负荷管理功能。有关详细信息，请参阅用于工作负荷管理的资源类。

使用分区切换进行优化

如果在表分区内进行大规模修改，则采用分区切换模式是合理的。如果数据修改很重要，并且跨越多个分区，则循环访问分区可实现相同的结果。

执行分区切换的步骤如下：

创建空分区
采用 CTAS 执行 “update”
将现有数据转出到输出表
转入新数据
清理数据

但是，为了帮助标识要切换的分区，请创建以下辅助过程。

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 池所需的时间。

重要

这两个 UPDATE 和 DELETE 操作都是完全记录日志的，因此这些撤销/重做操作可能比等效的最低记录日志操作花费更长时间。

最佳方案是在暂停或缩放专用 SQL 池之前完成正在执行的数据修改事务。但是，此方案可能并不总是可行的。若要缓解长时间回滚的风险，请考虑以下选项之一：

使用 CTAS 重新编写长时间运行的操作
将操作分解为区块;对行的子集进行操作

后续步骤

有关隔离级别和事务限制的详细信息，请参阅专用 SQL 池中的事务。有关其他最佳做法的概述，请参阅专用 SQL 池最佳做法。

反馈

此页面是否有帮助？

Last updated on 2026-03-27