Share via

Panduan desain untuk menggunakan tabel yang direplikasi di kumpulan SQL Synapse

  • Artikel

Artikel ini memberikan rekomendasi untuk mendesain tabel yang direplikasi dalam skema kumpulan SQL Synapse Anda. Gunakan rekomendasi ini untuk meningkatkan performa kueri dengan mengurangi pemindahan data dan kompleksitas kueri.

Prasyarat

Artikel ini mengasumsikan Anda sudah memahami konsep distribusi data dan pemindahan data di kumpulan SQL. Untuk mengetahui informasi lebih lanjut, lihat artikel arsitektur.

Sebagai bagian dari desain tabel, pahami sebanyak mungkin tentang data Anda dan bagaimana data dikueri.  Misalnya, pertimbangkan pertanyaan-pertanyaan ini:

  • Seberapa besar tabelnya?
  • Seberapa sering tabel direfresh?
  • Apakah saya memiliki tabel fakta dan dimensi di kumpulan SQL?

Apa itu tabel yang direplikasi?

Tabel yang direplikasi memiliki salinan lengkap tabel yang dapat diakses pada setiap simpul Azure Compute. Mereplikasi tabel akan menghapus kebutuhan untuk mentransfer data di antara simpul Azure Compute sebelum gabungan atau agregasi. Karena tabel memiliki beberapa salinan, tabel yang direplikasi berfungsi paling baik saat ukuran tabel kurang dari 2 GB dikompresi. 2 GB bukanlah batas yang sulit. Jika data statis dan tidak berubah, Anda dapat mereplikasi tabel yang lebih besar.

Diagram berikut menunjukkan tabel yang direplikasi yang dapat diakses pada setiap simpul Azure Compute. Dalam kumpulan SQL, tabel yang direplikasi sepenuhnya disalin ke database distribusi pada setiap simpul komputasi.

Replicated table

Tabel yang direplikasi berfungsi dengan baik untuk tabel dimensi dalam skema bintang. Tabel dimensi biasanya digabungkan ke tabel fakta, yang didistribusikan secara berbeda dari tabel dimensi. Dimensi biasanya memiliki ukuran yang memungkinkan penyimpanan dan pemeliharaan banyak salinan. Dimensi menyimpan data deskriptif yang berubah secara perlahan, seperti nama dan alamat pelanggan, dan detail produk. Sifat data yang berubah secara perlahan menyebabkan pemeliharaan tabel yang direplikasi lebih sedikit.

Pertimbangkan untuk menggunakan tabel yang direplikasi saat:

  • Ukuran tabel pada disk kurang dari 2 GB, terlepas dari jumlah baris. Untuk menemukan ukuran tabel, Anda bisa menggunakan perintah DBCC PDW_SHOWSPACEUSED: DBCC PDW_SHOWSPACEUSED('ReplTableCandidate').
  • Tabel digunakan dalam gabungan yang membutuhkan pemindahan data. Saat menggabungkan tabel yang tidak terdistribusi pada kolom yang sama, seperti tabel yang didistribusikan hash ke tabel round-robin, pemindahan data diperlukan untuk menyelesaikan kueri. Jika salah satu tabel berukuran kecil, pertimbangkan menggunakan tabel yang direplikasi. Dalam banyak kasus, sebaiknya gunakan tabel yang direplikasi bukan tabel round-robin. Untuk menampilkan operasi pemindahan data dalam rencana kueri, gunakan sys.dm_pdw_request_steps. BroadcastMoveOperation adalah operasi pemindahan data umum yang dapat dihilangkan dengan menggunakan tabel yang direplikasi.

Tabel yang direplikasi mungkin tidak menghasilkan performa kueri terbaik saat:

  • Tabel ini sering menyisipkan, memperbarui, dan menghapus operasi. Operasi bahasa manipulasi data (DML) memerlukan pembangunan kembali tabel yang direplikasi. Membangun kembali secara sering dapat menyebabkan performa menjadi lebih lambat.
  • Kumpulan SQL sering diskalakan. Penskalaan kumpulan SQL akan mengubah jumlah simpul Azure Compute, yang menyebabkan tabel yang direplikasi dibangun ulang.
  • Tabel memiliki kolom dalam jumlah besar, tetapi operasi data biasanya hanya mengakses kolom dalam jumlah kecil. Dalam skenario ini, daripada mereplikasi seluruh tabel, mungkin akan lebih efektif untuk mendistribusikan tabel, lalu membuat indeks pada kolom yang sering diakses. Saat kueri memerlukan perpindahan data, kumpulan SQL hanya memindahkan data untuk kolom yang diminta.

