Bagikan melalui

Mengoptimalkan transaksi di kumpulan SQL khusus di Azure Synapse Analytics

Pelajari cara mengoptimalkan performa kode transaksi di kumpulan SQL khusus sambil meminimalkan risiko untuk pemutaran kembali yang panjang.

Transaksi dan pengelogan

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

Perubahan pada kumpulan SQL dilacak menggunakan log transaksi. Setiap distribusi memiliki log transaksinya sendiri. Penulisan log transaksi bersifat otomatis. Tidak diperlukan konfigurasi. Meskipun proses ini menjamin penulisan, hal ini memang memperkenalkan beban tambahan dalam sistem. Anda dapat meminimalkan dampak ini dengan menulis kode yang efisien secara transaksional. Kode yang efisien secara transaksional secara luas termasuk dalam dua kategori.

  • Gunakan konstruksi pengelogan minimal jika memungkinkan
  • Memproses data menggunakan batch yang terdefinisi untuk menghindari transaksi tunggal yang berjalan lama
  • Gunakan pola pergantian partisi untuk perubahan besar pada partisi tertentu

Pencatatan minimal vs. penuh

Tidak seperti operasi yang dicatat sepenuhnya, yang menggunakan log transaksi untuk melacak setiap perubahan baris, operasi yang dicatat secara minimal hanya melacak alokasi ekstensi dan perubahan metadata. Oleh karena itu, pengelogan minimal hanya melibatkan pengelogan informasi yang diperlukan untuk mengembalikan transaksi setelah kegagalan, atau untuk permintaan eksplisit (ROLLBACK TRAN). Karena jauh lebih sedikit informasi yang dilacak dalam log transaksi, operasi dengan pencatatan minimal berkinerja lebih efisien daripada operasi serupa yang dicatat sepenuhnya. Selain itu, karena lebih sedikit penulisan masuk ke log transaksi, jumlah data log yang jauh lebih kecil dihasilkan dan lebih efisien I/O.

Batas keamanan transaksi hanya berlaku untuk operasi yang dicatat sepenuhnya.

Nota

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

Operasi dengan pencatatan minimal

Operasi berikut ini mampu dicatat secara minimal:

  • BUAT TABEL DENGAN MEMILIH (CTAS)
  • INSERT..SELECT
  • BUAT INDEKS
  • MEMBANGUN ULANG INDEKS
  • HAPUS INDEKS
  • TRUNCATE TABEL
  • HAPUS TABEL (DROP TABLE)
  • MENGUBAH TABEL PEMINDAHAN PARTISI

Nota

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

Pengelogan minimal dengan beban massal

CTAS dan INSERT... SELECT keduanya adalah operasi pemuatan massal. Namun, keduanya dipengaruhi oleh definisi tabel target dan bergantung pada skenario beban. Tabel berikut menjelaskan kapan operasi massal dicatat sepenuhnya atau minimal:

Indeks Utama Skenario Beban Mode Pengelogan
Tumpukan Apa saja Minimal
Indeks Terkelompok Tabel target kosong Minimal
Indeks Terkelompok Baris yang dimuat tidak tumpang tindih dengan halaman yang ada di target Minimal
Indeks Terkelompok Baris yang dimuat tumpang tindih dengan halaman yang ada dalam target Penuh
Indeks Penyimpan Kolom Berkluster Ukuran batch >= 102.400 per partisi dalam distribusi yang sejajar Minimal
Indeks Penyimpanan Kolom Berkluster Ukuran batch < 102,400 per distribusi sesuai partisi Penuh

Perlu dicatat bahwa setiap penulisan data untuk memperbarui indeks sekunder atau non-kluster akan selalu menjadi operasi yang dicatat sepenuhnya.

Penting

