Indeks pada tabel kumpulan SQL khusus di Azure Synapse Analytics

Tip

Microsoft Fabric Data Warehouse adalah gudang relasional skala perusahaan pada fondasi data lake, dengan arsitektur siap masa depan, AI bawaan, dan fitur baru. Jika Anda baru menggunakan pergudangan data, mulailah dengan Fabric Data Warehouse. Beban kerja kumpulan SQL terdedikasi yang ada dapat ditingkatkan ke Fabric untuk mengakses kemampuan baru di seluruh ilmu data, analitik waktu nyata, dan pelaporan.

• Mulai uji coba gratis Fabric.
• Migration Assistant untuk Fabric Data Warehouse.

Rekomendasi dan contoh untuk mengindeks tabel di kumpulan SQL khusus di Azure Synapse Analytics.

Jenis indeks

Kumpulan SQL khusus menawarkan beberapa opsi pengindeksan termasuk indeks penyimpan kolom berkluster, indeks berkluster dan indeks non-kluster, dan opsi nonindex yang juga dikenal sebagai heap.

Untuk membuat tabel dengan indeks, lihat dokumentasi CREATE TABLE (kumpulan SQL khusus).

Indeks penyimpan kolom berkluster

Secara default, kumpulan SQL khusus membuat indeks penyimpan kolom berkluster ketika tidak ada opsi indeks yang ditentukan pada tabel. Tabel penyimpan kolom berkluster menawarkan tingkat kompresi data tertinggi dan performa kueri keseluruhan terbaik. Tabel penyimpan kolom berkluster umumnya akan mengungguli indeks berkluster atau tabel timbunan dan biasanya merupakan pilihan terbaik untuk tabel besar. Untuk alasan ini, penyimpan kolom berkluster adalah tempat terbaik untuk memulai ketika Anda tidak yakin tentang cara mengindeks tabel Anda.

Untuk membuat tabel penyimpan kolom berkluster, tentukan CLUSTERED COLUMNSTORE INDEX dalam klausa WITH, atau biarkan klausa WITH mati:

CREATE TABLE myTable
  (  
    id int NOT NULL,  
    lastName varchar(20),  
    zipCode varchar(6)  
  )  
WITH ( CLUSTERED COLUMNSTORE INDEX );

Ada beberapa skenario di mana penyimpan kolom berkluster mungkin bukan opsi yang baik:

• Tabel penyimpan kolom tidak mendukung varchar(max), nvarchar(max), dan varbinary(max). Pertimbangkan timbunan atau indeks berkluster sebagai gantinya.
• Tabel penyimpan kolom bisa kurang efisien untuk data sementara. Pertimbangkan heap dan tabel sementara.
• Tabel kecil dengan kurang dari 60 juta baris. Pertimbangkan tabel tumpuk.

Tabel Heap

Saat Anda mendaratkan data untuk sementara di kumpulan SQL khusus, Anda dapat menemukan bahwa menggunakan tabel timbunan membuat proses keseluruhan lebih cepat. Ini karena pemindahan ke heap lebih cepat daripada ke tabel indeks dan dalam beberapa kasus pembacaan berikutnya dapat dilakukan dari cache. Jika Anda memuat data hanya untuk tahap sebelum menjalankan lebih banyak transformasi, memuat tabel ke tabel timbunan jauh lebih cepat daripada memuat data ke tabel penyimpan kolom berkluster. Selain itu, memuat data ke tabel sementara dimuat lebih cepat daripada memuat tabel ke penyimpanan permanen. Setelah pemuatan data, Anda dapat membuat indeks dalam tabel untuk performa kueri yang lebih cepat.

Tabel penyimpan kolom kluster mulai mencapai kompresi optimal setelah ada lebih dari 60 juta baris. Untuk tabel pencarian kecil, kurang dari 60 juta baris, pertimbangkan untuk menggunakan HEAP atau indeks berkluster untuk performa kueri yang lebih cepat.

Untuk membuat tabel heap, tentukan HEAP dalam klausa WITH:

CREATE TABLE myTable
  (  
    id int NOT NULL,  
    lastName varchar(20),  
    zipCode varchar(6)  
  )  
WITH ( HEAP );

Catatan

