Indeks penyimpan kolom - performa kueri

Berlaku untuk:SQL Server Database Azure SQLInstans Terkelola Azure SQLAzure Synapse Analytics Sistem Platform Analitik (PDW)Database SQL di Microsoft Fabric

Artikel ini mencakup rekomendasi untuk mendapatkan performa kueri yang cepat dengan menggunakan indeks penyimpan kolom.

Indeks penyimpan kolom dapat mencapai performa hingga 100 kali lebih baik pada beban kerja analitik dan pergudangan data, dan kompresi data hingga 10 kali lebih baik daripada indeks rowstore tradisional. Rekomendasi ini membantu kueri Anda mencapai performa kueri cepat yang memang menjadi tujuan dirancangnya indeks penyimpan kolom.

Rekomendasi untuk meningkatkan performa kueri

Berikut adalah beberapa rekomendasi untuk mencapai kinerja tinggi yang dirancang untuk disediakan oleh indeks penyimpan kolom.

1. Atur data untuk menghilangkan lebih banyak grup baris dari pemindaian tabel lengkap

Pilih urutan sisipkan dengan hati-hati. Dalam kasus umum di gudang data tradisional, data memang dimasukkan dalam urutan waktu dan analitik dilakukan dalam dimensi waktu. Misalnya, menganalisis penjualan menurut kuartal. Untuk beban kerja semacam ini, eliminasi grup baris terjadi secara otomatis. Di SQL Server 2016 (13.x), Anda dapat mengetahui jumlah grup baris yang dilewati sebagai bagian dari pemrosesan kueri.
Gunakan indeks rowstore berkluster. Jika predikat kueri umum ada di kolom (misalnya, C1) yang tidak terkait dengan urutan penyisipan, buat indeks berkluster rowstore pada kolom C1. Kemudian, hapus indeks berkluster penyimpanan baris dan buat indeks columnstore berkluster. Jika Anda membuat indeks penyimpan kolom berkluster secara eksplisit menggunakan MAXDOP = 1, indeks penyimpan kolom berkluster yang dihasilkan diurutkan dengan sempurna pada kolom C1. Jika Anda menentukan MAXDOP = 8, maka Anda akan melihat tumpang tindih nilai di delapan grup baris. Untuk indeks kolom non-kluster (NCCI), jika tabel memiliki indeks berkluster penyimpan baris, baris sudah diurutkan oleh kunci indeks berkluster. Dalam hal ini, indeks penyimpan kolom non-kluster juga secara otomatis diurutkan. Indeks penyimpan kolom tidak secara inheren mempertahankan urutan baris. Saat baris baru disisipkan atau baris yang lebih lama diperbarui, Anda mungkin perlu mengulangi proses karena performa kueri analitik mungkin memburuk.
Menerapkan partisi tabel. Anda dapat mempartisi indeks penyimpan kolom, lalu menggunakan eliminasi partisi untuk mengurangi jumlah grup baris yang akan dipindai. Misalnya, tabel fakta menyimpan pembelian yang dilakukan oleh pelanggan. Pola kueri umum adalah mengidentifikasi pembelian triwulanan dengan customer. Dalam hal ini, gabungkan kolom urutan sisipkan dengan partisi pada kolom customer. Setiap partisi berisi baris dari setiap customer yang diurutkan berdasarkan urutan penyisipan. Selain itu, pertimbangkan untuk menggunakan partisi tabel jika ada kebutuhan untuk menghapus data yang lebih lama dari kolom penyimpan data. Beralih dan memotong partisi yang tidak diperlukan adalah strategi yang efisien untuk menghapus data tanpa menghasilkan fragmentasi.
Hindari menghapus data dalam jumlah besar. Menghapus baris terkompresi dari grup baris bukanlah operasi sinkron. Akan mahal untuk mendekompresi grup baris, menghapus baris, lalu mengompresinya kembali. Oleh karena itu, saat Anda menghapus data dari grup baris terkompresi, grup baris ini masih dipindai, meskipun menampilkan lebih sedikit baris. Jika jumlah baris yang dihapus untuk beberapa grup baris cukup besar untuk digabungkan menjadi grup baris yang lebih sedikit, mengatur ulang penyimpan kolom akan meningkatkan kualitas indeks dan performa kueri. Jika proses penghapusan data Anda biasanya mengosongkan seluruh kelompok baris, pertimbangkan untuk menggunakan partisi tabel. Ganti partisi yang tidak diperlukan lagi dan memangkasnya, alih-alih menghapus baris.

