Organisation des tables et des index

Les tables et les index sont stockés sous la forme de collections de pages de 8 Ko. Cette rubrique décrit comment les pages des tables et des index sont organisées.

Organisation des tables

L'illustration suivante montre l'organisation d'une table. Une table est contenue dans une ou plusieurs partitions et chaque partition contient des lignes de données soit dans un segment de mémoire, soit dans un index cluster. Les pages du segment de mémoire ou de l'index cluster sont gérées dans une ou plusieurs unités d'allocation, en fonction des types de colonnes dans les lignes de données.

Partitions

Les pages de tables et d'index sont stockées sur une ou plusieurs partitions. Une partition est une unité d'organisation de données définie par l'utilisateur. Par défaut, une table ou un index n'a qu'une seule partition qui contient l'ensemble de ses pages. Le partition réside dans un groupe de fichiers unique. Une table ou un index associé à une partition unique représente la structure organisationnelle des tables et des index dans les versions précédentes de SQL Server.

Lorsqu'une table ou un index utilise plusieurs partitions, les données sont partitionnées horizontalement de manière à ce que les groupes de lignes soient mappés sur des partitions séparées, sur la base d'une colonne spécifiée. Les partitions peuvent être placées dans un ou plusieurs groupes de fichiers dans la base de données. La table ou l'index est traité comme une entité logique unique lors de l'exécution de requêtes ou de mises à jour sur les données. Pour plus d'informations, consultez Tables et index partitionnés.

Pour afficher les partitions utilisées par une table ou un index, utilisez la vue de catalogue sys.partitions (Transact-SQL).

Tables, segments de mémoire et index cluster

Les tables SQL Server utilisent une des deux méthodes suivantes pour organiser leurs pages de données au sein d'une partition :

• Les tables cluster sont des tables possédant un index cluster.

  Les lignes de données sont stockées dans l'ordre de la clé d'index cluster. L'index cluster est mis en œuvre sous la forme d'une arborescence binaire qui permet une extraction rapide des lignes d'après les valeurs de clés de leur index cluster. Les pages de chaque niveau de l'index, y compris les pages de données du niveau feuille, sont liées au sein d'une liste à double liaison. Toutefois, la navigation d'un niveau à l'autre se fait au moyen de valeurs de clés. Pour plus d'informations, consultez Structures des index cluster.

• Les segments de mémoire sont des tables qui n'ont pas d'index cluster.

  Les lignes de données et la séquence de pages de données ne sont pas stockées dans un ordre particulier. Les pages de données ne sont pas réunies dans une liste liée. Pour plus d'informations, consultez Structures des segments.

Les vues indexées ont la même structure de stockage que les tables cluster.

Lorsqu'un segment de mémoire ou une table cluster a plusieurs partitions, chacune d'entre elles a un segment de mémoire ou une arborescence binaire qui contient un groupe de lignes la concernant. Par exemple, si une table cluster a quatre partitions, il y aura quatre arborescences binaires : une dans chaque partition.

Index non-cluster

Les index non-cluster ont une structure de type arborescence binaire similaire à celle des index cluster. La différence est que les index non-cluster n'ont pas d'effet sur l'ordre des lignes de données. Le niveau feuille contient les lignes d'index. Chaque ligne d'index contient la valeur de clé non cluster, un localisateur de ligne et toute colonne comprise, ou non-clé. Le localisateur pointe vers la ligne de données qui a la valeur de clé. Pour plus d'informations, consultez Structures d'index non-cluster.

Index XML

Un index XML principal et plusieurs index XML secondaires peuvent être créés sur chaque colonne xml dans la table. Un index XML est une représentation fragmentaire et permanente des objets BLOB (binary large objects) XML dans la colonne du type de données xml. Les index XML sont stockés sous la forme de tables internes. Pour afficher des informations concernant les index XML, utilisez les vues de catalogue sys.xml_indexes et sys.internal_tables.

