Parcourir les aide-mémoire · SQL Server
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
- Problème
- Réponse rapide
- Code / commandes
- 1. Lister les tables par espace réservé
- 2. Séparer l’espace des données de celui des index
- 3. Afficher la taille des index d’une table
- 4. Afficher la taille par partition
- 5. Afficher les plus grandes tables par nombre de lignes
- 6. Comparer l’espace utilisé et l’espace réservé
- Interpréter le résultat
- Points à vérifier
- Un petit nombre de tables domine la base de données
- L’espace des index est proche de celui des données de base, voire supérieur
- Le nombre de lignes est élevé mais le stockage reste modéré
- Le stockage est élevé mais le nombre de lignes reste modéré
- Une partition contient la plupart des lignes
- Espace réservé inutilisé important
- Notes de production
- sys.dm_db_partition_stats repose sur les métadonnées et reste léger
- La taille des tables n’est pas la taille des fichiers de la base
- La compression modifie la relation entre lignes et stockage
- Les données LOB comptent
- Prudence avec le nettoyage automatisé
- Erreurs fréquentes
- 1. Compter les lignes une fois par index
- 2. Considérer l’espace réservé comme entièrement occupé par des données
- 3. Supprimer de gros index uniquement en fonction de leur taille
- 4. Réduire les fichiers parce qu’une table a été nettoyée
- 5. Comparer les tables uniquement par nombre de lignes
- 6. Ignorer la répartition entre partitions
- Étape suivante
- 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.
Ce contenu vous a-t-il été utile ?
Que pourrions-nous améliorer ?
Merci — cela m’aide à améliorer Javi Builds.
Restez au courant
Recevez uniquement les nouvelles de Javi Builds qui vous intéressent.
Consultez votre boîte de réception
Je vous ai envoyé un lien de confirmation. Votre abonnement sera actif après confirmation.