Optimalkan transaksi dengan kumpulan SQL khusus di Azure Synapse Analytics

  • Artikel

Pelajari cara mengoptimalkan performa kode transaksional Anda di kumpulan SQL khusus sambil meminimalkan risiko putar kembali yang panjang.

Transaksi dan pengelogan

Transaksi adalah komponen penting dari mesin kumpulan SQL. Transaksi digunakan selama pengubahan data. Transaksi ini bisa secara eksplisit atau implisit. Pernyataan tunggal INSERT, UPDATE, dan DELETE adalah contoh transaksi implisit. Transaksi eksplisit menggunakan BEGIN TRAN, COMMIT TRAN, atau ROLLBACK TRAN. Transaksi eksplisit biasanya digunakan saat beberapa pernyataan pengubahan perlu diikat bersama-sama ke dalam satu unit atomik.

Perubahan pada kumpulan SQL dilacak menggunakan log transaksi. Setiap distribusi memiliki log transaksinya sendiri. Penulisan log transaksi adalah otomatis. Tidak memerlukan adanya konfigurasi. Namun, sementara proses ini menjamin penulisan, ia memperkenalkan overhead dalam sistem. Anda dapat meminimalkan dampak ini dengan menulis kode yang efisien secara transaksional. Kode yang efisien secara transaksional secara luas terbagi dalam dua kategori.

  • Gunakan konstruksi pengelogan minimal kapan pun memungkinkan
  • Proses data menggunakan cakupan batch untuk menghindari transaksi tunggal yang berjalan lama
  • Adopsi pola pengalihan partisi untuk pengubahan besar pada partisi tertentu

Pengelogan minimal vs penuh

Tidak seperti operasi yang dicatat penuh yang menggunakan log transaksi untuk melacak setiap perubahan baris, operasi yang dicatat secara minimal melacak alokasi jangkauan dan perubahan meta-data saja. Oleh karena itu, pengelogan minimal hanya melibatkan pengelogan informasi yang diperlukan untuk menggulung balik transaksi setelah kegagalan, atau untuk permintaan eksplisit (ROLLBACK TRAN). Karena informasi yang lebih sedikit dilacak dalam log transaksi, operasi yang dicatat secara minimal menunjukkan performa yang lebih baik daripada operasi yang dicatat secara penuh dengan ukuran yang sama. Selain itu, karena lebih sedikit penulisan ke log transaksi, jumlah data log yang dihasilkan jauh lebih kecil sehingga I/O lebih efisien.

Batas keamanan transaksi hanya berlaku untuk operasi yang dicatat secara penuh.

Catatan

Operasi yang dicatat secara minimal dapat berpartisipasi dalam transaksi eksplisit. Karena semua perubahan dalam struktur alokasi dilacak, dimungkinkan untuk menggulung balik operasi yang dicatat secara minimal.

Operasi yang dicatat secara minimal

Operasi berikut ini mampu dicatat secara minimal:

  • CREATE TABLE AS SELECT (CTAS)
  • INSERT..SELECT
  • CREATE INDEX
  • UBAH INDEKS MEMBANGUN KEMBALI
  • DROP INDEX
  • POTONG TABEL
  • DROP TABLE
  • UBAH PARTISI SAKELAR TABEL

Catatan

Operasi pergerakan data internal (seperti BROADCAST dan SHUFFLE) tidak terpengaruh oleh batas keamanan transaksi.

Pengelogan minimal dengan muat massal

CTAS dan MASUKKAN..PILIH keduanya adalah operasi muat massal. Namun, keduanya dipengaruhi oleh definisi tabel target dan tergantung pada skenario beban. Tabel berikut menjelaskan kapan operasi massal dicatat sepenuhnya atau minimal:

Indeks Utama Skenario Muat Mode Pengelogan
Tumpukan Apa pun Minimal
Indeks berkluster Tabel target kosong Minimal
Indeks berkluster Baris yang dimuat tidak tumpang tindih dengan halaman yang sudah ada dalam target Minimal
Indeks berkluster Baris yang dimuat tidak tumpang tindih dengan halaman yang sudah ada dalam target Data
Indeks penyimpan kolom berkluster Ukuran batch >= 102.400 per partisi distribusi selaras Minimal
Indeks penyimpan kolom berkluster Ukuran batch < 102.400 per partisi distribusi selaras Data

Perlu dicatat bahwa setiap tulis untuk memperbarui indeks sekunder atau tidak berkluster akan selalu menjadi operasi yang sepenuhnya dicatat.

Penting

Kumpulan SQL khusus memiliki 60 distribusi. Oleh karena itu, dengan asumsi semua baris terdistribusi secara merata dan mendarat di satu partisi, batch Anda harus berisi 6.144.000 baris atau lebih besar untuk dicatat secara minimal saat menulis ke Indeks Penyimpanan Kolom Berkluster. Jika tabel dipartisi dan baris dimasukkan melewati batas partisi, maka Anda akan membutuhkan 6.144.000 baris per batas partisi dengan asumsi distribusi data merata. Setiap partisi di setiap distribusi harus secara independen melebihi ambang batas 102.400 baris agar sisipan dapat masuk ke distribusi secara minimal.