Jika Anda sering melakukan operasi INSERT, UPDATE, atau DELETE pada tabel tumpukan, disarankan untuk menyertakan rekonstruksi tabel dalam jadwal pemeliharaan Anda dengan menggunakan perintah ALTER TABLE. Contohnya, ALTER TABLE [SchemaName].[TableName] REBUILD. Praktik ini berkontribusi pada pengurangan fragmentasi, yang mengakibatkan peningkatan performa selama operasi baca.

Indeks berkluster dan nonkluster

Indeks berkluster dapat mengungguli tabel penyimpan kolom berkluster ketika satu baris perlu diambil dengan cepat. Untuk kueri di mana pencarian satu atau sangat sedikit baris diperlukan untuk dilakukan dengan kecepatan ekstrem, pertimbangkan indeks bercluster atau indeks sekunder noncluster. Kerugian dari menggunakan indeks berkluster adalah bahwa hanya kueri yang menggunakan filter sangat selektif pada kolom indeks berkluster yang akan mendapatkan manfaat. Untuk meningkatkan filter pada kolom lain, indeks non-kluster dapat ditambahkan ke kolom lain. Namun, setiap indeks yang ditambahkan ke tabel menambahkan ruang dan waktu pemrosesan pada saat pemuatan.

Untuk membuat tabel indeks berkluster, tentukan CLUSTERED INDEX dalam klausa WITH:

CREATE TABLE myTable
  (  
    id int NOT NULL,  
    lastName varchar(20),  
    zipCode varchar(6)  
  )  
WITH ( CLUSTERED INDEX (id) );

Untuk menambahkan indeks nonclustered pada tabel, gunakan sintaks berikut:

CREATE INDEX zipCodeIndex ON myTable (zipCode);

Mengoptimalkan indeks penyimpan kolom berkluster

Tabel penyimpan kolom berkluster menata data ke dalam segmen. Memiliki kualitas segmen yang tinggi sangat penting untuk mencapai performa kueri yang optimal pada tabel penyimpanan kolom. Kualitas segmen dapat diukur dengan jumlah baris dalam grup baris terkompresi. Kualitas segmen paling optimal tercapai ketika setidaknya ada 100.000 baris per grup baris terkompresi. Performa akan meningkat seiring jumlah baris tiap grup baris mendekati 1.048.576 baris, yang merupakan jumlah maksimum baris yang dapat dimuat oleh sebuah grup baris.

Tampilan di bawah ini dapat dibuat dan digunakan pada sistem Anda untuk menghitung baris rata-rata per grup baris dan mengidentifikasi indeks penyimpan kolom kluster suboptimal. Kolom terakhir pada tampilan ini menghasilkan pernyataan SQL yang dapat digunakan untuk membangun kembali indeks Anda.

CREATE VIEW dbo.vColumnstoreDensity
AS
SELECT
        GETDATE()                                                               AS [execution_date]
