Mengelola ruang file untuk database di Azure SQL Database

Artikel
07/02/2024

Artikel ini menjelaskan berbagai jenis ruang penyimpanan untuk database di Azure SQL Database, dan langkah-langkah yang dapat diambil ketika ruang file yang dialokasikan perlu dikelola secara eksplisit.

Gambaran Umum

Dengan Azure SQL Database, ada pola beban kerja di mana alokasi file data yang mendasar untuk database dapat menjadi lebih besar dari jumlah halaman data yang digunakan. Kondisi ini dapat terjadi ketika ruang yang digunakan meningkat dan data kemudian dihapus. Hal ini dikarenakan ruang file yang dialokasikan tidak diklaim kembali secara otomatis ketika data dihapus.

Memantau penggunaan ruang file dan menyusutkan file data mungkin diperlukan dalam skenario berikut:

Memungkinkan pertumbuhan data dalam kumpulan elastis saat ruang file yang dialokasikan untuk databasenya mencapai ukuran kumpulan maksimum.
Izinkan penurunan ukuran maksimum database tunggal atau kumpulan elastis.
Izinkan mengubah database tunggal atau kumpulan elastis ke tingkat layanan atau tingkat performa yang berbeda dengan ukuran maksimal yang lebih rendah.

Catatan

Operasi penyusutan tidak boleh dianggap sebagai operasi pemeliharaan rutin. File data dan log yang bertambah karena operasi bisnis yang teratur dan berulang tidak memerlukan operasi penyusutan.

Memantau penggunaan ruang file

Sebagian besar metrik ruang penyimpanan ditampilkan di API berikut ini hanya mengukur ukuran halaman data yang digunakan:

API metrik berbasis Azure Resource Manager termasuk get-metrik PowerShell

Namun, API berikut juga mengukur ukuran ruang yang dialokasikan untuk database dan kumpulan elastis:

T-SQL: sys.resource_stats
T-SQL: sys.elastic_pool_resource_stats

Memahami jenis ruang penyimpanan untuk database

Memahami jumlah ruang penyimpanan berikut ini penting untuk mengelola ruang file database.

Kuantitas database	Definisi	Komentar
Ruang data yang digunakan	Jumlah ruang yang digunakan untuk menyimpan data database.	Umumnya, ruang yang digunakan meningkat (berkurang) pada sisipan (penghapusan). Dalam beberapa kasus, ruang yang digunakan tidak berubah pada sisipan atau penghapusan tergantung pada jumlah dan pola data yang terlibat dalam operasi dan fragmentasi apa pun. Misalnya, menghapus satu baris dari setiap halaman data tidak selalu mengurangi ruang yang digunakan.
Ruang data dialokasikan	Jumlah ruang file yang diformat disediakan untuk menyimpan data database.	Jumlah ruang yang dialokasikan tumbuh secara otomatis, tetapi tidak pernah berkurang setelah penghapusan. Perilaku ini memastikan bahwa sisipan di masa depan lebih cepat karena ruang tidak perlu diformat ulang.
Ruang data dialokasikan tetapi tidak digunakan	Perbedaan antara jumlah ruang data yang dialokasikan dan ruang data yang digunakan.	Kuantitas ini menunjukkan jumlah maksimum ruang kosong yang dapat direklamasi dengan menyusutkan file data database.
Ukuran maksimal data	Jumlah ruang maksimum yang dapat digunakan untuk menyimpan data database.	Jumlah ruang data yang dialokasikan tidak dapat tumbuh melebihi ukuran maksimal data.

Diagram berikut ini mengilustrasikan hubungan antara berbagai jenis ruang penyimpanan untuk database.

Diagram yang menunjukkan ukuran perbedaan konsep ruang database dalam tabel kuantitas database.

Mengkueri database tunggal untuk informasi ruang file

Gunakan kueri berikut pada sys.database_files untuk mengembalikan jumlah ruang file database yang dialokasikan dan jumlah ruang yang tidak digunakan yang dialokasikan. Unit hasil kueri dalam MB.

-- Connect to a user database
SELECT file_id, type_desc,
       CAST(FILEPROPERTY(name, 'SpaceUsed') AS decimal(19,4)) * 8 / 1024. AS space_used_mb,
       CAST(size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 AS decimal(19,4)) AS space_unused_mb,
       CAST(size AS decimal(19,4)) * 8 / 1024. AS space_allocated_mb,
       CAST(max_size AS decimal(19,4)) * 8 / 1024. AS max_size_mb
FROM sys.database_files;

Memahami jenis ruang penyimpanan untuk kumpulan elastis

Memahami jumlah ruang penyimpanan berikut ini penting untuk mengelola ruang file kumpulan elastis.