Note

Dimulai dengan SQL Server 2019 (15.x), tuple-mover dibantu oleh tugas penggabungan yang berjalan di latar belakang. Tugas ini secara otomatis mengompresi grup baris delta OPEN yang lebih kecil yang telah ada selama beberapa waktu, seperti yang ditentukan oleh ambang batas internal, atau menggabungkan grup baris TERKOMPRESI dari mana sejumlah besar baris telah dihapus. Ini meningkatkan kualitas indeks kolom dari waktu ke waktu. Jika menghapus data dalam jumlah besar dari indeks penyimpan kolom diperlukan, pertimbangkan untuk memisahkan operasi tersebut menjadi batch penghapusan yang lebih kecil dari waktu ke waktu. Batching memungkinkan tugas penggabungan latar belakang untuk menangani tugas penggabungan grup baris yang lebih kecil, dan meningkatkan kualitas indeks. Kemudian, tidak perlu menjadwalkan jendela pemeliharaan reorganisasi indeks setelah penghapusan data. Untuk informasi selengkapnya tentang istilah dan konsep penyimpan kolom, lihat Indeks penyimpan kolom: gambaran umum.

2. Rencanakan memori yang cukup untuk membuat indeks penyimpan kolom secara paralel

Membuat indeks penyimpan kolom secara default adalah operasi paralel, kecuali memori tersebut dibatasi. Membuat indeks secara paralel membutuhkan lebih banyak memori daripada membuat indeks secara serial. Ketika ada memori yang cukup, membuat indeks penyimpan kolom memerlukan waktu sekitar 1,5 kali lebih lama dibandingkan dengan membangun indeks pohon B pada kolom yang sama.

Memori yang diperlukan untuk membuat indeks penyimpan kolom tergantung pada jumlah kolom, jumlah kolom string, tingkat paralelisme (DOP), dan karakteristik data. Misalnya, jika tabel Anda memiliki kurang dari satu juta baris, Mesin Database hanya menggunakan satu utas untuk membuat indeks penyimpan kolom.

Jika tabel Anda memiliki lebih dari satu juta baris, tetapi Mesin Database tidak bisa mendapatkan alokasi memori yang cukup besar untuk membuat indeks menggunakan MAXDOP, Mesin Database secara otomatis mengurangi MAXDOP sesuai kebutuhan. Dalam beberapa kasus, DOP harus dikurangi menjadi satu untuk membangun indeks di bawah memori yang dibatasi dalam peruntukan memori yang tersedia.

Sejak SQL Server 2016 (13.x), kueri selalu beroperasi dalam mode batch. Dalam rilis sebelumnya, eksekusi batch hanya digunakan ketika DOP lebih besar dari satu.

Penjelasan tentang performa columnstore

Indeks penyimpanan kolom mencapai kinerja kueri tinggi dengan menggabungkan pemrosesan mode batch dalam memori berkecepatan tinggi dengan teknik yang sangat mengurangi persyaratan I/O. Karena kueri analitik memindai sejumlah besar baris, kueri biasanya terikat I/O, dan karenanya mengurangi I/O selama eksekusi kueri sangat penting untuk desain indeks penyimpan kolom. Setelah data dibaca ke dalam memori, sangat penting untuk mengurangi jumlah operasi dalam memori.

Indeks penyimpan kolom mengurangi I/O dan mengoptimalkan operasi dalam memori melalui kompresi data tinggi, eliminasi penyimpan kolom, penghapusan grup baris, dan pemrosesan batch.

Kompresi data

Indeks penyimpan kolom mencapai kompresi data hingga 10 kali lebih besar daripada indeks rowstore. Ini sangat mengurangi I/O yang diperlukan untuk menjalankan kueri analitik dan karenanya meningkatkan performa kueri.