,       DB_Name()                                                               AS [database_name]
,       s.name                                                                  AS [schema_name]
,       t.name                                                                  AS [table_name]
,       MAX(p.partition_number)                                                 AS [table_partition_count]
,       SUM(rg.[total_rows])                                                    AS [row_count_total]
,       SUM(rg.[total_rows])/COUNT(DISTINCT rg.[distribution_id])               AS [row_count_per_distribution_MAX]
,       CEILING((SUM(rg.[total_rows])*1.0/COUNT(DISTINCT rg.[distribution_id]))/1048576) AS [rowgroup_per_distribution_MAX]
,       SUM(CASE WHEN rg.[State] = 0 THEN 1                   ELSE 0    END)    AS [INVISIBLE_rowgroup_count]
,       SUM(CASE WHEN rg.[State] = 0 THEN rg.[total_rows]     ELSE 0    END)    AS [INVISIBLE_rowgroup_rows]
,       MIN(CASE WHEN rg.[State] = 0 THEN rg.[total_rows]     ELSE NULL END)    AS [INVISIBLE_rowgroup_rows_MIN]
,       MAX(CASE WHEN rg.[State] = 0 THEN rg.[total_rows]     ELSE NULL END)    AS [INVISIBLE_rowgroup_rows_MAX]
,       AVG(CASE WHEN rg.[State] = 0 THEN rg.[total_rows]     ELSE NULL END)    AS [INVISIBLE_rowgroup_rows_AVG]
,       SUM(CASE WHEN rg.[State] = 1 THEN 1                   ELSE 0    END)    AS [OPEN_rowgroup_count]
,       SUM(CASE WHEN rg.[State] = 1 THEN rg.[total_rows]     ELSE 0    END)    AS [OPEN_rowgroup_rows]
,       MIN(CASE WHEN rg.[State] = 1 THEN rg.[total_rows]     ELSE NULL END)    AS [OPEN_rowgroup_rows_MIN]
,       MAX(CASE WHEN rg.[State] = 1 THEN rg.[total_rows]     ELSE NULL END)    AS [OPEN_rowgroup_rows_MAX]
,       AVG(CASE WHEN rg.[State] = 1 THEN rg.[total_rows]     ELSE NULL END)    AS [OPEN_rowgroup_rows_AVG]
,       SUM(CASE WHEN rg.[State] = 2 THEN 1                   ELSE 0    END)    AS [CLOSED_rowgroup_count]
,       SUM(CASE WHEN rg.[State] = 2 THEN rg.[total_rows]     ELSE 0    END)    AS [CLOSED_rowgroup_rows]
,       MIN(CASE WHEN rg.[State] = 2 THEN rg.[total_rows]     ELSE NULL END)    AS [CLOSED_rowgroup_rows_MIN]
,       MAX(CASE WHEN rg.[State] = 2 THEN rg.[total_rows]     ELSE NULL END)    AS [CLOSED_rowgroup_rows_MAX]
,       AVG(CASE WHEN rg.[State] = 2 THEN rg.[total_rows]     ELSE NULL END)    AS [CLOSED_rowgroup_rows_AVG]
,       SUM(CASE WHEN rg.[State] = 3 THEN 1                   ELSE 0    END)    AS [COMPRESSED_rowgroup_count]
,       SUM(CASE WHEN rg.[State] = 3 THEN rg.[total_rows]     ELSE 0    END)    AS [COMPRESSED_rowgroup_rows]
,       SUM(CASE WHEN rg.[State] = 3 THEN rg.[deleted_rows]   ELSE 0    END)    AS [COMPRESSED_rowgroup_rows_DELETED]
,       MIN(CASE WHEN rg.[State] = 3 THEN rg.[total_rows]     ELSE NULL END)    AS [COMPRESSED_rowgroup_rows_MIN]
,       MAX(CASE WHEN rg.[State] = 3 THEN rg.[total_rows]     ELSE NULL END)    AS [COMPRESSED_rowgroup_rows_MAX]
,       AVG(CASE WHEN rg.[State] = 3 THEN rg.[total_rows]     ELSE NULL END)    AS [COMPRESSED_rowgroup_rows_AVG]
,       'ALTER INDEX ALL ON ' + s.name + '.' + t.NAME + ' REBUILD;'             AS [Rebuild_Index_SQL]
FROM    sys.[dm_pdw_nodes_db_column_store_row_group_physical_stats] rg
JOIN    sys.[pdw_nodes_tables] nt                   ON  rg.[object_id]          = nt.[object_id]
                                                    AND rg.[pdw_node_id]        = nt.[pdw_node_id]
                                                    AND rg.[distribution_id]    = nt.[distribution_id]
JOIN    sys.[pdw_permanent_table_mappings] mp                 ON  nt.[name]               = mp.[physical_name]
JOIN    sys.[tables] t                              ON  mp.[object_id]  = t.[object_id]
JOIN    sys.[schemas] s                             ON t.[schema_id]    = s.[schema_id]
JOIN    sys.[partitions] p                          ON P.object_id      = t.object_id
GROUP BY
        s.[name]
,       t.[name];

Sekarang setelah Anda membuat tampilan, jalankan kueri ini untuk mengidentifikasi tabel dengan grup baris dengan kurang dari 100-K baris. Anda mungkin ingin meningkatkan ambang batas 100 K jika Anda mencari kualitas segmen yang lebih optimal.

SELECT    *
FROM    [dbo].[vColumnstoreDensity]
WHERE    COMPRESSED_rowgroup_rows_AVG < 100000
        OR INVISIBLE_rowgroup_rows_AVG < 100000;

Setelah menjalankan kueri, Anda bisa mulai melihat data dan menganalisis hasil Anda. Tabel ini menjelaskan apa yang harus dicari dalam analisis grup baris Anda.

