Memaksimalkan kualitas grup baris untuk indeks penyimpanan kolom di kumpulan SQL khusus

Artikel
06/01/2023

Kualitas grup baris ditentukan oleh jumlah baris dalam grup baris. Meningkatkan memori yang tersedia dapat memaksimalkan jumlah baris yang dikompresi indeks penyimpan kolom ke dalam setiap grup baris. Gunakan metode ini untuk meningkatkan tingkat kompresi dan performa kueri indeks penyimpan kolom.

Alasan pentngnya ukuran grup baris

Karena indeks penyimpan kolom memindai tabel dengan pemindaian segmen kolom dari setiap grup baris, memaksimalkan jumlah baris di setiap grup baris meningkatkan performa kueri.

Ketika grup baris memiliki jumlah baris yang tinggi, pemadatan data meningkat yang berarti ada lebih sedikit data untuk dibaca dari disk.

Untuk informasi selengkapnya tentang grup baris, lihat Panduan Indeks Penyimpan kolom.

Ukuran target untuk grup baris

Untuk performa kueri terbaik, tujuannya adalah memaksimalkan jumlah baris per grup baris dalam indeks penyimpan kolom. Grup baris dapat memiliki maksimum 1.048.576 baris.

Tidak apa-apa untuk tidak memiliki jumlah baris maksimum per grup baris. Indeks penyimpan kolom mencapai performa yang baik ketika grup baris memiliki setidaknya 100.000 baris.

Grup baris bisa dipangkas selama pemadatan

Selama muat massal atau indeks penyimpan kolom dibangun ulang, terkadang tidak tersedia cukup memori untuk memadatkan semua baris yang ditunjuk untuk setiap grup baris. Ketika ada tekanan memori, indeks penyimpan kolom memangkas ukuran grup baris sehingga pemadatan ke penyimpan kolom dapat berhasil.

Saat memori tidak mencukupi untuk memadatkan setidaknya 10.000 baris ke dalam setiap grup baris, kesalahan akan dihasilkan.

Untuk informasi selengkapnya tentang pemuatan massal, lihat Memuat massal ke dalam indeks penyimpan kolom terkluster.

Cara memantau kualitas grup baris

DMV sys.dm_pdw_nodes_db_column_store_row_group_physical_stats (sys.dm_db_column_store_row_group_physical_stats berisi definisi tampilan yang cocok dengan SQL DB) yang memaparkan informasi berguna seperti jumlah baris dalam grup baris dan alasan pemangkasan apabila terdapat pemangkasan.

Anda bisa membuat tampilan berikut ini sebagai cara yang berguna untuk meminta DMV ini untuk mendapatkan informasi tentang pemangkasan grup baris.

create view dbo.vCS_rg_physical_stats
as
with cte
as
(
select   tb.[name]                    AS [logical_table_name]
,        rg.[row_group_id]            AS [row_group_id]
,        rg.[state]                   AS [state]
,        rg.[state_desc]              AS [state_desc]
,        rg.[total_rows]              AS [total_rows]
,        rg.[trim_reason_desc]        AS trim_reason_desc
,        mp.[physical_name]           AS physical_name
FROM    sys.[schemas] sm
JOIN    sys.[tables] tb               ON  sm.[schema_id]          = tb.[schema_id]
JOIN    sys.[pdw_table_mappings] mp   ON  tb.[object_id]          = mp.[object_id]
JOIN    sys.[pdw_nodes_tables] nt     ON  nt.[name]               = mp.[physical_name]
JOIN    sys.[dm_pdw_nodes_db_column_store_row_group_physical_stats] rg      ON  rg.[object_id]     = nt.[object_id]
                                                                            AND rg.[pdw_node_id]   = nt.[pdw_node_id]
                                        AND rg.[distribution_id]    = nt.[distribution_id]
)
select *
from cte;

Laporan trim_reason_desc menjelaskan apakah grup baris dipangkas (trim_reason_desc = NO_TRIM menyiratkan tidak ada pemangkasan dan grup baris memiliki kualitas optimal). Alasan pemangkasan berikut menunjukkan pemangkasan dini grup baris:

BULKLOAD: Alasan pemangkasan ini digunakan ketika batch baris yang masuk untuk muatan memiliki kurang dari 1 juta baris. Mesin akan membuat grup baris terkompresi jika ada lebih dari 100.000 baris yang disisipkan (dibandingkan dengan menyisipkan ke toko delta) tetapi menetapkan alasan pemangkasan ke BULKLOAD. Dalam skenario ini, pertimbangkan untuk meningkatkan muatan batch Anda untuk menyertakan lebih banyak baris. Selain itu, evaluasi kembali skema partisi Anda untuk memastikan tidak terlalu terperinci karena grup baris tidak dapat mencakup batasan partisi.
MEMORY_LIMITATION: Untuk membuat grup baris dengan 1 juta baris, sejumlah memori kerja diperlukan oleh mesin. Saat memori sesi pemuatan yang tersedia kurang dari memori kerja yang diperlukan, grup baris akan dipangkas sebelum waktunya. Bagian berikut menjelaskan cara memperkirakan memori yang diperlukan dan mengalokasikan lebih banyak memori.
DICTIONARY_SIZE: Alasan pemangkasan ini menunjukkan bahwa pemangkasan grup baris terjadi karena setidaknya ada satu kolom string dengan string kardinalitas lebar dan/atau tinggi. Ukuran kamus dibatasi hingga 16 MB dalam memori dan setelah batasan ini tercapai grup baris dikompresi. Jika Anda mengalami situasi ini, pertimbangkan untuk mengisolasi kolom bermasalah ke dalam tabel terpisah.

