Kompresi Data

Artikel
06/13/2017

SQL Server 2014 mendukung kompresi baris dan halaman untuk tabel dan indeks penyimpanan baris, dan mendukung kompresi penyimpan kolom dan arsip penyimpan kolom untuk tabel dan indeks penyimpan kolom.

Untuk tabel dan indeks rowstore, gunakan fitur pemadatan data untuk membantu mengurangi ukuran database. Selain menghemat ruang, kompresi data dapat membantu meningkatkan kinerja beban kerja intensif I/O karena data disimpan dalam halaman yang lebih sedikit dan kueri perlu membaca lebih sedikit halaman dari disk. Namun, sumber daya CPU tambahan diperlukan di server database untuk mengompresi dan mendekompresi data, sementara data ditukar dengan aplikasi. Anda bisa mengonfigurasi kompresi baris dan halaman pada objek database berikut:

Seluruh tabel yang disimpan sebagai tumpukan.
Seluruh tabel yang disimpan sebagai indeks berkluster.
Seluruh indeks nonkluster.
Seluruh tampilan terindeks.
Untuk tabel dan indeks yang dipartisi, Anda dapat mengonfigurasi opsi pemadatan untuk setiap partisi, dan berbagai partisi objek tidak harus memiliki pengaturan pemadatan yang sama.

Untuk tabel dan indeks penyimpan kolom, semua tabel dan indeks penyimpan kolom selalu menggunakan kompresi penyimpan kolom dan ini tidak dapat dikonfigurasi pengguna. Gunakan kompresi arsip penyimpan kolom untuk lebih mengurangi ukuran data untuk situasi ketika Anda mampu membayar waktu tambahan dan sumber daya CPU untuk menyimpan dan mengambil data. Anda dapat mengonfigurasi kompresi arsip penyimpan kolom pada objek database berikut:

Seluruh tabel penyimpan kolom atau seluruh indeks penyimpan kolom berkluster. Karena tabel penyimpan kolom disimpan sebagai indeks penyimpan kolom berkluster, kedua pendekatan memiliki hasil yang sama.
Seluruh indeks penyimpan kolom nonkluster.
Untuk tabel penyimpan kolom dan indeks penyimpan kolom yang dipartisi, Anda dapat mengonfigurasi opsi kompresi arsip untuk setiap partisi, dan berbagai partisi tidak harus memiliki pengaturan kompresi arsip yang sama.

Pertimbangan saat Anda Menggunakan Kompresi Baris dan Halaman

Saat Anda menggunakan kompresi baris dan halaman, ketahui pertimbangan berikut:

Detail kompresi data dapat berubah tanpa pemberitahuan dalam paket layanan atau rilis berikutnya.
Pemadatan tidak tersedia di setiap edisi SQL Server. Untuk informasi selengkapnya, lihat Fitur yang Didukung oleh Edisi SQL Server 2014.
Pemadatan tidak tersedia untuk tabel sistem.
Pemadatan dapat memungkinkan lebih banyak baris disimpan di halaman, tetapi tidak mengubah ukuran baris maksimum tabel atau indeks.
Tabel tidak dapat diaktifkan untuk pemadatan ketika ukuran baris maksimum ditambah overhead kompresi melebihi ukuran baris maksimum 8060 byte. Misalnya, tabel yang memiliki kolom c1char(8000) dan c2char(53) tidak dapat dikompresi karena overhead kompresi tambahan. Ketika format penyimpanan vardecimal digunakan, pemeriksaan ukuran baris dilakukan saat format diaktifkan. Untuk pemadatan baris dan halaman, pemeriksaan ukuran baris dilakukan saat objek awalnya dikompresi, lalu diperiksa saat setiap baris disisipkan atau dimodifikasi. Pemadatan memberlakukan dua aturan berikut:
- Pembaruan untuk jenis panjang tetap harus selalu berhasil.
- Menonaktifkan kompresi data harus selalu berhasil. Bahkan jika baris terkompresi cocok di halaman, yang berarti kurang dari 8060 byte; SQL Server mencegah pembaruan yang tidak cocok pada baris saat tidak dikompresi.
Saat daftar partisi ditentukan, jenis kompresi dapat diatur ke ROW, PAGE, atau NONE pada partisi individual. Jika daftar partisi tidak ditentukan, semua partisi diatur dengan properti kompresi data yang ditentukan dalam pernyataan . Saat tabel atau indeks dibuat, pemadatan data diatur ke NONE kecuali ditentukan lain. Saat tabel dimodifikasi, kompresi yang ada dipertahankan kecuali ditentukan lain.
Jika Anda menentukan daftar partisi atau partisi yang berada di luar rentang, kesalahan akan dihasilkan.
Indeks non-kluster tidak mewarisi properti pemadatan tabel. Untuk memadatkan indeks, Anda harus secara eksplisit mengatur properti pemadatan indeks. Secara default, pengaturan pemadatan untuk indeks akan diatur ke NONE saat indeks dibuat.
Ketika indeks berkluster dibuat pada heap, indeks berkluster mewarisi status kompresi timbunan kecuali status kompresi alternatif ditentukan.
Saat timbunan dikonfigurasi untuk kompresi tingkat halaman, halaman hanya menerima pemadatan tingkat halaman dengan cara berikut:
- Data diimpor secara massal dengan pengoptimalan massal diaktifkan.
- Data disisipkan menggunakan INSERT INTO ... Sintaks WITH (TABLOCK) dan tabel tidak memiliki indeks non-kluster.
- Tabel dibangun kembali dengan menjalankan ALTER TABLE ... Buat ulang pernyataan dengan opsi pemadatan HALAMAN.
Halaman baru yang dialokasikan dalam timbunan sebagai bagian dari operasi DML tidak akan menggunakan pemadatan HALAMAN hingga tumpukan dibangun kembali. Bangun kembali tumpukan dengan menghapus dan menerapkan kembali kompresi, atau dengan membuat dan menghapus indeks berkluster.
Mengubah pengaturan kompresi timbunan mengharuskan semua indeks non-kluster pada tabel dibangun kembali sehingga mereka memiliki penunjuk ke lokasi baris baru di tumpukan.
Anda dapat mengaktifkan atau menonaktifkan kompresi ROW atau PAGE secara online atau offline. Mengaktifkan kompresi pada tumpukan adalah utas tunggal untuk operasi online.
Persyaratan ruang disk untuk mengaktifkan atau menonaktifkan kompresi baris atau halaman sama dengan untuk membuat atau membangun ulang indeks. Untuk data yang dipartisi, Anda dapat mengurangi ruang yang diperlukan dengan mengaktifkan atau menonaktifkan pemadatan untuk satu partisi pada satu waktu.
Untuk menentukan status kompresi partisi dalam tabel yang dipartisi, kueri kolom data_compression tampilan katalog sys.partitions.
Saat Anda memadatkan indeks, halaman tingkat daun dapat dikompresi dengan pemadatan baris dan halaman. Halaman tingkat non-daun tidak menerima pemadatan halaman.
Karena ukurannya, jenis data bernilai besar terkadang disimpan secara terpisah dari data baris normal pada halaman tujuan khusus. Pemadatan data tidak tersedia untuk data yang disimpan secara terpisah.
Tabel yang menerapkan format penyimpanan vardesimal di SQL Server 2005 akan mempertahankan pengaturan tersebut saat ditingkatkan. Anda dapat menerapkan pemadatan baris ke tabel yang memiliki format penyimpanan vardesimal. Namun, karena kompresi baris adalah superset dari format penyimpanan vardesimal, tidak ada alasan untuk mempertahankan format penyimpanan vardesimal. Nilai desimal tidak mendapatkan pemadatan tambahan saat Anda menggabungkan format penyimpanan vardesimal dengan pemadatan baris. Anda dapat menerapkan pemadatan halaman ke tabel yang memiliki format penyimpanan vardesimal; namun, kolom format penyimpanan vardecimal mungkin tidak akan mencapai pemadatan tambahan.

