Optimasi kinerja dengan indeks penyimpan kolom yang diurutkan

Berlaku untuk: SQL Server 2022 (16.x) dan versi yang lebih baru Azure SQL DatabaseAzure SQL Managed Instancebasis data SQL di Microsoft Fabric

Indeks penyimpan kolom yang diurutkan dapat memberikan performa yang lebih cepat dengan melompati sejumlah besar data yang diurutkan yang tidak cocok dengan predikat kueri. Saat memuat data ke dalam indeks penyimpan kolom yang diurutkan dan mempertahankan urutan melalui pembangunan ulang indeks membutuhkan waktu lebih lama daripada dalam indeks yang tidak diurutkan, kueri terindeks dapat berjalan lebih cepat dengan penyimpan kolom yang diurutkan.

Saat kueri membaca indeks penyimpan kolom, Mesin Database memeriksa nilai minimum dan maksimum yang disimpan di setiap segmen kolom. Proses ini menghilangkan segmen yang berada di luar batas predikat kueri. Dengan kata lain, ia melompati segmen-segmen ini saat membaca data dari disk atau memori. Kueri selesai lebih cepat jika jumlah segmen yang akan dibaca dan ukuran totalnya secara signifikan lebih kecil.

Dengan pola pemuatan data tertentu, data dalam indeks penyimpan kolom mungkin diurutkan secara implisit tanpa menentukan ORDER klausa. Misalnya, jika pemuatan data terjadi setiap hari, maka data mungkin diurutkan berdasarkan load_date kolom. Dalam hal ini, performa kueri sudah bisa mendapat manfaat dari urutan implisit ini. Mengurutkan indeks penyimpan kolom dengan kolom yang sama load_date secara eksplisit pada klausul ORDER tidak mungkin memberikan manfaat performa tambahan.

Untuk ketersediaan indeks penyimpan kolom yang diurutkan di berbagai platform SQL dan versi SQL Server, lihat Ketersediaan indeks penyimpan kolom yang diurutkan.

Untuk informasi selengkapnya tentang fitur yang baru ditambahkan untuk indeks penyimpan kolom, lihat Apa yang baru dalam indeks penyimpan kolom.

Indeks penyimpan kolom yang diurutkan vs. tidak diurutkan

Dalam indeks penyimpan kolom, data di setiap kolom setiap grup baris dikompresi ke dalam segmen terpisah. Setiap segmen berisi metadata yang menjelaskan nilai minimum dan maksimumnya, sehingga proses eksekusi kueri dapat melewati segmen yang berada di luar batas predikat kueri.

Saat indeks penyimpan kolom tidak diurutkan, penyusun indeks tidak mengurutkan data sebelum mengompresinya ke dalam segmen. Itu berarti bahwa segmen dengan rentang nilai yang tumpang tindih dapat terjadi, menyebabkan kueri membaca lebih banyak segmen untuk mendapatkan data yang diperlukan. Akibatnya, kueri dapat memakan waktu lebih lama untuk diselesaikan.

Saat Anda membuat indeks kolom yang diurutkan dengan menentukan ORDER klausul dalam pernyataan CREATE COLUMNSTORE INDEX, Mesin Database mengurutkan data di setiap segmen dari setiap kolom urutan sebelum pembangun indeks mengompresi data ke dalam segmen. Dengan data yang diurutkan, tumpang tindih segmen dikurangi atau dihilangkan, memungkinkan kueri menggunakan eliminasi segmen yang lebih efisien dan dengan demikian performa yang lebih cepat karena ada lebih sedikit segmen dan lebih sedikit data untuk dibaca.

Mengurangi tumpang tindih segmen dan meningkatkan performa kueri

Saat Anda membangun indeks penyimpan kolom yang diurutkan, Mesin Database mengurutkan data berdasarkan upaya terbaik. Tergantung pada memori yang tersedia, ukuran data, tingkat paralelisme, jenis indeks (berkluster vs. non-kluster), dan jenis build indeks (offline vs. online), urutan dalam kolom dalam indeks penyimpan kolom mungkin penuh tanpa tumpang tindih segmen, atau parsial dengan beberapa tumpang tindih segmen. Ketika ada lebih sedikit segmen yang tumpang tindih, kueri yang dapat memanfaatkan urutan kolom berjalan lebih cepat.

