Parcourir les aide-mémoire · SQL Server

Technologie: SQL ServerCatégorie: Stockage et capacitéNiveau: IntermédiaireMis à jour

Vérifier la taille des tables SQL Server

Mesurez le nombre de lignes et l’espace de stockage des tables SQL Server, distinguez l’espace des données de celui des index et identifiez les tables qui font croître la base de données.

Sur cette page
  1. Problème
  2. Réponse rapide
  3. Code / commandes
  4. 1. Lister les tables par espace réservé
  5. 2. Séparer l’espace des données de celui des index
  6. 3. Afficher la taille des index d’une table
  7. 4. Afficher la taille par partition
  8. 5. Afficher les plus grandes tables par nombre de lignes
  9. 6. Comparer l’espace utilisé et l’espace réservé
  10. Interpréter le résultat
  11. Points à vérifier
  12. Un petit nombre de tables domine la base de données
  13. L’espace des index est proche de celui des données de base, voire supérieur
  14. Le nombre de lignes est élevé mais le stockage reste modéré
  15. Le stockage est élevé mais le nombre de lignes reste modéré
  16. Une partition contient la plupart des lignes
  17. Espace réservé inutilisé important
  18. Notes de production
  19. sys.dm_db_partition_stats repose sur les métadonnées et reste léger
  20. La taille des tables n’est pas la taille des fichiers de la base
  21. La compression modifie la relation entre lignes et stockage
  22. Les données LOB comptent
  23. Prudence avec le nettoyage automatisé
  24. Erreurs fréquentes
  25. 1. Compter les lignes une fois par index
  26. 2. Considérer l’espace réservé comme entièrement occupé par des données
  27. 3. Supprimer de gros index uniquement en fonction de leur taille
  28. 4. Réduire les fichiers parce qu’une table a été nettoyée
  29. 5. Comparer les tables uniquement par nombre de lignes
  30. 6. Ignorer la répartition entre partitions
  31. Étape suivante
  32. Pour aller plus loin

Problème

Une base de données SQL Server grossit, le stockage est sous pression, une opération de maintenance prend plus de temps que prévu, ou vous devez identifier les tables qui consomment le plus d’espace.

L’objectif est de répondre aux questions suivantes :

  • Quelles sont les tables les plus volumineuses ?
  • Combien de lignes contiennent-elles ?
  • Quelle part de l’espace est utilisée par les données et quelle part par les index ?
  • Quels objets font croître la base de données ?
  • Une table volumineuse est-elle normale, ou sa taille révèle-t-elle un problème de rétention, d’indexation ou de modèle de données ?

Réponse rapide

Pour obtenir un inventaire fiable et léger du nombre de lignes et de l’espace alloué, agrégez sys.dm_db_partition_stats par table.

SELECT
    s.name AS schema_name,
    t.name AS table_name,
    SUM(ps.row_count) AS row_count,
    CAST(SUM(ps.reserved_page_count) * 8.0 / 1024 AS decimal(18,2)) AS reserved_mb,
    CAST(SUM(ps.used_page_count) * 8.0 / 1024 AS decimal(18,2)) AS used_mb,
    CAST(
        SUM(
            CASE
                WHEN ps.index_id IN (0, 1)
                    THEN ps.in_row_data_page_count
                       + ps.lob_used_page_count
                       + ps.row_overflow_used_page_count
                ELSE 0
            END
        ) * 8.0 / 1024
        AS decimal(18,2)
    ) AS data_mb
FROM sys.dm_db_partition_stats AS ps
INNER JOIN sys.tables AS t
    ON t.object_id = ps.object_id
INNER JOIN sys.schemas AS s
    ON s.schema_id = t.schema_id
WHERE
    t.is_ms_shipped = 0
GROUP BY
    s.name,
    t.name
ORDER BY
    reserved_mb DESC;

Utilisez cette requête comme première passe lorsque vous devez comprendre où part l’espace de la base de données.

Code / commandes

1. Lister les tables par espace réservé

Utilisez cette requête pour obtenir rapidement un inventaire classé.

SELECT
    s.name AS schema_name,
    t.name AS table_name,
    SUM(ps.row_count) AS row_count,
    CAST(SUM(ps.reserved_page_count) * 8.0 / 1024 AS decimal(18,2)) AS reserved_mb,
    CAST(SUM(ps.used_page_count) * 8.0 / 1024 AS decimal(18,2)) AS used_mb
FROM sys.dm_db_partition_stats AS ps
INNER JOIN sys.tables AS t
    ON t.object_id = ps.object_id
