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

Ayrılmış SQL havuzunda işlem kodunuzun performansını iyileştirmeyi ve uzun geri alma riskini en aza indirmeyi öğ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 minimal günlük yapılar kullanın
• Uzun süreli tekil işlemlerden kaçınmak için kapsamlı gruplar kullanarak verileri işleyin.
• Verilen bir bölümde yapılan büyük değişiklikler için bölüm değiştirme deseni benimseyin.

Minimum kayıt vs. tam kayıt

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 tutma, yalnızca bir hatadan sonra işlemi geri almak için gereken bilgilerin veya açık bir isteğe (ROLLBACK TRAN) yönelik bilgilerin günlüğe kaydedilmesini içerir. İşlem günlüğünde çok daha az bilgi izlendiği için, minimal günlüğe kaydedilen bir işlem, benzer boyuttaki tam günlüğe kaydedilen bir 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 bu da G/Ç açısından daha verimlidir.

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

Uyarı

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

Asgari günlüğe kaydedilen işlemler

Aşağıdaki işlemler minimal düzeyde kaydedilebilme kapasitesine sahiptir:

• TABLO OLUŞTUR SEÇİM OLARAK (CTAS)
• EKLE..SEÇ
• İNDİS OLUŞTUR
• İNDEKSİ YENİDEN OLUŞTURMA
• INDEX SİL
• TABLOYU KISALT
• TABLOYU SİL
• TABLOYU DEĞİŞTİR, BÖLÜMÜ DEĞİŞTİR

Uyarı

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

Toplu yük 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ük senaryosuna bağlıdır. Aşağıdaki tabloda toplu işlemlerin ne zaman tam veya minimum günlüğe kaydedildiği açıklanmaktadır.

Birincil Dizin	Yükleme Senaryosu	Kayıt Modu
Yığın	Herhangi biri	Asgari
Kümelenmiş Dizin	Boş hedef tablo	Asgari
Kümelenmiş Dizin	Yüklenen satırlar hedefteki mevcut sayfalarla çakışmaz	Asgari
Kümelenmiş Dizin	Yüklenen satırlar hedefteki mevcut sayfalarla çakışıyor	Tamamen dolu
Kümelenmiş Columnstore Dizini	Bölüme hizalı dağıtım başına toplu iş boyutu >= 102.400	Asgari
Kümelenmiş Columnstore Dizini	Bölüm başına hizalanmış dağıtımda toplu boyut < 102.400	Tamamen dolu

İkincil veya kümelenmemiş dizinleri güncelleştirmek için yapılan tüm yazma işlemlerinin her zaman tam olarak günlüğe kaydedilen işlemler olacağını belirtmek gerekir.

Önemli

Ayrılmış sql havuzu 60 dağıtıma sahiptir. Kümelenmiş Columnstore Dizinine yazarken, tüm satırların eşit olarak dağıtıldığını ve tek bir bölüme eklendiğini varsayarsak, işlem toplu verinizin en az 6.144.000 satır veya daha fazla içermesi gerekir. Tablo bölümlenmişse ve eklenen satırlar bölüm sınırlarına yayılıyorsa, veri dağıtımını bile varsayarak 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 kaydı yapılabilmesi için 102.400 satır eşiğini bağımsız olarak aşması gerekir.

Verileri kümelenmiş dizinli, boş olmayan bir tabloya yüklemek genellikle tam günlüğe kaydedilmiş ve kısmi günlüğe kaydedilmiş satırların bir karışımını içerebilir. Kümelenmiş dizin, sayfaların dengeli bir ağacıdır (b-ağacı). Eğer yazılan sayfa zaten başka bir işlemden satırlar içeriyorsa, bu yazma işlemleri tamamen günlüğe kaydedilecektir. Ancak, sayfa boşsa, o sayfaya yazma işlemi minimum düzeyde kayıt altına alınır.

Silmeleri iyileştirme

DELETE, tam olarak günlüğe kaydedilen bir işlemdir. Bir tablodaki veya bölümdeki büyük miktarda veriyi silmeniz gerekiyorsa, tutmak istediğiniz verileri SELECT daha mantıklıdır; bu işlem en az günlüğe kaydedilmiş bir işlem olarak çalıştırılabilir. Verileri seçmek için CTASile 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 tablodaki veya bölümdeki çok sayıda satırı güncelleştirmeniz gerekiyorsa, bunu yapmanın çok daha verimli bir yolu, CTAS gibi minimum kaydedilen bir işlem kullanmaktır.

Aşağıdaki örnekte tam tablo güncelleştirmesi CTAS'ye dönüştürülmüştür, böylece minimum günlük kaydı yapılabilir.

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]

Uyarı

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

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

Eğer birtablo bölümü içinde büyük ölçekli değişikliklerle karşılaşırsanız, bölüm değiştirme modeli mantıklı olur. 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 değiştirme adımları şu şekildedir:

1. Boş bölüm oluşturma
2. CTAS olarak 'güncelleştirme' işlemini yapın
3. Mevcut verileri 'out' tablosuna aktarın
4. Yeni verileri değiştirme
5. Verileri temizleme

Ancak, hangi bölümlerin değiştirileceğini belirlemeye 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ına ulaşmak 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, çalışma 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ıza, sürdürmenize ve ölçeklendirmenize olanak tanır. 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 başlatmışsa, bu çalışmanın geri alınması gerekir. Bu işlem, ayrılmış SQL havuzunuzu duraklatmak veya ölçeklendirmek için gereken süreyi etkileyebilir.

Önemli

Hem UPDATE hem de DELETE tam olarak günlüğe kaydedilen işlemlerdir ve bu nedenle bu geri alma/yineleme işlemleri, eşit minimum 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 uçuş verileri değiştirme işlemlerinin tamamlanmasını sağlamaktır. 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 İşlemler. Diğer En İyi Yöntemlere genel bakış için bkz. Ayrılmış SQL havuzu en iyi yöntemleri.