Petunjuk / Saran

Bahkan jika urutan dalam kolom indeks penyimpanan kolom bersifat parsial, segmen masih dapat dihilangkan (dilewati). Urutan penuh tidak diperlukan untuk mendapatkan manfaat performa jika urutan parsial menghindari banyak tumpang tindih segmen.

Tabel berikut ini menjelaskan jenis urutan yang dihasilkan saat Anda membuat atau membangun kembali indeks penyimpan kolom yang diurutkan, bergantung pada opsi build indeks.

Prasyarat	Jenis pesanan
ONLINE = ON dan MAXDOP = 1	Penuh
ONLINE = OFF, MAXDOP = 1, dan data yang akan diurutkan sepenuhnya sesuai dengan kapasitas memori ruang kerja kueri	Penuh
Semua kasus lainnya	Partial

Dalam kasus pertama ketika baik ONLINE = ON dan MAXDOP = 1 ada, pengurutan tidak dibatasi oleh memori workspace kueri karena pembuatan online indeks columnstore yang diurutkan menggunakan tempdb database untuk menumpahkan data yang tidak muat dalam memori. Pendekatan ini dapat membuat proses build indeks lebih lambat karena I/O tambahan tempdb, dan membutuhkan ruang kosong yang cukup di tempdb. Namun, karena build indeks dilakukan secara online, kueri dapat terus menggunakan indeks yang ada saat indeks yang diurutkan baru sedang dibangun.

Demikian pula, dengan rekonstruksi offline dari indeks kolomstore yang dipartisi, rekonstruksi dilakukan per partisi. Partisi lain tetap tersedia untuk kueri.

Saat MAXDOP lebih besar dari 1, setiap utas yang digunakan untuk pembangunan indeks penyimpanan kolom berurutan memproses subset data dan mengurutkannya secara lokal. Tidak ada pengurutan global di seluruh data yang diurutkan berdasarkan utas yang berbeda. Menggunakan utas paralel dapat mengurangi waktu untuk membuat indeks, tetapi menghasilkan segmen yang lebih tumpang tindih daripada saat menggunakan satu utas.

Anda dapat membuat atau membangun kembali indeks penyimpan kolom yang diurutkan secara online hanya di beberapa platform SQL dan versi SQL Server. Untuk informasi selengkapnya, lihat Ringkasan fitur untuk rilis produk.

Di SQL Server, operasi indeks online tidak tersedia di semua edisi. Untuk informasi selengkapnya, lihat Edisi dan fitur yang didukung SQL Server 2025 dan Melakukan operasi indeks secara online.

Untuk jenis data dan pengodean tertentu, tampilan sistem sys.column_store_segments dapat membantu Anda menemukan jumlah tumpang tindih segmen. Contoh skrip berdasarkan tampilan ini menentukan kualitas urutan untuk kolom yang memenuhi syarat pada semua indeks kolom di database saat ini.

Kinerja kueri

Perolehan performa dari indeks penyimpan kolom yang diurutkan tergantung pada pola kueri, ukuran data, jumlah segmen yang tumpang tindih, dan sumber daya komputasi yang tersedia untuk eksekusi kueri.

Kueri dengan pola berikut biasanya berjalan lebih cepat dengan indeks penyimpan kolom yang diurutkan:

• Kueri yang memiliki predikat kesetaraan, ketidaksetaraan, atau rentang.
• Kueri di mana kolom predikat dan kolom CCI yang diurutkan sama.

Dalam contoh berikut, tabel T1 memiliki indeks penyimpan kolom berkluster dengan Col_C, , Col_Bdan Col_A sebagai kolom yang diurutkan.

CREATE CLUSTERED COLUMNSTORE INDEX OrderedCCI
ON T1
ORDER (Col_C, Col_B, Col_A);

Kueri 1 mendapat manfaat dari indeks penyimpan kolom yang diurutkan lebih dari kueri 2 dan 3, karena kueri 1 mereferensikan semua kolom yang diurutkan dalam predikatnya.

-- query 1
SELECT *
FROM T1
WHERE Col_C = 'c'
      AND Col_B = 'b'
      AND Col_A = 'a';

-- query 2
SELECT *
FROM T1
WHERE Col_B = 'b'
      AND Col_A = 'a';

-- query 3
SELECT *
FROM T1
WHERE Col_A = 'a'
      AND Col_C = 'c';

Kinerja pemuatan data

Performa beban data ke dalam tabel dengan indeks penyimpan kolom yang diurutkan mirip dengan tabel yang dipartisi. Memuat data dapat memakan waktu lebih lama daripada dengan indeks penyimpan kolom yang tidak diurutkan karena operasi pengurutan data, tetapi kueri dapat berjalan lebih cepat setelahnya.

Menambahkan data baru atau memperbarui data yang sudah ada

Data baru yang dihasilkan dari batch DML atau operasi pemuatan massal pada tabel dengan indeks penyimpan kolom yang diurutkan diurutkan dalam batch tersebut saja. Tidak ada pengurutan global yang menyertakan data yang ada dalam tabel karena grup baris terkompresi dalam indeks penyimpan kolom tidak dapat diubah.

Untuk mengurangi tumpang tindih segmen setelah menyisipkan data baru atau memperbarui data yang ada, bangun ulang indeks penyimpan kolom.

Examples

Membuat indeks penyimpan kolom yang diurutkan

Indeks penyimpan kolom berurutan terkluster:

CREATE CLUSTERED COLUMNSTORE INDEX OCCI
ON dbo.Table1
ORDER(Column1, Column2);

Indeks penyimpan kolom berurutan tanpa kluster:

CREATE NONCLUSTERED COLUMNSTORE INDEX ONCCI
ON dbo.Table1(Column1, Column2, Column3)
ORDER(Column1, Column2);

Periksa kolom yang diurutkan dan urutan ordinal

SELECT OBJECT_SCHEMA_NAME(c.object_id) AS schema_name,
       OBJECT_NAME(c.object_id) AS table_name,
       c.name AS column_name,
       i.column_store_order_ordinal
FROM sys.index_columns AS i
     INNER JOIN sys.columns AS c
         ON i.object_id = c.object_id
        AND c.column_id = i.column_id
WHERE column_store_order_ordinal > 0;

Menambahkan atau menghapus kolom pesanan dan membangun kembali indeks penyimpan kolom berurutan yang sudah ada

Indeks penyimpan kolom berurutan terkluster:

CREATE CLUSTERED COLUMNSTORE INDEX OCCI
ON dbo.Table1
ORDER(Column1, Column2)
WITH (DROP_EXISTING = ON);

Indeks penyimpan kolom berurutan tanpa kluster:

CREATE NONCLUSTERED COLUMNSTORE INDEX ONCCI
ON dbo.Table1(Column1, Column2, Column3)
ORDER(Column1, Column2)
WITH (DROP_EXISTING = ON);

Membuat indeks penyimpan kolom berkluster yang diurutkan secara online dengan urutan penuh pada tabel heap

CREATE CLUSTERED COLUMNSTORE INDEX OCCI
ON dbo.Table1
ORDER(Column1)
WITH (ONLINE = ON, MAXDOP = 1);

Membangun kembali indeks penyimpan kolom berkluster yang diurutkan secara online dengan urutan penuh

CREATE CLUSTERED COLUMNSTORE INDEX OCCI
ON dbo.Table1
ORDER(Column1)
WITH (DROP_EXISTING = ON, ONLINE = ON, MAXDOP = 1);

Konten terkait

• panduan desain indeks Columnstore
• Indeks penyimpan kolom - panduan pemuatan data
• Mulai dengan indeks penyimpanan kolom untuk analitik operasional secara real-time
• Indeks penyimpan kolom di pergudangan data
• Optimalkan pemeliharaan indeks untuk meningkatkan performa kueri dan mengurangi konsumsi sumber daya
• Arsitektur indeks kolom
• BUAT INDEKS (Transact-SQL)
• ALTER INDEX (Transact-SQL)