sp_estimate_data_compression_savings (T-SQL)
Berlaku untuk:
SQL ServerAzure SQL DatabaseAzure SQL Managed Instance
Mengembalikan ukuran objek yang diminta saat ini dan memperkirakan ukuran objek untuk status pemadatan yang diminta. Pemadatan dapat dievaluasi untuk seluruh tabel atau bagian tabel. Ini termasuk timbunan, indeks berkluster, indeks non-kluster, indeks penyimpan kolom, tampilan terindeks, dan partisi tabel dan indeks. Objek dapat dikompresi dengan menggunakan kompresi arsip baris, halaman, penyimpan kolom, atau penyimpan kolom. Jika tabel, indeks, atau partisi sudah dikompresi, Anda dapat menggunakan prosedur ini untuk memperkirakan ukuran tabel, indeks, atau partisi jika dikompresi ulang atau disimpan tanpa pemadatan.
Dimulai dengan SQL Server 2022 (16.x), Anda dapat memadatkan data XML di luar baris dalam kolom menggunakan xml jenis data, mengurangi persyaratan penyimpanan dan memori. Untuk informasi selengkapnya, lihat CREATE TABLE (Transact-SQL) dan CREATE INDEX (Transact-SQL). sp_estimate_data_compression_savings mendukung perkiraan kompresi XML.
Catatan
Pemadatan dan sp_estimate_data_compression_savings tidak tersedia di setiap edisi SQL Server. Untuk daftar fitur yang didukung oleh edisi SQL Server, lihat Edisi dan fitur yang didukung SQL Server 2022.
Prosedur sys.sp_estimate_data_compression_savings tersimpan sistem tersedia di Azure SQL Database dan Azure SQL Managed Instance.
Untuk memperkirakan ukuran objek jika menggunakan pengaturan kompresi yang diminta, prosedur tersimpan ini mengambil sampel objek sumber dan memuat data ini ke dalam tabel dan indeks yang setara yang dibuat di tempdb. Tabel atau indeks yang dibuat kemudian dikompresi tempdb ke pengaturan yang diminta dan perkiraan penghematan kompresi dihitung.
Untuk mengubah status pemadatan tabel, indeks, atau partisi, gunakan pernyataan ALTER TABLE atau ALTER INDEX . Untuk informasi umum tentang kompresi, lihat Kompresi Data.
Catatan
Jika data yang ada terfragmentasi, Anda mungkin dapat mengurangi ukurannya tanpa menggunakan pemadatan dengan membangun kembali indeks. Untuk indeks, faktor pengisian akan diterapkan selama pembangunan ulang indeks. Ini dapat meningkatkan ukuran indeks.
Sintaks
sp_estimate_data_compression_savings
[ @schema_name = ] 'schema_name'
, [ @object_name = ] 'object_name'
, [ @index_id = ] index_id
, [ @partition_number = ] partition_number
, [ @data_compression = ] 'data_compression'
, [ @xml_compression = ] xml_compression
[ ; ]
Argumen
[ @schema_name = ] 'schema_name'
Nama skema database yang berisi tabel atau tampilan terindeks. schema_name adalah sysname. Jika schema_name NULL, skema default pengguna saat ini digunakan.
[ @object_name = ] 'object_name'
Nama tabel atau tampilan terindeks tempat indeks berada. object_name adalah sysname.
[ @index_id = ] index_id
ID indeks. index_id int, dan bisa menjadi salah satu nilai berikut: nomor ID indeks, NULL, atau 0 jika object_id adalah timbunan. Untuk mengembalikan informasi untuk semua indeks untuk tabel atau tampilan dasar, tentukan NULL. Jika Anda menentukan NULL, Anda juga harus menentukan NULL untuk partition_number.
[ @partition_number = ] partition_number
Nomor partisi dalam objek. partition_number int, dan bisa menjadi salah satu nilai berikut: jumlah partisi indeks atau tumpukan, NULL atau 1 untuk indeks atau timbunan yang tidak dipartisi.
Untuk menentukan partisi, Anda juga dapat menentukan fungsi $PARTITION . Untuk mengembalikan informasi untuk semua partisi objek pemilik, tentukan NULL.
[ @data_compression = ] 'data_compression'
Jenis kompresi yang akan dievaluasi. data_compression bisa menjadi salah satu nilai berikut: NONE, ROW, PAGE, COLUMNSTORE, atau COLUMNSTORE_ARCHIVE.
Untuk SQL Server 2022 (16.x) dan yang lebih baru, NULL juga merupakan nilai yang mungkin. data_compression tidak boleh NULL jika xml_compression NULL.
[ @xml_compression = ] xml_compression
Berlaku untuk: SQL Server 2022 (16.x) dan versi yang lebih baru, Azure SQL Database, dan Azure SQL Managed Instance.
Menentukan apakah akan menghitung penghematan untuk kompresi XML. xml_compression bit, dan bisa NULL, 0, atau 1. Defaultnya adalah NULL.
xml_compression tidak boleh NULL jika data_compression NULL.
Mengembalikan nilai kode
0 (berhasil) atau 1 (kegagalan).
Tataan hasil
Tataan hasil berikut dikembalikan untuk memberikan ukuran saat ini dan perkiraan untuk tabel, indeks, atau partisi.
| Nama kolom | Jenis data | Deskripsi |
|---|---|---|
| nama_objek | nama sysname | Nama tabel atau tampilan terindeks. |
| nama_skema | nama sysname | Skema tabel atau tampilan terindeks. |
| index_id | int | ID indeks indeks: 0 = Timbunan 1 = Indeks berkluster > 1 = Indeks nonclustered |
| partition_number | int | Nomor partisi. Mengembalikan 1 untuk tabel atau indeks yang tidak dipartisi. |
| size_with_current_compression_setting (KB) | bigint | Ukuran tabel, indeks, atau partisi yang diminta seperti yang saat ini ada. |
| size_with_requested_compression_setting (KB) | bigint | Perkiraan ukuran tabel, indeks, atau partisi yang menggunakan pengaturan pemadatan yang diminta; dan, jika berlaku, faktor pengisian yang ada, dan dengan asumsi tidak ada fragmentasi. |
| sample_size_with_current_compression_setting (KB) | bigint | Ukuran sampel dengan pengaturan pemadatan saat ini. Ini termasuk fragmentasi apa pun. |
| sample_size_with_requested_compression_setting (KB) | bigint | Ukuran sampel yang dibuat dengan menggunakan pengaturan kompresi yang diminta; dan, jika berlaku, faktor pengisian yang ada dan tidak ada fragmentasi. |
Keterangan
Gunakan sp_estimate_data_compression_savings untuk memperkirakan penghematan yang dapat terjadi saat Anda mengaktifkan tabel atau partisi untuk kompresi baris, halaman, penyimpan kolom, penyimpanan kolom, atau XML. Misalnya, jika ukuran rata-rata baris dapat dikurangi sebesar 40 persen, Anda berpotensi mengurangi ukuran objek sebesar 40 persen. Anda mungkin tidak menerima penghematan ruang karena ini tergantung pada faktor pengisian dan ukuran baris. Misalnya, jika Anda memiliki baris yang panjangnya 8.000 byte dan Anda mengurangi ukurannya sebesar 40 persen, Anda masih dapat mencocokkan hanya satu baris pada halaman data. Tidak ada penghematan.
Jika hasil berjalan sp_estimate_data_compression_savings pada tabel atau indeks yang tidak dikompresi menunjukkan bahwa ukuran akan meningkat, ini berarti bahwa banyak baris menggunakan hampir seluruh presisi jenis data, dan penambahan overhead kecil yang diperlukan untuk format terkompresi lebih dari penghematan dari kompresi. Dalam kasus yang jarang terjadi ini, jangan aktifkan pemadatan.
Jika tabel sudah diaktifkan untuk pemadatan, Anda dapat menggunakan sp_estimate_data_compression_savings untuk memperkirakan ukuran rata-rata baris jika tabel tidak dikompresi.
Kunci bersama niat (IS) diperoleh pada tabel selama operasi ini. Jika kunci IS tidak dapat diperoleh, prosedur akan diblokir. Tabel dipindai di bawah tingkat isolasi yang diterapkan baca default.
Jika pengaturan kompresi yang diminta sama dengan pengaturan kompresi saat ini, prosedur tersimpan akan mengembalikan perkiraan ukuran tanpa fragmentasi data dan menggunakan faktor pengisian yang ada untuk indeks pada objek sumber.
Jika indeks atau ID partisi tidak ada, tidak ada hasil yang dikembalikan.
Izin
SELECT Memerlukan izin pada tabel, VIEW DATABASE STATE dan VIEW DEFINITION pada database yang berisi tabel dan di tempdb.
Pembatasan
Sebelum SQL Server 2019 (15.x), prosedur ini tidak berlaku untuk indeks penyimpan kolom, dan karenanya tidak menerima parameter kompresi data COLUMNSTORE dan COLUMNSTORE_ARCHIVE. Dimulai dengan SQL Server 2019 (15.x), dan di Azure SQL Database dan Azure SQL Managed Instance, indeks penyimpan kolom dapat digunakan sebagai objek sumber untuk estimasi, dan sebagai jenis kompresi yang diminta.
Saat Metadata TempDB yang Dioptimalkan Memori diaktifkan, pembuatan indeks penyimpan kolom pada tabel sementara tidak didukung. Karena batasan ini, sp_estimate_data_compression_savings tidak didukung dengan parameter pemadatan data COLUMNSTORE dan COLUMNSTORE_ARCHIVE saat Metadata TempDB yang Dioptimalkan Memori diaktifkan.
SQL Server 2022 (16.x) Release Candidate (RC) 0 tidak memperkirakan penghematan untuk indeks XML.
Pertimbangan untuk indeks penyimpan kolom
Dimulai dengan SQL Server 2019 (15.x), dan di Azure SQL Database dan Azure SQL Managed Instance, sp_estimate_compression_savings mendukung perkiraan kompresi arsip penyimpan kolom dan penyimpan kolom. Tidak seperti pemadatan halaman dan baris, menerapkan kompresi penyimpan kolom ke objek memerlukan pembuatan indeks penyimpan kolom baru. Untuk alasan ini, saat menggunakan opsi COLUMNSTORE dan COLUMNSTORE_ARCHIVE prosedur ini, jenis objek sumber yang disediakan untuk prosedur menentukan jenis indeks penyimpan kolom yang digunakan untuk perkiraan ukuran terkompresi. Tabel berikut mengilustrasikan objek referensi yang digunakan untuk memperkirakan penghematan kompresi untuk setiap jenis objek sumber saat @data_compression parameter diatur ke COLUMNSTORE atau COLUMNSTORE_ARCHIVE.
| Objek Sumber | Objek referensi |
|---|---|
| Heap | Indeks penyimpan kolom dalam kluster |
| Indeks dalam kluster | Indeks penyimpan kolom dalam kluster |
| Indeks nonclustered | Indeks penyimpan kolom nonclustered (termasuk kolom kunci dan kolom apa pun yang disertakan dari indeks nonclustered yang disediakan, dan kolom partisi tabel, jika ada) |
| Indeks penyimpan kolom nonclustered | Indeks penyimpan kolom nonclustered (termasuk kolom yang sama dengan indeks penyimpan kolom noncluster yang disediakan) |
| Indeks penyimpan kolom dalam kluster | Indeks penyimpan kolom dalam kluster |
Catatan
Saat memperkirakan kompresi penyimpan kolom dari objek sumber rowstore (indeks terkluster, indeks atau tumpukan berkluster), jika ada kolom dalam objek sumber yang memiliki tipe data yang tidak didukung dalam indeks penyimpan kolom, sp_estimate_compression_savings akan gagal dengan kesalahan.
Demikian pula, ketika @data_compression parameter diatur ke NONE, ROW, atau PAGE dan objek sumber adalah indeks penyimpan kolom, tabel berikut menguraikan objek referensi yang digunakan.
| Objek Sumber | Objek referensi |
|---|---|
| Indeks penyimpan kolom dalam kluster | Heap |
| Indeks penyimpan kolom nonclustered | Indeks nonclustered (termasuk kolom yang terkandung dalam indeks penyimpan kolom berkluster sebagai kolom kunci, dan kolom partisi tabel, jika ada, sebagai kolom yang disertakan) |
Catatan
Saat memperkirakan kompresi rowstore (NONE, ROW, atau PAGE) dari objek sumber penyimpan kolom, pastikan bahwa indeks sumber tidak berisi lebih dari 32 kolom kunci karena ini adalah batas yang didukung dalam indeks rowstore (nonclustered).
Contoh
J. Memperkirakan penghematan dengan kompresi ROW
Contoh berikut memperkirakan ukuran tabel jika dikompresi Production.WorkOrderRouting dengan menggunakan ROW pemadatan.
USE AdventureWorks2022;
GO
EXEC sys.sp_estimate_data_compression_savings
'Production', 'WorkOrderRouting', NULL, NULL, 'ROW';
GO
B. Memperkirakan penghematan dengan pemadatan PAGE dan XML
Berlaku untuk: SQL Server 2022 (16.x)
Contoh berikut memperkirakan ukuran Production.ProductModel tabel jika dikompresi dengan menggunakan PAGE kompresi, dan nilai xml_compression diaktifkan.
USE AdventureWorks2022;
GO
EXEC sys.sp_estimate_data_compression_savings
'Production', 'ProductModel', NULL, NULL, 'PAGE', 1;
GO
Konten terkait
Saran dan Komentar
Segera hadir: Sepanjang tahun 2024 kami akan menghentikan penggunaan GitHub Issues sebagai mekanisme umpan balik untuk konten dan menggantinya dengan sistem umpan balik baru. Untuk mengetahui informasi selengkapnya, lihat: https://aka.ms/ContentUserFeedback.
Kirim dan lihat umpan balik untuk