Indeks penyimpan kolom membaca data terkompresi dari disk, yang berarti lebih sedikit byte data yang perlu dibaca ke dalam memori.
Indeks penyimpan kolom menyimpan data dalam bentuk terkompresi dalam memori, mengurangi I/O dengan menghindari membaca data yang sama ke dalam memori. Misalnya, dengan pemadatan 10 kali, indeks penyimpan kolom dapat menyimpan 10 kali lebih banyak data dalam memori, dibandingkan dengan menyimpan data dalam bentuk yang tidak dikompresi. Dengan lebih banyak data dalam memori, kemungkinan besar indeks penyimpan kolom menemukan data yang dibutuhkan dalam memori tanpa menimbulkan pembacaan yang tidak perlu dari disk.
Penyimpan kolom mengindeks data berdasarkan kolom alih-alih menurut baris, mencapai tingkat kompresi tinggi dan mengurangi ukuran data yang disimpan pada disk. Setiap kolom dikompresi dan disimpan secara independen. Data dalam kolom selalu memiliki jenis data yang sama, dan cenderung memiliki nilai yang sama. Teknik kompresi data penyimpan kolom sangat bagus untuk mencapai tingkat kompresi yang lebih tinggi ketika nilai serupa.

Misalnya, tabel fakta menyimpan alamat pelanggan dan memiliki kolom untuk country-region. Jumlah total nilai yang mungkin kurang dari 200. Beberapa nilai tersebut diulang berkali-kali. Jika tabel fakta memiliki 100 juta baris, kolom country-region tersebut mudah dipadatkan dan membutuhkan sedikit penyimpanan. Pemadatan baris demi baris tidak dapat memanfaatkan kesamaan nilai kolom dengan cara ini, dan harus menggunakan lebih banyak byte untuk memadatkan nilai di kolom country-region.

Eliminasi kolom

Indeks penyimpan kolom melewatkan pembacaan pada kolom yang tidak dirujuk oleh kueri. Eliminasi kolom semakin mengurangi I/O untuk eksekusi kueri dan karenanya meningkatkan performa kueri.

Penghapusan kolom dimungkinkan karena data diatur dan dikompresi kolom menurut kolom. Sebaliknya, ketika data disimpan baris demi baris, nilai kolom di setiap baris disimpan secara fisik bersama-sama dan tidak dapat dipisahkan dengan mudah. Prosesor Kueri perlu dibaca di seluruh baris untuk mengambil nilai kolom tertentu, meningkatkan I/O karena data tambahan tidak perlu dibaca ke dalam memori.

Misalnya, jika tabel memiliki 50 kolom dan kueri hanya menggunakan lima kolom tersebut, indeks penyimpan kolom hanya mengambil lima kolom dari disk. Ini melompati pembacaan di 45 kolom lainnya, mengurangi I/O sebesar 90%, dengan asumsi semua kolom memiliki ukuran yang sama. Jika data yang sama disimpan di rowstore, prosesor kueri perlu membaca 45 kolom yang tersisa.

Eliminasi grup baris

Untuk pemindaian tabel lengkap, persentase besar data biasanya tidak cocok dengan kriteria predikat kueri. Dengan menggunakan metadata, indeks penyimpan kolom dapat melewati pembacaan di grup baris yang tidak berisi data yang diperlukan untuk hasil kueri, semuanya tanpa I/O aktual. Kemampuan ini, yang disebut eliminasi grup baris, mengurangi I/O untuk pemindaian tabel penuh dan karenanya meningkatkan performa kueri.

Kapan indeks penyimpan kolom perlu melakukan pemindaian tabel penuh?

Dimulai dengan SQL Server 2016 (13.x), Anda dapat membuat satu atau beberapa indeks rowstore non-kluster reguler, atau indeks pohon-B, pada indeks columnstore terkelompok. Indeks pohon B non-kluster dapat mempercepat kueri yang memiliki predikat kesetaraan atau predikat dengan rentang nilai kecil. Untuk predikat yang lebih rumit, pengoptimal kueri mungkin memilih pemindaian tabel lengkap. Tanpa kemampuan untuk melewati grup baris, pemindaian tabel lengkap dapat memakan waktu, terutama untuk tabel besar.

Kapan kueri analitik mendapat manfaat dari eliminasi grup baris untuk pemindaian tabel penuh?

