Sql Server

Wie man den Speicherplatzverbrauch von Tabellen in SQL Server mit T-SQL überprüft

Bei der Verwaltung einer Datenbank ist es wichtig, zu verstehen, wie viel Speicherplatz jede Tabelle verbraucht, um die Optimierung und Kapazitätsplanung zu gewährleisten. Während viele Benutzer es möglicherweise gewohnt sind, die Tabellen Eigenschaften über die grafische Benutzeroberfläche SQL Server Management Studio (SSMS) zu überprüfen, kann es äußerst vorteilhaft sein, zu wissen, wie man dieselbe Aufgabe mit T-SQL durchführen kann, insbesondere für Automatisierung und komplexe Datenbankverwaltungsaufgaben.

In diesem Blogbeitrag werden wir eine einfache Methode durchgehen, um den Speicherplatz zu bestimmen, der von Tabellen in SQL Server über T-SQL belegt wird. Dieser Ansatz verbessert nicht nur Ihre Effizienz, sondern bietet auch die Möglichkeit, tiefer in die SQL-Skripterstellung einzutauchen.

Übersicht der Lösung

Wir werden temporäre Tabellen zusammen mit dem System gespeicherten Verfahren sp_spaceused verwenden, um Informationen über die verschiedenen Tabellen in Ihrer Datenbank zu sammeln. Hier ist eine Übersicht dessen, was wir erreichen werden:

  1. Erstellen von temporären Tabellen, um Größeninformationen zu speichern.
  2. Ausführen des Befehls sp_spaceused, um Größe-Daten von allen Tabellen zu sammeln.
  3. Analysieren der gesammelten Daten in lesbare Metriken.
  4. Anzeigen der Ausgaben, sortiert nach reservierter Größe.

Lassen Sie uns anfangen!

Schritt 1: Temporäre Tabellen erstellen

Der erste Schritt besteht darin, zwei temporäre Tabellen zu definieren, die uns helfen, die Größendetails zu speichern, die wir extrahieren werden. In SQL Server haben temporäre Tabellen das Präfix #.

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)

Erläuterung:

  • #tmpSizeChar: Enthält Ergebnisse im Zeichenformat, das die Größe mit Einheiten beibehält (z.B. MB, KB).
  • #tmpSizeInt: Speichert Ergebnisse im Ganzzahlenformat, um die Berechnung zu erleichtern.

Schritt 2: Größendaten von Tabellen sammeln

Wir verwenden das gespeicherte Verfahren sp_msforeachtable, um den Befehl sp_spaceused auf jeder Tabelle in der Datenbank auszuführen. Dieser Befehl liefert detaillierte Informationen zur Raumnutzung jeder Tabelle.

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

Erläuterung:

  • NOCOUNT ON: Verhindert, dass die Nachricht zurückgegeben wird, die die Anzahl der von einer T-SQL-Anweisung betroffenen Zeilen angibt, wodurch die Leistung bei Skripten mit mehreren Anweisungen verbessert wird.

Schritt 3: Gesammelte Daten analysieren

Nachdem wir die Daten gesammelt haben, müssen wir die Zeichen-Daten in Ganzzahlen umwandeln, die für die Analyse geeignet sind.

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

Erläuterung:

  • In diesem Schritt wird der numerische Teil der reservierten Größen extrahiert und in Ganzzahlen für zuverlässige Berechnungen umgewandelt. Es entfernt im Wesentlichen die Zeichen (wie KB, MB) aus den Größen-Strings.

Schritt 4: Ergebnisse anzeigen

Schließlich holen wir die zusammengefassten Ergebnisse aus unserer temporären Ganzzahl-Tabelle ab und zeigen sie an.

SELECT * FROM #tmpSizeInt
ORDER BY reserved_size_KB DESC

Erläuterung:

  • Dieser SQL-Befehl ruft alle Datensätze aus #tmpSizeInt ab und sortiert sie nach der Spalte reserved_size_KB, sodass Sie sehen können, welche Tabellen den meisten Speicherplatz beanspruchen.

Fazit

Durch die Befolgung der oben skizzierten Schritte haben Sie nun eine strukturierte Methode, um zu bestimmen, wie viel Speicherplatz eine Tabelle in SQL Server mit T-SQL verwendet. Diese Methode kann besonders nützlich für Datenbankadministratoren und Entwickler sein, die eine optimale Leistung aufrechterhalten und die Ressourcennutzung in ihren SQL Server-Umgebungen im Auge behalten möchten.

Nun können Sie die GUI-Klicks durch einen leistungsstarken Befehlszeilenansatz ersetzen, der Ihre Fähigkeiten im Datenbankmanagement grundlegend verbessert!