sp_estimate_data_compression_savings (T-SQL)
Berlaku untuk:
SQL ServerAzure SQL Database Azure 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.
Prosedur sys.sp_estimate_data_compression_savings tersimpan sistem tersedia di Azure SQL Database dan Azure SQL Managed Instance.
Dimulai dengan SQL Server 2022 (16.x), Anda dapat memadatkan data XML di luar baris dalam kolom menggunakan jenis data xml , mengurangi persyaratan penyimpanan dan memori. Untuk informasi selengkapnya, lihat MEMBUAT TABEL dan MEMBUAT INDEKS. 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.
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 pemadatan, lihat Pemadatan 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 = ] N'schema_name'
, [ @object_name = ] N'object_name'
, [ @index_id = ] index_id
, [ @partition_number = ] partition_number
, [ @data_compression = ] N'data_compression'
[ , [ @xml_compression = ] xml_compression ]
[ ; ]
Argumen
[ @schema_name = ] N'schema_name'
Nama skema database yang berisi tabel atau tampilan terindeks. @schema_name adalah sysname, tanpa default. Jika @schema_name adalah NULL, skema default pengguna saat ini digunakan.
[ @object_name = ] N'object_name'
Nama tabel atau tampilan terindeks tempat indeks berada. @object_name adalah sysname, tanpa default.
[ @index_id = ] index_id
ID indeks. @index_id int, dan bisa menjadi salah satu nilai berikut:
- nomor ID indeks
NULL0jika 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:
- nomor partisi indeks atau timbunan
NULL1untuk indeks atau tumpukan yang tidak dipartisi
Untuk menentukan partisi, Anda juga dapat menentukan fungsi $PARTITION . Untuk mengembalikan informasi untuk semua partisi objek pemilik, tentukan NULL.
[ @data_compression = ] N'data_compression'
Menentukan jenis kompresi yang akan dievaluasi. @data_compression adalah nvarchar(60), dan bisa menjadi salah satu nilai berikut:
NONEROWPAGECOLUMNSTORECOLUMNSTORE_ARCHIVE
Untuk SQL Server 2022 (16.x) dan versi yang lebih baru, NULL juga merupakan nilai yang mungkin. @data_compression tidak bisa NULL jika @xml_compression adalah 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 adalah bit, dan bisa menjadi salah satu nilai berikut:
NULL(default)01
@xml_compression tidak bisa NULL jika @data_compression adalah 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 |
|---|---|---|
object_name |
nama sysname | Nama tabel atau tampilan terindeks. |
schema_name |
nama sysname | Skema tabel atau tampilan terindeks. |
index_id |
int | ID indeks indeks:0 = Timbunan1 = Indeks terkluster>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. Ukuran ini mencakup 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 mengembalikan perkiraan ukuran tanpa fragmentasi data, 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.
Batasan
Di SQL Server 2017 (14.x) dan versi yang lebih lama, prosedur ini tidak berlaku untuk indeks penyimpan kolom, dan karenanya tidak menerima parameter COLUMNSTORE kompresi data dan COLUMNSTORE_ARCHIVE. Di SQL Server 2019 (15.x) dan versi yang lebih baru, 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 keterbatasan ini, sp_estimate_data_compression_savings tidak didukung dengan COLUMNSTORE parameter kompresi data dan COLUMNSTORE_ARCHIVE saat Metadata TempDB yang Dioptimalkan Memori diaktifkan.
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 COLUMNSTORE opsi 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 parameter @data_compression diatur ke atau COLUMNSTORE COLUMNSTORE_ARCHIVE.
| Objek sumber | Objek referensi |
|---|---|
| **Tumpukan | Indeks penyimpan kolom dalam kluster |
| Indeks berkluster | 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 berkluster | 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 parameter @data_compression 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 berkluster | 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
Sampel kode Transact-SQL dalam artikel ini menggunakan AdventureWorks2022 database sampel atau AdventureWorksDW2022 , yang dapat Anda unduh dari halaman beranda Sampel Microsoft SQL Server dan Proyek Komunitas.
J. Memperkirakan penghematan dengan kompresi ROW
Contoh berikut memperkirakan ukuran tabel jika dikompresi Production.WorkOrderRouting dengan menggunakan ROW pemadatan.
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) dan versi yang lebih baru
Contoh berikut memperkirakan ukuran Production.ProductModel tabel jika dikompresi dengan menggunakan PAGE pemadatan, dan nilai @xml_compression diaktifkan.
EXEC sys.sp_estimate_data_compression_savings
'Production', 'ProductModel', NULL, NULL, 'PAGE', 1;
GO