Misalnya, bisnis ritel memodelkan data penjualan mereka menggunakan tabel fakta dengan indeks kolom berkluster. Setiap penjualan baru menyimpan berbagai atribut transaksi, termasuk tanggal produk dijual. Menariknya, meskipun indeks penyimpan kolom tidak menjamin urutan yang diurutkan, baris dalam tabel ini dimuat dalam urutan pengurutan tanggal. Seiring waktu tabel ini tumbuh. Meskipun bisnis ritel mungkin menyimpan data penjualan selama 10 tahun terakhir, kueri analitik mungkin hanya perlu menghitung agregat untuk kuartal terakhir. Indeks penyimpan kolom dapat menghilangkan akses data untuk 39 kuartal sebelumnya hanya dengan melihat metadata untuk kolom tanggal. Ini adalah pengurangan 97% dalam jumlah data yang dibaca ke dalam memori dan diproses.

Grup baris mana yang dilewati dalam pemindaian tabel penuh?

Untuk menentukan grup baris mana yang akan dihilangkan, indeks penyimpan kolom menggunakan metadata untuk menyimpan nilai minimum dan maksimum setiap segmen kolom untuk setiap grup baris. Ketika tidak ada rentang segmen kolom yang memenuhi kriteria predikat kueri, seluruh grup baris dilewati begitu saja tanpa melakukan I/O sebenarnya. Ini berfungsi karena data biasanya dimuat dalam urutan yang diurutkan. Meskipun pengurutan baris tidak dijamin, nilai data serupa sering berada dalam grup baris yang sama atau grup baris tetangga.

Untuk informasi selengkapnya tentang grup baris, lihat Panduan desain indeks penyimpan kolom.

Eksekusi batch mode

Eksekusi mode batch memproses baris-barisan dalam grup, biasanya hingga 900 baris sekali waktu, untuk meningkatkan efisiensi. Misalnya, kueri SELECT SUM(Sales) FROM SalesData menghitung total penjualan dari SalesData tabel. Dalam mode batch, mesin kueri memproses data dalam grup 900 baris. Pendekatan ini mengurangi biaya akses metadata dan jenis overhead lainnya dengan menyebarkannya ke semua baris dalam satu batch, daripada membebankan overhead pada setiap baris. Selain itu, mode batch berfungsi dengan data terkompresi jika memungkinkan dan menghapus beberapa operator pertukaran yang digunakan dalam mode baris, secara signifikan mempercepat kueri analitik.

Tidak semua operator eksekusi kueri dapat dijalankan dalam mode batch. Misalnya, operasi bahasa manipulasi data (DML) seperti menyisipkan, menghapus, atau memperbarui dijalankan satu baris sekaligus. Operator mode batch seperti Pemindaian, Penggabungan, Agregat, Pengurutan, dan lainnya dapat meningkatkan kinerja kueri. Karena indeks penyimpan kolom diperkenalkan di SQL Server 2012 (11.x), ada upaya berkelanjutan untuk memperluas operator yang dapat dijalankan dalam mode batch. Tabel berikut menunjukkan operator yang berjalan dalam mode batch sesuai dengan versi produk.

Operator dalam Mode Batch	Saat digunakan	SQL Server 2012 (11.x)	SQL Server 2014 (12.x)	SQL Server 2016 (13.x) dan SQL Database¹	Comments
Operasi DML (sisipkan, hapus, perbarui, gabungkan)		no	no	no	Perolehan performa dari menggunakan mode batch dengan DML tidak signifikan.
pemindaian indeks penyimpan kolom	SCAN	Tidak tersedia	yes	yes	Untuk indeks penyimpanan kolom, kita dapat memindahkan predikat ke simpul SCAN.
Pemindaian indeks penyimpan kolom (tidak terkluster)	SCAN	yes	yes	yes	yes
pencarian pada indeks		Tidak tersedia	Tidak tersedia	no	Kami melakukan operasi pencarian menggunakan indeks B-tree nonclustered dalam mode baris.
skalar komputasi	Ekspresi yang mengevaluasi ke nilai skalar.	yes	yes	yes	Seperti semua operator mode batch, ada beberapa batasan pada jenis data.
perangkaian	UNION dan UNION ALL	no	yes	yes
filter	Menerapkan predikat	yes	yes	yes
kecocokan hash	Fungsi agregat berbasis hash, gabungan hash luar, gabungan hash kanan, gabungan hash kiri, gabungan dalam kanan, gabungan dalam kiri	yes	yes	yes	Pembatasan untuk agregasi: tidak ada min/maks untuk string. Fungsi agregasi yang tersedia adalah sum/count/avg/min/max. Pembatasan untuk gabungan: tidak ada gabungan jenis yang tidak cocok pada jenis non-bilangan bulat.
gabungkan gabungan		no	no	no
kueri multi-utas		yes	yes	yes
perulangan berlapis		no	no	no
kueri berutas tunggal yang berjalan di bawah MAXDOP 1		no	no	yes
kueri berutas tunggal dengan rencana kueri serial		no	no	yes
pilah	Urutan berdasarkan klausa pada SCAN dengan indeks penyimpan kolom.	no	no	yes
pengurutan tertinggi		no	no	yes
agregat jendela		Tidak tersedia	Tidak tersedia	yes	Operator baru di SQL Server 2016 (13.x).