Kumpulan SQL khusus memiliki 60 distribusi. Oleh karena itu, dengan asumsi semua baris didistribusikan secara merata dan masuk dalam satu partisi, batch Anda harus berisi 6.144.000 baris atau lebih besar untuk dicatat seminimal mungkin saat menulis ke Indeks Penyimpan Kolom Berkluster. Jika tabel dipartisi dan baris yang dimasukkan mencakup perbatasan partisi, maka Anda akan memerlukan 6.144.000 baris per perbatasan partisi dengan asumsi distribusi data merata. Setiap partisi dalam setiap distribusi harus secara independen melebihi ambang batas 102.400 baris agar entri dicatat secara minimal ke dalam distribusi.

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

Mengoptimalkan penghapusan

DELETE adalah operasi yang dicatat sepenuhnya. Jika Anda perlu menghapus jumlah data yang besar pada tabel atau partisi, sering kali lebih masuk akal untuk SELECT data yang ingin Anda simpan, yang dapat dijalankan dengan pencatatan 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 sepenuhnya. Jika Anda perlu memperbarui sejumlah besar baris dalam tabel atau partisi, sering kali bisa jauh lebih efisien untuk menggunakan operasi yang dicatat secara minimal seperti CTAS untuk melakukannya.

Dalam contoh di bawah, pembaruan tabel lengkap telah dikonversi ke CTAS sehingga pencatatan minimal dimungkinkan.

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]

Nota

Membuat ulang tabel besar dapat memperoleh manfaat dari 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 modifikasi skala besar di dalam partisi tabel, maka pola pengalihan partisi masuk akal. Jika modifikasi data signifikan dan mencakup beberapa partisi, maka iterasi atas partisi mencapai hasil yang sama.

Langkah-langkah untuk melakukan sakelar partisi adalah sebagai berikut:

  1. Membuat partisi kosong
  2. Laksanakan 'pembaruan' sebagai CTAS
  3. Memindahkan data yang ada ke tabel keluaran
  4. Beralih ke data baru
  5. Membersihkan data tersebut

Namun, untuk membantu mengidentifikasi partisi yang akan dialihkan, buat 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 menjaga contoh pengalihan partisi 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

Meminimalkan pencatatan dengan kelompok kecil

Untuk operasi modifikasi data besar, mungkin masuk akal untuk membagi operasi menjadi gugus atau batch untuk mencakup unit kerja.

Kode berikut adalah contoh yang berfungsi. Ukuran batch telah diatur ke angka sepele untuk menyoroti teknik. 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 sesuai permintaan. Ketika Anda menjeda atau menskalakan kumpulan SQL khusus Anda, penting untuk dipahami bahwa setiap transaksi dalam penerbangan segera dihentikan; menyebabkan transaksi terbuka digulung balik. Jika tugas sistem Anda telah memulai modifikasi data yang berlangsung lama dan belum selesai sebelum operasi jeda atau penyesuaian skala, maka pekerjaan ini perlu dibatalkan. Perubahan ini dapat berdampak pada waktu yang diperlukan untuk menjeda atau menskalakan kumpulan SQL khusus Anda.

Penting

Keduanya UPDATE dan DELETE merupakan operasi yang sepenuhnya dicatat dan sehingga operasi pembatalan/pengulangan ini dapat memakan waktu lebih lama secara signifikan daripada operasi yang dicatat secara minimal yang setara.

Skenario terbaik adalah membiarkan transaksi modifikasi data penerbangan selesai sebelum menjeda atau menskalakan kumpulan SQL khusus. Namun, skenario ini mungkin tidak selalu praktis. Untuk mengurangi risiko pembatalan yang memakan waktu lama, pertimbangkan salah satu opsi berikut:

  • Menulis ulang operasi jangka panjang menggunakan CTAS
  • Bagi operasi menjadi beberapa bagian dan operasikan pada subset baris.

Langkah berikutnya

Lihat Transaksi di kumpulan SQL khusus untuk mempelajari selengkapnya tentang tingkat isolasi dan batas transaksi. Untuk gambaran umum Praktik Terbaik lainnya, lihat Praktik terbaik kumpulan SQL Khusus.

  • Last updated on