Tip

Untuk panduan selengkapnya tentang pengindeksan dan tabel yang direplikasi, lihat lembar Cheat untuk kumpulan SQL khusus (sebelumnya SQL DW) di Azure Synapse Analytics.

Menggunakan tabel yang direplikasi dengan predikat kueri sederhana

Sebelum Anda memilih untuk mendistribusikan atau mereplikasi tabel, pikirkan tentang tipe kueri yang Anda rencanakan untuk dijalankan terhadap tabel. Bila memungkinkan,

  • Gunakan tabel yang direplikasi untuk kueri dengan predikat kueri sederhana, seperti kesetaraan atau ketidaksetaraan.
  • Gunakan tabel terdistribusi untuk kueri dengan predikat kueri kompleks, seperti LIKE atau NOT LIKE.

Kueri intensif CPU memiliki performa terbaik saat pekerjaan didistribusikan ke semua simpul Azure Compute. Misalnya, kueri yang menjalankan komputasi pada setiap baris tabel memiliki performa lebih baik pada tabel terdistribusi daripada tabel yang direplikasi. Karena tabel yang direplikasi disimpan secara penuh pada setiap simpul Azure Compute, kueri intensif CPU terhadap tabel yang direplikasi berjalan terhadap seluruh tabel pada setiap simpul Azure Compute. Komputasi ekstra dapat memperlambat performa kueri.

Misalnya, kueri ini memiliki predikat kompleks. Kueri ini berjalan lebih cepat ketika data berada dalam tabel terdistribusi daripada tabel yang direplikasi. Dalam contoh ini, data dapat didistribusikan round-robin.

SELECT EnglishProductName
FROM DimProduct
WHERE EnglishDescription LIKE '%frame%comfortable%';

Mengonversi tabel round-robin yang ada menjadi tabel yang direplikasi

Jika Anda sudah memiliki tabel round-robin, sebaiknya konversikan tabel tersebut ke tabel yang direplikasi jika tabel tersebut memenuhi kriteria yang diuraikan dalam artikel ini. Tabel yang direplikasi meningkatkan performa pada tabel round-robin karena menghilangkan kebutuhan akan pemindahan data. Tabel round-robin selalu memerlukan pemindahan data untuk bergabung.

Contoh ini menggunakan CTAS untuk mengubah tabel DimSalesTerritory menjadi tabel yang direplikasi. Contoh ini berfungsi terlepas dari apakah DimSalesTerritory didistribusikan hash atau round-robin.

CREATE TABLE [dbo].[DimSalesTerritory_REPLICATE]
WITH
  (
    HEAP,  
    DISTRIBUTION = REPLICATE  
  )  
AS SELECT * FROM [dbo].[DimSalesTerritory]
OPTION  (LABEL  = 'CTAS : DimSalesTerritory_REPLICATE')

-- Switch table names
RENAME OBJECT [dbo].[DimSalesTerritory] to [DimSalesTerritory_old];
RENAME OBJECT [dbo].[DimSalesTerritory_REPLICATE] TO [DimSalesTerritory];

DROP TABLE [dbo].[DimSalesTerritory_old];

Contoh performa kueri untuk round-robin versus direplikasi

Tabel yang direplikasi tidak memerlukan perpindahan data untuk gabungan karena seluruh tabel sudah ada pada setiap simpul Azure Compute. Jika tabel dimensi didistribusikan round-robin, gabungan akan menyalin tabel dimensi secara penuh ke setiap simpul Azure Compute. Untuk memindahkan data, rencana kueri berisi operasi yang disebut BroadcastMoveOperation. Jenis operasi perpindahan data ini akan memperlambat performa kueri dan dihilangkan dengan menggunakan tabel yang direplikasi. Untuk melihat langkah-langkah rencana kueri, gunakan tampilan katalog sistem sys.dm_pdw_request_steps.

