SQL Server
Comment mesurer la taille des tables SQL Server
Mesurez les lignes, l’espace réservé, les données, les index et l’allocation inutilisée sans compter deux fois les partitions ou les unités d’allocation.
Dans cet article
La taille d’une table donne du contexte à une investigation de performances, mais ne constitue pas un verdict. Dix millions de lignes étroites peuvent occuper moins d’espace qu’un million de lignes contenant de grandes valeurs d’objets, et une grande table peut être performante si les chemins d’accès correspondent à la charge. Mesurez les lignes et les pages avant de tirer des conclusions sur les scans, la maintenance, la rétention ou le partitionnement.
Problème
Les utilisateurs signalent qu’une requête sur SalesDb a ralenti après plusieurs mois de croissance. Vous devez savoir quelles tables sont les plus volumineuses, quelle part de l’espace correspond aux données plutôt qu’aux index, si une allocation est inutilisée et comment les lignes se répartissent entre les partitions.
SQL Server expose plusieurs granularités liées : tables, index, partitions et unités d’allocation. Une jointure imprudente entre ces éléments peut multiplier les lignes et compter l’espace plusieurs fois. Les premières requêtes utilisent sys.dm_db_partition_stats, qui fournit déjà les nombres de pages au niveau des partitions et facilite une agrégation fiable.
Diagnostic rapide : tables les plus volumineuses de la base actuelle
Exécutez cette requête dans la base à examiner. Les partitions du heap ou de l’index clustered (index_id 0 ou 1) contiennent le nombre de lignes de la table ainsi que les données in-row, LOB et row-overflow. Les index nonclustered contribuent à l’espace réservé et utilisé, mais ne doivent pas être ajoutés au nombre de lignes.
;WITH table_space AS
(
SELECT
ps.object_id,
SUM(CASE WHEN ps.index_id IN (0, 1) THEN ps.row_count ELSE 0 END) AS row_count,
SUM(ps.reserved_page_count) AS reserved_pages,
SUM(ps.used_page_count) AS used_pages,
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
FROM sys.dm_db_partition_stats AS ps
GROUP BY ps.object_id
)
SELECT
DB_NAME() AS database_name,
SCHEMA_NAME(t.schema_id) AS schema_name,
t.name AS table_name,
space.row_count,
CAST(space.reserved_pages * 8.0 / 1024 AS decimal(19, 2)) AS reserved_mb,
CAST(space.data_pages * 8.0 / 1024 AS decimal(19, 2)) AS data_mb,
CAST(space.nonclustered_index_pages * 8.0 / 1024 AS decimal(19, 2)) AS nonclustered_indexes_mb,
CAST((space.reserved_pages - space.used_pages) * 8.0 / 1024 AS decimal(19, 2)) AS unused_mb,
CAST(space.reserved_pages * 8.0 / 1024 / 1024 AS decimal(19, 2)) AS reserved_gb
FROM table_space AS space
INNER JOIN sys.tables AS t
ON t.object_id = space.object_id
WHERE t.is_ms_shipped = 0
ORDER BY space.reserved_pages DESC, schema_name, table_name;data_mb comprend les pages in-row, LOB et row-overflow du heap ou de l’index clustered. nonclustered_indexes_mb indique les pages utilisées par les index rowstore secondaires et les autres index dont index_id > 1. reserved_mb comprend les pages allouées mais inutilisées de tous les index. Ce sont des mesures opérationnelles utiles, mais ne supposez pas qu’elles constituent une facture de stockage exacte pour chaque type d’index spécialisé.
Requête : une table précise
Définissez le schéma et la table une seule fois. OBJECT_ID évite de devoir modifier les noms d’objets partout dans la requête et distingue correctement les schémas.
DECLARE @SchemaName sysname = N'dbo';
DECLARE @TableName sysname = N'SalesOrder';
DECLARE @ObjectId int = OBJECT_ID(QUOTENAME(@SchemaName) + N'.' + QUOTENAME(@TableName), N'U');
IF @ObjectId IS NULL
THROW 50000, 'The requested user table does not exist in the current database.', 1;
SELECT
DB_NAME() AS database_name,
OBJECT_SCHEMA_NAME(ps.object_id) AS schema_name,
OBJECT_NAME(ps.object_id) AS table_name,
SUM(CASE WHEN ps.index_id IN (0, 1) THEN ps.row_count ELSE 0 END) AS row_count,
CAST(SUM(ps.reserved_page_count) * 8.0 / 1024 AS decimal(19, 2)) AS reserved_mb,
CAST(SUM(ps.used_page_count) * 8.0 / 1024 AS decimal(19, 2)) AS used_mb,
CAST(SUM(ps.reserved_page_count - ps.used_page_count) * 8.0 / 1024 AS decimal(19, 2)) AS unused_mb
FROM sys.dm_db_partition_stats AS ps
WHERE ps.object_id = @ObjectId
GROUP BY ps.object_id;Requête : nombre de lignes et taille par partition
La répartition des partitions compte pour l’élimination, la maintenance des sliding windows et le skew. Cela ne signifie pas qu’une grande table doit automatiquement être partitionnée.
DECLARE @SchemaName sysname = N'dbo';
DECLARE @TableName sysname = N'SalesOrder';
DECLARE @ObjectId int = OBJECT_ID(QUOTENAME(@SchemaName) + N'.' + QUOTENAME(@TableName), N'U');
SELECT
i.name AS index_name,
i.index_id,
ps.partition_number,
ps.row_count,
CAST(ps.reserved_page_count * 8.0 / 1024 AS decimal(19, 2)) AS reserved_mb,
CAST(ps.used_page_count * 8.0 / 1024 AS decimal(19, 2)) AS used_mb
FROM sys.dm_db_partition_stats AS ps
INNER JOIN sys.indexes AS i
ON i.object_id = ps.object_id
AND i.index_id = ps.index_id
WHERE ps.object_id = @ObjectId
ORDER BY i.index_id, ps.partition_number;Cette requête renvoie chaque partition d’index séparément. N’additionnez pas row_count pour tous les index : les mêmes lignes logiques de la table sont représentées dans la structure de base et dans chaque index. Pour compter les lignes de la table, ne faites la somme que pour index_id 0 ou 1.
Interpréter les résultats
Les pages SQL Server font 8 Ko ; multiplier leur nombre par 8 puis diviser par 1 024 donne des Mo. Divisez les Mo par 1 024 pour obtenir des Go. Les conversions décimales évitent la troncature des entiers. Les nombres de lignes de sys.dm_db_partition_stats sont des métadonnées maintenues et peuvent être approximatifs en cas d’activité concurrente ; utilisez-les pour le diagnostic et l’analyse de capacité, pas pour un rapprochement comptable.
L’espace réservé est alloué à un objet. L’espace utilisé est la partie occupée de cette allocation. La différence ne correspond pas automatiquement à de l’espace récupérable auprès du système d’exploitation, et une allocation inutilisée n’indique pas forcément un problème. Les fichiers de données conservent généralement de l’espace libre pour leur croissance future.
Les valeurs LOB comme varchar(max), nvarchar(max), varbinary(max), XML et certaines structures columnstore peuvent faire fortement diverger le volume en octets du nombre de lignes. C’est pourquoi la première requête inclut explicitement les pages LOB et row-overflow de la table de base.
Ce que les résultats peuvent indiquer
Une table qui domine le volume des données peut justifier une analyse des règles de rétention et des modes d’accès. Un volume d’index très supérieur aux données de base peut être adapté à un système principalement en lecture, ou signaler des index larges qui se chevauchent. Une allocation inutilisée importante peut suivre des reconstructions d’index, des suppressions ou des profils de croissance ; réduire régulièrement la taille des fichiers est généralement contre-productif et peut provoquer de la fragmentation et des agrandissements répétés.
Une répartition inégale des lignes par partition peut être attendue lorsque les périodes récentes sont plus actives. Elle peut également révéler un problème de frontière ou de distribution des données. Vérifiez dans les prédicats et les plans d’exécution que les requêtes importantes bénéficient réellement de l’élimination des partitions.
Que vérifier ensuite ?
Reliez la taille à la requête lente. Examinez les lectures logiques et le plan. Déterminez quels index sont utilisés, quel est leur coût en écriture et si les statistiques représentent la distribution actuelle. Pour les très grandes tables, étudiez la rétention, la compression, la durée de maintenance, les modes d’écriture et l’adéquation du columnstore avant de modifier la conception physique.
Erreurs fréquentes
- Joindre
sys.allocation_unitspar plusieurs chemins et compter accidentellement plusieurs fois la même allocation. - Additionner les nombres de lignes de tous les index.
- Considérer les nombres de lignes des métadonnées comme exacts pendant des écritures concurrentes.
- Ignorer les pages LOB et row-overflow.
- Supposer qu’une grande table doit être partitionnée.
- Réduire les fichiers de données dans le cadre d’un nettoyage régulier sans comprendre la repousse et la fragmentation.
Notes de production
Ces requêtes sont en lecture seule et généralement peu coûteuses, mais l’accès au catalogue et aux DMV participe tout de même à la concurrence sur les métadonnées. Exécutez-les dans la base cible et enregistrez son nom avec les résultats. Dans un groupe de disponibilité, vérifiez quelle réplique a été interrogée et si ses données sont assez récentes pour l’usage prévu.
La taille est surtout utile comme tendance. Capturez-la selon un calendrier raisonnable, comparez les taux de croissance et mettez-les en relation avec la rétention et la charge. Une mesure ponctuelle indique ce qui est volumineux aujourd’hui ; une tendance indique ce qui risque de devenir un problème opérationnel.
Ce contenu vous a-t-il été utile ?
Que pourrions-nous améliorer ?
Merci — cela m’aide à améliorer Javi Builds.
Continuer à apprendre
- Aide-mémoireVérifier la taille des tables SQL ServerMesurez 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.Ouvrir l’aide-mémoire
- Feuille de routeSQL Server Troubleshooting & DiagnosticsMesurez les lignes, l’espace réservé, les données, les index et l’allocation inutilisée sans compter deux fois les partitions ou les unités d’allocation.Voir la feuille de route
Que devrais-je créer ensuite ?
Je développe Javi Builds autour des problèmes concrets rencontrés par les data engineers. Dites-moi quel sujet vous aimeriez voir ensuite.
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.