Guide d’architecture de page et d’étendue

S’applique à :SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceAzure Synapse AnalyticsAnalytics Platform System (PDW)Base de données SQL dans Microsoft Fabric

Ce guide décrit la structure des pages et des étendues, ainsi que l’organisation des pages et étendues dans les fichiers de données.

Une page est une unité fondamentale de stockage de données dans le moteur de base de données. L’espace disque alloué à un fichier de données (.mdf ou .ndf) d’une base de données est logiquement divisé en pages numérotées consécutivement de 0 à n. Les opérations d’E/S de disque sur les fichiers de données sont effectuées au niveau de la page. Autrement dit, le moteur de base de données lit ou écrit des pages de données entières.

Une étendue est un ensemble de huit pages physiquement contiguës, utilisé pour gérer les pages de manière efficace. Chaque page appartient à une étendue.

Les fichiers journaux des transactions (.ldf) ne contiennent pas de pages. Ils contiennent une série d’enregistrements de journal qui n’ont pas de taille fixe.

Pages

Dans un livre ordinaire, tout le contenu est écrit sur des pages. Comme pour un livre, le moteur de base de données écrit toutes les lignes de données sur les pages. La taille de chaque page est la même : 8 Kib. Dans un livre, la plupart des pages contiennent les données ou le contenu principal du livre. Certaines pages contiennent des métadonnées décrivant le contenu, par exemple la table des matières et l’index.

De même, la plupart des pages de la base de données contiennent des lignes de données réelles. Ces pages sont appelées pages de données. Les pages texte/LOB contiennent également des données, mais sont utilisées uniquement par les types de données Large Object (LOB). Les pages d’index contiennent des structures d’index qui permettent de trouver efficacement les données. Enfin, une variété de pages système stockent les métadonnées décrivant l’organisation et les propriétés des données.

Le tableau suivant décrit les types de pages.

Chaque page commence par un en-tête de 96 octets qui sert à stocker les informations système relatives à la page. Ces informations incluent le numéro de page, le type de page et peuvent inclure d’autres métadonnées telles que l’ID d’objet et l’ID d’index de l’objet et de l’index qui possèdent la page.

Une structure appelée tableau d’emplacements est stockée à la fin de la page. Chaque élément de 2 octets dans le tableau d’emplacements correspond à une ligne stockée sur la page. Un élément du tableau de slots stocke le décalage en octets de la ligne par rapport au début de la page. Le moteur de base de données utilise ces décalages pour localiser les lignes d’une page.

Lorsque le moteur de base de données ajoute une ligne à une page vide, il stocke la ligne immédiatement après l’en-tête. L’élément de tableau d’emplacements pour la première ligne est stocké à la fin de la page. À mesure que d’autres lignes sont ajoutées, elles sont stockées l’une après l’autre du début à la fin de la page, tandis que le tableau d’emplacements passe de la fin au début de la page, comme illustré dans le diagramme suivant.

À mesure que les lignes d’une page sont supprimées ou mises à jour au fil du temps, l’espace libre peut apparaître parmi les lignes restantes. Lorsqu’une nouvelle ligne est ajoutée, elle peut être stockée dans cet espace libre, si l’espace est suffisant. Cela signifie que les lignes d’une page peuvent ne pas être stockées physiquement dans un ordre particulier. Toutefois, le moteur de base de données gère les entrées de tableau d’emplacements dans un ordre logique. Par conséquent, les lignes d’une page sont également accessibles dans un ordre logique, par exemple l’ordre défini par la clé de l’index BTree propriétaire de la page.

Prise en charge des lignes de grande taille

Pour prendre en charge les grandes lignes qui ne tiennent pas sur une seule page, la partie de la ligne qui ne convient pas peut être stockée sur d’autres pages. La taille maximale des données et de la surcharge pouvant être contenues dans une seule ligne d’une page est de 8 060 octets.