Misalnya, dalam kueri berikut terhadap skema AdventureWorks, tabel FactInternetSales didistribusikan oleh hash. Tabel DimDate dan DimSalesTerritory adalah tabel dimensi yang lebih kecil. Kueri ini mengembalikan total penjualan di Amerika Utara untuk tahun fiskal 2004:

SELECT [TotalSalesAmount] = SUM(SalesAmount)
FROM dbo.FactInternetSales s
INNER JOIN dbo.DimDate d
  ON d.DateKey = s.OrderDateKey
INNER JOIN dbo.DimSalesTerritory t
  ON t.SalesTerritoryKey = s.SalesTerritoryKey
WHERE d.FiscalYear = 2004
  AND t.SalesTerritoryGroup = 'North America'

Kita membuat kembali DimDate dan DimSalesTerritory sebagai tabel round-robin. Sebagai hasilnya, kueri memperlihatkan rencana kueri berikut, yang memiliki beberapa operasi pemindahan siaran:

Round-robin query plan

Kita membuat ulang DimDate dan DimSalesTerritory sebagai tabel yang direplikasi, dan menjalankan kueri lagi. Rencana kueri yang dihasilkan jauh lebih pendek dan tidak memiliki perpindahan siaran apa pun.

Replicated query plan

Pertimbangan performa untuk memodifikasi tabel yang direplikasi

Kumpulan SQL mengimplementasikan tabel yang direplikasi dengan mempertahankan versi master tabel. Kumpulan SQL menyalin versi master ke database distribusi pertama pada setiap simpul Azure Compute. Ketika ada perubahan, versi master diperbarui terlebih dahulu, kemudian tabel pada setiap simpul Azure Compute dibangun kembali. Pembangunan kembali tabel yang direplikasi mencakup menyalin tabel ke setiap simpul Azure Compute lalu membuat indeks. Misalnya, tabel yang direplikasi pada DW2000c memiliki lima salinan data. Salinan master dan salinan lengkap pada setiap simpul Azure Compute. Semua data disimpan dalam database distribusi. Kumpulan SQL menggunakan model ini untuk mendukung pernyataan modifikasi data yang lebih cepat dan operasi penskalaan yang fleksibel.

Pembangunan kembali asinkron dipicu oleh kueri pertama terhadap tabel yang direplikasi setelah:

  • Data dimuat atau dimodifikasi
  • Instans SQL Synapse diskalakan ke tingkat yang berbeda
  • Definisi tabel diperbarui

Pembangunan ulang tidak diperlukan setelah:

  • Menjeda operasi
  • Melanjutkan operasi

Pembangunan ulang tidak terjadi segera setelah data dimodifikasi. Sebagai gantinya, pembangunan ulang dipicu kueri memilih dari tabel saat pertama kali. Kueri yang memicu pembangunan ulang langsung dibaca dari versi master tabel saat data disalin secara asinkron ke setiap simpul Azure Compute. Kueri berikutnya akan terus menggunakan versi master tabel hingga penyalinan data selesai. Jika ada aktivitas yang terjadi terhadap tabel yang direplikasi yang memaksa pembangunan kembali lainnya, salinan data tidak valid dan pernyataan pemilihan berikutnya akan memicu penyalinan data kembali.

Menggunakan indeks secara konservatif

Praktik pengindeksan standar berlaku untuk tabel yang direplikasi. Kumpulan SQL membangun kembali setiap indeks tabel yang direplikasi sebagai bagian dari pembangunan kembali. Hanya gunakan indeks ketika peningkatan performa melebihi biaya membangun kembali indeks.

Beban data batch

Saat memuat data ke dalam tabel yang direplikasi, cobalah untuk meminimalkan pembuatan ulang dengan mengelompokkan beban bersama-sama. Lakukan pemuatan beban batch sebelum menjalankan pernyataan select.

Misalnya, pola beban ini memuat data dari empat sumber dan memanggil empat pembangunan kembali.

  • Muat dari sumber 1.
  • Pernyataan select memicu pembangunan ulang 1.
  • Muat dari sumber 2.
  • Pernyataan select memicu pembangunan ulang 2.
  • Muat dari sumber 3.
  • Pernyataan select memicu pembangunan ulang 3.
  • Muat dari sumber 4.
  • Pernyataan select memicu pembangunan ulang 4.

Misalnya, pola beban ini memuat data dari empat sumber dan memanggil empat pembangunan ulang.

  • Muat dari sumber 1.
  • Muat dari sumber 2.
  • Muat dari sumber 3.
  • Muat dari sumber 4.
  • Pernyataan select memicu pembangunan ulang.

Membangun ulang tabel yang direplikasi setelah pemuatan batch

Untuk memastikan waktu eksekusi kueri yang konsisten, pertimbangkan untuk memaksa pembangunan tabel yang direplikasi setelah beban batch. Jika tidak, kueri pertama masih akan menggunakan pemindahan data untuk menyelesaikan kueri.

Operasi 'Build Replicated Table Cache' dapat menjalankan hingga dua operasi secara bersamaan. Misalnya, jika Anda mencoba membangun kembali cache untuk lima tabel, sistem akan menggunakan staticrc20 (yang tidak dapat dimodifikasi) untuk membangun dua tabel secara bersamaan pada saat itu. Oleh karena itu, disarankan untuk menghindari penggunaan tabel besar yang direplikasi melebihi 2 GB, karena ini dapat memperlambat pembangunan kembali cache di seluruh simpul dan meningkatkan waktu keseluruhan.

Kueri ini menggunakan DMV sys.pdw_replicated_table_cache_state untuk mencantumkan tabel yang direplikasi yang telah dimodifikasi, tetapi tidak dibangun ulang.

SELECT SchemaName = SCHEMA_NAME(t.schema_id)
 , [ReplicatedTable] = t.[name]
 , [RebuildStatement] = 'SELECT TOP 1 * FROM ' + '[' + SCHEMA_NAME(t.schema_id) + '].[' + t.[name] +']'
FROM sys.tables t 
JOIN sys.pdw_replicated_table_cache_state c 
  ON c.object_id = t.object_id
JOIN sys.pdw_table_distribution_properties p
  ON p.object_id = t.object_id
WHERE c.[state] = 'NotReady'
AND p.[distribution_policy_desc] = 'REPLICATE'

Untuk memicu pembangunan ulang, jalankan pernyataan berikut pada setiap tabel dalam output sebelumnya.

SELECT TOP 1 * FROM [ReplicatedTable]

Catatan

Jika Anda berencana untuk membangun kembali statistik tabel yang direplikasi yang tidak di-cache, pastikan untuk memperbarui statistik sebelum memicu cache. Memperbarui statistik akan membatalkan cache, sehingga urutannya penting.

Contoh: Mulai dengan UPDATE STATISTICS, lalu picu pembangunan kembali cache. Dalam contoh berikut, sampel yang benar memperbarui statistik lalu memicu pembangunan kembali cache.

-- Incorrect sequence. Ensure that the rebuild operation is the last statement within the batch.
BEGIN
SELECT TOP 1 * FROM [ReplicatedTable]

UPDATE STATISTICS [ReplicatedTable]
END

-- Correct sequence. Ensure that the rebuild operation is the last statement within the batch.
BEGIN
UPDATE STATISTICS [ReplicatedTable]

SELECT TOP 1 * FROM [ReplicatedTable]
END

Untuk memantau proses pembangunan ulang, Anda dapat menggunakan sys.dm_pdw_exec_requests, di mana command akan dimulai dengan 'BuildReplicatedTableCache'. Contohnya:

-- Monitor Build Replicated Cache
SELECT *
FROM sys.dm_pdw_exec_requests
WHERE command like 'BuildReplicatedTableCache%'

Tip

Kueri ukuran tabel dapat digunakan untuk memverifikasi tabel mana yang memiliki kebijakan distribusi yang direplikasi dan yang lebih besar dari 2 GB.

Langkah berikutnya

Untuk membuat tabel yang direplikasi, gunakan salah satu pernyataan berikut:

Untuk gambaran umum tabel terdistribusi, lihat tabel terdistribusi.