Kolom	Cara menggunakan data ini
[table_partition_count]	Jika tabel dipartisi, maka Anda dapat mengharapkan jumlah grup baris terbuka yang lebih tinggi. Setiap partisi dalam distribusi dapat secara teori memiliki grup baris terbuka yang terkait dengannya. Faktorkan ini ke dalam analisis Anda. Tabel kecil yang telah dipartisi dapat dioptimalkan dengan menghapus partisi sama sekali karena ini akan meningkatkan kompresi.
[row_count_total]	Jumlah baris total untuk tabel. Misalnya, Anda dapat menggunakan nilai ini untuk menghitung persentase baris dalam status terkompresi.
[row_count_per_distribution_MAX]	Jika semua baris didistribusikan secara merata, nilai ini akan menjadi jumlah target baris per distribusi. Bandingkan nilai ini dengan compressed_rowgroup_count.
[COMPRESSED_rowgroup_rows]	Jumlah total baris dalam format penyimpan kolom untuk tabel.
[COMPRESSED_rowgroup_rows_AVG]	Jika jumlah rata-rata baris secara signifikan kurang dari # maksimum baris untuk grup baris, maka pertimbangkan untuk menggunakan CTAS atau ALTER INDEX REBUILD untuk mengkompresi ulang data
[COMPRESSED_rowgroup_count]	Jumlah grup baris dalam format penyimpanan kolom. Jika angka ini sangat tinggi dibandingkan dengan tabel, itu adalah indikator bahwa kepadatan columnstore rendah.
[COMPRESSED_rowgroup_rows_DELETED]	Baris dihapus secara logis dalam format penyimpan kolom. Jika angka relatif tinggi terhadap ukuran tabel, pertimbangkan untuk membuat ulang partisi atau membangun kembali indeks karena langkah ini akan menghapusnya secara fisik.
[TERKOMPRESI_kelompok_baris_MIN]	Gunakan ini dengan kolom AVG dan MAX untuk memahami jangkauan nilai untuk kelompok baris di kolomstore Anda. Angka rendah di atas ambang beban (102.400 per distribusi sejajar partisi) menunjukkan bahwa pengoptimalan tersedia dalam beban data
[COMPRESSED_rowgroup_rows_MAX]	Seperti di atas
[OPEN_rowgroup_count]	Kelompok baris terbuka normal. Seseorang dapat secara wajar mengharapkan satu grup baris TERBUKA untuk setiap distribusi tabel (60). Angka yang berlebihan menyarankan pemuatan data di seluruh partisi. Periksa ulang strategi partisi untuk memastikan itu sudah tepat
[OPEN_rowgroup_rows]	Setiap grup baris dapat memiliki 1.048.576 baris di dalamnya sebagai maksimum. Gunakan nilai ini untuk melihat seberapa penuh grup baris yang terbuka saat ini
[OPEN_rowgroup_rows_MIN]	Grup terbuka menunjukkan bahwa data sedang dimuat secara bertahap ke dalam tabel atau bahwa muatan sebelumnya meluap ke baris yang tersisa dalam grup baris ini. Gunakan kolom MIN, MAX, AVG untuk melihat berapa banyak data yang ada di grup baris OPEN. Untuk tabel kecil, dapat mencapai 100% dari semua data! Dalam hal ini ALTER INDEX REBUILD untuk memaksa data masuk ke columnstore.
[OPEN_rowgroup_rows_MAX]	Seperti di atas
[OPEN_rowgroup_rows_AVG]	Seperti di atas
[CLOSED_rowgroup_rows]	Lihat baris grup yang tertutup sebagai pemeriksaan.
[CLOSED_rowgroup_count]	Jumlah grup baris tertutup sebaiknya rendah, jika memang ada yang terlihat. Grup baris tertutup dapat dikonversi ke grup baris terkompresi menggunakan ALTER INDEX ... PERINTAH REORGANIZE. Namun, ini biasanya tidak diperlukan. Grup tertutup secara otomatis dikonversi ke grup baris penyimpan kolom oleh proses "penggerak tuple" latar belakang.
[CLOSED_rowgroup_rows_MIN]	Grup baris tertutup harus memiliki tingkat pengisian yang sangat tinggi. Jika tingkat pengisian untuk grup baris tertutup rendah, maka analisis lebih lanjut dari penyimpan kolom diperlukan.
[CLOSED_rowgroup_rows_MAX]	Seperti di atas
[CLOSED_rowgroup_rows_AVG]	Seperti di atas
[Rebuild_Index_SQL]	SQL untuk membangun kembali indeks penyimpan kolom untuk tabel

Dampak pemeliharaan indeks

Kolom Rebuild_Index_SQL dalam vColumnstoreDensity view berisi perintah ALTER INDEX REBUILD yang dapat digunakan untuk mengembalikan indeks Anda. Saat membangun kembali indeks Anda, pastikan Anda mengalokasikan memori yang cukup untuk sesi yang membangun kembali indeks Anda. Untuk melakukan ini, tingkatkan kelas sumber daya pengguna yang memiliki izin untuk membangun kembali indeks pada tabel ini ke minimum yang disarankan. Misalnya, lihat Membangun ulang indeks untuk meningkatkan kualitas segmen nanti di artikel ini.

Untuk tabel dengan indeks penyimpan kolom berkluster yang diurutkan, ALTER INDEX REBUILD akan mengurutkan ulang data menggunakan tempdb. Pantau tempdb selama operasi pembangunan ulang. Jika Anda membutuhkan lebih banyak ruang tempdb, tingkatkan kumpulan database. Turunkan skala setelah pembangunan ulang indeks selesai.

Untuk tabel dengan indeks penyimpan kolom berkluster yang diurutkan, ALTER INDEX REORGANIZE tidak mengurutkan ulang data. Untuk mengurutkan ulang data, gunakan ALTER INDEX REBUILD.

Untuk informasi selengkapnya tentang indeks penyimpan kolom berkluster yang diurutkan, lihat Penyetelan performa dengan indeks penyimpan kolom berkluster yang diurutkan.

Penyebab kualitas indeks penyimpan kolom yang buruk

Jika Anda telah mengidentifikasi tabel dengan kualitas segmen yang buruk, Anda ingin mengidentifikasi akar penyebabnya. Di bawah ini adalah beberapa penyebab umum lain dari kualitas segmen yang buruk:

1. Tekanan memori saat indeks dibangun
2. Volume operasi DML yang tinggi
3. Operasi beban kecil atau beban netes
4. Terlalu banyak partisi

Faktor-faktor ini dapat menyebabkan indeks penyimpan kolom memiliki kurang dari optimal 1 juta baris per grup baris. Mereka juga dapat menyebabkan baris masuk ke kelompok baris delta alih-alih kelompok baris terkompresi.

Catatan

Tabel penyimpan kolom biasanya tidak mendorong data ke segmen penyimpan kolom terkompresi hingga ada lebih dari 1 juta baris per tabel. Jika tabel dengan indeks penyimpan kolom berkluster memiliki banyak grup baris terbuka dengan jumlah baris total yang tidak memenuhi ambang untuk pemadatan (1 juta baris), grup baris ini akan tetap terbuka dan disimpan sebagai data baris. Akibatnya, ini akan meningkatkan ukuran database distribusi karena tidak dikompresi. Selain itu, grup baris terbuka ini tidak akan mendapat manfaat dari CCI dan akan membutuhkan lebih banyak sumber daya untuk dipertahankan. Mungkin disarankan untuk menggunakan ALTER INDEX REORGANIZE.

Tekanan memori saat indeks dibangun

Jumlah baris per grup baris terkompresi terkait langsung dengan lebar baris dan jumlah memori yang tersedia untuk memproses grup baris. Ketika baris ditulis ke tabel kolom di bawah tekanan memori, kualitas segmen kolom dapat terpengaruh. Oleh karena itu, praktik terbaik adalah memberikan sesi yang menulis ke tabel indeks penyimpan kolom Anda akses ke memori sebanyak mungkin. Karena ada trade-off antara memori dan konkurensi, panduan tentang alokasi memori yang tepat tergantung pada data di setiap baris tabel Anda, unit gudang data yang dialokasikan untuk sistem Anda, dan jumlah slot konkurensi yang dapat Anda berikan ke sesi yang menulis data ke tabel Anda.

Volume operasi DML yang tinggi