INNER JOIN sys.schemas AS s
    ON s.schema_id = t.schema_id
WHERE
    t.is_ms_shipped = 0
GROUP BY
    s.name,
    t.name
ORDER BY
    reserved_mb DESC;

reserved_mb est généralement le premier tri le plus utile, car il indique l’espace que SQL Server a alloué à l’objet.


2. Séparer l’espace des données de celui des index

Cette requête est plus utile lorsqu’une table volumineuse pourrait en réalité l’être à cause de ses index.

WITH table_space AS
(
    SELECT
        ps.object_id,

        SUM(ps.row_count) AS row_count,

        SUM(
            CASE
                WHEN ps.index_id IN (0, 1)
                    THEN ps.in_row_data_page_count
                       + ps.lob_used_page_count
                       + ps.row_overflow_used_page_count
                ELSE 0
            END
        ) AS data_pages,

        SUM(
            CASE
                WHEN ps.index_id > 1
                    THEN ps.used_page_count
                ELSE 0
            END
        ) AS nonclustered_index_pages,

        SUM(ps.reserved_page_count) AS reserved_pages,
        SUM(ps.used_page_count) AS used_pages
    FROM sys.dm_db_partition_stats AS ps
    GROUP BY
        ps.object_id
)
SELECT
    s.name AS schema_name,
    t.name AS table_name,
    ts.row_count,
    CAST(ts.data_pages * 8.0 / 1024 AS decimal(18,2)) AS data_mb,
    CAST(ts.nonclustered_index_pages * 8.0 / 1024 AS decimal(18,2)) AS nonclustered_index_mb,
    CAST(ts.used_pages * 8.0 / 1024 AS decimal(18,2)) AS used_mb,
    CAST(ts.reserved_pages * 8.0 / 1024 AS decimal(18,2)) AS reserved_mb
FROM table_space AS ts
INNER JOIN sys.tables AS t
    ON t.object_id = ts.object_id
INNER JOIN sys.schemas AS s
    ON s.schema_id = t.schema_id
WHERE
    t.is_ms_shipped = 0
ORDER BY
    reserved_mb DESC;

Utilisez-la lorsque vous devez savoir si le stockage est dominé par la table de base ou par les index secondaires.


3. Afficher la taille des index d’une table

Une fois une table volumineuse repérée, examinez ses index en détail.

DECLARE @schema_name sysname = N'dbo';
DECLARE @table_name  sysname = N'YourTable';

SELECT
    i.index_id,
    i.name AS index_name,
    i.type_desc,
    SUM(ps.row_count) AS row_count,
    CAST(SUM(ps.used_page_count) * 8.0 / 1024 AS decimal(18,2)) AS used_mb,
    CAST(SUM(ps.reserved_page_count) * 8.0 / 1024 AS decimal(18,2)) AS reserved_mb
FROM sys.indexes AS i
INNER JOIN sys.dm_db_partition_stats AS ps
    ON ps.object_id = i.object_id
   AND ps.index_id = i.index_id
WHERE
    i.object_id = OBJECT_ID(QUOTENAME(@schema_name) + N'.' + QUOTENAME(@table_name))
GROUP BY
    i.index_id,
    i.name,
    i.type_desc
ORDER BY
    reserved_mb DESC;

C’est utile lorsque :

  • la maintenance des index est lente
  • les sauvegardes grossissent
  • le coût des écritures est élevé
  • vous soupçonnez des index en double ou surdimensionnés

Ne supprimez pas un index uniquement parce qu’il est volumineux. Vérifiez d’abord si la charge de travail l’utilise.


4. Afficher la taille par partition

Pour les tables partitionnées, les totaux au niveau de la table peuvent masquer l’endroit où se trouvent réellement les données.

DECLARE @schema_name sysname = N'dbo';
DECLARE @table_name  sysname = N'YourPartitionedTable';

SELECT
    ps.partition_number,
    SUM(ps.row_count) AS row_count,
    CAST(SUM(ps.used_page_count) * 8.0 / 1024 AS decimal(18,2)) AS used_mb,
    CAST(SUM(ps.reserved_page_count) * 8.0 / 1024 AS decimal(18,2)) AS reserved_mb
FROM sys.dm_db_partition_stats AS ps
WHERE
    ps.object_id = OBJECT_ID(QUOTENAME(@schema_name) + N'.' + QUOTENAME(@table_name))
GROUP BY
    ps.partition_number
ORDER BY
    ps.partition_number;

Utilisez cette requête pour vérifier que les données sont réparties entre les partitions comme prévu.

Une stratégie de partitionnement fortement déséquilibrée peut compromettre à la fois les objectifs de performance et de maintenance.


5. Afficher les plus grandes tables par nombre de lignes

L’espace et le nombre de lignes répondent à des questions différentes.

SELECT
    s.name AS schema_name,
    t.name AS table_name,
    SUM(ps.row_count) AS row_count
FROM sys.dm_db_partition_stats AS ps
INNER JOIN sys.tables AS t
    ON t.object_id = ps.object_id
INNER JOIN sys.schemas AS s
    ON s.schema_id = t.schema_id
WHERE
    t.is_ms_shipped = 0
    AND ps.index_id IN (0, 1)
GROUP BY
    s.name,
    t.name
ORDER BY
    row_count DESC;

Filtrez sur les lignes du tas ou de l’index cluster (index_id 0 ou 1) afin de ne pas compter les mêmes lignes de table une fois par index non cluster.


6. Comparer l’espace utilisé et l’espace réservé

Un écart important entre l’espace réservé et l’espace utilisé peut mériter une analyse.

SELECT
    s.name AS schema_name,
    t.name AS table_name,
    CAST(SUM(ps.reserved_page_count) * 8.0 / 1024 AS decimal(18,2)) AS reserved_mb,
    CAST(SUM(ps.used_page_count) * 8.0 / 1024 AS decimal(18,2)) AS used_mb,
    CAST(
        (SUM(ps.reserved_page_count) - SUM(ps.used_page_count)) * 8.0 / 1024
        AS decimal(18,2)
    ) AS unused_reserved_mb
FROM sys.dm_db_partition_stats AS ps
INNER JOIN sys.tables AS t
    ON t.object_id = ps.object_id
INNER JOIN sys.schemas AS s
    ON s.schema_id = t.schema_id
WHERE
    t.is_ms_shipped = 0
GROUP BY
    s.name,
    t.name
ORDER BY
    unused_reserved_mb DESC;

Ne réduisez pas immédiatement la base de données parce qu’un objet possède des pages réservées inutilisées. Comprenez d’abord son profil de croissance.

Interpréter le résultat

Champ Pourquoi c’est important
row_count Nombre de lignes actuel approximatif, issu des métadonnées de partition. Utile pour les comparaisons de capacité et d’échelle.
reserved_mb Espace alloué à l’objet, y compris les pages allouées actuellement inutilisées.
used_mb Part de l’espace réservé actuellement utilisée.
data_mb Espace utilisé par les données du tas ou de l’index cluster, y compris les pages LOB et de dépassement de ligne (row-overflow) dans les exemples ci-dessus.
nonclustered_index_mb Espace utilisé par les index non cluster.
unused_reserved_mb Espace alloué mais actuellement inutilisé.
partition_number Permet d’examiner la répartition entre les partitions de la table.
index_id Identifie les structures de tas, d’index cluster et d’index non cluster.

Points à vérifier

Un petit nombre de tables domine la base de données

Cela signifie généralement que la planification de la capacité doit d’abord se concentrer sur ces objets.

Examinez :

  • la politique de rétention
  • le rythme de croissance
  • la stratégie d’archivage
  • le partitionnement
  • la compression
  • l’empreinte des index

L’espace des index est proche de celui des données de base, voire supérieur

Ce n’est pas forcément un problème.

Les tables larges et les charges de travail orientées lecture peuvent légitimement nécessiter un espace d’index important.

Mais c’est un bon signal pour passer en revue :

  • les index inutilisés
  • les index qui se chevauchent
  • les colonnes INCLUDE redondantes
  • les chemins d’accès en double
  • l’amplification des écritures

Le nombre de lignes est élevé mais le stockage reste modéré

Les causes possibles sont notamment :

  • des lignes étroites
  • la compression
  • un faible usage des LOB
  • une conception de schéma efficace

Un nombre de lignes élevé ne signifie pas automatiquement une pression sur le stockage.

Le stockage est élevé mais le nombre de lignes reste modéré

Recherchez :

  • varchar(max) / nvarchar(max)
  • XML
  • des données binaires volumineuses
  • des données en dépassement de ligne (row-overflow)
  • des index larges
  • des schémas épars (sparse) ou larges

Une partition contient la plupart des lignes

Interprétations possibles :

  • une concentration attendue sur la période en cours
  • un déséquilibre de la clé de partition
  • des partitions historiques qui ne sont pas gérées comme prévu
  • une stratégie de partitionnement inadaptée aux modèles d’accès

Espace réservé inutilisé important

Les raisons possibles sont notamment :

  • des suppressions récentes
  • une activité de reconstruction d’index
  • les modèles d’allocation
  • l’historique de croissance de l’objet