La restriction de 8 060 octets ne s’applique pas aux données des colonnes utilisant les types de données LOB. Par défaut pour ces colonnes, les données sont stockées dans la ligne en cas d’espace suffisant. Sinon, la ligne contient un pointeur de 16 octets vers une arborescence distincte de pages texte/LOB stockant les données LOB dans une unité d’allocation LOB_DATA. L’option large value types out of rowde table contrôle ce comportement.

La restriction de 8 060 octets est assouplie pour les tables et les index qui contiennent des colonnes de longueur variable à l’aide des types de données varchar, nvarchar, varbinary, sql_variant ou CLR définis par l’utilisateur. Lorsque la taille totale des lignes de toutes les colonnes de longueur fixe et variable d’un segment de mémoire ou d’un index dépasse la limite de 8 060 octets, le moteur de base de données déplace dynamiquement une ou plusieurs colonnes de longueur variable vers des pages dans une unité d’allocation ROW_OVERFLOW_DATA , en commençant par la colonne la plus large.

Cette opération est réalisée chaque fois qu'une opération d'insertion ou de mise à jour augmente la taille totale de la ligne au-delà de la limite de 8 060 octets. Lorsqu’une colonne est déplacée vers une page dans une unité d’allocation ROW_OVERFLOW_DATA , un pointeur de 24 octets sur la page d’origine d’une unité d’allocation IN_ROW_DATA est conservé. Si une opération ultérieure réduit la taille de ligne, le moteur de base de données déplace dynamiquement les colonnes vers la page de données d’origine.

Par exemple, une table peut être créée avec deux colonnes : une varchar(7000) et une autre varchar(2000). Individuellement, aucune colonne ne dépasse 8 060 octets, mais combinées, elles le feraient si la largeur entière de chaque colonne est remplie. Si cela se produit, le moteur de base de données déplace dynamiquement la colonne de longueur variable varchar(7000) de la page d’origine vers les pages d’une unité d’allocation ROW_OVERFLOW_DATA .

Lorsqu’une table ou un index a des colonnes de type varchar, nvarchar, varbinary, sql_variant ou CLR définies par l’utilisateur qui peuvent dépasser 8 060 octets par ligne, tenez compte des éléments suivants :

• Le déplacement de lignes volumineuses vers une autre page se produit dynamiquement, car les lignes sont allongées en fonction des opérations de mise à jour. Dans une unité d'allocation IN_ROW_DATA, les opérations de mise à jour qui raccourcissent les lignes peuvent les ramener à la page d'origine.

  Ce déplacement de données entraîne des E/S de disque supplémentaires. Les opérations de traitement des requêtes telles que les tris ou les jointures sur des enregistrements volumineux qui contiennent des données de dépassement de ligne peuvent être plus lentes.

  Par conséquent, lorsque vous concevez une table avec plusieurs colonnes de type varchar, nvarchar, varbinary, sql_variant ou CLR définies par l’utilisateur, tenez compte du pourcentage de lignes susceptibles de circuler et de la fréquence à laquelle ces données de dépassement de capacité sont susceptibles d’être interrogées. Pour éviter des performances plus lentes, normalisez la table pour déplacer certaines de ces colonnes vers une autre table pour réduire ou éliminer la probabilité d’utiliser le stockage de dépassement de ligne.

• La longueur des colonnes individuelles doit toujours être comprise dans la limite de 8 000 octets pour les colonnes de type varchar, nvarchar, varbinary, sql_variant et CLR définies par l’utilisateur. Seule la combinaison de leurs longueurs peut dépasser la limite de 8 060 octets par ligne d'une table.

• La somme des longueurs d’autres colonnes de type de données, par exemple char, nchar et données int , doit toujours se trouver dans la limite de lignes de 8 060 octets. Toutefois, les colonnes utilisant les types de données LOB tels que varchar(max), nvarchar(max) et varbinary(max) sont exemptées de la limite de 8 060 octets par ligne.