Pour plus d'informations sur les index XML, consultez Index sur les colonnes de type de données XML.

Unités d'allocation

Une unité d'allocation est une collection de pages au sein d'un segment de mémoire ou d'une arborescence binaire qui permet de gérer les données en fonction de leur type. Le tableau suivant énumère les types d'unités d'allocation utilisées pour gérer les données dans les tables et les index.

Type d'unité d'allocation	Usage
IN_ROW_DATA	Données ou lignes d'index qui contiennent toutes les données, à l'exception des données de type LOB (large object). Les pages sont de type Data ou Index.
LOB_DATA	Données d'objet volumineuses stockées dans un ou plusieurs des types de données suivants : text, ntext, image, xml, varchar(max), nvarchar(max), varbinary(max) ou types CLR définis par l'utilisateur (CLR UDT). Les pages sont de type texte/image.
ROW_OVERFLOW_DATA	Données à longueur variable stockées dans les colonnes varchar, nvarchar, varbinary ou sql_variant qui dépassent la taille limite de 8 060 octets par ligne. Les pages sont de type texte/image.

Pour plus d'informations sur les types de pages, consultez Fonctionnement des pages et étendues.

Un segment de mémoire ou une arborescence binaire peut avoir une seule unité d'allocation par type dans une partition donnée. Pour afficher les informations relatives aux unités d'allocation d'une table ou d'un index, utilisez la vue de catalogue sys.allocation_units.

Unité d'allocation IN_ROW_DATA

Pour chaque partition utilisée par une table (segment de mémoire ou cluster), un index ou une vue indexée, il y a une seule unité d'allocation IN_ROW_DATA qui se compose d'une collection de pages de données. Cette unité d'allocation contient également d'autres collections de pages servant à implémenter chaque index non-cluster et XML défini pour la table ou la vue. Les collections de pages dans chaque partition d'une table, d'un index ou d'une vue indexée sont ancrées au moyen de pointeurs de page dans la vue système sys.system_internals_allocation_units.

Important

La vue système sys.system_internals_allocation_units est réservée à un usage exclusivement interne de Microsoft SQL Server. La compatibilité future n'est pas garantie.

Chaque partition de table, d'index et de vue indexée a, dans la vue système sys.system_internals_allocation_units, une ligne qui lui correspond et qui est rendue unique par un ID de conteneur (container_id). Ces ID de conteneur sont mappés sur les valeurs de la colonne partition_id dans la vue de catalogue sys.partitions qui maintient la relation entre les données de table, d'index ou de vue indexée stockées dans une partition et les unités d'allocation utilisées pour gérer les données dans la partition.

L'allocation des pages à la partition de table, d'index ou de vue indexée est gérée par une chaîne de pages IAM. La colonne first_iam_page de la vue système sys.system_internals_allocation_units pointe vers la première page IAM dans la chaîne de pages IAM gérant l'espace alloué à la table, l'index ou la vue indexée dans l'unité d'allocation IN_ROW_DATA.

sys.partitions retourne une ligne pour chaque partition dans une table ou un index.

• Un segment de mémoire dispose d'une ligne dans sys.partitions avec index_id = 0.

  La colonne first_iam_page dans la vue système sys.system_internals_allocation_units pointe vers la chaîne IAM pour la collection de pages de données de segment de mémoire dans la partition spécifiée. Le serveur utilise les pages IAM pour rechercher les pages dans la collection de pages de données car il n'y a pas de lien entre les deux.

• Un index cluster sur une table ou une vue possède une ligne dans sys.partitions avec index_id = 1.

  La colonne root_page dans la vue système sys.system_internals_allocation_units pointe vers le sommet de l'arborescence binaire de l'index cluster dans la partition spécifiée. Le serveur utilise l'arborescence binaire pour rechercher les pages de données dans la partition.

