Sql Server

Cara Memeriksa Penggunaan Ruang Disk untuk Tabel di SQL Server Menggunakan T-SQL

Saat mengelola database, memahami berapa banyak ruang disk yang dikonsumsi oleh setiap tabel sangat penting untuk optimisasi dan perencanaan kapasitas. Meskipun banyak pengguna mungkin sudah terbiasa memeriksa properti tabel melalui antarmuka grafis SQL Server Management Studio (SSMS), mengetahui cara melakukan tugas yang sama menggunakan T-SQL bisa sangat berguna, terutama untuk otomatisasi dan tugas manajemen database yang lebih lanjut.

Dalam posting blog ini, kita akan berjalan melalui metode sederhana untuk menentukan ruang disk yang digunakan oleh tabel di SQL Server melalui T-SQL. Pendekatan ini tidak hanya meningkatkan efisiensi Anda tetapi juga memberikan kesempatan untuk menyelami lebih dalam ke dalam skrip SQL.

Ikhtisar Solusi

Kita akan memanfaatkan tabel sementara yang dipasangkan dengan prosedur tersimpan sistem sp_spaceused untuk mengumpulkan informasi tentang berbagai tabel di database Anda. Berikut adalah rincian dari apa yang akan kita capai:

  1. Buat tabel sementara untuk menampung informasi ukuran.
  2. Jalankan perintah sp_spaceused untuk mengumpulkan data ukuran dari semua tabel.
  3. Analisis data yang dikumpulkan menjadi metrik yang dapat dibaca.
  4. Tampilkan output yang disusun berdasarkan ukuran yang dicadangkan.

Mari kita mulai!

Langkah 1: Buat Tabel Sementara

Langkah pertama adalah mendefinisikan dua tabel sementara yang akan membantu kita menyimpan detail ukuran yang akan kita ekstrak. Di SQL Server, tabel sementara diawali dengan tanda #.

CREATE TABLE #tmpSizeChar (
     table_name sysname ,
     row_count int,
     reserved_size varchar(50),
     data_size varchar(50),
     index_size varchar(50),
     unused_size varchar(50))                              

CREATE TABLE #tmpSizeInt (
     table_name sysname ,
     row_count int,
     reserved_size_KB int,
     data_size_KB int,
     index_size_KB int,
     unused_size_KB int)

Penjelasan:

  • #tmpSizeChar: Menyimpan hasil dalam format karakter, yang mempertahankan ukuran dengan satuan (misalnya, MB, KB).
  • #tmpSizeInt: Menyimpan hasil dalam format integer untuk memudahkan perhitungan.

Langkah 2: Kumpulkan Data Ukuran dari Tabel

Kita menggunakan prosedur tersimpan sp_msforeachtable untuk menjalankan perintah sp_spaceused pada setiap tabel di database. Perintah ini menyediakan informasi terperinci tentang penggunaan ruang setiap tabel.

SET NOCOUNT ON
INSERT #tmpSizeChar
EXEC sp_msforeachtable 'sp_spaceused ''?'''

Penjelasan:

  • NOCOUNT ON: Mencegah pesan yang menunjukkan jumlah baris yang dipengaruhi oleh pernyataan T-SQL dikembalikan, yang meningkatkan performa untuk skrip dengan banyak pernyataan.

Langkah 3: Analisis Data yang Dihimpun

Setelah mengumpulkan data, kita sekarang perlu mengubah data karakter menjadi nilai integer yang sesuai untuk analisis.

INSERT INTO #tmpSizeInt (
        table_name,
        row_count,
        reserved_size_KB,
        data_size_KB,
        index_size_KB,
        unused_size_KB
        )
SELECT  [table_name],
        row_count,
        CAST(SUBSTRING(reserved_size, 0, PATINDEX('% %', reserved_size)) AS int) reserved_size,
        CAST(SUBSTRING(data_size, 0, PATINDEX('% %', data_size)) AS int) data_size,
        CAST(SUBSTRING(index_size, 0, PATINDEX('% %', index_size)) AS int) index_size,
        CAST(SUBSTRING(unused_size, 0, PATINDEX('% %', unused_size)) AS int) unused_size
FROM #tmpSizeChar

Penjelasan:

  • Langkah ini mengekstrak bagian numerik dari ukuran yang dicadangkan dan mengubahnya menjadi integer untuk perhitungan yang dapat diandalkan. Ini pada dasarnya menghapus karakter (seperti KB, MB) dari string ukuran.

Langkah 4: Tampilkan Hasil

Akhirnya, kita mengambil dan menampilkan hasil yang diringkas dari tabel sementara berukuran integer kita.

SELECT * FROM #tmpSizeInt
ORDER BY reserved_size_KB DESC

Penjelasan:

  • Perintah SQL ini mengambil semua catatan dari #tmpSizeInt dan mengurutkannya berdasarkan kolom reserved_size_KB, memungkinkan Anda melihat tabel mana yang mengonsumsi ruang disk paling banyak.

Kesimpulan

Dengan mengikuti langkah-langkah yang diuraikan di atas, Anda sekarang memiliki cara terstruktur untuk mengetahui berapa banyak ruang disk yang digunakan oleh tabel di SQL Server dengan T-SQL. Metode ini dapat sangat berguna bagi administrator database dan pengembang yang ingin menjaga performa optimal dan melacak penggunaan sumber daya di seluruh lingkungan SQL Server mereka.

Sekarang, Anda dapat mengganti klik GUI dengan pendekatan baris perintah yang kuat, yang secara fundamental meningkatkan keterampilan manajemen database Anda!