Catatan

SQL Server 2014 mendukung format penyimpanan vardesimal; namun, karena kompresi tingkat baris mencapai tujuan yang sama, format penyimpanan vardesimal tidak digunakan lagi. Fitur ini akan dihapus dalam versi Microsoft SQL Server mendatang. Hindari menggunakan fitur ini dalam pekerjaan pengembangan baru, dan rencanakan untuk memodifikasi aplikasi yang saat ini menggunakan fitur ini.

Menggunakan Penyimpan Kolom dan Kompresi Arsip Penyimpan Kolom


Berlaku untuk: SQL Server (SQL Server 2014 hingga versi saat ini).

Dasar

Tabel dan indeks penyimpan kolom selalu disimpan dengan pemadatan penyimpan kolom. Anda dapat lebih mengurangi ukuran data penyimpan kolom dengan mengonfigurasi kompresi tambahan yang disebut kompresi arsip. Untuk melakukan kompresi arsip, SQL Server menjalankan algoritma kompresi Microsoft XPRESS pada data. Tambahkan atau hapus kompresi arsip dengan menggunakan jenis kompresi data berikut:

Gunakan COLUMNSTORE_ARCHIVE kompresi data untuk memadatkan data penyimpan kolom dengan kompresi arsip.
Gunakan pemadatan data COLUMNSTORE untuk mendekompresi kompresi arsip. Data yang dihasilkan ini akan terus dikompresi dengan pemadatan penyimpan kolom.

Untuk menambahkan kompresi arsip, gunakan ALTER TABLE (Transact-SQL) atau ALTER INDEX (Transact-SQL) dengan opsi REBUILD dan KOMPRESI DATA = COLUMNSTORE.

Contoh:

ALTER TABLE ColumnstoreTable1   
REBUILD PARTITION = 1 WITH (DATA_COMPRESSION =  COLUMNSTORE_ARCHIVE) ;  
  
ALTER TABLE ColumnstoreTable1   
REBUILD PARTITION = ALL WITH (DATA_COMPRESSION =  COLUMNSTORE_ARCHIVE) ;  
  
ALTER TABLE ColumnstoreTable1   
REBUILD PARTITION = ALL WITH (DATA_COMPRESSION =  COLUMNSTORE_ARCHIVE ON PARTITIONS (2,4)) ;

Untuk menghapus kompresi arsip dan memulihkan data ke kompresi penyimpan kolom, gunakan ALTER TABLE (Transact-SQL) atau ALTER INDEX (Transact-SQL) dengan opsi BANGUN ULANG dan KOMPRESI DATA = COLUMNSTORE.

Contoh:

ALTER TABLE ColumnstoreTable1   
REBUILD PARTITION = 1 WITH (DATA_COMPRESSION =  COLUMNSTORE) ;  
  
ALTER TABLE ColumnstoreTable1   
REBUILD PARTITION = ALL WITH (DATA_COMPRESSION =  COLUMNSTORE) ;  
  
ALTER TABLE ColumnstoreTable1   
REBUILD PARTITION = ALL WITH (DATA_COMPRESSION =  COLUMNSTORE ON PARTITIONS (2,4) ) ;

Contoh berikutnya ini mengatur kompresi data ke penyimpan kolom pada beberapa partisi, dan ke pengarsipan penyimpan kolom pada partisi lain.

ALTER TABLE ColumnstoreTable1   
REBUILD PARTITION = ALL WITH (  
    DATA_COMPRESSION =  COLUMNSTORE ON PARTITIONS (4,5),  
    DATA COMPRESSION = COLUMNSTORE_ARCHIVE ON PARTITIONS (1,2,3)  
) ;

Performa

Memadatkan indeks penyimpan kolom dengan kompresi arsip akan menyebabkan indeks berkinerja lebih lambat daripada indeks penyimpan kolom yang tidak memiliki kompresi arsip. Gunakan kompresi arsip hanya ketika Anda mampu menggunakan waktu tambahan dan sumber daya CPU untuk memadatkan dan mengambil data.