¹ Berlaku untuk tingkat SQL Server 2016 (13.x), SQL Database Premium, tingkat Standar - S3 dan yang lebih tinggi, dan semua tingkatan vCore, dan Sistem Platform Analitik (PDW)

Untuk informasi selengkapnya, lihat Panduan Arsitektur Pemrosesan Kueri.

Pushdown agregat

Jalur eksekusi normal untuk komputasi agregat untuk mengambil baris yang memenuhi syarat dari simpul SCAN dan menggabungkan nilai dalam Mode Batch. Meskipun ini memberikan performa yang baik, dimulai dengan SQL Server 2016 (13.x), operasi agregat dapat didorong ke simpul SCAN. Pushdown agregat meningkatkan performa komputasi agregat dengan peningkatan yang sangat signifikan dalam eksekusi Mode Batch, dengan syarat kondisi berikut terpenuhi.

Agregatnya adalah MIN, , MAXSUM, COUNT dan COUNT(*).
Operator agregat harus berada di atas node SCAN atau node SCAN dengan GROUP BY.
Agregat ini bukan agregat yang berbeda.
Kolom agregat bukan kolom string.
Kolom agregat bukan kolom virtual.
Jenis data input dan output harus salah satu dari yang berikut ini dan harus pas dalam 64 bit:
- tinyint, int, bigint, smallint, bit
- smallmoney, money, desimal, dan numerik dengan presisi <= 18
- smalldate, date, datetime, datetime2, time

Misalnya, pengurangan agregat dilakukan dalam kedua kueri berikut.

SELECT  productkey, SUM(TotalProductCost)
FROM FactResellerSalesXL_CCI
GROUP BY productkey;
    
SELECT  SUM(TotalProductCost)
FROM FactResellerSalesXL_CCI;

Pushdown predikat string

Saat merancang skema gudang data, pemodelan skema yang direkomendasikan adalah menggunakan skema bintang atau skema snowflake yang terdiri dari satu atau beberapa tabel fakta dan banyak tabel dimensi.

Tip

Tabel fakta menyimpan pengukuran bisnis atau transaksi dan tabel dimensi menyimpan dimensi di mana fakta perlu dianalisis. Untuk informasi selengkapnya tentang pemodelan dimensi, lihat Pemodelan dimensi di Microsoft Fabric.

Misalnya, fakta dapat menjadi catatan yang mewakili penjualan produk tertentu di wilayah tertentu sementara dimensi mewakili sekumpulan wilayah, produk, dan sebagainya. Tabel fakta dan tabel dimensi terhubung melalui relasi kunci primer/asing. Kueri analitik yang paling umum digunakan menggabungkan satu atau beberapa tabel dimensi dengan tabel fakta.

Mari kita pertimbangkan tabel dimensi Products. Kunci primer yang khas adalah ProductCode, umumnya direpresentasikan sebagai string. Untuk performa kueri yang optimal, praktik terbaik adalah membuat kunci pengganti, yang umumnya kolom bilangan bulat, untuk merujuk ke baris dalam tabel dimensi dari tabel fakta.

Indeks penyimpan kolom menjalankan kueri analitik dengan gabungan dan predikat yang melibatkan kunci berbasis numerik atau bilangan bulat secara efisien. SQL Server 2016 (13.x) secara signifikan meningkatkan performa kueri analitik dengan kolom berbasis string, dengan meneruskan predikat dengan kolom string ke simpul SCAN.

