Mengelola ruang file untuk database di Azure SQL Managed Instance

Berlaku untuk:Azure SQL Managed Instance

Artikel ini menjelaskan cara memantau dan mengelola file dalam database di Azure SQL Managed Instance. Ini mencakup cara memantau ukuran file database, menyusutkan log transaksi, memperbesar file log transaksi, dan mengontrol pertumbuhan file log transaksi.

Artikel ini berlaku untuk Azure SQL Managed Instance. Untuk informasi tentang mengelola ukuran file log transaksi di SQL Server, lihat Mengelola ukuran file log transaksi.

Memahami jenis ruang penyimpanan untuk database

Memahami jumlah ruang penyimpanan berikut 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 penambahan (penghapusan). Dalam beberapa kasus, ruang yang digunakan tidak berubah saat penyisipan atau penghapusan, tergantung pada jumlah dan pola data yang terlibat dalam operasi serta tingkat fragmentasi. 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 penyisipan di masa mendatang 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 data maksimal 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. Satuan hasil kueri adalah 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;

Memantau penggunaan ruang log

Pantau penggunaan ruang log dengan menggunakan sys.dm_db_log_space_usage. DMV ini mengembalikan informasi tentang jumlah ruang log yang saat ini digunakan, dan menunjukkan kapan log transaksi membutuhkan pemotongan.

Untuk informasi tentang ukuran file log saat ini, ukuran maksimumnya, dan opsi autogrow untuk file, gunakan kolom , , sizedan max_size untuk file log tersebut growthdi sys.database_files.

Metrik ruang penyimpanan yang ditampilkan di API metrik berbasis Azure Resource Manager hanya mengukur ukuran halaman data yang digunakan. Misalnya, lihat PowerShell Get-AZMetric.

Menyusutkan ukuran file log

Untuk mengurangi ukuran fisik file log fisik dengan menghapus ruang yang tidak digunakan, susutkan file log. Pengurangan hanya berdampak ketika file log transaksi mengandung ruang yang tidak terpakai. Jika file log penuh, kemungkinan karena transaksi terbuka, selidiki apa yang mencegah pemotongan log transaksi.

Perhatian

Operasi penyusutan tidak boleh dianggap sebagai operasi pemeliharaan rutin. File data dan log yang tumbuh karena operasi bisnis reguler berulang tidak memerlukan operasi penyusutan. Perintah shrink memengaruhi performa database saat berjalan, dan jika memungkinkan harus dijalankan selama periode penggunaan rendah. Menyusutkan file data tidak disarankan jika beban kerja aplikasi reguler menyebabkan file tumbuh ke ukuran yang sama yang dialokasikan lagi.

Waspadai potensi dampak performa negatif dari menyusutnya file database. Untuk informasi selengkapnya, lihat pemeliharaan indeks setelah menyusut. Dalam kasus yang jarang terjadi, pencadangan database otomatis dapat memengaruhi operasi penyusutan. Jika perlu, coba lagi operasi penyusutan.

Sebelum menyusutkan log transaksi, ingatlah faktor-faktor yang dapat menunda pemotongan log. Jika ruang penyimpanan diperlukan kembali setelah log diperkecil, log transaksi akan membesar lagi, yang menyebabkan overhead kinerja selama operasi pembesaran log. Untuk informasi selengkapnya, lihat bagian rekomendasi .

Anda hanya dapat menyusutkan file log saat database online, dan setidaknya satu file log virtual (VLF) gratis. Dalam beberapa kasus, menyusutkan log mungkin tidak dimungkinkan sampai setelah pemotongan log berikutnya.

Faktor-faktor, seperti transaksi yang berjalan lama, dapat menjaga VLF tetap aktif untuk jangka waktu yang lama, dapat membatasi penyusutan log, atau bahkan mencegah log menyusut sama sekali. Untuk informasi, lihat Faktor-faktor yang dapat menunda truncasi log.

Menyusutkan file log akan menghapus satu atau beberapa VLF yang tidak memiliki bagian dari log logis (yaitu, VLF yang tidak aktif). Saat Anda menyusutkan file log transaksi, VLF yang tidak aktif dihapus dari akhir file log untuk mengurangi log menjadi sekitar ukuran target.

Untuk informasi selengkapnya tentang operasi penyusutan, tinjau dokumentasi berikut:

Menyusutkan file log (tanpa menyusutkan file database)

Memantau kejadian penyusutan berkas log

  • Peristiwa Penyusutan Otomatis File Log Kelas.

Memantau ruang log

Pemeliharaan indeks setelah penyusutan

Setelah operasi penyusutan selesai terhadap file data, indeks dapat menjadi terfragmentasi. Fragmentasi mengurangi efektivitas pengoptimalan performa indeks untuk beban kerja tertentu, seperti kueri menggunakan pemindaian 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.