Volume tinggi operasi DML yang memperbarui dan menghapus baris dapat memperkenalkan inefisiensi ke dalam penyimpanan kolom. Ini terutama berlaku ketika sebagian besar baris dalam grup baris dimodifikasi.

• Menghapus baris dari grup baris terkompresi hanya menandai baris secara logis sebagai dihapus. Baris tetap berada dalam grup baris terkompresi hingga partisi atau tabel dibangun kembali.
• Menyisipkan baris menambahkan baris ke tabel rowstore internal yang disebut grup baris delta. Baris yang disisipkan tidak dikonversi ke columnstore hingga grup baris delta penuh dan ditandai sebagai tertutup. Grup baris ditutup setelah mencapai kapasitas maksimum 1.048.576 baris.
• Memperbarui baris dalam format penyimpan kolom diproses sebagai penghapusan logis lalu disisipkan. Baris yang dimasukkan dapat disimpan di dalam penyimpanan delta.

Operasi pembaruan dan penyisipan batch yang melebihi batas massal 102.400 baris per distribusi yang diselaraskan dengan partisi langsung dialihkan ke format penyimpanan kolom. Namun, dengan asumsi distribusi yang merata, Anda harus memodifikasi lebih dari 6,144 juta baris dalam satu operasi agar hal ini terjadi. Jika jumlah baris untuk distribusi sejajar partisi yang diberikan kurang dari 102.400, baris masuk ke penyimpanan delta dan tetap di sana sampai baris yang memadai telah dimasukkan atau dimodifikasi untuk menutup grup baris atau indeks telah dibangun kembali.

Operasi beban kecil atau beban netes

Beban kecil yang mengalir ke kumpulan SQL khusus terkadang juga disebut sebagai beban menetes. Mereka biasanya mewakili aliran data yang hampir konstan diserap oleh sistem. Namun, karena aliran ini hampir terus menerus volume baris tidak terlalu besar. Seringkali, data berada secara signifikan di bawah ambang batas yang dibutuhkan untuk pemindahan langsung ke format kolomstore.

Dalam situasi ini, seringkali lebih baik untuk mendaratkan data terlebih dahulu di penyimpanan blob Azure dan membiarkannya menumpuk sebelum memuat. Teknik ini sering dikenal sebagai batching mikro.

Terlalu banyak partisi

Hal lain yang perlu dipertimbangkan adalah dampak partisi pada tabel kolom stok berkluster Anda. Sebelum mempartisi, kumpulan SQL khusus sudah membagi data Anda menjadi 60 database. Pemartisian selanjutnya membagi data Anda. Jika Anda mempartisi data, pertimbangkan bahwa setiap partisi membutuhkan setidaknya 1 juta baris untuk mendapatkan manfaat dari indeks penyimpan kolom berkluster. Jika Anda mempartisi tabel Anda menjadi 100 partisi, maka tabel Anda membutuhkan setidaknya 6 miliar baris untuk mendapatkan manfaat dari indeks penyimpan kolom berkluster (60 distribusi 100 partisi 1 juta baris). Jika tabel 100 partisi Anda tidak memiliki 6 miliar baris, kurangi jumlah partisi atau pertimbangkan untuk menggunakan tabel tumpukan sebagai gantinya.

Setelah tabel Anda dimuat dengan beberapa data, ikuti langkah-langkah di bawah ini untuk mengidentifikasi dan membangun kembali tabel dengan indeks penyimpan kolom berkluster sub-optimal.

Membangun kembali indeks untuk meningkatkan kualitas segmen

Langkah 1: Mengidentifikasi atau membuat pengguna yang menggunakan kelas sumber daya yang tepat

Salah satu cara cepat untuk segera meningkatkan kualitas segmen adalah dengan membangun kembali indeks. SQL yang dikembalikan oleh tampilan di atas berisi pernyataan ALTER INDEX REBUILD, yang dapat digunakan untuk membangun kembali indeks Anda. Saat membangun kembali indeks Anda, pastikan Anda mengalokasikan memori yang cukup untuk sesi yang membangun kembali indeks Anda. Untuk melakukan ini, tingkatkan kelas sumber daya pengguna yang memiliki izin untuk membangun kembali indeks pada tabel ini ke minimum yang disarankan.