• Chaque index non-cluster créé sur une table ou une vue possède une ligne dans sys.partitions avec index_id > 1.

  La colonne root_page dans la vue système sys.system_internals_allocation_units pointe vers le sommet de l'arborescence binaire de l'index non-cluster dans la partition spécifiée.

• Chaque table qui contient au moins une colonne LOB possède également une ligne dans sys.partitions avec index_id > 250.

  La colonne first_iam_page pointe vers la chaîne de pages IAM qui gère les pages dans l'unité d'allocation LOB_DATA.

Unité d'allocation ROW_OVERFLOW_DATA

Pour chaque partition utilisée par une table (segment de mémoire ou cluster), un index ou une vue indexée, il y a une unité d'allocation ROW_OVERFLOW_DATA. Celle-ci contient zéro (0) page jusqu'à ce qu'une ligne de données avec colonnes à longueur variable (varchar, nvarchar, varbinary ou sql_variant) dans l'unité d'allocation IN_ROW_DATA dépasse la taille limite de 8 Ko. Lorsque cette limite est atteinte, SQL Server déplace la colonne la plus large de cette ligne vers une page dans l'unité d'allocation ROW_OVERFLOW_DATA. Un pointeur 24 octets vers ces données hors ligne est maintenu sur la page d'origine.

Les pages texte/image dans l'unité d'allocation ROW_OVERFLOW_DATA sont gérées de la même manière que celles de l'unité d'allocation LOB_DATA, c'est-à-dire par une chaîne de pages IAM.

Unité d'allocation LOB_DATA

Lorsqu'une table ou un index contient un ou plusieurs types de données LOB, une unité d'allocation LOB_DATA est allouée par partition pour la gestion du stockage de ces données. Les types de données LOB sont text, ntext, image, xml, varchar(max), nvarchar(max), varbinary(max) et les types CLR définis par l'utilisateur.

Exemple de partition et d'unité d'allocation

L'exemple suivant retourne les données de partition et d'unité d'allocation pour deux tables : DatabaseLog, un segment de mémoire avec des données LOB et aucun index non cluster, et Currency, une table cluster sans données LOB et avec un index non cluster. Les deux tables ont une seule partition.

USE AdventureWorks2008R2;
GO
SELECT o.name AS table_name,p.index_id, i.name AS index_name , au.type_desc AS allocation_type, au.data_pages, partition_number
FROM sys.allocation_units AS au
    JOIN sys.partitions AS p ON au.container_id = p.partition_id
    JOIN sys.objects AS o ON p.object_id = o.object_id
    JOIN sys.indexes AS i ON p.index_id = i.index_id AND i.object_id = p.object_id
WHERE o.name = N'DatabaseLog' OR o.name = N'Currency'
ORDER BY o.name, p.index_id;

Voici l'ensemble de résultats obtenu. Notez que la table DatabaseLog utilise les trois types d'unités d'allocation parce qu'elle contient à la fois des pages de type données et des pages de type texte/image. La table Currency ne contient pas de données LOB, mais l'unité d'allocation est tout de même requise pour gérer les pages de données. Si, par la suite, une colonne de type de données LOB est ajoutée à la table Currency, une unité d'allocation LOB_DATA sera créée pour la gestion de ces données.

table_name  index_id index_name               allocation_type     data_pages  partition_number 
----------- -------- -----------------------  ---------------     -----------  ------------
Currency    1        PK_Currency_CurrencyCode IN_ROW_DATA         1           1
Currency    3        AK_Currency_Name         IN_ROW_DATA         1           1
DatabaseLog 0        NULL                     IN_ROW_DATA         160         1
DatabaseLog 0        NULL                     ROW_OVERFLOW_DATA   0           1
DatabaseLog 0        NULL                     LOB_DATA            49          1
(5 row(s) affected)

Voir aussi

Référence

sys.allocation_units (Transact-SQL)

sys.partitions (Transact-SQL)

sys.data_spaces (Transact-SQL)