Mengevaluasi kerapatan halaman indeks

Jika memotong file data tidak menghasilkan pengurangan ruang yang memadai dalam ruang yang dialokasikan, Anda mungkin memutuskan untuk menyusutkan file data database untuk mengklaim kembali ruang yang tidak digunakan dari file-file tersebut. Namun, sebagai langkah opsional tetapi disarankan, Anda harus menentukan rata-rata kerapatan halaman untuk indeks dalam database terlebih dahulu. Untuk jumlah data yang sama, proses penyusutan selesai lebih cepat jika kepadatan halaman tinggi, karena halaman yang dipindahkan lebih sedikit. Jika kerapatan halaman rendah untuk beberapa indeks, pertimbangkan untuk melakukan pemeliharaan pada indeks ini guna meningkatkan kepadatan halaman sebelum menyusutkan file data. Langkah ini memungkinkan proses penyusutan mencapai pengurangan yang lebih besar pada ruang penyimpanan teralokasi.

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 dapat memakan waktu lama (jam) untuk diselesaikan. Selain itu, menyusun ulang atau menata ulang indeks yang besar juga memerlukan waktu dan penggunaan sumber daya yang signifikan. Ada kompromi antara meluangkan waktu tambahan untuk meningkatkan kepadatan halaman di satu sisi, dan mengurangi durasi penyusutan serta mendapatkan penghematan ruang yang lebih besar di sisi 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 saat melakukannya, serta sisakan cadangan sumber daya yang memadai untuk beban kerja aplikasi. Pantau konsumsi sumber daya (CPU, IO Data, Log IO) di portal Microsoft Azure, atau dengan menggunakan tampilan sys.dm_db_resource_stats . Mulai pembangunan kembali paralel lebih lanjut hanya jika pemanfaatan sumber daya pada masing-masing dimensi ini tetap jauh lebih rendah dari 100%. Jika pemanfaatan CPU, IO Data, atau IO Log berada di 100%, Anda dapat meningkatkan skala database untuk memiliki lebih banyak inti CPU dan meningkatkan throughput IO, memungkinkan pembangunan kembali yang lebih paralel 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. Jenis pembuatan ulang ini memungkinkan beban kerja serentak tetap menggunakan tabel selama pembuatan ulang berlangsung, dan memungkinkan Anda melanjutkan pembuatan ulang jika prosesnya terhenti 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.

Mengompres beberapa file data

Seperti yang disebutkan sebelumnya, pengurangan dengan pemindahan data merupakan proses yang memerlukan waktu lama. Jika database memiliki beberapa file data, Anda dapat mempercepat prosesnya dengan menyusutkan beberapa file data secara paralel. Anda melakukan operasi ini dengan membuka beberapa sesi database, dan menggunakan DBCC SHRINKFILE pada setiap sesi dengan nilai yang berbeda file_id . 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 proses shrink, beban kerja mungkin mulai menggunakan ruang penyimpanan yang dikosongkan oleh shrink sebelum proses tersebut selesai dan memotong file. Dalam hal ini, penyusutan tidak dapat mengurangi ruang yang dialokasikan ke target yang ditentukan.

Anda dapat mengurangi masalah ini dengan menyusutkan setiap file dalam langkah yang lebih kecil. Ini berarti bahwa dalam DBCC SHRINKFILE perintah, Anda menetapkan target yang sedikit lebih kecil dari ruang yang dialokasikan saat ini untuk file. 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 ini selesai, perintah ini memotong file dan mengurangi ukuran yang dialokasikan menjadi 170.000 MB. Anda kemudian dapat mengulangi perintah ini, mengatur target terlebih dahulu ke 140.000 MB, lalu ke 110.000 MB, dan sebagainya, hingga file diciutkan 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 bisa nonlinear, 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, gunakan kueri penggunaan ruang 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. Membangun ulang indeks dapat untuk sementara meningkatkan alokasi ruang lebih jauh, namun mengecilkan file data lagi setelah membangun ulang indeks seharusnya menghasilkan pengurangan alokasi ruang yang lebih besar.

Menambah ukuran file log

Di Azure SQL Managed Instance, Anda dapat menambahkan ruang ke file log dengan memperbesar file log yang ada, jika ruang disk mengizinkan. Menambahkan file log ke database tidak didukung. Satu file log transaksi sudah memadai, kecuali jika ruang log mulai habis dan ruang disk pada volume yang menyimpan file log juga mulai habis.

Untuk memperbesar ukuran file log, gunakan klausa MODIFY FILE dari pernyataan ALTER DATABASE, dan tentukan sintaks SIZE dan MAXSIZE. Untuk informasi selengkapnya, lihat ALTER DATABASE (Transact-SQL) File and Filegroup options.

