Bagikan melalui

Indeks penyimpan kolom - panduan pemuatan data

Berlaku untuk:SQL ServerDatabase Azure SQLInstans Terkelola Azure SQLAzure Synapse AnalyticsSistem Platform Analitik (PDW)Database SQL di Microsoft Fabric

Opsi dan rekomendasi untuk memuat data ke dalam indeks penyimpan kolom dengan menggunakan metode pemuatan massal dan penyisipan massal SQL standar. Memuat data ke dalam indeks penyimpan kolom adalah bagian penting dari proses pergudangan data apa pun karena memindahkan data ke dalam indeks sebagai persiapan untuk analitik.

Baru mengenal indeks penyimpanan kolom? Lihat Indeks penyimpan kolom - gambaran umum dan Arsitektur Indeks Penyimpan Kolom.

Apa itu pemuatan massal?

Pemuatan massal mengacu pada cara sejumlah besar baris ditambahkan ke penyimpanan data. Ini adalah cara paling berperforma untuk memindahkan data ke indeks penyimpan kolom karena beroperasi pada batch baris. Pemuatan massal mengisi grup baris hingga kapasitas maksimum dan memadatkannya langsung ke dalam penyimpanan kolom. Hanya baris di akhir beban yang tidak memenuhi minimal 102.400 baris per grup baris yang masuk ke deltastore.

Untuk melakukan pemuatan massal, Anda dapat menggunakan bcp Utility, Integration Services, atau memilih baris dari tabel penyangga.

Cuplikan layar memperlihatkan pemuatan ke dalam indeks penyimpan kolom berkluster.

Seperti yang disarankan diagram, beban massal:

  • Tidak menyortir data sebelumnya. Data disisipkan ke dalam grup baris dalam urutan diterimanya.
  • Jika ukuran batch adalah >= 102400, baris langsung dimuat ke dalam grup baris terkompresi. Anda harus memilih ukuran >batch =102400 untuk impor massal yang efisien, karena Anda dapat menghindari pemindahan baris data ke grup baris delta sebelum baris akhirnya dipindahkan ke grup baris terkompresi oleh utas latar belakang, penggerak tuple (TM).
  • Jika ukuran < batch 102.400 atau jika baris yang tersisa adalah < 102.400, baris dimuat ke dalam grup baris delta.

Note

Pada tabel rowstore dengan data indeks penyimpan kolom berkluster, SQL Server selalu menyisipkan data ke dalam tabel dasar. Data tidak pernah disisipkan langsung ke dalam indeks penyimpan kolom.

Pemuatan massal memiliki pengoptimalan performa bawaan ini:

  • Beban paralel: Anda dapat memiliki beberapa beban massal bersamaan (sisipan massal atau bcp ) yang masing-masing memuat file data terpisah. Tidak seperti pemuatan massal rowstore ke SQL Server, Anda tidak perlu menentukan TABLOCK karena setiap utas impor massal memuat data secara eksklusif ke dalam grup baris terpisah (grup baris terkompresi atau delta) dengan kunci eksklusif di atasnya.

  • Pengurangan pengelogan: Data yang langsung dimuat ke dalam grup baris terkompresi, menyebabkan pengurangan ukuran log yang signifikan. Misalnya, jika data dikompresi 10x, log transaksi yang sesuai kira-kira 10x lebih kecil tanpa memerlukan TABLOCK atau model pemulihan yang dicatat secara massal/Sederhana. Setiap data yang masuk ke grup baris delta sepenuhnya dicatat. Ini termasuk ukuran batch apa pun yang kurang dari 102.400 baris. Praktik terbaik adalah menggunakan batchsize >= 102400. Karena tidak TABLOCK diperlukan, Anda dapat memuat data secara paralel.

  • Pengelogan minimal: Anda bisa mendapatkan pengurangan lebih lanjut dalam pengelogan jika Anda mengikuti prasyarat untuk pengelogan minimal. Namun, tidak seperti memuat data ke dalam rowstore, TABLOCK mengarah ke X kunci (eksklusif) pada tabel daripada BU kunci (pembaruan massal) dan oleh karena itu beban data paralel tidak dapat dilakukan. Untuk informasi selengkapnya tentang penguncian, lihat Mengunci dan membuat versi baris.

  • Pengoptimalan penguncian: Kunci X pada grup baris secara otomatis diperoleh saat memuat data ke dalam grup baris terkompresi. Namun, ketika melakukan pemuatan massal ke dalam kelompok baris delta, sebuah X kunci diperoleh untuk kelompok baris tersebut. Namun, Mesin Basis Data tetap memperoleh kunci halaman dan kunci ekstensi karena kunci X kelompok baris bukan bagian dari hierarki kunci.

Jika Anda memiliki indeks pohon B non-kluster pada indeks penyimpan kolom, tidak ada pengoptimalan penguncian atau pengelogan untuk indeks itu sendiri tetapi pengoptimalan pada indeks penyimpan kolom berkluster seperti yang dijelaskan sebelumnya berlaku.

Merencanakan ukuran beban massal untuk meminimalkan grup baris delta

Indeks kolom berkinerja terbaik ketika sebagian besar baris dikompresi ke dalam columnstore dan tidak berada di grup baris delta. Yang terbaik adalah mengukur beban Anda sehingga baris langsung masuk ke penyimpan kolom dan melewati deltastore sebanyak mungkin.

Skenario ini menjelaskan kapan baris yang dimuat langsung diarahkan ke penyimpanan kolom atau kapan mereka diarahkan ke deltastore. Dalam contoh, setiap grup baris dapat memiliki 102.400-1.048.576 baris per grup baris. Dalam praktiknya, ukuran maksimum grup baris bisa lebih kecil dari 1.048.576 baris ketika ada tekanan memori.

Baris untuk pemuatan massal Baris ditambahkan ke grup baris terkompresi Baris ditambahkan ke grup baris delta
102,000 0 102,000
145,000 145,000

Ukuran grup baris: 145.000		 0
1,048,577 1,048,576

Ukuran grup baris: 1.048.576.		 1
2,252,152 2,252,152

Ukuran grup baris: 1.048.576, 1.048.576, 155.000.		 0

Contoh berikut menunjukkan hasil pemuatan 1.048.577 baris ke dalam tabel. Hasilnya menunjukkan bahwa satu grup baris TERKOMPRESI di penyimpan kolom (sebagai segmen kolom terkompresi), dan 1 baris di deltastore.

SELECT object_id, index_id, partition_number, row_group_id, delta_store_hobt_id,
    state, state_desc, total_rows, deleted_rows, size_in_bytes
FROM sys.dm_db_column_store_row_group_physical_stats;

Cuplikan layar menunjukkan grup baris dan deltastore untuk pemuatan batch.

Menggunakan tabel penahapan untuk meningkatkan performa

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][Sementara] juga akan memuat jauh lebih cepat daripada memuat tabel ke penyimpanan permanen.

Pola umum untuk beban data adalah memuat data ke dalam tabel penahapan, melakukan beberapa transformasi lalu memuatnya ke dalam tabel target menggunakan perintah berikut:

INSERT INTO [<columnstore index>]
SELECT col1 /* include actual list of columns in place of col1*/
FROM [<Staging Table>]

Perintah ini memuat data ke dalam indeks penyimpan kolom dengan cara yang mirip dengan bcp atau penyisipan massal, tetapi dilakukan dalam satu batch. Jika jumlah baris dalam tabel < penahapan 102400, baris dimuat ke dalam grup baris delta jika tidak, baris langsung dimuat ke dalam grup baris terkompresi. Salah satu batasan utamanya adalah bahwa operasi ini INSERT memiliki satu utas. Untuk memuat data secara paralel, Anda dapat membuat lebih dari satu tabel penahapan atau mengeluarkan INSERT/SELECT dengan rentang baris yang tidak tumpang tindih dari tabel penahapan. Batasan ini hilang dengan SQL Server 2016 (13.x). Perintah berikut memuat data dari tabel penahapan secara paralel tetapi Anda harus menentukan TABLOCK. Anda mungkin merasa ini bertentangan dengan apa yang dikatakan sebelumnya dengan pemuatan massal data, namun perbedaan utamanya adalah beban data paralel dari tabel perantara dilaksanakan dalam transaksi yang sama.

INSERT INTO [<columnstore index>] WITH (TABLOCK)
SELECT col1 /* include actual list of columns in place of col1*/
FROM [<Staging Table>]

Ada pengoptimalan berikut yang tersedia saat memuat ke dalam indeks penyimpan kolom berkluster dari tabel penahapan:

  • Pengoptimalan log: Mengurangi pengelogan saat data dimuat ke dalam grup baris terkompresi.
  • Pengoptimalan penguncian: Saat memuat data ke dalam grup baris terkompresi, kunci pada grup baris didapatkan. Namun, ketika melakukan pemuatan massal ke dalam kelompok baris delta, sebuah X kunci diperoleh untuk kelompok baris tersebut. Namun, Mesin Basis Data tetap memperoleh kunci halaman dan kunci ekstensi karena kunci X kelompok baris bukan bagian dari hierarki kunci.

Jika Anda memiliki satu atau beberapa indeks non-kluster, tidak ada pengoptimalan penguncian atau pengelogan untuk indeks itu sendiri, tetapi pengoptimalan pada indeks penyimpan kolom berkluster seperti yang dijelaskan sebelumnya masih ada.

Apa itu penyisipan trickle?

Penyisipan trickle mengacu pada cara masing-masing baris berpindah ke indeks penyimpan kolom. Penyisipan trickle menggunakan perintah INSERT INTO. Dengan penyisipan trik, semua baris masuk ke deltastore. Ini berguna untuk sejumlah kecil baris, tetapi tidak praktis untuk beban besar.

INSERT INTO [<table-name>] VALUES ('some value' /*replace with actual set of values*/)

Note

Utas bersamaan menggunakan INSERT INTO untuk menyisipkan nilai ke dalam indeks penyimpan kolom berkluster dapat menyisipkan baris ke dalam grup baris deltastore yang sama.

Setelah grup baris berisi 1,048,576 baris, grup baris delta ditandai sebagai ditutup tetapi masih tersedia untuk kueri dan operasi pembaruan/hapus. Namun, baris yang baru disisipkan akan masuk ke grup baris deltastore yang sudah ada atau yang baru dibuat. Ada utas latar belakang yang disebut tuple mover (TM) yang memadatkan grup baris delta tertutup secara berkala setiap 5 menit atau lebih. Anda dapat secara eksplisit memanggil perintah berikut untuk memadatkan grup baris delta tertutup.

ALTER INDEX [<index-name>] on [<table-name>] REORGANIZE

Jika Anda ingin memaksa grup baris delta ditutup dan dikompresi, Anda dapat menjalankan perintah berikut. Anda mungkin ingin menjalankan perintah ini jika Anda selesai memuat baris dan tidak mengharapkan baris baru. Dengan menutup dan mengompresi grup baris delta secara eksplisit, Anda dapat menyimpan penyimpanan lebih lanjut dan meningkatkan performa kueri analitik. Praktik terbaik adalah memanggil perintah ini jika Anda tidak mengharapkan baris baru disisipkan.

ALTER INDEX [<index-name>] on [<table-name>] REORGANIZE with (COMPRESS_ALL_ROW_GROUPS = ON)

Cara kerja pemuatan ke dalam tabel yang dipartisi

Untuk data yang dipartisi, Mesin Database terlebih dahulu menetapkan setiap baris ke partisi, lalu melakukan operasi penyimpan kolom pada data dalam partisi. Setiap partisi memiliki grup barisnya sendiri dan setidaknya satu grup baris delta.

Konten terkait

  • Last updated on