Kuantitas kumpulan elastis	Definisi	Komentar
Ruang data yang digunakan	Penjumlahan ruang data yang digunakan oleh semua database dalam kumpulan elastis.
Ruang data dialokasikan	Penjumlahan ruang data yang dialokasikan oleh semua database dalam kumpulan elastis.
Ruang data dialokasikan tetapi tidak digunakan	Perbedaan antara jumlah ruang data yang dialokasikan dan ruang data yang digunakan oleh semua database dalam kumpulan elastis.	Kuantitas ini menunjukkan jumlah ruang maksimum yang dialokasikan untuk kumpulan elastis yang dapat direklamasi dengan menyusutkan file data database.
Ukuran maksimal data	Jumlah maksimum ruang data yang dapat digunakan oleh kumpulan elastis untuk semua databasenya.	Ruang yang dialokasikan untuk kumpulan elastis tidak boleh melebihi ukuran maksimum kumpulan elastis. Jika kondisi ini terjadi, maka ruang yang dialokasikan yang tidak digunakan dapat direklamasi dengan menyusutkan file data database.

Catatan

Pesan kesalahan "Kumpulan elastis telah mencapai batas penyimpanannya" menunjukkan bahwa objek database telah dialokasikan cukup ruang untuk memenuhi batas penyimpanan kumpulan elastis, tetapi mungkin ada ruang yang tidak digunakan dalam alokasi ruang data. Pertimbangkan untuk meningkatkan batas penyimpanan kumpulan elastis, atau sebagai solusi jangka pendek, mengosongkan ruang data menggunakan sampel di Merebut kembali ruang yang dialokasikan yang tidak digunakan. Anda juga harus mengetahui potensi dampak performa negatif dari menyusutnya file database, lihat Pemeliharaan indeks setelah menyusut.

Mengkueri kumpulan elastis untuk informasi ruang penyimpanan

Kueri berikut dapat digunakan untuk menentukan jumlah ruang penyimpanan untuk kumpulan elastis.

Ruang data kumpulan elastis yang digunakan

Ubah kueri berikut untuk mengembalikan jumlah ruang data kumpulan elastis yang digunakan. Unit hasil kueri dalam MB.

-- Connect to master
-- Elastic pool data space used in MB  
SELECT TOP 1 avg_storage_percent / 100.0 * elastic_pool_storage_limit_mb AS ElasticPoolDataSpaceUsedInMB
FROM sys.elastic_pool_resource_stats
WHERE elastic_pool_name = 'ep1'
ORDER BY end_time DESC;

Ruang data kumpulan elastis dialokasikan dan ruang yang tidak digunakan dialokasikan

Ubah contoh berikut untuk mengembalikan tabel yang mencantumkan ruang yang dialokasikan dan ruang yang tidak digunakan yang dialokasikan untuk setiap database dalam kumpulan elastis. Tabel memerintahkan database dari database tersebut dengan jumlah terbesar ruang yang tidak digunakan yang dialokasikan untuk jumlah ruang yang paling sedikit tidak digunakan yang dialokasikan. Unit hasil kueri dalam MB.

Hasil kueri untuk menentukan ruang yang dialokasikan untuk setiap database dalam kumpulan dapat ditambahkan bersamaan untuk menentukan total ruang yang dialokasikan untuk kumpulan elastis. Ruang kumpulan elastis yang dialokasikan tidak boleh melebihi ukuran maksimum kumpulan elastis.

Penting

Modul PowerShell Azure Resource Manager masih didukung oleh Azure SQL Database, tetapi semua pengembangan di masa mendatang adalah untuk modul Az.Sql. Modul AzureRM akan terus menerima perbaikan bug hingga setidaknya bulan Desember 2020. Argumen untuk perintah dalam modul Az dan dalam modul AzureRm secara substansial identik. Untuk informasi selengkapnya tentang kompatibilitas keduanya, lihat Memperkenalkan modul Azure PowerShell Az baru.

Skrip PowerShell memerlukan modul SQL Server PowerShell - lihat Mengunduh modul PowerShell untuk diinstal.

$resourceGroupName = "<resourceGroupName>"
$serverName = "<serverName>"
$poolName = "<poolName>"
$userName = "<userName>"
$password = "<password>"

