Bagikan melalui

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

Dengan memungkinkan eliminasi segmen secara efisien, indeks kolomstore terurut memberikan kinerja yang lebih cepat dengan melewatkan banyak data terurut yang tidak sesuai dengan predikat kueri. Memuat data ke dalam indeks penyortiran kolom yang diurutkan dan membuatnya tetap diurutkan melalui pembangunan ulang indeks dapat memakan waktu lebih lama daripada dalam indeks yang tidak diurutkan karena operasi pengurutan data, namun dengan kueri indeks penyortiran kolom yang diurutkan dapat berjalan lebih cepat setelahnya.

Saat pengguna mengkueri tabel penyimpan kolom, pengoptimal memeriksa nilai minimum dan maksimum yang disimpan di setiap segmen. Segmen yang berada di luar batas predikat kueri tidak dibaca dari disk ke memori. Kueri dapat selesai lebih cepat jika jumlah segmen yang akan dibaca dan ukuran totalnya lebih kecil.

Untuk ketersediaan indeks penyimpan kolom yang diurutkan, 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 segmen yang berada di luar batas predikat kueri tidak dibaca dari disk selama eksekusi 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 dari disk dan membutuhkan waktu lebih lama untuk diselesaikan.

Saat Anda membuat indeks penyimpan kolom yang diurutkan, Mesin Database mengurutkan data yang ada berdasarkan kunci pesanan yang Anda tentukan sebelum penyusun indeks mengompresinya ke dalam segmen. Dengan data yang diurutkan, tumpang tindih segmen dikurangi atau dihilangkan, memungkinkan kueri untuk memiliki eliminasi segmen yang lebih efisien sehingga performa lebih cepat karena ada segmen yang lebih sedikit untuk dibaca dari disk.

Tergantung pada memori yang tersedia, ukuran data, tingkat paralelisme, jenis indeks (terkluster vs. non-kluster), dan jenis pembangunan indeks (offline vs. online), pengurutan untuk indeks penyimpanan kolom yang diurutkan mungkin sepenuhnya (tanpa tumpang tindih segmen) atau sebagian (dengan beberapa tumpang tindih segmen). Misalnya, pengurutan parsial terjadi ketika memori yang tersedia tidak mencukupi untuk pengurutan penuh. Kueri yang menggunakan indeks penyimpan kolom yang diurutkan sering dijalankan lebih cepat daripada dengan indeks yang tidak diurutkan meskipun indeks yang diurutkan dibuat menggunakan pengurutan parsial.

Pengurutan penuh disediakan untuk indeks penyimpan kolom terkluster yang dibuat atau dibangun kembali dengan opsi ONLINE = ON dan MAXDOP = 1. Dalam hal ini, pengurutan tidak dibatasi oleh memori yang tersedia karena menggunakan database tempdb untuk menumpahkan data yang tidak muat dalam memori. Ini dapat membuat proses pembangunan indeks lebih lambat karena I/O tempdb tambahan. Namun, dengan pembangunan ulang indeks online, kueri dapat terus menggunakan indeks yang ada saat indeks yang diurutkan baru sedang dibangun kembali.

Pengurutan penuh mungkin juga disediakan untuk indeks penyimpan kolom terkluster dan terkluster yang dibuat atau dibangun kembali dengan opsi ONLINE = OFF dan MAXDOP = 1 jika jumlah data yang akan diurutkan cukup kecil agar sepenuhnya pas dalam memori yang tersedia.

Dalam semua kasus lain, pengurutan dalam indeks penyimpan kolom berurutan adalah parsial.

Note

Saat ini, indeks penyimpan kolom yang diurutkan dapat dibuat atau dibangun kembali secara online hanya di Azure SQL Database, di Azure SQL Managed InstanceAUTD, dan di SQL Server 2025 (17.x).

Untuk memeriksa rentang segmen untuk kolom dan menentukan apakah ada tumpang tindih segmen, gunakan kueri berikut, ganti tempat penampung dengan skema, tabel, dan nama kolom Anda:

SELECT OBJECT_SCHEMA_NAME(o.object_id) AS schema_name,
       o.name AS table_name,
       cols.name AS column_name,
       pnp.index_id,
       cls.row_count,
       pnp.data_compression_desc,
       cls.segment_id,
       cls.column_id,
       cls.min_data_id,
       cls.max_data_id