Memuat data ke dalam tabel yang tidak kosong dengan indeks berkluster sering kali dapat berisi campuran baris yang sepenuhnya dicatat dan yang sedikit dicatat. Indeks berkluster adalah pohon seimbang (b-tree) halaman. Jika halaman yang ditulis sudah berisi baris dari transaksi lain, maka tulisan-tulisan ini akan dicatat sepenuhnya. Namun, jika halaman kosong, maka penulisan ke halaman itu akan dicatat secara minimal.

Mengoptimalkan penghapusan

DELETE adalah operasi yang dicatat secara penuh. Jika Anda perlu menghapus sejumlah besar data dalam tabel atau partisi, sering kali lebih masuk akal untuk SELECT data yang ingin Anda simpan, yang dapat dijalankan sebagai operasi yang dicatat secara minimal. Untuk memilih data, buat tabel baru dengan CTAS. Setelah dibuat, gunakan RENAME untuk menukar tabel lama Anda dengan tabel yang baru dibuat.

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

Mengoptimalkan pembaruan

UPDATE adalah operasi yang dicatat secara penuh. Jika Anda perlu memperbarui sejumlah besar baris dalam tabel atau partisi, sering bisa menjadi lebih efisien untuk menggunakan operasi yang dicatat secara minimal seperti CTAS.

Dalam contoh di bawah pembaruan tabel lengkap yang telah dikonversi ke CTAS sehingga memungkinkan pengelogan minimal.

Dalam hal ini, kami secara retrospektif menambahkan jumlah diskon ke penjualan dalam tabel:

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

Catatan

Membuat ulang tabel besar dapat bermanfaat dengan menggunakan fitur manajemen beban kerja kumpulan SQL khusus. Untuk informasi selengkapnya, lihat Kelas sumber daya untuk manajemen beban kerja.

Mengoptimalkan dengan pengalihan partisi

Jika dihadapkan dengan pengubahan skala besar dalam partisi tabel, maka pola pengalihan partisi bisa dilakukan. Jika modifikasi data signifikan dan mencakup beberapa partisi, maka iterasi atas partisi menghasilkan hasil yang sama.

Langkah-langkah untuk melakukan partisi pengalihan adalah sebagai berikut:

  1. Buat partisi yang dikosongkan
  2. Lakukan 'pembaruan' sebagai CTAS
  3. Alihkan data yang sudah ada ke tabel luar
  4. Beralih ke data baru
  5. Bersihkan datanya

Namun, untuk membantu mengidentifikasi partisi yang akan dialihkan, buatlah prosedur pembantu berikut.

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

Prosedur ini memaksimalkan penggunaan kembali kode dan membuat contoh pengalihan partisi menjadi lebih ringkas.

Kode berikut menunjukkan langkah-langkah yang disebutkan sebelumnya untuk mencapai rutinitas pengalihan partisi penuh.

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

Minimalkan pengelogan dengan batch kecil

Untuk operasi pengubahan data yang besar, mungkin masuk akal untuk membagi operasi menjadi gugusan atau batch untuk cakupan unit kerja.

Kode berikut adalah contoh yang berfungsi. Ukuran batch telah diset ke angka trivial untuk menyorot tekniknya. Pada kenyataannya, ukuran batch akan jauh lebih besar.

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

Panduan jeda dan penskalaan

Kumpulan SQL khusus memungkinkan Anda menjeda, melanjutkan, dan menskalakan kumpulan SQL khusus Anda sesuai permintaan. Ketika Anda menjeda atau menskalakan kumpulan SQL khusus Anda, penting untuk memahami bahwa setiap transaksi di dalam penerbangan segera diakhiri dan menyebabkan setiap transaksi yang terbuka digulung kembali. Jika beban kerja Anda memiliki masalah dengan pengubahan data yang berjalan lama dan tidak lengkap sebelum operasi jeda atau skala, maka pekerjaan ini harus dibatalkan. Pembatalan pekerjaan ini dapat memengaruhi waktu yang diperlukan untuk menjeda atau menskalakan kumpulan SQL khusus Anda.

Penting

Keduanya UPDATE dan DELETE merupakan operasi yang dicatat secara penuh sehingga operasi batalkan/ulangi ini dapat berlangsung lebih lama secara signifikan daripada operasi yang sama yang dicatat secara minimal.

Skenario terbaik adalah membiarkan transaksi pengubahan data di dalam penerbangan selesai sebelum menjeda atau menskalakan kumpulan SQL khusus Anda. Namun, skenario ini mungkin tidak selalu praktis. Untuk mengurangi risiko putar kembali yang panjang, pertimbangkan salah satu opsi berikut:

  • Tulis ulang operasi jangka panjang menggunakan CTAS
  • Pecahkan operasi menjadi gugusan; beroperasi pada subset baris

Langkah berikutnya

Lihat Transaksi dalam kumpulan SQL khusus untuk mempelajari lebih lanjut tentang tingkat isolasi dan batas transaksional. Untuk ringkasan tentang Praktik Terbaik lainnya, lihat Praktik terbaik kumpulan SQL Khusus.