Azure Synapse Analytics'te ayrılmış SQL havuzundaki işlemleri iyileştirme

Uzun geri alma risklerini en aza indirirken ayrılmış SQL havuzundaki işlem kodunuzun performansını iyileştirmeyi öğrenin.

İşlemler ve günlüğe kaydetme

İşlemler, ilişkisel SQL havuzu altyapısının önemli bir bileşenidir. İşlemler veri değişikliği sırasında kullanılır. Bu işlemler açık veya örtük olabilir. Tek INSERT, UPDATE ve DELETE deyimlerinin tümü örtük işlemlere örnektir. Açık işlemler BEGIN TRAN, COMMIT TRAN veya ROLLBACK TRAN kullanır. Açık işlemler genellikle birden çok değişiklik deyiminin tek bir atomik ünitede birbirine bağlanması gerektiğinde kullanılır.

SQL havuzundaki değişiklikler işlem günlükleri kullanılarak izlenir. Her dağıtımın kendi işlem günlüğü vardır. İşlem günlüğü yazma işlemleri otomatiktir. Yapılandırma gerekmez. Ancak, bu işlem yazma işlemini garanti ederken sistemde bir ek yük getirir. İşlemsel olarak verimli kod yazarak bu etkiyi en aza indirebilirsiniz. İşlemsel olarak verimli kod geniş anlamda iki kategoriye ayrılır.

• Mümkün olduğunda en düşük günlük yapılarını kullanın
• Tekil uzun süre çalışan işlemlerden kaçınmak için kapsamlı toplu işlemleri kullanarak verileri işleme
• Belirli bir bölümde yapılan büyük değişiklikler için bölüm değiştirme deseni benimseme

En az ve tam günlük

Her satır değişikliğini izlemek için işlem günlüğünü kullanan tam olarak günlüğe kaydedilen işlemlerden farklı olarak, en az günlüğe kaydedilen işlemler yalnızca kapsam ayırmalarını ve meta veri değişikliklerini izler. Bu nedenle, minimum günlük yalnızca bir hatadan sonra işlemi geri almak veya açık bir istek (ROLLBACK TRAN) için gereken bilgileri günlüğe kaydetmeyi içerir. İşlem günlüğünde çok daha az bilgi izlense de, en düşük düzeyde günlüğe kaydedilen bir işlem benzer boyuttaki tam günlüğe kaydedilen işlemden daha iyi performans gösterir. Ayrıca işlem günlüğüne daha az yazma işlemi gittiği için çok daha az miktarda günlük verisi oluşturulur ve G/Ç daha verimlidir.

İşlem güvenliği sınırları yalnızca tam olarak günlüğe kaydedilen işlemler için geçerlidir.

Not

En az günlüğe kaydedilen işlemler açık işlemlere katılabilir. Ayırma yapılarındaki tüm değişiklikler izlendiğinden, en az günlüğe kaydedilen işlemleri geri almak mümkündür.

En az günlüğe kaydedilen işlemler

Aşağıdaki işlemler minimum düzeyde günlüğe kaydedilebilir:

• SELECT OLARAK TABLO OLUŞTURMA (CTAS)
• EKLE.. SEÇİN
• CREATE INDEX
• ALTER INDEX REBUILD
• DROP INDEX
• TRUNCATE TABLE
• DROP TABLE
• ALTER TABLE SWITCH PARTITION

Not

İç veri taşıma işlemleri (BROADCAST ve SHUFFLE gibi) işlem güvenlik sınırından etkilenmez.

Toplu yükleme ile minimum günlük kaydı

CTAS ve INSERT... SELECT her ikisi de toplu yükleme işlemleridir. Ancak her ikisi de hedef tablo tanımından etkilenir ve yükleme senaryosuna bağlıdır. Aşağıdaki tabloda toplu işlemlerin ne zaman tam veya en az günlüğe kaydediltiği açıklanmaktadır:

Birincil Dizin	Yükleme Senaryosu	Günlük Modu
Yığın	Herhangi biri	En az
Kümelenmiş	Boş hedef tablo	En az
Kümelenmiş	Yüklenen satırlar hedefteki mevcut sayfalarla çakışmaz	En az
Kümelenmiş	Yüklenen satırlar hedefteki mevcut sayfalarla çakışıyor	Tam
Kümelenmiş Columnstore Dizini	Toplu iş boyutu >= bölüme hizalı dağıtım başına 102.400	En az
Kümelenmiş Columnstore Dizini	Bölüme hizalı dağıtım başına toplu iş boyutu < 102.400	Tam

İkincil veya kümelenmemiş dizinleri güncelleştirmeye yönelik yazma işlemlerinin her zaman tam olarak günlüğe kaydedilen işlemler olacağını belirtmek önemlidir.

Önemli

Ayrılmış SQL havuzu 60 dağıtıma sahiptir. Bu nedenle, tüm satırların eşit olarak dağıtıldığını ve tek bir bölüme eklendiğini varsayarsak, toplu işleminizin Kümelenmiş Columnstore Dizinine yazarken en az günlüğe kaydedilecek 6.144.000 veya daha büyük satır içermesi gerekir. Tablo bölümlenmişse ve eklenen satırlar yayılmış bölüm sınırlarına yayılıyorsa, veri dağıtımının eşit olduğu varsayılarak bölüm sınırı başına 6.144.000 satır gerekir. Her dağıtımdaki her bölümün, eklemenin dağıtımda en az düzeyde oturum açması için 102.400 satır eşiğini bağımsız olarak aşması gerekir.