Ne considérez pas l’espace réservé inutilisé comme une obligation de réduire les fichiers.

Notes de production

sys.dm_db_partition_stats repose sur les métadonnées et reste léger

Pour inventorier la taille des tables, il est généralement préférable à un parcours de la table ou à un COUNT(*) sur de gros objets de production.

Les nombres de lignes conviennent au diagnostic opérationnel et au travail de capacité, mais ils ne sont pas équivalents à un COUNT(*) transactionnel exact exécuté avec la sémantique d’isolation de votre charge de travail.

La taille des tables n’est pas la taille des fichiers de la base

L’allocation des objets répond à la question :

Quels objets consomment des pages de la base de données ?

Elle ne répond pas à :

  • l’espace libre restant dans le fichier de données
  • l’espace disque occupé par les fichiers de la base de données
  • la taille du journal des transactions
  • la taille des sauvegardes

Ces points nécessitent des requêtes distinctes au niveau des fichiers ou de la base de données.

La compression modifie la relation entre lignes et stockage

La compression de ligne ou de page et le columnstore peuvent rendre difficile la comparaison directe des nombres de lignes bruts entre tables.

Pour planifier le stockage, utilisez les pages réellement allouées, et pas seulement le nombre de lignes.

Les données LOB comptent

Les données de grande taille peuvent consommer un espace de stockage hors ligne (out-of-row) considérable.

Une table peut paraître petite en nombre de lignes mais volumineuse en pages à cause de :

  • varchar(max)
  • nvarchar(max)
  • varbinary(max)
  • XML
  • d’autres allocations LOB

Prudence avec le nettoyage automatisé

Les tables volumineuses sont souvent au cœur des processus métier.

Avant de supprimer, d’archiver ou de purger des données, validez :

  • les exigences de rétention
  • les clés étrangères
  • les rapports en aval
  • les implications pour la réplication ou le CDC
  • les attentes en matière de sauvegarde et de restauration
  • l’impact des traitements par lots sur le journal

Erreurs fréquentes

1. Compter les lignes une fois par index

Si vous additionnez row_count sur tous les ID d’index, vous pouvez surestimer le nombre de lignes de la table, car les index non cluster contiennent eux aussi des lignes.

Pour le nombre de lignes d’une table, utilisez les structures de tas ou d’index cluster (index_id IN (0,1)), ou structurez soigneusement votre agrégation.

2. Considérer l’espace réservé comme entièrement occupé par des données

Les pages réservées incluent des pages allouées mais inutilisées.

Comparez reserved_page_count avec used_page_count.

3. Supprimer de gros index uniquement en fonction de leur taille

Un index volumineux peut rester essentiel.

Vérifiez l’utilisation de l’index et les plans de requête avant de le supprimer.

4. Réduire les fichiers parce qu’une table a été nettoyée

Des cycles fréquents de réduction et de croissance peuvent provoquer de la fragmentation et une agitation opérationnelle inutile.

Ne récupérez de l’espace dans les fichiers que lorsqu’il existe une raison de capacité claire et un plan délibéré.

5. Comparer les tables uniquement par nombre de lignes

La largeur et les caractéristiques de stockage des lignes varient considérablement.

Associez toujours le nombre de lignes à l’utilisation réelle des pages.

6. Ignorer la répartition entre partitions

Une table partitionnée peut être volumineuse pour des raisons légitimes tout en étant mal répartie.

Examinez le nombre de lignes et de pages par partition avant de supposer que la stratégie de partitionnement fonctionne.

Étape suivante

Utilisez le profil de taille pour décider où poursuivre l’analyse.

  • Une table domine le stockage → examinez la rétention, le rythme de croissance, la largeur du schéma, l’usage des LOB et le partitionnement.
  • Les index dominent l’espace → passez en revue l’utilisation des index, les doublons et le surcoût en écriture.
  • Une partition domine → validez le choix de la clé de partition et la gestion du cycle de vie.
  • Espace réservé inutilisé important → comprenez l’historique de croissance et de suppression de l’objet avant d’envisager une récupération d’espace.
  • La table croît rapidement → mettez en place une supervision des tendances plutôt que de vous fier à un instantané ponctuel.
  • Le fichier de la base reste bien plus volumineux que le total des tables → examinez l’espace libre, les objets internes et l’allocation au niveau des fichiers.
  • Le stockage est correct mais les performances sont mauvaises → revenez au diagnostic de la charge de travail, des attentes, des requêtes et des index plutôt qu’à l’analyse de capacité.

← Tous les aide-mémoire