Pemadatan data

  • Artikel

Berlaku untuk:SQL ServerAzure SQL DatabaseAzure SQL Managed Instance

SQL Server, Azure SQL Database, dan Azure SQL Managed Instance mendukung kompresi baris dan halaman untuk tabel dan indeks rowstore, dan mendukung kompresi pengarsipan penyimpan kolom dan penyimpan kolom untuk tabel dan indeks penyimpan kolom.

Untuk tabel dan indeks rowstore, gunakan fitur kompresi 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 dapat mengonfigurasi kompresi baris dan halaman pada objek database berikut:

  • Seluruh tabel yang disimpan sebagai tumpukan.
  • Seluruh tabel yang disimpan sebagai indeks berkluster.
  • Seluruh indeks non-klusster.
  • Seluruh tampilan terindeks.
  • Untuk tabel dan indeks yang dipartisi, Anda dapat mengonfigurasi opsi kompresi 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 yang tidak terdaftar.
  • 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 pengarsipan yang sama.

Catatan

Data juga dapat dikompresi menggunakan format algoritma GZIP. Ini adalah langkah tambahan dan paling cocok untuk mengompresi bagian data saat mengarsipkan data lama untuk penyimpanan jangka panjang. Data yang dikompresi menggunakan COMPRESS fungsi tidak dapat diindeks. Untuk informasi selengkapnya, lihat COMPRESS (Transact-SQL).

Pertimbangan pemadatan 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 tersedia di Azure SQL Database

  • Pemadatan tidak tersedia di setiap edisi SQL Server. Untuk informasi selengkapnya, lihat daftar edisi dan fitur yang didukung di akhir bagian ini.

  • 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 saat ukuran baris maksimum ditambah overhead kompresi melebihi ukuran baris maksimum 8.060 byte. Misalnya, tabel yang memiliki kolom c1 CHAR(8000) dan c2 CHAR(53) tidak dapat dikompresi karena overhead kompresi tambahan. Saat 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 dicentang 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 pas di halaman, yang berarti kurang dari 8.060 byte; SQL Server mencegah pembaruan yang tidak pas pada baris saat tidak dikompresi.

  • Data di luar baris tidak dikompresi saat mengaktifkan pemadatan data. Misalnya, catatan XML yang lebih besar dari 8.060 byte menggunakan halaman di luar baris, yang tidak dikompresi.

  • Beberapa jenis data tidak terpengaruh oleh kompresi data. Untuk detail selengkapnya, lihat Bagaimana kompresi baris memengaruhi penyimpanan.

  • Ketika 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, kompresi data diatur ke NONE kecuali ditentukan lain. Saat tabel dimodifikasi, pemadatan yang ada dipertahankan kecuali ditentukan lain.

  • Jika Anda menentukan daftar partisi atau partisi yang berada di luar rentang, kesalahan akan dihasilkan.

  • Indeks nonclustered tidak mewarisi properti kompresi tabel. Untuk memadatkan indeks, Anda harus secara eksplisit mengatur properti kompresi indeks. Secara default, pengaturan pemadatan untuk indeks diatur ke NONE saat indeks dibuat.

  • Ketika indeks berkluster dibuat pada timbunan, 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 ... WITH (TABLOCK) sintaksis dan tabel tidak memiliki indeks yang tidak terkluster.
    • Tabel dibangun kembali dengan menjalankan ALTER TABLE ... REBUILD pernyataan dengan PAGE opsi pemadatan.

  • Halaman baru yang dialokasikan dalam timbunan sebagai bagian dari operasi DML tidak menggunakan PAGE pemadatan sampai timbunan 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 ROW atau PAGE memadatkan 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 data_compression kolom sys.partitions tampilan katalog.

  • 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 vardecimal di SQL Server 2005 (9.x), mempertahankan pengaturan tersebut saat ditingkatkan. Anda dapat menerapkan kompresi baris ke tabel yang memiliki format penyimpanan vardecimal . Namun, karena kompresi baris adalah superset dari format penyimpanan vardecimal , tidak ada alasan untuk mempertahankan format penyimpanan vardecimal . Nilai desimal tidak mendapatkan pemadatan tambahan saat Anda menggabungkan format penyimpanan vardecimal dengan pemadatan baris. Anda dapat menerapkan pemadatan halaman ke tabel yang memiliki format penyimpanan vardecimal ; namun, kolom format penyimpanan vardecimal mungkin tidak mencapai kompresi tambahan.

    Catatan

    Semua versi SQL Server yang didukung mendukung format penyimpanan vardecimal ; namun, karena kompresi data mencapai tujuan yang sama, format penyimpanan vardecimal tidak digunakan lagi. Fitur ini akan dihapus dalam versi SQL Server yang akan datang. Hindari menggunakan fitur ini dalam pekerjaan pengembangan baru, dan rencanakan untuk memodifikasi aplikasi yang saat ini menggunakan fitur ini.