# get list of databases in elastic pool
$databasesInPool = Get-AzSqlElasticPoolDatabase -ResourceGroupName $resourceGroupName `
    -ServerName $serverName -ElasticPoolName $poolName
$databaseStorageMetrics = @()

# for each database in the elastic pool, get space allocated in MB and space allocated unused in MB
foreach ($database in $databasesInPool) {
    $sqlCommand = "SELECT DB_NAME() as DatabaseName, `
    SUM(size/128.0) AS DatabaseDataSpaceAllocatedInMB, `
    SUM(size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0) AS DatabaseDataSpaceAllocatedUnusedInMB `
    FROM sys.database_files `
    GROUP BY type_desc `
    HAVING type_desc = 'ROWS'"
    $serverFqdn = "tcp:" + $serverName + ".database.windows.net,1433"
    $databaseStorageMetrics = $databaseStorageMetrics + 
        (Invoke-Sqlcmd -ServerInstance $serverFqdn -Database $database.DatabaseName `
            -Username $userName -Password $password -Query $sqlCommand)
}

# display databases in descending order of space allocated unused
Write-Output "`n" "ElasticPoolName: $poolName"
Write-Output $databaseStorageMetrics | Sort -Property DatabaseDataSpaceAllocatedUnusedInMB -Descending | Format-Table

Cuplikan layar berikut adalah contoh output skrip:

Cuplikan layar output cmdlet PowerShell terkait, memperlihatkan ruang yang dialokasikan kumpulan elastis, dan ruang yang tidak dialokasikan yang tidak digunakan.

Ukuran maksimum data kumpulan elastis

Ubah kueri T-SQL berikut untuk mengembalikan ukuran maksimal data kumpulan elastis yang terakhir direkam. Unit hasil kueri dalam MB.

-- Connect to master
-- Elastic pools max size in MB
SELECT TOP 1 elastic_pool_storage_limit_mb AS ElasticPoolMaxSizeInMB
FROM sys.elastic_pool_resource_stats
WHERE elastic_pool_name = 'ep1'
ORDER BY end_time DESC;

Klaim kembali ruang yang dialokasikan dan tidak digunakan

Penting

Perintah penyusutan berdampak pada performa database saat berjalan, dan jika memungkinkan harus dijalankan selama periode penggunaan rendah.

Menyusutkan file data

Azure SQL Database tidak secara otomatis menyusutkan file data karena mungkin berdampak pada performa database. Namun, pelanggan mungkin menyusutkan file data melalui layanan mandiri pada saat mereka memilih. Operasi ini tidak boleh menjadi operasi yang dijadwalkan secara teratur, melainkan kejadian satu kali sebagai tanggapan terhadap pengurangan signifikan dalam penggunaan ruang file data yang digunakan.

Tip

Tidak disarankan untuk menyusutkan file data jika beban kerja aplikasi reguler akan menyebabkan file membesar lagi ke ukuran yang sama yang dialokasikan.

Di Azure SQL Database, untuk menyusutkan file, Anda dapat menggunakan perintah DBCC SHRINKDATABASE atau DBCC SHRINKFILE:

DBCC SHRINKDATABASE menyusutkan semua data dan file log dalam database menggunakan satu perintah. Perintah ini akan menyusutkan satu file data dalam satu waktu, yang dapat memakan waktu lama untuk database yang lebih besar. Perintah ini juga menyusutkan file log, yang biasanya tidak diperlukan karena Azure SQL Database menyusutkan file log secara otomatis jika diperlukan.
DBCC SHRINKFILE perintah mendukung skenario yang lebih canggih:
- Perintah ini dapat menargetkan file individu yang diperlukan, bukan menyusutkan semua file dalam database.
- Setiap perintah DBCC SHRINKFILE dapat berjalan secara paralel dengan perintah DBCC SHRINKFILE yang lainnya untuk menyusutkan beberapa file dalam satu waktu dan mengurangi jumlah waktu penyusutan, dengan mengorbankan penggunaan sumber daya yang lebih tinggi dan peluang yang lebih tinggi untuk memblokir kueri pengguna, jika pengguna melakukan eksekusi selama penyusutan.
  - Menyusutkan beberapa file data secara bersamaan memungkinkan Anda menyelesaikan operasi penyusutan lebih cepat. Jika Anda menggunakan penyusutan file data bersamaan, Anda mungkin mengamati pemblokiran sementara dari satu permintaan penyusutan oleh permintaan lain.
- Jika ekor file tidak berisi data, bagian tersebut dapat mengurangi ukuran file yang dialokasikan secara lebih cepat dengan menentukan argumen TRUNCATEONLY. Argumen ini tidak memerlukan pergerakan data dalam file.
Untuk informasi lebih lanjut terkait perintah penyusutan ini, lihat DBCC SHRINKDATABASE dan DBCC SHRINKFILE.
Operasi penyusutan database dan file didukung dalam pratinjau untuk Azure SQL Database Hyperscale. Untuk informasi selengkapnya, lihat Menyusut untuk Azure SQL Database Hyperscale.

Contoh berikut harus dijalankan saat tersambung ke database pengguna target, bukan database master.

Untuk menggunakan DBCC SHRINKDATABASE guna menyusutkan semua file data dan file log dalam database yang diberikan:

-- Shrink database data space allocated.
DBCC SHRINKDATABASE (N'database_name');

Di Azure SQL Database, database mungkin memiliki satu atau beberapa file data, yang dibuat secara otomatis saat data bertambah. Untuk menentukan tata letak file database Anda, termasuk ukuran yang digunakan dan dialokasikan dari setiap file, kueri tampilan katalog sys.database_files menggunakan skrip sampel berikut:

-- Review file properties, including file_id and name values to reference in shrink commands
SELECT file_id,
       name,
       CAST(FILEPROPERTY(name, 'SpaceUsed') AS bigint) * 8 / 1024. AS space_used_mb,
       CAST(size AS bigint) * 8 / 1024. AS space_allocated_mb,
       CAST(max_size AS bigint) * 8 / 1024. AS max_file_size_mb
FROM sys.database_files
WHERE type_desc IN ('ROWS','LOG');

Anda dapat menjalankan penyusutan hanya terhadap satu file melalui perintah DBCC SHRINKFILE, misalnya:

-- Shrink database data file named 'data_0` by removing all unused at the end of the file, if any.
DBCC SHRINKFILE ('data_0', TRUNCATEONLY);
GO

Waspadai potensi dampak performa negatif dari menyusutnya file database, lihat Pemeliharaan indeks setelah menyusut.

Menyusutkan file log transaksi

Tidak seperti file data, Azure SQL Database secara otomatis menyusutkan file log transaksi untuk menghindari penggunaan ruang berlebihan yang dapat menyebabkan kesalahan di luar ruang. Biasanya tidak perlu bagi pelanggan untuk menyusutkan file log transaksi.

Di tingkat layanan Premium dan Bisnis Penting, jika log transaksi menjadi besar, mungkin secara signifikan berkontribusi pada konsumsi penyimpanan lokal menuju batas penyimpanan lokal maksimum. Jika konsumsi penyimpanan lokal mendekati batas, pelanggan mungkin memilih untuk menyusutkan log transaksi menggunakan perintah DBCC SHRINKFILE seperti yang ditunjukkan dalam contoh berikut. Ini melepaskan penyimpanan lokal segera setelah perintah selesai, tanpa menunggu operasi penyusutan otomatis berkala.

Contoh berikut harus dijalankan saat tersambung ke database pengguna target, bukan master database.

-- Shrink the database log file (always file_id 2), by removing all unused space at the end of the file, if any.
DBCC SHRINKFILE (2, TRUNCATEONLY);

Penyusutan otomatis

Sebagai alternatif dari menyusutkan file data secara manual, penyusutan otomatis dapat diaktifkan untuk database. Namun, penyusutan otomatis dapat kurang efektif dalam mereklamasi ruang file daripada DBCC SHRINKDATABASE dan DBCC SHRINKFILE.

Secara default, penyusutan otomatis dinonaktifkan seperti yang direkomendasikan untuk sebagian besar database. Jika perlu mengaktifkan penyusutan otomatis, disarankan untuk menonaktifkannya setelah tujuan manajemen ruang tercapai, alih-alih mengaktifkannya secara permanen. Untuk informasi selengkapnya, lihat Pertimbangan untuk AUTO_SHRINK.

Penyusutan otomatis dapat membantu dalam skenario tertentu saat kumpulan elastis berisi banyak database yang mengalami pertumbuhan dan pengurangan penggunaan ruang file data yang signifikan, yang menyebabkan kumpulan mendekati batas ukuran maksimumnya. Skenario ini bukan skenario yang umum.

Untuk mengaktifkan penyusutan otomatis, jalankan perintah berikut saat tersambung ke database Anda (bukan master database).

-- Enable auto-shrink for the current database.
ALTER DATABASE CURRENT SET AUTO_SHRINK ON;

Untuk informasi selengkapnya tentang perintah ini, lihat opsi DATABASE SET.

Pemeliharaan indeks setelah penyusutan

Setelah operasi penyusutan selesai terhadap file data, indeks mungkin menjadi terfragmentasi. Hal ini mengurangi efektivitas optimisasi performa untuk beban kerja tertentu, seperti kueri yang menggunakan pemindaian berukuran besar. Jika penurunan performa terjadi setelah operasi penyusutan selesai, pertimbangkan pemeliharaan indeks untuk membangun kembali indeks. Perlu diingat bahwa pembangunan kembali indeks memerlukan ruang kosong dalam database, dan karenanya dapat menyebabkan ruang yang dialokasikan meningkat, menangkal efek penyusutan.

Untuk informasi lebih lanjut terkait pemeliharaan indeks, lihat Mengoptimalkan pemeliharaan indeks untuk meningkatkan performa kueri dan mengurangi konsumsi sumber daya.

Menyusutkan database besar

Ketika ruang yang dialokasikan database dalam ratusan gigabyte atau lebih tinggi, penyusutan mungkin memerlukan waktu yang signifikan untuk diselesaikan, sering diukur dalam jam, atau hari untuk database multi-terabyte. Terdapat optimisasi proses dan praktik terbaik yang dapat Anda gunakan untuk membuat proses ini lebih efisien dan tidak berpengaruh ke beban kerja aplikasi.

Merekam garis besar penggunaan ruang

Sebelum memulai penyusutan, rekam ruang yang saat ini digunakan dan dialokasikan dalam setiap file database dengan menjalankan kueri penggunaan ruang berikut:

SELECT file_id,
       CAST(FILEPROPERTY(name, 'SpaceUsed') AS bigint) * 8 / 1024. AS space_used_mb,
       CAST(size AS bigint) * 8 / 1024. AS space_allocated_mb,
       CAST(max_size AS bigint) * 8 / 1024. AS max_size_mb
FROM sys.database_files
WHERE type_desc = 'ROWS';

Setelah penyusutan telah selesai, Anda dapat menjalankan lagi kueri ini dan membandingkan hasilnya dengan garis besar awal.

Memotong file data

Disarankan untuk menjalankan penyusutan terlebih dahulu untuk setiap file data dengan parameter TRUNCATEONLY. Dengan cara ini, jika ada ruang yang dialokasikan tetapi tidak digunakan di akhir file, itu dihapus dengan cepat dan tanpa pergerakan data. Contoh perintah berikut ini memotong file data dengan file_id 4:

DBCC SHRINKFILE (4, TRUNCATEONLY);

Setelah perintah ini dijalankan untuk setiap file data, Anda dapat menjalankan kembali kueri penggunaan ruang untuk melihat pengurangan dalam ruang yang dialokasikan, jika ada. Anda juga dapat menampilkan ruang yang dialokasikan untuk database di portal Microsoft Azure.

Mengevaluasi kerapatan halaman indeks

Jika file data yang terpotong tidak memberikan pengurangan yang memadai dalam ruang yang dialokasikan, Anda harus menyusutkan file data. Namun, sebagai langkah opsional tetapi disarankan, Anda harus menentukan rata-rata kerapatan halaman untuk indeks dalam database terlebih dahulu. Untuk data dalam jumlah sama, penyusutan akan lebih cepat selesai jika kerapatan halaman tinggi, karena harus memindahkan lebih sedikit halaman. Jika kerapatan halaman rendah untuk beberapa indeks, pertimbangkan untuk melakukan pemeliharaan pada indeks ini guna meningkatkan kepadatan halaman sebelum menyusutkan file data. Hal ini juga akan membuat penyusutan menghasilkan pengurangan yang lebih dalam di ruang penyimpanan yang dialokasikan.

Untuk menentukan kerapatan halaman untuk semua indeks dalam database, gunakan kueri berikut. Kerapatan halaman dilaporkan dalam kolom avg_page_space_used_in_percent.

SELECT OBJECT_SCHEMA_NAME(ips.object_id) AS schema_name,
       OBJECT_NAME(ips.object_id) AS object_name,
       i.name AS index_name,
       i.type_desc AS index_type,
       ips.avg_page_space_used_in_percent,
       ips.avg_fragmentation_in_percent,
       ips.page_count,
       ips.alloc_unit_type_desc,
       ips.ghost_record_count
FROM sys.dm_db_index_physical_stats(DB_ID(), default, default, default, 'SAMPLED') AS ips
INNER JOIN sys.indexes AS i 
ON ips.object_id = i.object_id
   AND
   ips.index_id = i.index_id
ORDER BY page_count DESC;

Jika ada indeks dengan jumlah halaman tinggi yang memiliki kerapatan halaman yang lebih rendah dari 60-70%, pertimbangkan untuk menyusun ulang atau menata ulang indeks ini sebelum menyusutkan file data.

Catatan

Untuk database yang lebih besar, kueri untuk menentukan kepadatan halaman mungkin membutuhkan waktu lama (jam) untuk diselesaikan. Selain itu, menyusun ulang atau menata ulang indeks yang besar juga memerlukan waktu dan penggunaan sumber daya yang signifikan. Terdapat pertukaran kondisi antara menghabiskan waktu tambahan untuk meningkatkan kepadatan halaman di satu sisi, dan mengurangi durasi penyusutan serta mencapai penghematan ruang yang lebih tinggi di sisi yang lain.

Jika ada beberapa indeks dengan kepadatan halaman rendah, Anda mungkin dapat membangunnya kembali secara paralel pada beberapa sesi database untuk mempercepat proses. Namun, pastikan Anda tidak mendekati batas sumber daya database dengan melakukannya, dan meninggalkan headroom sumber daya yang memadai untuk beban kerja aplikasi yang mungkin berjalan. Pantau konsumsi sumber daya (CPU, Data IO, Log IO) di portal Microsoft Azure atau gunakan tampilan sys.dm_db_resource_stats dan mulai penyusunan ulang paralel tambahan hanya jika penggunaan sumber daya pada setiap dimensi ini tetap lebih rendah dari 100%. Jika penggunaan CPU, Data IO, atau Log IO adalah 100%, Anda dapat meningkatkan skala database agar memiliki lebih banyak inti CPU dan meningkatkan throughput IO. Ini mungkin memungkinkan pembangunan kembali paralel tambahan untuk menyelesaikan proses lebih cepat.

Contoh perintah pembangunan ulang indeks

Berikut ini adalah contoh perintah untuk membangun kembali indeks dan meningkatkan kepadatan halamannya, menggunakan pernyataan ALTER INDEX :

ALTER INDEX [index_name] ON [schema_name].[table_name] 
REBUILD WITH (FILLFACTOR = 100, MAXDOP = 8, 
ONLINE = ON (WAIT_AT_LOW_PRIORITY (MAX_DURATION = 5 MINUTES, ABORT_AFTER_WAIT = NONE)), 
RESUMABLE = ON);

Perintah ini memulai penyusunan ulang indeks yang dapat dilanjutkan dan bersifat online. Langkah ini memungkinkan beban kerja bersama terus menggunakan tabel tersebut saat penyusunan ulang sedang berlangsung, dan memungkinkan Anda melanjutkan penyusunan ulang jika terganggu karena alasan apa pun. Namun, jenis penyusunan ulang ini lebih lambat daripada menyusun ulang secara offline, yang memblokir akses ke tabel. Jika tidak ada beban kerja lain yang perlu mengakses tabel selama penyusunan ulang, atur opsi ONLINE dan RESUMABLE ke OFF serta hapus klausul WAIT_AT_LOW_PRIORITY.

Untuk informasi lebih lanjut terkait pemeliharaan indeks, lihat Mengoptimalkan pemeliharaan indeks untuk meningkatkan performa kueri dan mengurangi konsumsi sumber daya.

Menyusutkan beberapa file data

Seperti yang disebutkan sebelumnya, penyusutan dengan pergerakan data merupakan proses yang berjalan lama. Jika database memiliki beberapa file data, Anda dapat mempercepat prosesnya dengan menyusutkan beberapa file data secara paralel. Anda melakukan ini dengan membuka beberapa sesi database, dan menggunakan DBCC SHRINKFILE di setiap sesi dengan nilai file_id yang berbeda. Sama dengan menyusun ulang indeks sebelumnya, pastikan Anda memiliki headroom sumber daya yang memadai (CPU, Data IO, Log IO) sebelum memulai setiap perintah penyusutan paralel baru.

Contoh perintah berikut menyusutkan file data dengan file_id 4, mencoba mengurangi ukuran yang dialokasikan menjadi 52.000 MB dengan memindahkan halaman dalam file:

DBCC SHRINKFILE (4, 52000);

Jika Anda ingin mengurangi ruang yang dialokasikan untuk file ke ukuran paling minimum, jalankan pernyataan tanpa menentukan ukuran target:

DBCC SHRINKFILE (4);

Jika beban kerja berjalan bersamaan dengan penyusutan, beban kerja mungkin mulai menggunakan ruang penyimpanan yang dikosongkan dengan menyusut sebelum penyusutan selesai dan memotong file. Dalam hal ini, penyusutan tidak akan dapat mengurangi ruang yang dialokasikan ke target yang ditentukan.

Anda dapat menguranginya dengan menyusutkan setiap file dalam langkah-langkah yang lebih kecil. Artinya, dalam perintah DBCC SHRINKFILE, Anda menetapkan target yang sedikit lebih kecil daripada ruang yang dialokasikan saat ini untuk file, seperti yang terlihat dalam hasil kueri penggunaan ruang garis besar. Misalnya, jika ruang yang dialokasikan untuk file dengan file_id 4 adalah 200.000 MB, dan Anda ingin menyusutkannya menjadi 100.000 MB, Anda dapat terlebih dahulu menetapkan target ke 170.000 MB:

DBCC SHRINKFILE (4, 170000);

Setelah perintah selesai, file akan terpotong dan mengurangi ukuran yang dialokasikan menjadi 170.000 MB. Lalu Anda dapat mengulangi perintah ini, mengatur target menjadi 140.000 MB terlebih dahulu, lalu ke 110.000 MB, dst., hingga file menyusut ke ukuran yang diinginkan. Jika perintah selesai tetapi file tidak terpotong, gunakan langkah-langkah yang lebih kecil, misalnya 15.000 MB daripada 30.000 MB.

Untuk memantau kemajuan penyusutan untuk semua sesi penyusutan yang berjalan bersamaan, Anda dapat menggunakan kueri berikut:

SELECT command,
       percent_complete,
       status,
       wait_resource,
       session_id,
       wait_type,
       blocking_session_id,
       cpu_time,
       reads,
       CAST(((DATEDIFF(s,start_time, GETDATE()))/3600) AS varchar) + ' hour(s), '
                     + CAST((DATEDIFF(s,start_time, GETDATE())%3600)/60 AS varchar) + 'min, '
                     + CAST((DATEDIFF(s,start_time, GETDATE())%60) AS varchar) + ' sec' AS running_time
FROM sys.dm_exec_requests AS r
LEFT JOIN sys.databases AS d
ON r.database_id = d.database_id
WHERE r.command IN ('DbccSpaceReclaim','DbccFilesCompact','DbccLOBCompact','DBCC');

Catatan

Kemajuan penyusutan dapat bersifat non-linier, dan nilai dalam percent_complete kolom mungkin tetap tidak berubah untuk jangka waktu yang lama, meskipun penyusutan masih berlangsung.

Setelah penyusutan selesai untuk semua file data, jalankan kembali kueri penggunaan ruang (atau cek masuk portal Microsoft Azure) untuk menentukan pengurangan yang dihasilkan dalam ukuran penyimpanan yang dialokasikan. Jika masih ada perbedaan besar antara ruang yang digunakan dan ruang yang dialokasikan, Anda dapat membangun kembali indeks seperti yang dijelaskan sebelumnya. Ini untuk sementara dapat meningkatkan ruang yang dialokasikan lebih lanjut, namun menyusutkan file data lagi setelah membangun kembali indeks akan mengakibatkan pengurangan ruang yang lebih dalam dalam dalam ruang yang dialokasikan.

Kesalahan sementara selama penyusutan

Terkadang, perintah penyusutan dapat gagal dengan berbagai kesalahan seperti batas waktu dan kebuntuan. Secara umum, kesalahan ini hanya sementara, dan tidak terjadi lagi jika perintah yang sama diulang. Jika penyusutan gagal dengan kesalahan, kemajuan yang telah dilakukannya selama ini dalam memindahkan halaman data dipertahankan, dan perintah penyusutan yang sama dapat dijalankan lagi untuk melanjutkan penyusutan file.

Contoh skrip berikut ini menunjukkan cara menjalankan penyusutan dalam perulangan coba lagi untuk secara otomatis mencoba kembali hingga jumlah waktu yang dapat dikonfigurasi saat kesalahan waktu habis atau kesalahan kebuntuan terjadi. Pendekatan coba lagi ini berlaku untuk banyak kesalahan lain yang mungkin terjadi selama penyusutan.

DECLARE @RetryCount int = 3; -- adjust to configure desired number of retries
DECLARE @Delay char(12);

-- Retry loop
WHILE @RetryCount >= 0
BEGIN

BEGIN TRY

DBCC SHRINKFILE (1); -- adjust file_id and other shrink parameters

-- Exit retry loop on successful execution
SELECT @RetryCount = -1;

END TRY
BEGIN CATCH
    -- Retry for the declared number of times without raising an error if deadlocked or timed out waiting for a lock
    IF ERROR_NUMBER() IN (1205, 49516) AND @RetryCount > 0
    BEGIN
        SELECT @RetryCount -= 1;

        PRINT CONCAT('Retry at ', SYSUTCDATETIME());

        -- Wait for a random period of time between 1 and 10 seconds before retrying
        SELECT @Delay = '00:00:0' + CAST(CAST(1 + RAND() * 8.999 AS decimal(5,3)) AS varchar(5));
        WAITFOR DELAY @Delay;
    END
    ELSE -- Raise error and exit loop
    BEGIN
        SELECT @RetryCount = -1;
        THROW;
    END
END CATCH
END;

Selain batas waktu dan kebuntuan, penyusutan dapat mengalami kesalahan karena masalah tertentu yang diketahui.

Berikut adalah kesalahan yang ditampilkan dan langkah mitigasinya:

Nomor kesalahan: 49503, pesan kesalahan: %.*ls: Halaman %d:%d tidak dapat dipindahkan karena halaman tersebut adalah halaman penyimpanan versi persisten luar baris. Alasan penahanan halaman: %ls. Stempel waktu penahanan halaman: %I64d.

Kesalahan ini terjadi ketika terdapat transaksi aktif yang berjalan lama yang telah menghasilkan versi baris dalam penyimpanan versi persisten (PVS). Halaman yang berisi versi baris ini tidak dapat dipindahkan dengan penyusutan, karenanya halaman tidak dapat membuat kemajuan dan gagal dengan kesalahan ini.

Untuk mengatasinya, Anda harus menunggu hingga transaksi yang berjalan lama ini selesai. Atau, Anda dapat mengidentifikasi dan mengakhiri transaksi jangka panjang ini, tetapi ini dapat memengaruhi aplikasi Anda jika tidak menangani kegagalan transaksi dengan anggun. Salah satu cara untuk menemukan transaksi yang berjalan lama adalah dengan menjalankan kueri berikut dalam database tempat Anda menjalankan perintah penyusutan:

-- Transactions sorted by duration
SELECT st.session_id,
       dt.database_transaction_begin_time,
       DATEDIFF(second, dt.database_transaction_begin_time, CURRENT_TIMESTAMP) AS transaction_duration_seconds,
       dt.database_transaction_log_bytes_used,
       dt.database_transaction_log_bytes_reserved,
       st.is_user_transaction,
       st.open_transaction_count,
       ib.event_type,
       ib.parameters,
       ib.event_info
FROM sys.dm_tran_database_transactions AS dt
INNER JOIN sys.dm_tran_session_transactions AS st
ON dt.transaction_id = st.transaction_id
OUTER APPLY sys.dm_exec_input_buffer(st.session_id, default) AS ib
WHERE dt.database_id = DB_ID()
ORDER BY transaction_duration_seconds DESC;

Anda dapat mengakhiri transaksi menggunakan perintah KILL dan menentukan nilai session_id terkait dari hasil kueri:

KILL 4242; -- replace 4242 with the session_id value from query results

Perhatian

Mengakhiri transaksi dapat berdampak negatif pada beban kerja.

Setelah transaksi yang berjalan lama telah dihentikan atau selesai, proses di latar belakang internal akan membersihkan versi baris yang tidak lagi diperlukan setelah beberapa waktu. Anda dapat memantau ukuran PVS untuk mengukur kemajuan pembersihan, menggunakan kueri berikut. Jalankan kueri dalam database tempat Anda menjalankan perintah penyusutan:

SELECT pvss.persistent_version_store_size_kb / 1024. / 1024 AS persistent_version_store_size_gb,
       pvss.online_index_version_store_size_kb / 1024. / 1024 AS online_index_version_store_size_gb,
       pvss.current_aborted_transaction_count,
       pvss.aborted_version_cleaner_start_time,
       pvss.aborted_version_cleaner_end_time,
       dt.database_transaction_begin_time AS oldest_transaction_begin_time,
       asdt.session_id AS active_transaction_session_id,
       asdt.elapsed_time_seconds AS active_transaction_elapsed_time_seconds
FROM sys.dm_tran_persistent_version_store_stats AS pvss
LEFT JOIN sys.dm_tran_database_transactions AS dt
ON pvss.oldest_active_transaction_id = dt.transaction_id
   AND
   pvss.database_id = dt.database_id
LEFT JOIN sys.dm_tran_active_snapshot_database_transactions AS asdt
ON pvss.min_transaction_timestamp = asdt.transaction_sequence_num
   OR
   pvss.online_index_min_transaction_timestamp = asdt.transaction_sequence_num
WHERE pvss.database_id = DB_ID();

Setelah ukuran PVS yang dilaporkan dalam kolom persistent_version_store_size_gb benar-benar berkurang dibandingkan ukuran aslinya, penyusutan yang dijalankan kembali akan berhasil.

Nomor kesalahan: 5223, pesan kesalahan: %.*ls: Halaman kosong %d:%d tidak dapat dialokasi.

Kesalahan ini dapat terjadi jika ada operasi pemeliharaan indeks yang sedang berlangsung seperti ALTER INDEX. Coba lagi perintah penyusutan setelah operasi ini selesai.

Jika kesalahan ini tetap ada, indeks yang terkait mungkin harus disusun ulang. Untuk menemukan indeks yang akan disusun ulang, jalankan kueri berikut ini dalam database yang sama tempat Anda menjalankan perintah penyusutan:

SELECT OBJECT_SCHEMA_NAME(pg.object_id) AS schema_name,
       OBJECT_NAME(pg.object_id) AS object_name,
       i.name AS index_name,
       p.partition_number
FROM sys.dm_db_page_info(DB_ID(), <file_id>, <page_id>, default) AS pg
INNER JOIN sys.indexes AS i
ON pg.object_id = i.object_id
   AND
   pg.index_id = i.index_id
INNER JOIN sys.partitions AS p
ON pg.partition_id = p.partition_id;

Sebelum menjalankan kueri ini, ganti tempat penampung <file_id> dan <page_id> dengan nilai aktual dari pesan kesalahan yang Anda terima. Misalnya, jika pesan berisi Halaman kosong 1:62669 tidak dapat dialokasi, maka <file_id> adalah 1 dan <page_id> adalah 62669.

Susun ulang indeks yang diidentifikasi oleh kueri, lalu coba lagi perintah penyusutan.

Nomor kesalahan: 5201, pesan kesalahan: DBCC SHRINKDATABASE: ID File %d dari ID database %d telah dilewati karena file tidak memiliki cukup ruang kosong untuk mengklaim kembali.

Kesalahan ini berarti file data tidak dapat menyusut lebih lanjut. Anda dapat melanjutkan ke file data berikutnya.

Untuk informasi tentang ukuran maksimal database, lihat:

Bagikan melalui

Mengelola ruang file untuk database di Azure SQL Database

Gambaran Umum

Memantau penggunaan ruang file

Memahami jenis ruang penyimpanan untuk database

Mengkueri database tunggal untuk informasi ruang file

Memahami jenis ruang penyimpanan untuk kumpulan elastis

Mengkueri kumpulan elastis untuk informasi ruang penyimpanan

Ruang data kumpulan elastis yang digunakan

Ruang data kumpulan elastis dialokasikan dan ruang yang tidak digunakan dialokasikan

Ukuran maksimum data kumpulan elastis

Klaim kembali ruang yang dialokasikan dan tidak digunakan

Menyusutkan file data

Menyusutkan file log transaksi

Penyusutan otomatis

Pemeliharaan indeks setelah penyusutan

Menyusutkan database besar

Merekam garis besar penggunaan ruang

Memotong file data

Mengevaluasi kerapatan halaman indeks

Contoh perintah pembangunan ulang indeks

Menyusutkan beberapa file data

Kesalahan sementara selama penyusutan

Saran dan Komentar

Sumber Daya Tambahan:

Bagikan melalui

Mengelola ruang file untuk database di Azure SQL Database

Gambaran Umum

Memantau penggunaan ruang file

Memahami jenis ruang penyimpanan untuk database

Mengkueri database tunggal untuk informasi ruang file

Memahami jenis ruang penyimpanan untuk kumpulan elastis

Mengkueri kumpulan elastis untuk informasi ruang penyimpanan

Ruang data kumpulan elastis yang digunakan

Ruang data kumpulan elastis dialokasikan dan ruang yang tidak digunakan dialokasikan

Ukuran maksimum data kumpulan elastis

Klaim kembali ruang yang dialokasikan dan tidak digunakan

Menyusutkan file data

Menyusutkan file log transaksi

Penyusutan otomatis

Pemeliharaan indeks setelah penyusutan

Menyusutkan database besar

Merekam garis besar penggunaan ruang

Memotong file data

Mengevaluasi kerapatan halaman indeks

Contoh perintah pembangunan ulang indeks

Menyusutkan beberapa file data

Kesalahan sementara selama penyusutan

Konten terkait

Saran dan Komentar

Sumber Daya Tambahan: