ApsaraDB RDS：Masalah Ruang Tidak Mencukupi untuk RDS for SQL Server

Menganalisis Penyebab

Total ruang data merupakan jumlah dari semua ukuran file data dan terdiri dari dua bagian: ruang yang dialokasikan dan ruang yang tidak dialokasikan.

• Ruang yang dialokasikan mencakup ruang yang digunakan dan tidak digunakan. Ruang yang tidak digunakan hanya dapat dialokasikan untuk catatan baru di tabel atau indeks yang sama dan tidak dapat dimanfaatkan langsung oleh objek basis data lainnya.

• Ruang yang tidak dialokasikan terdiri dari ekstent yang sepenuhnya belum dialokasikan. Setiap ekstent adalah ruang kontigu sebesar 64 KB. Ruang ini tidak terkait dengan objek basis data apa pun. Anda dapat melepaskan ruang ini dengan mengecilkan file.

Jika volume data terus bertambah, ruang yang tidak dialokasikan biasanya kecil. Dalam skenario ini, mengecilkan file secara langsung tidak efektif. Anda harus terlebih dahulu mengoptimalkan dan mengklaim kembali ruang yang telah dialokasikan sebelum mempertimbangkan untuk mengecilkan file.

Mengklaim Kembali Ruang Data

Metode berikut sering digunakan untuk mengklaim kembali ruang data:

• Mengarsipkan Data

  Hapus data yang jarang digunakan, seperti data historis lama, dari database. Anda juga dapat memigrasikan data tersebut ke instans basis data lain atau mengarsipkannya dalam format lain. Metode ini secara langsung mengurangi volume data dan ruang penyimpanan yang digunakan.

  Metode ini efektif untuk mengendalikan pertumbuhan ruang data tetapi memerlukan desain khusus untuk objek basis data dan logika aplikasi. Diperlukan kerja sama dari perancang dan pengembang aplikasi.

• Kompresi Data

  Instans yang menjalankan SQL Server 2016 atau yang lebih baru dan Instans Perusahaan yang menjalankan versi sebelum 2016 memiliki fitur kompresi data bawaan. Anda dapat mengaktifkan kompresi baris atau halaman pada tabel, indeks, atau partisi tunggal. Untuk informasi lebih lanjut, lihat Kompresi Data.

  Rasio kompresi data sangat bervariasi tergantung pada skema tabel, tipe data kolom, dan distribusi nilai. Rasio tersebut bisa berkisar antara 10% hingga 90%. SQL Server menyediakan prosedur tersimpan khusus, sp_estimate_data_compression_savings, untuk membantu Anda dengan cepat memperkirakan penghematan penyimpanan yang dihasilkan dari mengaktifkan kompresi pada tabel atau indeks tertentu.

  Catatan

  • Mengubah pengaturan kompresi pada tabel atau indeks adalah operasi Bahasa Definisi Data (DDL). Untuk tabel besar, operasi ini dapat menyebabkan kunci tabel jangka panjang dan memengaruhi bisnis Anda. Lakukan operasi ini selama jam-jam sepi.

  • Untuk instans RDS for SQL Server Enterprise, Anda dapat mengatur parameter ONLINE menjadi ON. Kemudian, Anda dapat menjalankan modifikasi dengan dampak minimal pada bisnis Anda.

  • Kompresi data meningkatkan overhead CPU. Evaluasilah dampak sesuai kebutuhan. Aktifkan kompresi data hanya pada tabel besar.

• Defragmentasi Indeks

  Tingkat fragmentasi indeks yang tinggi dapat memperlambat kueri dan meningkatkan penggunaan penyimpanan data. Mendefragmentasi indeks dapat meningkatkan kecepatan kueri dan melepaskan ruang penyimpanan yang tidak diperlukan.

  Lihat Tingkat Fragmentasi Indeks: Di Konsol RDS, buka halaman Autonomy Services > Performance Optimization. Klik tab Index Usage untuk melihat tingkat fragmentasi indeks untuk setiap tabel. Layanan otonomi memberikan saran untuk membangun ulang atau mengatur ulang indeks.

  Catatan

  • Tingkat fragmentasi indeks adalah persentase halaman indeks yang berdekatan secara logis tetapi tidak berdekatan secara fisik. Ini berbeda dari persentase ruang bebas dalam halaman indeks. Namun, indeks dengan tingkat fragmentasi tinggi kemungkinan besar juga memiliki persentase ruang yang dapat diklaim kembali yang tinggi.

  • Untuk menganalisis persentase rata-rata ruang bebas dalam halaman indeks, Anda dapat menjalankan kueri pada tampilan sistem sys.dm_db_index_physical_stats dalam mode SAMPLED atau DETAILED. Kemudian, periksa nilai di kolom avg_page_space_used_in_percent dari set hasil. Untuk informasi lebih lanjut, lihat sys.dm_db_index_physical_stats (Transact-SQL). Kueri ini membaca banyak halaman indeks dan dapat memengaruhi kinerja basis data. Lakukan operasi ini selama jam-jam sepi.

  • Operasi Rebuild Index

    Operasi ini memberikan optimasi yang lebih baik dan lebih efisien untuk indeks dengan tingkat fragmentasi tinggi. Secara default, operasi rebuild mengunci tabel yang sesuai. Untuk Instans Perusahaan, Anda dapat mengatur parameter ONLINE menjadi ON untuk menghindari kunci tabel jangka panjang.

    Penting

    Untuk indeks besar, operasi rebuild dapat menyebabkan peningkatan signifikan dalam ruang penyimpanan basis data dan ukuran log dalam jangka pendek. Sebelum Anda membangun ulang indeks, pastikan instans RDS memiliki ruang penyimpanan bebas yang setidaknya dua kali ukuran indeks yang sedang dibangun ulang.

    • Lihat Ruang Penyimpanan Instans RDS: Buka halaman Basic Information instans RDS. Di bagian Instance Resources, Anda dapat melihat total dan ruang penyimpanan yang digunakan oleh instans.

    • Jika ruang bebas tidak cukup, Anda dapat melakukan skala keluar penyimpanan. Setelah skala keluar berhasil, Anda tidak perlu memulai ulang instans. Sistem secara otomatis menerapkan ruang baru, dan perubahan tersebut berlaku segera.

    ALTER INDEX <IX_YourIndexName> ON <YourTableName> REBUILD WITH (ONLINE = ON);

    Setelah perintah dijalankan, sistem secara asinkron mengumpulkan statistik indeks lagi di latar belakang. Proses ini memakan waktu. Oleh karena itu, tingkat fragmentasi yang ditampilkan di halaman konsol mungkin tidak diperbarui segera. Anda dapat mengklik tombol Recollect untuk mengumpulkan data terbaru secara manual. Setelah pengumpulan data selesai, Anda dapat mengklik Export Script untuk mengunduh skrip ke mesin lokal Anda dan memastikan apakah tingkat fragmentasi indeks telah berkurang.

    

  • Operasi Reorganisasi Indeks

    Operasi ini lebih efisien untuk indeks dengan tingkat fragmentasi rendah, tetapi efek optimisasinya tidak sebesar rebuild.

Mengecilkan File Data

Metode pengklaiman kembali ruang data yang dijelaskan pada bagian sebelumnya umumnya dapat mengurangi penggunaan ruang data secara efektif. Jika ruang instans masih mengalami tekanan, Anda dapat menggunakan salah satu dari dua metode berikut:

• Metode 1: Mengecilkan file data dalam siklus mini-batch. Disarankan untuk mengecilkan file sebesar 5 GB dalam setiap iterasi. Berikut adalah contohnya:

  -- Skrip ini hanya berlaku untuk SQL Server 2012 dan versi yang lebih baru. Tentukan parameter berikut sebelum digunakan.
  DECLARE @dbName NVARCHAR(128) = 'YourDBName'  -- Nama database
  DECLARE @fileName NVARCHAR(128)  -- Nama file data
  DECLARE @targetSize INT = 2000   -- Ukuran target (MB)
  DECLARE @shrinkSize INT = 5120   -- Ukuran pengecilan dalam setiap iterasi loop (MB). Kami merekomendasikan 5 GB.
  DECLARE @currentSize INT         -- Ukuran saat ini
  DECLARE @freeSize INT            -- Ukuran ruang bebas
  DECLARE @usedSize INT            -- Ukuran ruang yang digunakan
  
  DECLARE @sql NVARCHAR(500)       
  DECLARE @waitTime INT = 10      -- Waktu tunggu setelah setiap operasi pengecilan (detik)
  
  -- Dapatkan nama file data
  SELECT @fileName = name
  FROM sys.master_files
  WHERE database_id = DB_ID(@dbName)
  AND type_desc = 'ROWS'
  
  
  -- Pengecilan dalam loop
  WHILE 1 = 1
  BEGIN
      -- Dapatkan ukuran saat ini, ukuran ruang bebas, dan ukuran ruang yang digunakan
      DECLARE @sql0 NVARCHAR(MAX) = N'
      USE [' + @dbName + '];
      SELECT
        @currentSize = (SUM(total_page_count) * 1.0 / 128),
        @freesize = (SUM(unallocated_extent_page_count) * 1.0 / 128)
      FROM sys.dm_db_file_space_usage
      WHERE database_id = DB_ID();'
  
      EXEC sp_executesql 
        @sql0,
        N'@currentSize INT OUTPUT, @freesize INT OUTPUT',
        @currentSize OUTPUT,
        @freesize OUTPUT
      
      PRINT 'Ukuran saat ini:' + CAST(@currentSize AS VARCHAR(10)) + 'MB'
      PRINT 'Ukuran bebas:' + CAST(@freeSize AS VARCHAR(10)) + 'MB'
  
      set @usedSize=@currentSize - @freeSize
      PRINT 'Ukuran yang digunakan:' + CAST(@usedSize AS VARCHAR(10)) + 'MB'
  
      -- Periksa apakah ukuran target lebih kecil dari ukuran ruang yang digunakan
      IF @targetSize <= @usedSize
      BEGIN
          PRINT 'Ukuran target terlalu kecil. Tentukan ukuran baru. Ukuran target tidak boleh lebih kecil dari: ' + CAST(@usedSize AS VARCHAR(20)) + 'MB'
          BREAK
      END
  
      
      -- Keluar jika ukuran target tercapai
      IF @currentSize <= @targetSize
      BEGIN
          PRINT 'Pengecilan selesai. Ukuran saat ini: ' + CAST(@currentSize AS VARCHAR(20)) + 'MB'
          BREAK
      END
      
      -- Hitung ukuran baru setelah operasi pengecilan ini
      DECLARE @newSize INT = @currentSize - @shrinkSize
      IF @newSize < @targetSize
          SET @newSize = @targetSize
          
      -- Jalankan operasi pengecilan
      SET @sql = 'USE [' + @dbName + '];DBCC SHRINKFILE (N''' + @fileName + ''', ' + CAST(@newSize AS VARCHAR(20)) + ')'
      PRINT 'Menjalankan pengecilan: ' + @sql
      EXEC (@sql)
      
      -- Tunggu beberapa saat dan lanjutkan
      PRINT 'Menunggu ' + CAST(@waitTime AS VARCHAR(10)) + ' detik untuk melanjutkan...'
      WAITFOR DELAY '00:00:10'
  END

• Metode 2: Jalankan perintah DBCC SHRINKFILE untuk mengecilkan file data tunggal secara langsung. Perintah ini melepaskan ruang yang tidak dialokasikan dalam file data ke sistem operasi. Untuk informasi lebih lanjut, lihat Mengecilkan Database dan DBCC SHRINKFILE (Transact-SQL).

  DBCC SHRINKFILE(<File ID>, <Target size in MB>)

  Klik untuk melihat contoh

  Pada gambar di atas, ukuran sebuah ekstent adalah 64 KB. Total ukuran file data adalah (1673344 × 64) / 1024 = 104.584 MB. Ruang yang dialokasikan adalah (1313432 × 64) / 1024 = 82.089,5 MB. Ini berarti ukuran total file data setelah kompresi tidak dapat kurang dari 82.089,5 MB. Untuk mengecilkan ukuran total file data menjadi 90.000 MB, Anda dapat menjalankan perintah berikut:

  DBCC SHRINKFILE(1, 90000)

FAQ

Lihat ruang log yang digunakan

Mengembalikan ruang log relatif sederhana. Anda dapat menggunakan perintah DBCC SQLPERF(LOGSPACE) atau DAS untuk melihat persentase ruang yang digunakan dalam file log database. Jika persentase ruang yang digunakan tinggi, memperkecil file log memiliki sedikit efek. Anda dapat menanyakan Tampilan sistem sys.databases. Periksa keluaran dari kolom log_reuse_wait dan log_reuse_wait_desc untuk menentukan mengapa ruang tidak dapat dikembalikan.

Perkecil log transaksi

Setelah operasi pemulihan selesai, Anda dapat membuka halaman Monitoring and Alarms dari Instans RDS untuk melihat status ruang log terbaru.

Analisis penyebab

Solusi

Sebuah RDS for SQL Server database mencakup file data dan file log. Metode untuk mengembalikan ruang untuk masing-masing adalah sebagai berikut:

Analisis penyebab

Ruang file lainnya mengacu pada ruang yang digunakan oleh file seperti sqlserver.other_size, mastersize, modelsize, dan msdbsize. File-file ini biasanya kecil, tetapi dalam beberapa kasus dapat menjadi sangat besar. Contohnya:

• Terdapat banyak log kesalahan (errorlog) dengan ukuran file mencapai beberapa gigabyte atau lebih.

• File dump memori dihasilkan secara otomatis selama pengecualian kritis.

Solusi

1. Buka tab Standard Monitoring pada halaman Monitoring and Alarms di instans RDS untuk melihat ruang yang digunakan oleh file-file ini. Untuk informasi lebih lanjut tentang metrik, lihat Lihat Pemantauan Standar.

   

2. Jika errorlog menggunakan sejumlah besar ruang, Anda dapat membuka halaman Log Management untuk membersihkan log kesalahan. Untuk informasi lebih lanjut, lihat Kelola Log.

   Catatan

   Jika file lainnya, seperti sqlserver.other_size, menggunakan terlalu banyak ruang, Anda dapat menghubungi dukungan teknis untuk bantuan dalam mengidentifikasi penyebab dan menyelesaikan masalah tersebut.