FROM sys.partitions AS pnp
INNER JOIN sys.tables AS t
ON pnp.object_id = t.object_id
INNER JOIN sys.objects AS o
ON t.object_id = o.object_id
INNER JOIN sys.column_store_segments AS cls
ON pnp.partition_id = cls.partition_id
INNER JOIN sys.columns AS cols
ON o.object_id = cols.object_id
   AND
   cls.column_id = cols.column_id
WHERE OBJECT_SCHEMA_NAME(o.object_id) = '<Schema Name>'
      AND
      o.name = '<Table Name>'
      AND
      cols.name = '<Column Name>'
ORDER BY o.name, pnp.index_id, cls.min_data_id;

Misalnya, output dari kueri ini untuk indeks penyimpan kolom yang diurutkan sepenuhnya mungkin terlihat sebagai berikut. Perhatikan bahwa tidak ada tumpang tindih di kolom min_data_id dan max_data_id untuk segmen yang berbeda.

schema_name table_name column_name index_id row_count data_compression_desc segment_id column_id min_data_id max_data_id
----------- ---------- ----------- -------- --------- --------------------- ---------- --------- ----------- -----------
dbo         Table1     Column1     1        479779    COLUMNSTORE           0          1         -17         1469515
dbo         Table1     Column1     1        887658    COLUMNSTORE           1          1         1469516     2188146
dbo         Table1     Column1     1        930144    COLUMNSTORE           2          1         2188147     11072928

Note

Dalam indeks penyimpan kolom yang diurutkan, data baru yang dihasilkan dari batch DML atau operasi pemuatan data yang sama diurutkan dalam batch tersebut saja. Tidak ada pengurutan global yang menyertakan data yang ada dalam tabel.

Untuk mengurutkan data dalam indeks setelah menyisipkan data baru atau memperbarui data yang ada, buat ulang indeks.

Untuk pembangunan ulang offline dari indeks penyimpan kolom yang dipartisi, pembangunan ulang dilakukan satu partisi pada satu waktu. Data dalam partisi yang sedang dibangun ulang tidak tersedia sampai pembangunan ulang selesai untuk partisi tersebut.

Data tetap tersedia selama pembangunan ulang online. Untuk informasi selengkapnya, lihat Melakukan operasi indeks secara online.

Kinerja kueri

Perolehan performa dari indeks penyimpan kolom yang diurutkan tergantung pada pola kueri, ukuran data, seberapa baik data diurutkan, struktur fisik segmen, 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 ini, tabel T1 memiliki indeks penyimpan kolom berkluster yang diurutkan dalam urutan Col_C, , Col_Bdan Col_A.

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

Performa kueri 1 dan 2 dapat memperoleh manfaat dari indeks penyimpan kolom yang diurutkan lebih dari kueri 3 dan 4, karena mereferensikan semua kolom yang diurutkan.

-- 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_C = 'c' AND Col_A = 'a';

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

-- query 4
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, namun kueri dapat berjalan lebih cepat setelahnya.

Kurangi segmen yang tumpang tindih

Jumlah segmen yang tumpang tindih tergantung pada ukuran data yang akan diurutkan, memori yang tersedia, dan pengaturan tingkat paralelisme maksimum (MAXDOP) selama proses pembangunan indeks columnstore yang diurutkan. Strategi berikut mengurangi tumpang tindih segmen, namun dapat membuat proses build indeks memakan waktu lebih lama.

  • Jika build indeks online tersedia, gunakan opsi ONLINE = ON dan MAXDOP = 1 saat membuat indeks penyimpan kolom berkluster yang diurutkan. Ini membuat indeks yang diurutkan sepenuhnya.
  • Jika pembuatan indeks online tidak tersedia, gunakan opsi MAXDOP = 1.
  • Pra-urutkan data menurut kunci pengurutan sebelum dimuat.

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. Menggunakan operasi berulir tunggal memberikan kualitas kompresi tertinggi. Anda dapat menentukan MAXDOP dengan perintah CREATE INDEX.

Examples

Periksa kolom yang diurutkan dan urutan ordinal

SELECT 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;

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);

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 pengurutan penuh pada tabel timbunan

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 pengurutan penuh

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

Konten terkait

  • Last updated on