Manfaat performa yang lebih lambat adalah penyimpanan berkurang yang berguna untuk data yang tidak sering diakses. Misalnya, jika Anda memiliki partisi untuk setiap bulan data, dan sebagian besar aktivitas Anda adalah selama beberapa bulan terakhir, Anda dapat mengarsipkan bulan yang lebih lama untuk mengurangi persyaratan penyimpanan.

Metadata

Tampilan sistem berikut berisi informasi tentang kompresi data untuk indeks berkluster:

sys.indexes (Transact-SQL) - Kolom type dan type_desc mencakup COLUMNSTORE TERKLUSTER dan PENYIMPAN KOLOM NONCLUSTERED.
sys.partitions (Transact-SQL) - Kolom data_compression dan data_compression_desc mencakup COLUMNSTORE dan COLUMNSTORE_ARCHIVE.

Prosedur sp_estimate_data_compression_savings (Transact-SQL) tidak berlaku untuk indeks penyimpan kolom.

Bagaimana Pemadatan Mempengaruhi Tabel dan Indeks yang Dipartisi

Saat Anda menggunakan kompresi data dengan tabel dan indeks yang dipartisi, ketahui pertimbangan berikut:

Ketika partisi dibagi dengan menggunakan pernyataan ALTER PARTITION, kedua partisi mewarisi atribut kompresi data dari partisi asli.
Ketika dua partisi digabungkan, partisi yang dihasilkan mewarisi atribut kompresi data dari partisi tujuan.
Untuk mengalihkan partisi, properti kompresi data partisi harus cocok dengan properti pemadatan tabel.
Ada dua variasi sintaks yang dapat Anda gunakan untuk memodifikasi pemadatan tabel atau indeks yang dipartisi:
- Sintaks berikut hanya membangun kembali partisi yang direferensikan:
```
ALTER TABLE <table_name>   
REBUILD PARTITION = 1 WITH (DATA_COMPRESSION =  <option>)  
```
- Sintaks berikut membangun kembali seluruh tabel dengan menggunakan pengaturan pemadatan yang ada untuk partisi apa pun yang tidak direferensikan:
```
ALTER TABLE <table_name>   
REBUILD PARTITION = ALL   
WITH (DATA_COMPRESSION = PAGE ON PARTITIONS(<range>),  
... )  
```
Indeks yang dipartisi mengikuti prinsip yang sama menggunakan ALTER INDEX.
Ketika indeks berkluster dihilangkan, partisi heap yang sesuai mempertahankan pengaturan kompresi data mereka kecuali skema partisi dimodifikasi. Jika skema partisi diubah, semua partisi dibangun kembali ke status tidak terkompresi. Untuk menghilangkan indeks berkluster dan mengubah skema partisi memerlukan langkah-langkah berikut:
1. Hilangkan indeks berkluster.
2. Ubah tabel dengan menggunakan ALTER TABLE ... MEMBANGUN... opsi yang menentukan opsi pemadatan.
Untuk menghilangkan indeks berkluster OFFLINE adalah operasi yang sangat cepat, karena hanya tingkat atas indeks berkluster yang dihapus. Ketika indeks berkluster dihilangkan ONLINE, SQL Server harus membangun kembali tumpukan dua kali, sekali untuk langkah 1 dan sekali untuk langkah 2.

Bagaimana Kompresi Mempengaruhi Replikasi

Saat Anda menggunakan kompresi data dengan replikasi, ketahui pertimbangan berikut:

Ketika Agen Rekam Jepret menghasilkan skrip skema awal, skema baru akan menggunakan pengaturan pemadatan yang sama untuk tabel dan indeksnya. Pemadatan tidak dapat diaktifkan hanya pada tabel dan bukan indeks.
Untuk replikasi transaksional, opsi skema artikel menentukan objek dan properti dependen apa yang harus ditulis. Untuk informasi selengkapnya, lihat sp_addarticle.

Agen Distribusi tidak memeriksa Pelanggan tingkat bawah saat menerapkan skrip. Jika replikasi kompresi dipilih, membuat tabel pada Pelanggan tingkat bawah akan gagal. Dalam kasus topologi campuran, jangan aktifkan replikasi kompresi.
Untuk replikasi penggabungan, tingkat kompatibilitas publikasi mengambil alih opsi skema dan menentukan objek skema yang akan ditulis.

Dalam kasus topologi campuran, jika tidak diperlukan untuk mendukung opsi kompresi baru, tingkat kompatibilitas publikasi harus diatur ke versi Pelanggan tingkat bawah. Jika diperlukan, kompres tabel pada Pelanggan setelah dibuat.

Tabel berikut ini memperlihatkan pengaturan replikasi yang mengontrol pemadatan selama replikasi.

Niat pengguna	Mereplikasi skema partisi untuk tabel atau indeks	Mereplikasi pengaturan kompresi	Perilaku pembuatan skrip
Untuk mereplikasi skema partisi dan mengaktifkan pemadatan pada Pelanggan pada partisi.	True	True	Skrip baik skema partisi maupun pengaturan kompresi.
Untuk mereplikasi skema partisi tetapi tidak mengompresi data pada Pelanggan.	True	FALSE	Skrip keluar skema partisi tetapi bukan pengaturan kompresi untuk partisi.
Untuk tidak mereplikasi skema partisi dan tidak mengompresi data pada Pelanggan.	FALSE	FALSE	Tidak mempartisi skrip atau pengaturan pemadatan.
Untuk memadatkan tabel pada Pelanggan jika semua partisi dikompresi pada Publisher, tetapi tidak mereplikasi skema partisi.	Salah	True	Memeriksa apakah semua partisi diaktifkan untuk pemadatan. Skrip kompresi pada tingkat tabel.

Bagaimana Kompresi Mempengaruhi Komponen SQL Server Lainnya

Pemadatan terjadi di mesin penyimpanan dan data disajikan ke sebagian besar komponen lain dari SQL Server dalam keadaan tidak terkompresi. Ini membatasi efek kompresi pada komponen lain terhadap hal berikut:

Operasi impor dan ekspor massal

Saat data diekspor, bahkan dalam format asli, data adalah output dalam format baris yang tidak dikompresi. Ini dapat menyebabkan ukuran file data yang diekspor secara signifikan lebih besar dari data sumber.

Saat data diimpor, jika tabel target telah diaktifkan untuk pemadatan, data dikonversi oleh mesin penyimpanan menjadi format baris terkompresi. Ini dapat menyebabkan peningkatan penggunaan CPU dibandingkan dengan ketika data diimpor ke dalam tabel yang tidak dikompresi.

Ketika data diimpor secara massal ke dalam tumpukan dengan pemadatan halaman, operasi impor massal akan mencoba memadatkan data dengan pemadatan halaman saat data disisipkan.
Pemadatan tidak memengaruhi pencadangan dan pemulihan.
Pemadatan tidak memengaruhi pengiriman log.
Pemadatan data tidak kompatibel dengan kolom jarang. Oleh karena itu, tabel yang berisi kolom jarang tidak dapat dikompresi atau tidak bisa kolom jarang ditambahkan ke tabel terkompresi.
Mengaktifkan pemadatan dapat menyebabkan rencana kueri berubah karena data disimpan menggunakan jumlah halaman dan jumlah baris yang berbeda per halaman.

Lihat juga

Implementasi Pemadatan Baris
Implementasi Pemadatan Halaman
Implementasi Kompresi Unicode
BUAT SKEMA PARTISI (Transact-SQL)
CREATE PARTITION FUNCTION (Transact-SQL)
BUAT TABEL (Transact-SQL)
ALTER TABLE (Transact-SQL)
CREATE INDEX (Transact-SQL)
ALTER INDEX (Transact-SQL)

Bagikan melalui