Bagikan melalui

Indeks Penyimpan Kolom - Performa kueri

  • Artikel

Berlaku untuk: SQL Server Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics Analytics Platform System (PDW)

Artikel ini mencakup rekomendasi untuk mencapai performa kueri cepat dengan 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 dirancang untuk disediakan indeks penyimpan kolom.

Rekomendasi untuk meningkatkan performa kueri

Berikut adalah beberapa rekomendasi untuk mencapai indeks penyimpan kolom berkinerja tinggi dirancang untuk disediakan.

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 berkluster rowstore. Jika predikat kueri umum ada di kolom (misalnya, C1) yang tidak terkait dengan urutan penyisipan, buat indeks berkluster rowstore pada kolom C1. Kemudian, letakkan indeks berkluster rowstore dan buat indeks penyimpan kolom 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 penyimpan kolom non-kluster (NCCI), jika tabel memiliki indeks berkluster rowstore, 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 menemukan pembelian triwulanan dengan customer. Dalam hal ini, gabungkan kolom sisipkan pesanan dengan partisi pada customer kolom. Setiap partisi berisi baris untuk setiap customer, diurutkan saat penyisipan. Selain itu, pertimbangkan untuk menggunakan partisi tabel jika ada kebutuhan untuk menghapus data yang lebih lama dari penyimpan kolom. 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 mengoleskan seluruh grup baris, pertimbangkan untuk menggunakan partisi tabel. Alihkan partisi yang tidak diperlukan lagi dan potong, alih-alih menghapus baris.

    Catatan

    Dimulai dengan SQL Server 2019 (15.x), tuple-mover dibantu oleh tugas penggabungan 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 internal, atau menggabungkan grup baris TERKOMPRESI dari tempat sejumlah besar baris telah dihapus. Ini meningkatkan kualitas indeks penyimpan 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 mengambil urutan 1,5 kali selama membangun 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, SQL Server hanya menggunakan satu utas untuk membuat indeks penyimpan kolom.

Jika tabel Anda memiliki lebih dari satu juta baris, tetapi SQL Server tidak bisa mendapatkan peruntukan memori yang cukup besar untuk membuat indeks menggunakan MAXDOP, SQL Server secara otomatis menurun 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.

Performa Penyimpan Kolom Dijelaskan

Indeks penyimpan kolom mencapai performa 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.

Pemadatan 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 memadatkan country-region dengan mudah 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 dalam country-region kolom.

Eliminasi kolom

Indeks penyimpan kolom melewati pembacaan dalam kolom yang tidak diperlukan untuk hasil 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 dengan mudah dipisahkan. 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 5 kolom tersebut, indeks penyimpan kolom hanya mengambil 5 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 rowstore noncluster reguler, atau pohon B, indeks pada indeks penyimpan kolom berkluster. Indeks pohon B berkluster dapat mempercepat kueri yang memiliki predikat kesetaraan atau predikat dengan rentang kecil nilai. 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 penyimpan 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 tanpa melakukan I/O aktual. 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 Mode Batch

Eksekusi mode batch mengacu pada pemrosesan sekumpulan baris, biasanya hingga 900 baris, bersama-sama untuk efisiensi eksekusi. Misalnya, kueri SELECT SUM (Sales) FROM SalesData menggabungkan total penjualan dari tabel SalesData. Dalam eksekusi mode batch, mesin eksekusi kueri menghitung agregat dalam grup 900 nilai. Ini menyebarkan metadata biaya akses dan jenis overhead lainnya di semua baris dalam batch, daripada membayar biaya untuk setiap baris sehingga secara signifikan mengurangi jalur kode. Pemrosesan mode batch beroperasi pada data terkompresi jika memungkinkan dan menghilangkan beberapa operator pertukaran yang digunakan oleh pemrosesan mode baris, mempercepat kueri analitik berdasarkan urutan besarnya.

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 Pindai, Gabung, Agregat, Urutkan, dan lainnya dapat meningkatkan performa 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 Mode Batch Saat digunakan SQL Server 2012 (11.x) SQL Server 2014 (12.x) SQL Server 2016 (13.x) dan SQL Database1 Komentar
Operasi DML (sisipkan, hapus, perbarui, gabungkan) no no no DML bukan operasi mode batch karena tidak paralel. Bahkan ketika kami mengaktifkan pemrosesan batch mode serial, kami tidak melihat keuntungan yang signifikan dengan memungkinkan DML diproses dalam mode batch.
pemindaian indeks penyimpan kolom SCAN Tidak tersedia yes yes Untuk indeks penyimpan kolom, kita dapat mendorong predikat ke simpul SCAN.
Pemindaian indeks penyimpan kolom (tidak terkluster) SCAN yes yes yes yes
pencarian indeks Tidak tersedia Tidak tersedia no Kami melakukan operasi pencarian melalui indeks pohon B 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.
Rangkaian 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
urutkan Urutan berdasarkan klausa pada SCAN dengan indeks penyimpan kolom. no no yes
sortir atas no no yes
agregat jendela Tidak tersedia Tidak tersedia yes Operator baru di SQL Server 2016 (13.x).

1 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 berdasarkan urutan besarnya di atas eksekusi Mode Batch, asalkan 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, uang, desimal dan numerik dengan presisi <= 18
    • smalldate, date, datetime, datetime2, time

Misalnya, pushdown 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 dimensi terhubung melalui hubungan kunci primer/asing. Kueri analitik yang paling umum digunakan menggabungkan satu atau beberapa tabel dimensi dengan tabel fakta.

Mari kita pertimbangkan tabel Productsdimensi . Kunci primer yang khas adalah ProductCode, umumnya direpresentasikan sebagai string. Untuk performa kueri, ini adalah praktik terbaik untuk membuat kunci pengganti, biasanya 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) meningkatkan performa kueri analitik dengan kolom berbasis string secara signifikan, dengan mendorong ke bawah predikat dengan kolom string ke simpul SCAN.

Pushdown predikat string memanfaatkan kamus primer/sekunder yang dibuat untuk kolom untuk 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 pushdown predikat string, eksekusi kueri menghitung predikat terhadap 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 yang mengurangi jumlah baris yang perlu mengalir keluar dari simpul pemindaian.
  • Jumlah perbandingan string berkurang. Dalam contoh ini, hanya 100 perbandingan string yang diperlukan sebagai terhadap 1 juta perbandingan. Ada beberapa batasan:
    • Tidak ada pushdown predikat string untuk grup baris delta. Tidak ada kamus untuk kolom dalam grup baris delta.
    • Tidak ada pushdown predikat string jika kamus melebihi entri 64 KB.
    • Ekspresi yang mengevaluasi null tidak didukung.

Eliminasi segmen

Pilihan jenis data mungkin berdampak signifikan pada predikat filter umum berbasis performa kueri untuk kueri pada indeks penyimpan 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 akan menguntungkan fitur ini sampai dibangun kembali menggunakan REBUILD atau DROP/CREATE.

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

Saat ini, hanya SQL Server 2022 (16.x) dan yang lebih baru yang mendukung penghapusan grup baris penyimpan kolom berkluster untuk awalan LIKE predikat, misalnya column LIKE 'string%'. Penghapusan segmen tidak didukung untuk penggunaan non-awalan dari LIKE, seperti column LIKE '%string'.

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

Dengan menggunakan opsi koneksi kueri SET STATISTICS IO, Anda dapat menampilkan eliminasi segmen dalam tindakan. Cari output seperti berikut ini untuk menunjukkan bahwa eliminasi segmen telah terjadi. Grup baris terdiri dari segmen kolom, sehingga ini mungkin menunjukkan eliminasi segmen. Contoh output kueri berikut SET STATISTICS IO , sekitar 83% data dilewati oleh kueri:

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

Konten terkait