Pemadatan data

Berlaku untuk:Database SQL Server Azure SQL Azure SQL Managed Instance

SQL Server, Azure SQL Database, dan Azure SQL Managed Instance mendukung kompresi baris dan halaman untuk tabel dan indeks penyimpanan baris, serta 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.

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

  • Pemadatan tidak tersedia di setiap edisi SQL Server. Untuk informasi selengkapnya, lihat Fitur yang Didukung oleh Edisi SQL Server 2016, Edisi, dan fitur yang didukung SQL Server 2017, dan Edisi serta fitur SQL Server 2019 yang didukung.

  • 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 c1 CHAR(8000) dan c2 CHAR(53) tidak dapat dikompresi karena overhead pemadatan 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.
  • Data off-row tidak dikompresi saat mengaktifkan pemadatan data. Misalnya, catatan XML yang lebih besar dari 8060 byte akan 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.

  • 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 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 ... WITH (TABLOCK) sintaksis dan tabel tidak memiliki indeks non-kluster.
    • Tabel dibangun kembali dengan menjalankan ALTER TABLE ... REBUILD pernyataan dengan opsi pemadatan HALAMAN.
  • Halaman baru yang dialokasikan dalam tumpukan sebagai bagian dari operasi DML tidak 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 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 mengimplementasikan format penyimpanan vardesimal dalam SQL Server 2005 (9.x), 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

    Semua versi SQL Server yang didukung mendukung format penyimpanan vardesimal; namun, karena kompresi data 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.

Pemadatan arsip penyimpan kolom dan penyimpan kolom

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 kompresi data COLUMNSTORE_ARCHIVE untuk memadatkan data penyimpan kolom dengan kompresi arsip.
  • Gunakan pemadatan data COLUMNSTORE untuk mendekompresi kompresi arsip. Data yang dihasilkan 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_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 opsi BANGUN ULANG dan KOMPRESI DATA = 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

Memadatkan indeks penyimpan kolom dengan kompresi arsip, 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 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 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:

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 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 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 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 ... REBUILD 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 memengaruhi replikasi

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

  • Ketika Agen Rekam Jepret menghasilkan skrip skema awal, skema baru 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 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. Dalam kasus topologi campuran, jangan aktifkan replikasi kompresi.

  • Untuk replikasi penggabungan, tingkat kompatibilitas publikasi mengambil alih opsi skema dan menentukan objek skema yang 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 memadatkan 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 di tingkat tabel.

Dampak pada komponen SQL Server lainnya

Berlaku untuk:Database SQL Server Azure SQL Azure SQL Managed Instance

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 ke yang 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.
    • Saat data diimpor secara massal ke dalam tumpukan 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.
  • Kompresi data tidak kompatibel dengan kolom jarang. Oleh karena itu, tabel yang berisi kolom jarang tidak dapat dikompresi atau 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