• La clé d’index d’un index cluster ne peut pas contenir de colonnes varchar qui ont des données dans une unité d’allocation ROW_OVERFLOW_DATA . Si un index clusterisé est créé sur une colonne varchar et que toutes les données existantes se trouvent dans une unité d'allocation IN_ROW_DATA, mais qu'une instruction INSERT ou UPDATE suivante déplace les données hors ligne, l'instruction échoue. Pour plus d’informations, consultez le guide de conception et d’architecture d’index.

• Vous pouvez inclure des colonnes qui contiennent des données de dépassement de ligne en tant que colonnes clés ou non clés d'un index non-cluster.

• La limite de taille de ligne pour les tables qui utilisent des colonnes éparses est de 8 018 octets. Lors de la conversion entre colonnes éparses et nonparses, lorsque les données converties et les données existantes dépassent 8 018 octets, l’erreur 576 est retournée. Lorsque les colonnes sont converties entre des types clairsemés et non clairsemés, le moteur de base de données conserve une copie des données de ligne actuelles. Cela double l'espace de stockage temporairement requis pour la ligne.

• Pour obtenir des informations sur les tables ou les index pouvant contenir des données de dépassement de ligne, utilisez la fonction de gestion dynamique sys.dm_db_index_physical_stats. Un index ou une partition a des données de dépassement de ligne si la fonction retourne des lignes où se trouve alloc_unit_type_desc la ROW_OVERFLOW_DATA colonne et que la page_count colonne est supérieure à 0.

Étendues

Une étendue est un ensemble de huit pages contiguës sur le plan physique. La taille de chaque étendue est de 64 Kio.

Il existe deux types d’étendues :

• Les étendues uniformes sont détenues par un seul objet, par exemple une table unique ; les huit pages de l’étendue ne peuvent être utilisées que par l’objet propriétaire.
• Les extensions mixtes sont partagées par huit objets au plus. Chacune des huit pages de l'extension peut être la propriété d'un objet différent.

Jusqu’à et y compris SQL Server 2014 (12.x), le moteur SQL Server n’alloue pas d'extent uniformes aux tables avec de petites quantités de données. Un nouveau tas ou un nouvel index alloue des pages à partir d’étendues mixtes. Lorsque le tas ou l’index augmente au point d'utiliser huit pages, il passe ensuite à des extensions uniformes pour toutes les allocations suivantes. Si vous créez un index sur une table existante qui possède un nombre de lignes suffisant pour générer huit pages dans l'index, toutes les allocations à l'index se trouvent dans des extensions uniformes.

À compter de SQL Server 2016 (13.x), le moteur de base de données utilise des étendues uniformes pour les allocations dans une base de données utilisateur et dans tempdb, à l’exception des allocations appartenant aux huit premières pages d’une chaîne IAM. Les allocations dans les bases de données master, msdb, et model conservent toujours le comportement précédent.

Jusqu’à SQL Server 2014 (12.x), vous pouvez utiliser l’indicateur de trace (TF) 1118 pour modifier l’allocation par défaut pour toujours utiliser des étendues uniformes. Pour plus d’informations sur cet indicateur de trace, consultez l’indicateur de trace 1118.

À compter de SQL Server 2016 (13.x), TF 1118 n’a aucun effet. La fonctionnalité fournie par TF 1118 précédemment est automatiquement activée pour toutes les bases de données utilisateur et pour tempdb. Pour les bases de données utilisateur, ce comportement peut être contrôlé par l’option MIXED_PAGE_ALLOCATION de base de données. La valeur par défaut est OFF, ce qui signifie que les étendues uniformes sont utilisées. Pour plus d’informations, consultez Options SET ALTER DATABASE.

À partir de SQL Server 2012 (11.x), la fonction système sys.dm_db_database_page_allocations peut fournir des informations d’allocation de page pour une base de données, une table, un index et une partition.

À compter de SQL Server 2019 (15.x), la fonction système sys.dm_db_page_info retourne des informations sur une page d’une base de données. La fonction retourne une ligne qui contient des données d’en-tête de page, notamment l’ID d’objet, l’ID d’index et l’ID de partition. Dans de nombreux cas, cette fonction peut être utilisée comme alternative prise en charge pour la commande non prise en charge DBCC PAGE .

Pages système

Chaque fichier de données contient un petit nombre de pages système spéciales qui suivent les métadonnées décrivant les étendues et les pages. Par exemple, les pages système suivent les étendues d’un fichier de données allouées et la quantité d’espace libre des pages. Cette section décrit ces pages système.

Pages GAM et SGAM

Le moteur de base de données utilise deux types de mappages d’allocation pour enregistrer l’allocation d’étendue :

• Pages GAM (Global Allocation Map)

  Les pages GAM enregistrent les étendues qui ont été allouées. Chaque page GAM couvre un intervalle d’environ 64 000 étendues, ou environ 4 gigaoctets (Gio) de données, appelé intervalle GAM. La page GAM a 1 bits pour chaque étendue dans l’intervalle qu’elle couvre. Si la valeur du bit est 1, l'extension est libre. En revanche, si sa valeur est 0, l'extension est allouée.

• Pages SGAM (Shared Global Allocation Map)

  Les pages SGAM enregistrent les extensions actuellement utilisées comme extensions mixtes et possédant au moins une page inutilisée. Chaque page SGAM couvre également un intervalle d’environ 64 000 étendues, ou environ 4 Gio de données. La page SGAM compte un bit pour chaque extension dans l'intervalle couvert. Si la valeur du bit est 1, l'extension est utilisée comme extension mixte et possède une page libre. Si le bit est 0, l’étendue n’est pas utilisée comme étendue mixte ou est une étendue mixte où toutes les pages sont utilisées.

Pour résumer, chaque étendue a les modèles de bits suivants définis dans les pages GAM et SGAM, en fonction de son utilisation actuelle.

Pour gérer les étendues, le moteur de base de données utilise les algorithmes conceptuels suivants :

• Pour allouer une étendue uniforme, le moteur de base de données recherche sur la page GAM un bit 1 et le définit sur 0.
• Pour trouver une étendue mixte avec des pages libres, le moteur de base de données recherche un bit 1 sur la page SGAM.
• Pour allouer une étendue mixte, le moteur de base de données recherche un bit 1 sur la page GAM, le définit sur 0, puis il définit également le bit correspondant sur la page SGAM 1.
• Pour libérer une extension, le moteur de base de données s’assure que le bit de la page GAM est positionné sur 1, et que le bit de la page SGAM est positionné sur 0.

Allocation de remplissage proportionnelle

Le moteur de base de données alloue des étendues à partir de celles qui sont disponibles dans le groupe de fichiers à l’aide d’un algorithme d’allocation proportionnel. Par exemple, dans un groupe de fichiers avec deux fichiers, si un fichier a double l’espace libre de l’autre, deux pages sont allouées à partir de ce fichier pour chaque page allouée à partir de l’autre fichier. Cela signifie que si les allocations continuent, tous les fichiers d’un groupe de fichiers atteignent un pourcentage similaire d’espace utilisé.

Pour plus d’informations, consultez La stratégie de remplissage de fichier et de groupe de fichiers.

Pages PFS

Les pages PFS (Page Free Space) enregistrent l’état d’allocation de chaque page et la quantité d’espace libre sur chaque page. Une page PFS a 1 octet pour chaque page qu’elle suit. L’octet enregistre si la page est allouée et, le cas échéant, qu’elle soit vide, 1 à 50 % complète, 51 à 80 % complète, 81 à 95 % complète ou 96 à 100 %.

Une fois qu’une extension a été allouée à un objet, le moteur de base de données utilise des pages PFS pour suivre les pages dans l’étendue qui ont des données ou sont gratuites. Ces informations sont utilisées lorsque le moteur de base de données alloue une nouvelle page. La quantité d’espace libre dans une page est conservée uniquement pour les pages de tas et de texte/LOB. Ces informations sont utilisées lorsque le moteur de base de données doit trouver une page avec suffisamment d’espace libre disponible pour contenir une ligne nouvellement insérée.

