Segments (tables sans index cluster)

S’applique à :SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceBase de données SQL dans Microsoft Fabric

Un heap est une table sans index clusterisé. Vous pouvez créer un ou plusieurs index non clusterisés sur des tables stockées sous forme de tas. Le tas stocke les données sans spécifier d’ordre. En général, le tas stocke d’abord les données dans l’ordre dans lequel vous insérez les lignes. Toutefois, le moteur de base de données peut déplacer les données dans le segment afin de stocker les lignes de manière efficace. Dans les résultats des requêtes, on ne peut pas prédire l’ordre des données. Pour garantir l’ordre des lignes retournées à partir d’un segment, utilisez la clause ORDER BY. Pour spécifier un ordre logique permanent pour stocker les lignes, créez un index regroupé sur la table, afin que la table ne soit pas un tas.

Quand utiliser un tas

Un tas est idéal pour les tables que vous tronquez et rechargez fréquemment. Le Moteur de base de données optimise l’espace dans un tas en remplissant l’espace le plus ancien disponible.

Tenez compte des éléments suivants :

• Localiser de l’espace libre dans un tas peut être coûteux, surtout si de nombreuses suppressions ou mises à jour ont lieu.
• Les indices clusterés offrent des performances stables pour des tableaux que vous ne trunquez pas fréquemment.

Pour les tables que vous tronquez ou recréez régulièrement, comme les tables temporaires ou de staging, utiliser un tas est souvent plus efficace.

Le choix entre l’utilisation d’un segment de mémoire et d’un index cluster peut affecter considérablement les performances et l’efficacité de votre base de données.

Lorsque vous stockez une table sous forme de tas, vous identifiez des lignes individuelles par référence à un identifiant de ligne (RID) de 8 octets composé du numéro de fichier, du numéro de page de données et de l’emplacement sur la page (FileID :PageID :SlotID). L’ID de ligne est une structure petite et efficace.

Utilisez les tas comme tables de staging pour de grandes opérations d’insertion non ordonnées. Comme les tas ne font pas respecter un ordre d’insertion strict, l’opération d’insertion est généralement plus rapide qu’une insertion équivalente dans un index clusteré. Si vous lisez et traitez les données du tas jusqu’à une destination finale, envisagez de créer un index étroit non regroupé qui couvre le prédicat de recherche utilisé par la requête.

Vous pouvez aussi utiliser des tas lorsque vous accédez toujours aux données via des index non clusterisés et que le RID est plus petit qu’une clé d’index clusterée.

Si une table est un tas et ne possède aucun index non regroupé, alors vous devez lire toute la table (un scan de table) pour trouver une ligne. SQL Server ne peut pas rechercher un RID directement sur le tas. Ce comportement peut être acceptable si la table est petite.

Quand ne pas utiliser un tas

N’utilisez pas un tas lorsque les données sont fréquemment renvoyées dans un ordre trié. Un index regroupé sur la colonne de tri peut éviter l’opération de tri.

N’utilisez pas un tas lorsque les données sont fréquemment regroupées. Les données doivent être triées avant d’être regroupées, et un index groupé sur la colonne de tri peut éviter cette opération de tri.

N’utilisez pas un tas lorsque les plages de données sont fréquemment interrogées depuis la table. Un index cluster sur la colonne de plage évite de devoir trier le segment de mémoire entier.

N’utilisez pas de tas lorsqu’il n’y a pas d’index non regroupés et que la table est grande. La seule application de ce design est de retourner l’intégralité du contenu de la table sans ordre spécifié. Dans un heap, le moteur de base de données lit toutes les lignes pour trouver n’importe quelle ligne.

N’utilisez pas de tas si vous mettez fréquemment à jour les données. Si vous mettez à jour un enregistrement et que la mise à jour occupe plus d’espace dans les pages de données qu’elle n’en utilise actuellement, l’enregistrement se déplace vers une page de données qui dispose de suffisamment d’espace libre. Ce mouvement crée un enregistrement transféré pointant vers la nouvelle localisation des données. Le pointeur de transfert est écrit dans la page qui contenait précédemment les données, pour indiquer la nouvelle localisation physique. Ce mouvement introduit une fragmentation dans le tas. Lorsque le moteur de base de données parcourt un tas, il suit ces pointeurs. Cette action limite la performance de lecture anticipée et peut entraîner des E/S supplémentaires, ce qui réduit la performance du balayage.

