Sql Server

Comment Vérifier l’Utilisation de l’Espace Disque pour les Tables dans SQL Server en Utilisant T-SQL

Lors de la gestion d’une base de données, il est essentiel de comprendre combien d’espace disque chaque table consomme pour l’optimisation et la planification de la capacité. Bien que de nombreux utilisateurs soient habitués à vérifier les propriétés des tables via l’interface graphique du SQL Server Management Studio (SSMS), savoir effectuer la même tâche en utilisant T-SQL peut être extrêmement bénéfique, surtout pour l’automatisation et les tâches avancées de gestion de bases de données.

Dans ce billet de blog, nous allons détailler une méthode simple pour déterminer l’espace disque occupé par les tables dans SQL Server à l’aide de T-SQL. Cette approche non seulement améliore votre efficacité, mais elle offre également une opportunité de plonger plus profondément dans le script SQL.

Aperçu de la Solution

Nous allons utiliser des tables temporaires accompagnées de la procédure stockée système sp_spaceused pour recueillir des informations sur les différentes tables de votre base de données. Voici un aperçu de ce que nous allons accomplir :

  1. Créer des tables temporaires pour stocker les informations de taille.
  2. Exécuter la commande sp_spaceused pour recueillir les données de taille de toutes les tables.
  3. Analyser les données collectées en métriques lisibles.
  4. Afficher la sortie triée par taille réservée.

Commençons !

Étape 1 : Créer des Tables Temporaires

La première étape consiste à définir deux tables temporaires qui nous aideront à stocker les détails de taille que nous allons extraire. Dans SQL Server, les tables temporaires sont préfixées par un signe #.

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)

Explication :

  • #tmpSizeChar : Contient les résultats au format caractère, ce qui conserve la taille avec les unités (par exemple, Mo, Ko).
  • #tmpSizeInt : Stocke les résultats au format entier pour faciliter les calculs.

Étape 2 : Rassembler les Données de Taille des Tables

Nous utilisons la procédure stockée sp_msforeachtable pour exécuter la commande sp_spaceused sur chaque table de la base de données. Cette commande fournit des informations détaillées sur l’utilisation de l’espace de chaque table.

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

Explication :

  • NOCOUNT ON : Empêche le message indiquant le nombre de lignes affectées par une instruction T-SQL d’être renvoyé, ce qui améliore les performances pour les scripts contenant plusieurs instructions.

Étape 3 : Analyser les Données Collectées

Ayant rassemblé les données, nous devons maintenant transformer les données de type caractère en valeurs entières appropriées pour l’analyse.

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

Explication :

  • Cette étape extrait la partie numérique des tailles réservées et les convertit en entiers pour des calculs fiables. Elle élimine essentiellement les caractères (comme Ko, Mo) des chaînes de taille.

Étape 4 : Afficher les Résultats

Enfin, nous récupérons et affichons les résultats résumés de notre table temporaire de taille entière.

SELECT * FROM #tmpSizeInt
ORDER BY reserved_size_KB DESC

Explication :

  • Cette commande SQL récupère tous les enregistrements de #tmpSizeInt et les ordonne par la colonne reserved_size_KB, vous permettant ainsi de voir quelles tables consomment le plus d’espace disque.

Conclusion

En suivant les étapes décrites ci-dessus, vous disposez maintenant d’une méthode structurée pour déterminer combien d’espace disque une table utilise dans SQL Server avec T-SQL. Cette méthode peut être particulièrement utile pour les administrateurs de bases de données et les développeurs cherchant à maintenir des performances optimales et à suivre l’utilisation des ressources dans leurs environnements SQL Server.

Vous pouvez désormais remplacer les clics de l’interface graphique par une approche puissante en ligne de commande, améliorant fondamentalement vos compétences en gestion de bases de données !