Les index BTree ne nécessitent pas de suivi de l’espace libre de page, car le point auquel insérer une nouvelle ligne est toujours déterminé par les valeurs de clé d’index. Si une page d’un index BTree n’a pas suffisamment d’espace libre, une nouvelle page est ajoutée et environ la moitié des données de page d’origine sont déplacées vers la nouvelle page.

Intervalles GAM et PFS

Une nouvelle page PFS, GAM ou SGAM est ajoutée dans le fichier de données pour chaque plage additionnelle dont elle assure le suivi.

Il existe une nouvelle page PFS 8 088 pages après la première page PFS et des pages PFS supplémentaires dans les 8 088 intervalles de pages suivants. Dans un fichier de données, l’ID de page 1 est une page PFS, l’ID de page 8088 est une page PFS, l’ID de page 16176 est une page PFS, et ainsi de suite.

De même, il existe une paire de pages GAM et SGAM à partir des pages 2 et 3 respectivement, qui se répètent pour chaque intervalle GAM d’environ 64 000 étendues ou 4 Gio.

Le diagramme suivant montre la première occurrence des pages PFS, GAM et SGAM au début d’un fichier de données suivant la page d’en-tête du fichier. À mesure que le fichier augmente, les nouvelles pages PFS, GAM et SGAM apparaissent à leurs intervalles respectifs.

Pages Gestion des Identités et des Accès

Une page IAM (Index Allocation Map) mappe les étendues utilisées par une unité d’allocation dans un intervalle GAM. Une unité d’allocation est associée à une partition d’un tas ou d’un index, et peut être de l'un des trois types suivants :

• IN_ROW_DATA

  Contient des pages de données hors LOB ou des parties de données LOB qui peuvent tenir dans une ligne.

• LOB_DATA

  Contient des pages de données LOB, utilisées par des types de données tels que varchar(max), nvarchar(max), varbinary(max), xml et json.

• ROW_OVERFLOW_DATA

  Contient les pages de données LOB utilisées par les types de données de longueur variable tels que varchar, nvarchar, varbinary ou sql_variant lorsque les données dépassent la limite de taille de ligne de 8 060 octets.

Chaque partition d’un tas ou d’un index contient toujours au moins une unité d’allocation IN_ROW_DATA. Il peut également contenir des unités d'allocation LOB_DATA et ROW_OVERFLOW_DATA, en fonction des types de données et des tailles de ligne présents dans la partition.

Comme pour une page GAM ou SGAM, une page IAM couvre un intervalle de 4 Gio dans un fichier. Si l’unité d’allocation contient des étendues de plusieurs fichiers, ou plusieurs intervalles de 4 Gio d’un fichier, plusieurs pages IAM sont liées dans une chaîne IAM. Par conséquent, chaque unité d'allocation a au moins une page IAM pour chaque fichier où elle possède des extensions. Il peut également y avoir plusieurs pages IAM dans un fichier, si la plage des étendues allouées à l’unité d’allocation dans le fichier dépasse la plage qu’une seule page IAM peut enregistrer. Une page IAM d’un fichier peut suivre les étendues de ce fichier et dans tout autre fichier de la même base de données.

Contrairement aux pages PFS, GAM et SGAM qui se répètent à intervalles fixes, les pages IAM sont allouées comme requis pour chaque unité d’allocation. La vue système sys.system_internals_allocation_units pointe vers la première page IAM d’une unité d’allocation. Toutes les pages IAM de cette unité d'allocation sont liées entre elles et forment une chaîne IAM.

Une page IAM a un en-tête qui indique l’étendue de départ de la plage d’étendues mappées par cette page. Une page IAM a également une bitmap dans laquelle chaque bit représente une étendue. Le premier bit représente la première étendue de la plage, le second la deuxième étendue, et ainsi de suite. Si un bit est 0, l’étendue qu’elle représente n’est pas allouée à l’unité d’allocation propriétaire de la page IAM. Si un bit est égal à 1, l'étendue qu'il représente est allouée à l'unité d'allocation possédant la page IAM.

Lorsque le moteur de base de données doit insérer une nouvelle ligne et qu’aucun espace n’est disponible dans la page active, il utilise les pages IAM et PFS pour rechercher une page pour allouer la ligne. Pour les pages tas ou texte/LOB, elle utilise également les pages IAM et PFS pour trouver une page ayant suffisamment d’espace pour contenir la ligne. Le moteur de base de données utilise des pages IAM pour rechercher les étendues allouées à l’unité d’allocation. Pour chaque étendue, il recherche les pages PFS pour voir s’il existe une page qui peut être utilisée.

Pour les index BTree, le point d’insertion d’une nouvelle ligne est déterminé par la clé d’index, mais quand une nouvelle page est nécessaire, le processus décrit précédemment se produit.

Le moteur de base de données alloue une nouvelle étendue à une unité d’allocation lorsqu’il ne peut pas trouver rapidement une page dans une étendue existante avec suffisamment d’espace pour contenir la ligne insérée.

Pages DCM et BCM

Le moteur de base de données utilise deux types de pages système pour suivre les étendues modifiées depuis la dernière sauvegarde complète et les étendues modifiées par les opérations de copie en bloc.

Les pages DCM (Differential Changed Map) accélèrent les sauvegardes différentielles. La carte modifiée en bloc (BCM) accélère les opérations de copie en bloc lorsqu’une base de données utilise le modèle de récupération journalisé en bloc. À l'instar des pages GAM (Global Allocation Map) et SGAM (Shared Global Allocation Map), ces structures sont des bitmaps où chaque bit représente une étendue unique.

• Pages DCM

  Ces pages suivent les étendues qui ont changé depuis la dernière sauvegarde complète de la base de données. Si le bit pour une extension est 1, l’étendue a été modifiée. Si le bit est à 0, l'étendue n'a pas été modifiée.

  Les sauvegardes différentielles lisent les pages DCM pour déterminer quelles étendues ont été modifiées. Cela réduit le nombre de pages qu’une sauvegarde différentielle doit lire et écrire. La durée pendant laquelle une sauvegarde différentielle prend est proportionnelle au nombre d’étendues modifiées depuis la dernière sauvegarde complète de la base de données, et non à la taille totale de la base de données.

• Pages BCM

  Ces pages suivent les étendues qui ont été modifiées par les opérations journalisées en bloc depuis la dernière sauvegarde du journal des transactions. Si le bit pour une extension est 1, l’étendue a été modifiée. Si le bit est à 0, l'étendue n'a pas été modifiée.

  Bien que les pages BCM apparaissent dans toutes les bases de données, elles ne sont pertinentes que lorsque la base de données utilise le modèle de récupération journalisé en bloc. Dans ce modèle de récupération, lorsqu’une sauvegarde du journal des transactions est effectuée, le processus de sauvegarde analyse les pages BCM pour les étendues qui ont été modifiées. Il inclut ces étendues dans la sauvegarde de journal pour activer la récupération si la base de données est restaurée à partir d’une sauvegarde de base de données et d’une séquence de sauvegardes de journal des transactions.

  Les pages BCM ne sont pas pertinentes dans une base de données qui utilise le modèle de récupération simple, car aucune opération journalisée en bloc n’est entièrement journalisée. Ils ne sont pas pertinents dans une base de données qui utilise le modèle de récupération complet, car ce modèle de récupération traite les opérations journalisées en bloc comme des opérations entièrement journalisées.

Les pages DCM et BCM sont stockées aux mêmes intervalles GAM, d’environ 4 Gio, que les pages GAM et SGAM. Les pages DCM et BCM suivent les pages GAM et SGAM dans un fichier physique comme suit :

Contenu connexe

• Notions de base des E/S de SQL Server
• sys.allocation_units (Transact-SQL)
• Segments (tables sans index cluster)
• sys.dm_db_page_info (Transact-SQL)
• Lire des pages de données dans le moteur de base de données
• Écrire des pages dans le moteur de base de données