Untuk informasi selengkapnya, lihat Rekomendasi.

Mengontrol pertumbuhan file log transaksi

Untuk mengelola pertumbuhan file log transaksi, gunakan pernyataan ALTER DATABASE (Transact-SQL) File and Filegroup options. Perhatikan opsi berikut:

  • SIZE Gunakan opsi untuk mengubah ukuran file saat ini di unit KB, MB, GB, dan TB.
  • Gunakan opsi FILEGROWTH untuk mengubah inkremen pertumbuhan. Nilai 0 menunjukkan bahwa pertumbuhan otomatis diatur ke nonaktif dan tidak ada ruang tambahan yang diizinkan.
  • MAXSIZE Gunakan opsi untuk mengontrol ukuran maksimum file log di unit KB, MB, GB, dan TB atau untuk mengatur pertumbuhan ke UNLIMITED.

Rekomendasi

Saat Anda bekerja dengan file log transaksi, pertimbangkan rekomendasi berikut:

  • Atur kenaikan otomatis (autogrow) log transaksi, sebagaimana dikonfigurasi oleh opsi FILEGROWTH, agar cukup besar untuk memenuhi kebutuhan transaksi dalam beban kerja Anda. Atur inkremen pertumbuhan file pada file log agar cukup besar untuk menghindari perluasan yang terlalu sering. Anda dapat menentukan ukuran log transaksi dengan tepat dengan memantau jumlah ruang log yang digunakan selama:

    • Waktu yang diperlukan untuk menjalankan pencadangan penuh, karena pencadangan log tidak dapat terjadi sampai selesai.
    • Waktu yang diperlukan untuk operasi pemeliharaan indeks terbesar.
    • Waktu yang diperlukan untuk menjalankan batch terbesar dalam database.

  • Atur pertumbuhan otomatis untuk data dan file log menggunakan FILEGROWTH opsi di size alih-alih percentage, untuk memungkinkan kontrol yang lebih baik pada rasio pertumbuhan, karena persentase adalah jumlah yang terus bertambah.

    • Di Azure SQL Managed Instance, inisialisasi file instan dapat menguntungkan peristiwa pertumbuhan log transaksi hingga 64 MB. Kenaikan ukuran pertumbuhan otomatis default untuk database baru adalah 64 MB. Peristiwa pertumbuhan otomatis file log transaksi yang lebih besar dari 64 MB tidak dapat memperoleh manfaat dari inisialisasi file instan.
    • Sebagai praktik terbaik, jangan tetapkan FILEGROWTH nilai opsi di atas 1.024 MB untuk log transaksi.

  • Hindari mengatur kenaikan pertumbuhan otomatis kecil karena dapat menghasilkan terlalu banyak VLF kecil dan mengurangi performa. Untuk menentukan pembagian VLF optimal berdasarkan ukuran log transaksi saat ini dari semua database dalam instans tertentu, dan pertumbuhan yang diperlukan untuk mencapai ukuran yang diinginkan, lihat skrip ini untuk menganalisis dan memperbaiki VLF, yang disediakan oleh SQL Tiger Team.

  • Hindari mengatur kenaikan pertumbuhan otomatis yang besar karena dapat menyebabkan dua masalah:

    • Basis data dapat terhenti sementara saat ruang baru dialokasikan, yang berpotensi menyebabkan kueri kehabisan waktu.
    • Ini dapat menghasilkan jumlah VLF yang terlalu sedikit dan berukuran besar, serta juga dapat memengaruhi kinerja. Untuk menentukan pembagian VLF optimal berdasarkan ukuran log transaksi saat ini dari semua database dalam instans tertentu, dan pertumbuhan yang diperlukan untuk mencapai ukuran yang diinginkan, lihat skrip ini untuk menganalisis dan memperbaiki VLF, yang disediakan oleh SQL Tiger Team.

  • Bahkan dengan autogrow diaktifkan, Anda dapat menerima pesan bahwa log transaksi penuh jika tidak dapat tumbuh cukup cepat untuk memenuhi kebutuhan kueri Anda. Untuk informasi selengkapnya tentang mengubah peningkatan pertumbuhan, lihat opsi ALTER DATABASE (Transact-SQL) File dan Filegroup.

  • Anda dapat mengatur file log untuk menyusut secara otomatis. Namun praktik ini tidak disarankan, dan properti database auto_shrink diatur ke FALSE secara default. Jika Anda mengatur auto_shrink ke TRUE, penyusutan otomatis mengurangi ukuran file hanya ketika lebih dari 25 persen ruangnya tidak digunakan.

Konten terkait

  • Last updated on