Cara memperkirakan persyaratan memori

Untuk melihat perkiraan persyaratan memori guna mengompresi grup baris ukuran maksimum ke dalam indeks penyimpan kolom, pertimbangkan untuk membuat tampilan sampel dbo.vCS_mon_mem_grant. Kueri ini menampilkan ukuran peruntukan memori yang dibutuhkan grup baris untuk kompresi ke penyimpanan kolom.

Memori maksimum yang diperlukan untuk memadatkan satu grup baris kira-kira sebesar

72 MB +
#rows * #columns * 8 byte +
#rows * #short-string-columns * 32 byte +
#long-string-columns * 16 MB untuk kamus kompresi

Catatan

Kolom string pendek menggunakan jenis data string <= 32 byte dan kolom string panjang menggunakan jenis data string > 32 byte.

Untai (karakter) panjang dipadatkan dengan metode pemadatan yang dirancang untuk memadatkan teks. Metode pemadatan ini menggunakan kamus untuk menyimpan pola teks. Ukuran maksimum kamus adalah 16 MB. Hanya ada satu kamus untuk setiap kolom untai panjang di grup baris.

Cara mengurangi persyaratan memori

Gunakan teknik berikut untuk mengurangi persyaratan memori untuk memadatkan grup baris ke dalam indeks penyimpan kolom.

Menggunakan lebih sedikit kolom

Jika memungkinkan, desain tabel dengan lebih sedikit kolom. Saat grup baris dipadatkan ke dalam penyimpan kolom, indeks penyimpan kolom memadatkan setiap segmen kolom secara terpisah.

Oleh karena itu, persyaratan memori untuk memadatkan grup baris meningkat seiring dengan meningkatnya jumlah kolom.

Gunakan lebih sedikit kolom string

Kolom jenis data untai memerlukan lebih banyak memori daripada jenis data numerik dan tanggal. Untuk mengurangi persyaratan memori, pertimbangkan untuk menghapus kolom untai dari tabel fakta dan meletakkannya dalam tabel dimensi yang lebih kecil.

Persyaratan memori tambahan untuk pemadatan untai:

Jenis data untai hingga 32 karakter dapat memerlukan 32 byte tambahan per nilai.
Tipe data untai dengan lebih dari 32 karakter dikompresi menggunakan metode kamus. Setiap kolom dalam grup baris dapat memerlukan hingga 16 MB tambahan untuk menyusun kamus.

Hindari partisi berlebihan

Indeks penyimpan kolom membuat satu atau beberapa grup baris per partisi. Untuk kumpulan SQL khusus di Azure Synapse Analytics, jumlah partisi yang bertambah dengan cepat karena data didistribusikan dan setiap distribusi dipartisi.

Jika tabel memiliki terlalu banyak partisi, mungkin tidak ada cukup baris untuk mengisi grup baris. Kurangnya baris tidak menciptakan tekanan memori selama pemadatan. Akan tetapi, hal tersebut mengarah ke grup baris yang tidak mencapai performa kueri penyimpan kolom terbaik.

Alasan lain untuk menghindari partisi berlebihan adalah terdapat overhead memori untuk memuat baris ke dalam indeks penyimpan kolom pada tabel yang dipartisi.

Selama pemuatan, banyak partisi dapat menerima baris yang masuk, yang ditahan di memori sampai setiap partisi memiliki baris yang cukup untuk dikompresi. Memiliki terlalu banyak partisi menciptakan tekanan memori tambahan.

Menyederhanakan kueri pemuatan

Database berbagi memori yang diberikan untuk kueri di antara semua operator dalam kueri. Saat kueri muatan memiliki pengurutan dan gabungan yang kompleks, memori yang tersedia untuk pemadatan berkurang.

Rancang kueri muat untuk fokus hanya pada pemuatan kueri. Jika Anda perlu menjalankan transformasi pada data, jalankan terpisah dari kueri muat. Misalnya, tahap data dalam tabel tumpukan, jalankan transformasi, lalu muat tabel pementasan ke dalam indeks penyimpan kolom.

Tip

Anda juga dapat memuat data terlebih dahulu lalu gunakan sistem MPP untuk mengubah data.

Sesuaikan MAXDOP

Setiap distribusi memadatkan grup baris ke dalam penyimpan kolom secara paralel ketika ada lebih dari satu inti CPU yang tersedia per distribusi.

Paralelisme membutuhkan sumber daya memori tambahan, yang dapat menyebabkan tekanan memori dan pemangkasan baris grup.

Untuk mengurangi tekanan memori, Anda dapat menggunakan petunjuk kueri MAXDOP untuk memaksa operasi pemuatan berjalan dalam mode serial dalam setiap distribusi.

CREATE TABLE MyFactSalesQuota
WITH (DISTRIBUTION = ROUND_ROBIN)
AS SELECT * FROM FactSalesQuota
OPTION (MAXDOP 1);

Cara mengalokasikan lebih banyak memori

Ukuran DWU dan kelas sumber daya pengguna bersama-sama menentukan berapa banyak memori yang tersedia untuk kueri pengguna.

Untuk meningkatkan pemberian memori untuk kueri muatan, Anda dapat meningkatkan jumlah DWUs atau meningkatkan kelas sumber daya.

Untuk meningkatkan DWUs, lihat Bagaimana cara menskalakan performa?
Untuk mengubah kelas sumber daya untuk kueri, lihat Mengubah contoh kelas sumber daya pengguna.

Langkah berikutnya

Untuk menemukan cara lain untuk meningkatkan kinerja untuk kumpulan SQL khusus, lihat Gambaran umum performa.

Bagikan melalui