Untuk daftar fitur yang didukung oleh edisi SQL Server di Windows, lihat:

Kompresi arsip penyimpan kolom dan penyimpan kolom

Tabel penyimpan kolom dan indeks selalu disimpan dengan kompresi penyimpan kolom. Anda selanjutnya dapat 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 pengarsipan.
  • Gunakan COLUMNSTORE kompresi data untuk mendekompresi kompresi arsip. Data yang dihasilkan terus dikompresi dengan kompresi penyimpan kolom.

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

Contohnya:

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 REBUILD opsi dan DATA COMPRESSION = COLUMNSTORE.

Contohnya:

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

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

Manfaat kompresi arsip 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 untuk bulan-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:

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

Dampak pada tabel dan indeks yang dipartisi

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

  • Ketika partisi dibagi dengan menggunakan ALTER PARTITION pernyataan , 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 kompresi tabel.

  • Ada dua variasi sintaks yang dapat Anda gunakan untuk memodifikasi kompresi 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 ulang seluruh tabel dengan menggunakan pengaturan kompresi 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 tumpukan 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 ... REBUILD opsi yang menentukan opsi pemadatan.

    Untuk menghilangkan indeks OFFLINE berkluster adalah operasi 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 memengaruhi replikasi

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

  • Ketika Agen Rekam Jepret menghasilkan skrip skema awal, skema baru menggunakan pengaturan kompresi 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 diskrip. Untuk informasi selengkapnya, lihat sp_addarticle.

    Agen Distribusi tidak memeriksa Pelanggan tingkat bawah saat menerapkan skrip. Jika replikasi kompresi dipilih, pembuatan tabel pada Pelanggan tingkat bawah gagal. Untuk topologi campuran, jangan aktifkan replikasi kompresi.

  • Untuk replikasi penggabungan, tingkat kompatibilitas publikasi mengambil alih opsi skema dan menentukan objek skema yang diskrip.

    Untuk 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 kompresi pada Pelanggan pada partisi. Benar Benar Skrip baik skema partisi maupun pengaturan kompresi.
Untuk mereplikasi skema partisi tetapi tidak mengompresi data pada Pelanggan. Benar Salah Skrip keluar skema partisi tetapi bukan pengaturan kompresi untuk partisi.
Tidak mereplikasi skema partisi dan tidak mengompresi data pada Pelanggan. Salah Salah Tidak mempartisi skrip atau pengaturan kompresi.
Untuk memadatkan tabel pada Pelanggan jika semua partisi dikompresi pada Penerbit, tetapi tidak mereplikasi skema partisi. Salah Benar Memeriksa apakah semua partisi diaktifkan untuk pemadatan.

Skrip kompresi di tingkat tabel.

Pengaruh pada komponen SQL Server lainnya

Berlaku untuk:SQL ServerAzure SQL DatabaseAzure SQL Managed Instance

Pemadatan terjadi di Mesin Database dan data disajikan ke sebagian besar komponen SQL Server lainnya dalam keadaan tidak dikompresi. Ini membatasi efek kompresi pada komponen lain ke faktor-faktor 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, Mesin Database mengonversi data menjadi format baris terkompresi. Ini dapat menyebabkan peningkatan penggunaan CPU dibandingkan dengan saat data diimpor ke dalam tabel yang tidak dikompresi.
    • Ketika data diimpor secara massal ke dalam timbunan dengan pemadatan halaman, operasi impor massal 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.

Konten terkait