Lister la taille des tables SQL Server et l’espace disque est pratique pour analyser l’utilisation du disque pour chaque table. Trois façons différentes, mais similaires sont présentées ici. Le but, afficher les tables SQL Server et l’espace disque utilisé par chaque table. La troisième solution montre également le nombre de lignes pour chaque table.
Afficher les noms des tables SQL Server avec leur taille et l’espace disque utilisé
Consulter ces requêtes SQL Server pour afficher la liste des tables SQL Server et leur taille de différentes manières.
Cette première requête utilise différentes tables système liées avec une jointure, à savoir sys.tables, sys.indexes, sys.partitions, sys.allocation_units et sys.schemas.
Elle affiche le nom du schéma, le nom de la table, le nombre de lignes et l’espace disque. Soit l’espace total, utilisé et inutilisé pour chaque table d’une base SQL Server.
SELECT sch.name as SchemaName, tab.name as TableName, par.rows as RowCounts, sum(alc.total_pages) * 8 as TotalSpace, sum(alc.used_pages) * 8 as UsedSpace, (sum(alc.total_pages) - sum(alc.used_pages)) * 8 as UnusedSpace FROM sys.tables tab INNER JOIN sys.indexes ind ON tab.object_id = ind.object_id INNER JOIN sys.partitions par ON ind.object_id = par.object_id and ind.index_id = par.index_id INNER JOIN sys.allocation_units alc ON par.partition_id = alc.container_id LEFT OUTER JOIN sys.schemas sch ON tab.schema_id = sch.schema_id GROUP BY tab.name, sch.name, par.rows ORDER BY 1,2;
Pour aller plus loin, l’objectif est généralement de vérifier et de libérer de l’espace disque sur le serveur hébergeant la base de données. Typiquement, lorsque le disque dur du serveur commence à être plein. Alors cette requête affiche la dernière fois qu’une table a été accédée ou mise à jour.
C’est très utile avant la suppression. Par exemple, si une table n’a pas été consultée au cours des trois dernières années, elle n’est peut-être plus nécessaire.
L’utilisation de l’espace disque dans SQL Server est un sujet de maintenance classique
En effet, toute base de données grandit, parce que le journal grandit, les données grandissent, le fichier journal grandit, le fichier de sauvegarde grandit, etc.
Pour remédier à cela il est obligatoire de planifier des scripts de maintenance pour nettoyer les tables de log et supprimer régulièrement les données obsolètes, comme par exemple une période de cinq années glissantes pour rester en ligne.
Pour des raisons de conformité légale, selon le domaine d’activité, il peut être nécessaire de conserver et de sauvegarder les données plus longtemps.
Lister la taille des tables SQL Server avec la vue système information_schema.tables
Cette approche consiste à utiliser les données stockées dans le schéma information_schema concernant les tables. La requête Microsoft SQL utilise la table système information_schema.tables pour afficher tous les attributs et métadonnées de la base de données système.
Elle affiche les quatre colonnes disponibles dans la vue système des tables :
- TABLE_CATALOG est le nom de la base de données.
- TABLE_SCHEMA est le schéma hébergeant la table, par défaut il s’agit du propriétaire de la base de données (dbo).
- TABLE_NAME est le nom de la table.
- TABLE_TYPE est le type de la table.
Notez que nous filtrons la requête sur la vue pour afficher uniquement le type de table de base. D’autres types de tables peuvent également être affichés.
SELECT TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, TABLE_TYPE FROM information_schema.tables WHERE table_type='base table';
Lister toutes les tables SQL à partir de la table système sys.tables
La dernière requête utilise la table système sys.tables, elle est certainement la source de la vue système utilisée dans la première requête. Elle montre beaucoup plus d’informations et de métadonnées. Comme les données de création, la date de modification, et l’object_id réutilisable dans d’autres fonctions et procédures du système.
-- Utiliser la table système sys.tables SELECT * FROM sys.tables WHERE [type_desc] = 'USER_TABLE';
Pour aller plus loin dans le sujet de la maintenance des bases de données SQL Server, consultez ce court tutoriel pour libérer de l’espace disque avec un script SQL Shrink Database.
Soyez le premier à commenter