Pengoptimalan predikat string menggunakan kamus primer/sekunder yang dibuat untuk kolom guna meningkatkan performa kueri. Misalnya, pertimbangkan segmen kolom string dalam grup baris yang terdiri dari 100 nilai string yang berbeda. Setiap nilai string yang berbeda dirujuk rata-rata 10.000 kali, dengan asumsi satu juta baris. Dengan penerapan pushdown predikat string, eksekusi kueri melakukan penghitungan predikat terhadap nilai-nilai dalam kamus. Jika predikat memenuhi syarat, semua baris yang mengacu pada nilai kamus secara otomatis memenuhi syarat. Ini meningkatkan performa dengan dua cara:

Hanya baris yang memenuhi syarat yang dikembalikan, mengurangi jumlah baris yang harus keluar dari simpul pemindaian.
Jumlah perbandingan string telah dikurangi. Dalam contoh ini, hanya diperlukan 100 perbandingan string dibandingkan dengan 1 juta perbandingan. Ada beberapa batasan:
- Tidak ada penurunan predikat string untuk kelompok baris delta. Tidak ada kamus untuk kolom dalam grup baris delta.
- Tidak ada pendorongan predikat string jika kamus melebihi 64 KB entri.
- Ekspresi yang melakukan evaluasi terhadap null tidak didukung.

Eliminasi segmen

Pilihan jenis data mungkin berdampak signifikan pada performa kueri berdasarkan predikat filter umum untuk kueri pada indeks kolom.

Di data penyimpan kolom, grup baris terdiri dari segmen kolom. Ada metadata dengan setiap segmen untuk memungkinkan eliminasi segmen dengan cepat tanpa membacanya. Penghapusan segmen ini berlaku untuk jenis data numerik, tanggal, dan waktu, dan jenis data datetimeoffset dengan skala kurang dari atau sama dengan dua. Dimulai di SQL Server 2022 (16.x), kemampuan eliminasi segmen diperluas ke jenis data string, biner, guid, dan datetimeoffset untuk skala yang lebih besar dari dua.

Setelah meningkatkan ke versi SQL Server yang mendukung penghapusan segmen string min/maks (SQL Server 2022 (16.x) dan yang lebih baru), indeks penyimpan kolom tidak mendapat manfaat dari fitur ini sampai dibangun kembali menggunakan ALTER INDEX REBUILD atau CREATE INDEX WITH (DROP_EXISTING = ON).

Penghapusan segmen tidak berlaku untuk jenis data LOB, seperti panjang jenis data (maks).

Saat ini, hanya SQL Server 2022 (16.x) dan versi yang lebih baru yang mendukung eliminasi rowgroup kolomstore berkluster untuk awalan predikat LIKE, misalnya column LIKE 'string%'. Penghapusan segmen tidak didukung untuk penggunaan tanpa awalan LIKEseperti column LIKE '%string'.

Indeks penyimpan kolom yang diurutkan juga mendapat manfaat dari eliminasi segmen, terutama untuk kolom string. Dalam indeks penyimpan kolom berurutan, eliminasi segmen pada kolom pertama di kunci indeks paling efektif, karena diurutkan. Perolehan performa karena eliminasi segmen pada kolom lain dalam tabel kurang dapat diprediksi. Untuk informasi selengkapnya tentang indeks penyimpan kolom yang diurutkan, lihat Menggunakan indeks penyimpan kolom yang diurutkan untuk tabel gudang data besar. Untuk ketersediaan indeks penyimpan kolom yang diurutkan, lihat Ketersediaan indeks kolom yang diurutkan.

Dengan menggunakan pilihan koneksi kueri SET STATISTICS IO, Anda dapat menampilkan eliminasi segmen secara langsung. Cari output seperti berikut ini untuk menunjukkan bahwa eliminasi segmen telah terjadi. Kelompok baris terdiri dari segmen kolom, sehingga ini mungkin menandakan penghapusan segmen. Contoh keluaran kueri berikut SET STATISTICS IO menunjukkan bahwa sekitar 83% data dilewati oleh kueri:

...
Table 'FactResellerSalesPartCategoryFull'. Segment reads 16, segment skipped 83.
...

Saran dan Komentar

Apakah halaman ini membantu?

Last updated on 2025-11-18