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.

Par JaviPublié 12 min de lectureIntermédiaire

Icône de base SQL Server à côté de barres montrant les tailles relatives des tables Orders, Events et Customers

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.

Requête SQL: Diagnostic rapideT-SQL
;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;
T-SQL31 lignesAffichage en lecture seule. Aucune connexion à une base de données.

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.

Requête SQL: Une table préciseT-SQL
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;
T-SQL18 lignesAffichage en lecture seule. Aucune connexion à une base de données.

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.

Requête SQL: Nombre de lignes et taille par partitionT-SQL
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;
T-SQL17 lignesAffichage en lecture seule. Aucune connexion à une base de données.

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_units par 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.

Étiquettes

  • SQL Server
  • Performance
  • Dépannage
  • DMV
  • Indexation
  • Données à grande échelle