Kümelenmiş dizine sahip boş olmayan bir tabloya veri yüklemek genellikle tam olarak günlüğe kaydedilmiş ve en az günlüğe kaydedilen satırların bir karışımını içerebilir. Kümelenmiş dizin, sayfaların dengeli bir ağacıdır (b-ağaç). Yazılmakta olan sayfa zaten başka bir işlemden satırlar içeriyorsa, bu yazma işlemleri tamamen günlüğe kaydedilir. Ancak, sayfa boşsa o sayfaya yazma işlemi en az günlüğe kaydedilir.

Silmeleri iyileştirme

DELETE, tam olarak günlüğe kaydedilen bir işlemdir. Bir tablo veya bölümdeki büyük miktarda veriyi silmeniz gerekiyorsa, tutmak istediğiniz veriler genellikle daha anlamlıdır SELECT ve bu işlem en az günlüğe kaydedilen işlem olarak çalıştırılabilir. Verileri seçmek için CTAS ile yeni bir tablo oluşturun. Oluşturulduktan sonra, eski tablonuzu yeni oluşturulan tabloyla değiştirmek için RENAME kullanın.

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

Güncelleştirmeleri iyileştirme

UPDATE, tam olarak günlüğe kaydedilen bir işlemdir. Bir tablo veya bölümde çok sayıda satırı güncelleştirmeniz gerekiyorsa, bunu yapmak için CTAS gibi en az günlüğe kaydedilmiş bir işlemi kullanmak genellikle çok daha verimli olabilir.

Aşağıdaki örnekte tam bir tablo güncelleştirmesi CTAS'ye dönüştürüldü, böylece minimum günlük kaydı mümkündür.

Bu durumda, tablodaki satışlara geçmişe dönük olarak bir indirim tutarı ekliyoruz:

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

Not

Büyük tabloları yeniden oluşturmak, ayrılmış SQL havuzu iş yükü yönetimi özelliklerini kullanmaktan yararlanabilir. Daha fazla bilgi için bkz. İş yükü yönetimi için kaynak sınıfları.

Bölüm değiştirme ile iyileştirme

Tablo bölümü içinde büyük ölçekli değişikliklerle karşılaşırsanız bölüm değiştirme deseni mantıklıdır. Veri değişikliği önemliyse ve birden çok bölüme yayılıyorsa, bölümler üzerinde yineleme aynı sonucu elde eder.

Bölüm anahtarı gerçekleştirme adımları şunlardır:

1. Boş bir dışarı bölüm oluşturma
2. CTAS olarak 'güncelleştirmeyi' gerçekleştirme
3. Mevcut verileri out tablosuna değiştirme
4. Yeni verileri değiştirme
5. Verileri temizleme

Ancak, geçiş için bölümleri tanımlamaya yardımcı olmak için aşağıdaki yardımcı yordamı oluşturun.

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

Bu yordam, kodun yeniden kullanılmasını en üst düzeye çıkarır ve bölüm değiştirme örneğini daha kompakt tutar.

Aşağıdaki kod, tam bölüm değiştirme yordamı elde etmek için daha önce bahsedilen adımları gösterir.

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

Küçük toplu işlerle günlüğe kaydetmeyi en aza indirme

Büyük veri değiştirme işlemleri için, iş birimini kapsamak için işlemi öbeklere veya toplu işlemlere bölmek mantıklı olabilir.

Aşağıdaki kod, çalışan bir örnektir. Toplu iş boyutu, tekniği vurgulamak için önemsiz bir sayıya ayarlanmıştır. Gerçekte toplu iş boyutu önemli ölçüde daha büyük olacaktır.

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

Duraklatma ve ölçeklendirme kılavuzu

Ayrılmış SQL havuzu, ayrılmış SQL havuzunuzu isteğe bağlı olarak duraklatmanızı, sürdürmenizi ve ölçeklendirmenizi sağlar. Ayrılmış SQL havuzunuzu duraklattığınızda veya ölçeklendirdiğinizde, tüm uçuş içi işlemlerin hemen sonlandırıldığını anlamanız önemlidir; açık işlemlerin geri alınmasına neden olur. İş yükünüz duraklatma veya ölçeklendirme işleminden önce uzun süre çalışan ve tamamlanmamış bir veri değişikliği yapmışsa, bu çalışmanın geri alınması gerekir. Bu geri alma işlemi ayrılmış SQL havuzunuzu duraklatma veya ölçeklendirme süresini etkileyebilir.

Önemli

Hem hem DELETE de UPDATE tam olarak günlüğe kaydedilen işlemlerdir ve bu nedenle bu geri alma/yineleme işlemleri, eşdeğer en az günlüğe kaydedilen işlemlerden önemli ölçüde daha uzun sürebilir.

En iyi senaryo, ayrılmış bir SQL havuzunu duraklatmadan veya ölçeklendirmeden önce veri değişikliği işlemlerinin tamamlanmasına izin vermektir. Ancak bu senaryo her zaman pratik olmayabilir. Uzun bir geri alma riskini azaltmak için aşağıdaki seçeneklerden birini göz önünde bulundurun:

• CTAS kullanarak uzun süre çalışan işlemleri yeniden yazma
• İşlemi öbeklere bölün; satırların bir alt kümesinde çalışma

Sonraki adımlar

Yalıtım düzeyleri ve işlem sınırları hakkında daha fazla bilgi edinmek için bkz. Ayrılmış SQL havuzundaki işlemler. Diğer En İyi Yöntemlere genel bakış için bkz . Ayrılmış SQL havuzu en iyi yöntemleri.