Gérer les tas

Pour créer un segment, créez une table sans index cluster. Si la table possède déjà un index cluster, supprimez-le afin de reconvertir la table en segment.

Pour supprimer un segment, créez un index cluster sur le segment.

Pour reconstruire un segment afin de récupérer de l’espace perdu :

• Créez un index cluster dans le segment, puis supprimez cet index.
• Utilisez la commande ALTER TABLE ... REBUILD pour reconstruire le tas.

Identifier les tas

La requête suivante renvoie une liste de segments de la base de données actuelle. La liste comprend :

• Noms de table
• Noms de schémas
• Nombre de lignes
• Taille de la table en Ko
• Taille de l’index en Ko
• Espace inutilisé
• Colonne pour identifier un tas

SELECT t.name AS 'Your TableName',
       s.name AS 'Your SchemaName',
       p.rows AS 'Number of Rows in Your Table',
       SUM(a.total_pages) * 8 AS 'Total Space of Your Table (KB)',
       SUM(a.used_pages) * 8 AS 'Used Space of Your Table (KB)',
       (SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS 'Unused Space of Your Table (KB)',
       CASE
           WHEN i.index_id = 0 THEN 'Yes'
           ELSE 'No'
       END AS 'Is Your Table a Heap?'
FROM sys.tables AS t
     INNER JOIN sys.indexes AS i
         ON t.object_id = i.object_id
     INNER JOIN sys.partitions AS p
         ON i.object_id = p.object_id
        AND i.index_id = p.index_id
     INNER JOIN sys.allocation_units AS a
         ON p.partition_id = a.container_id
     LEFT OUTER JOIN sys.schemas AS s
         ON t.schema_id = s.schema_id
WHERE i.index_id <= 1 -- 0 for Heap, 1 for Clustered Index
GROUP BY t.name, s.name, i.index_id, p.rows
ORDER BY 'Your TableName';

Structures de tas

Un heap est une table sans index clusterisé. Les segments ont une ligne dans sys.partitions, avec index_id = 0 pour chaque partition utilisée par le segment. Par défaut, un tas comporte une seule partition. Lorsqu'un segment comporte plusieurs partitions, chacune d'elles possède une structure de segment contenant les données la concernant. Par exemple, si un segment comporte quatre partitions, il y a quatre structures de segment, une dans chaque partition.

Selon les types de données dans le tas, chaque structure de tas possède une ou plusieurs unités d’allocation pour stocker et gérer les données d’une partition spécifique. Au minimum, chaque tas possède une IN_ROW_DATA unité d’allocation par partition. La structure de tas comporte également une LOB_DATA unité d’allocation par partition, si elle contient des colonnes de gros objets (LOB). Il dispose également d’une ROW_OVERFLOW_DATA unité d’allocation par partition, si elle contient des colonnes de longueur variable dépassant la limite de 8 060 octets de taille de ligne.

La colonne first_iam_page dans la sys.system_internals_allocation_units vue système pointe vers la première page de la carte d’allocation d’indices (IAM) dans la chaîne de pages IAM qui gèrent l’espace alloué au tas dans une partition spécifique. SQL Server utilise les pages IAM pour se déplacer à travers le segment de mémoire. Les pages de données et les lignes à leur intérieur ne sont pas dans un ordre précis et ne sont pas liées. La seule connexion logique entre les pages de données concerne les informations enregistrées dans les pages IAM.

Vous pouvez effectuer des numérisations de tables ou des lectures sérielles d’un tas en scannant les pages IAM pour trouver les étendues qui contiennent les pages du tas. Parce que l’IAM représente les étendues dans le même ordre qu’elles existent dans les fichiers de données, cette structure signifie que les analyses du tas série progressent séquentiellement à travers chaque fichier. Utiliser les pages IAM pour définir la séquence de scan signifie aussi que les lignes du tas ne sont généralement pas retournées dans l’ordre dans lequel elles ont été insérées.

L’illustration suivante montre comment le moteur de base de données SQL Server utilise les pages IAM pour extraire des lignes de données dans un segment de mémoire de partition unique.

Contenu connexe

• CREATE INDEX (Transact-SQL)
• DROP INDEX (Transact-SQL)
• Index clusterisés et non-clusterisés