Di bawah ini adalah contoh cara mengalokasikan lebih banyak memori kepada pengguna dengan meningkatkan kelas sumber daya mereka. Untuk bekerja dengan kelas sumber daya, lihat Kelas sumber daya untuk manajemen beban kerja.

EXEC sp_addrolemember 'xlargerc', 'LoadUser';

Langkah 2: Membangun kembali indeks penyimpan kolom berkluster dengan pengguna kelas sumber daya yang lebih tinggi

Masuk sebagai pengguna dari langkah 1 (LoadUser), yang sekarang menggunakan kelas sumber daya yang lebih tinggi, dan jalankan pernyataan ALTER INDEX. Pastikan bahwa pengguna ini memiliki izin UBAH ke tabel tempat indeks sedang dibangun kembali. Contoh-contoh ini menunjukkan cara membangun kembali seluruh indeks penyimpan kolom atau cara membangun kembali satu partisi. Pada tabel besar, lebih praktis untuk membangun kembali indeks satu partisi pada satu waktu.

Atau, alih-alih membangun ulang indeks, Anda dapat menyalin tabel ke tabel baru menggunakan CTAS. Jalan mana yang terbaik? Untuk data dalam volume besar, CTAS biasanya lebih cepat daripada ALTER INDEX. Untuk volume data yang lebih kecil, ALTER INDEX lebih mudah digunakan dan tidak akan mengharuskan Anda untuk menukar tabel.

-- Rebuild the entire clustered index
ALTER INDEX ALL ON [dbo].[DimProduct] REBUILD;

-- Rebuild a single partition
ALTER INDEX ALL ON [dbo].[FactInternetSales] REBUILD Partition = 5;

-- Rebuild a single partition with archival compression
ALTER INDEX ALL ON [dbo].[FactInternetSales] REBUILD Partition = 5 WITH (DATA_COMPRESSION = COLUMNSTORE_ARCHIVE);

-- Rebuild a single partition with columnstore compression
ALTER INDEX ALL ON [dbo].[FactInternetSales] REBUILD Partition = 5 WITH (DATA_COMPRESSION = COLUMNSTORE);

Membangun kembali indeks di kumpulan SQL khusus adalah operasi offline. Untuk informasi selengkapnya tentang membangun ulang indeks, lihat bagian ALTER INDEX REBUILD di Columnstore Indexes Defragmentation, dan ALTER INDEX.

Langkah 3: Verifikasi kualitas segmen penyimpanan kolom berkluster telah meningkat

Jalankan ulang kueri yang mengidentifikasi tabel dengan kualitas segmen yang buruk dan verifikasi kualitas segmen telah meningkat. Jika kualitas segmen tidak meningkat, bisa jadi baris dalam tabel Anda terlalu lebar. Pertimbangkan untuk menggunakan kelas sumber daya atau DWU yang lebih tinggi saat membangun kembali indeks Anda.

Membangun ulang indeks dengan CTAS dan pengalihan partisi

Contoh ini menggunakan pernyataan CREATE TABLE AS SELECT (CTAS) dan pengalihan partisi untuk membangun kembali partisi tabel.

-- Step 1: Select the partition of data and write it out to a new table using CTAS
CREATE TABLE [dbo].[FactInternetSales_20000101_20010101]
    WITH    (   DISTRIBUTION = HASH([ProductKey])
            ,   CLUSTERED COLUMNSTORE INDEX
            ,   PARTITION   (   [OrderDateKey] RANGE RIGHT FOR VALUES
                                (20000101,20010101
                                )
                            )
            )
AS
SELECT  *
FROM    [dbo].[FactInternetSales]
WHERE   [OrderDateKey] >= 20000101
AND     [OrderDateKey] <  20010101
;

-- Step 2: Switch IN the rebuilt data with TRUNCATE_TARGET option
ALTER TABLE [dbo].[FactInternetSales_20000101_20010101] SWITCH PARTITION 2 TO  [dbo].[FactInternetSales] PARTITION 2 WITH (TRUNCATE_TARGET = ON);

Untuk informasi selengkapnya tentang membuat ulang partisi menggunakan CTAS, lihat Menggunakan partisi di kumpulan SQL khusus.

Konten terkait

Untuk informasi selengkapnya tentang mengembangkan tabel, lihat Mengembangkan tabel.